在PHP中按顺序查询多个MSSQL表中的一个值

问题描述：

I have 2 tables i need to query for a single result, but if it's found in $table0, I need to stop searching and use the values in that row. Something like this:

$p1_sql = "SELECT TOP 1 * FROM '$table0' WHERE phone1 = '$cidnumber'";
$p2_sql = "SELECT TOP 1 * FROM '$table0' WHERE phone2 = '$cidnumber'";
$c1_sql = "SELECT TOP 1 * FROM '$table1' WHERE contactphone = '$cidnumber'";
$c2_sql = "SELECT TOP 1 * FROM '$table1' WHERE contactphone2 = '$cidnumber'";
$c3_sql = "SELECT TOP 1 * FROM '$table1' WHERE contactphone3 = '$cidnumber'";
$c4_sql = "SELECT TOP 1 * FROM '$table1' WHERE contactphone4 = '$cidnumber'";

$p1_res = mssql_query($p1_sql);
$p1_row = mssql_num_rows($p1_res);

$p2_res = mssql_query($p2_sql);
$p2_row = mssql_num_rows($p2_row);

$c1_res = mssql_query($c1_sql);
$c1_row = mssql_num_rows($c1_res);

$c2_res = mssql_query($c2_sql);
$c2_row = mssql_num_rows($c2_res);

$c3_res = mssql_query($c3_sql);
$c3_row = mssql_num_rows($c3_res);

$c4_res = mssql_query($c4_sql);
$c4_row = mssql_num_rows($c4_res);

if ($p1_row = 1){
    $p1_res = $newres;
    goto okres;
} elseif ($p2_row = 1) {
    $p2_res = $newres;
    goto okres;
} elseif ($c1_row = 1) {
    $c1_res = $newres;
    goto okres;
} elseif ($c2_row = 1) {
    $c2_res = $newres;
    goto okres;
} elseif ($c3_row = 1) {
    $c3_res = $newres;
    goto okres;
} elseif ($c4_row = 1) {
    $c4_res = $newres;
    goto okres;
} else {
    $newres = "na";
    goto nares;
}

okres:
$cid_sel = mssql_query("SELECT TOP 1 * FROM '$table0' WHERE phone1 = '$cidnumber'");

This, however, is ugly and doesn't work. I was trying to use 'for each...' or 'while (something)', but couldn't wrap my head around how it would work. I don't even know if it would. What's the best way to go about this? It's my first forray into something like this and any help is appreciated.

我有2个表我需要查询单个结果，但是如果它在$ table0中找到，我需要停止搜索并使用该行中的值。这样的事情： p>

  $ p1_sql =“SELECT TOP 1 * FROM'$ table0'WHERERE1 ='$ cidnumber'”; 
 $ p2_sql =“SELECT TOP 1 *  FROM'$ table0'WHERE phone2 ='$ cidnumber'“; 
 $ c1_sql =”SELECT TOP 1 * FROM'$ table1'WHERERE contactphone ='$ cidnumber'“; 
 $ c2_sql =”SELECT TOP 1 * FROM'  $ table1'WHERE contactphone2 ='$ cidnumber'“; 
 $ c3_sql =”SELECT TOP 1 * FROM'$ table1'WHERERE contactphone3 ='$ cidnumber'“; 
 $ c4_sql =”SELECT TOP 1 * FROM'$ table1  'WHERE contactphone4 ='$ cidnumber'“; 
 
 $ p1_res = mssql_query（$ p1_sql）; 
 $ p1_row = mssql_num_rows（$ p1_res）; 
 
 $ p2_res = mssql_query（$ p2_sql）; 
 $  p2_row = mssql_num_rows（$ p2_row）; 
 
 $ c1_res = mssql_query（$ c1_sql）; 
 $ c1_row = mssql_num_rows（$ c1_res）; 
 
 $ c2_res = mssql_query（$ c2_sql）; 
 $ c2_row =  mssql_num_rows（$ c2_res）; 
 
 $ c3_res = mssql_query（$ c3_sql）; 
 $ c3_row = mssql_num_rows（$ c3_res）; 
 
 $ c4_res = mssql_query（$ c4_sql）; 
 $ c4_row = mssql_num_rows（  $ c4_res）; 
 
if（$ p1_row = 1）{
 $ p1_res = $ newres; 
 goto okres; 
} elseif（$ p2_  row = 1）{
 $ p2_res = $ newres; 
 goto okres; 
} elseif（$ c1_row = 1）{
 $ c1_res = $ newres; 
 goto okres; 
} elseif（$ c2_row =  1）{
 $ c2_res = $ newres; 
 goto okres; 
} elseif（$ c3_row = 1）{
 $ c3_res = $ newres; 
 goto okres; 
} elseif（$ c4_row = 1）  {
 $ c4_res = $ newres; 
 goto okres; 
} else {
 $ newres =“na”; 
 goto nares; 
} 
 
okres：
 $ cid_sel = mssql_query（“SELECT  TOP 1 * FROM'$ table0'WHERE phone1 ='$ cidnumber'“）; 
  code>  pre> 
 
 然而，这很丑陋并且不起作用。 我试图使用'for each ...'或'while（something）'，但无法理解它是如何工作的。 我甚至不知道是否会这样。 最好的方法是什么？ 这是我第一次参加这样的活动，感谢任何帮助。 p> 
  div>

答

I would add the different queries in to an array and loop (using foreach) that array to subsequent query the database. As soon as you find what you're looking for you break out of the loop using the break; command.

Using joins in pure sql could also be a solution, but I'm not entirely sure what you wish to achieve here.

Good luck!

答

It's unnecessary to do so many separate queries. Are you familiar with basic SQL, as far as JOINs and UNIONs? If not you should read on them-- what you want here seems achievable with a single query.

在PHP中按顺序查询多个MSSQL表中的一个值

相关推荐