Query for one value in multiple MSSQL tables sequentially in PHP

Question

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.

Ben G · Accepted Answer · 2011-05-24 23:34:17Z

2

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.

answered May 24, 2011 at 23:34

Ben G

26.9k35 gold badges112 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tsz Over a year ago

I thought about doing UNION ALL, but wouldn't it just be one large query then? What am I gaining? I guess what I really want to do is run each one at a time and check to see if a result occurred, and if so stop. Put an IF between each? Can I run an IF statement inside a query?

tsz Over a year ago

Or would SELECT CASE WHEN phone1 = $cidnumber THEN ??? ELSE CASE WHEN phone2 = $cidnumber THEN ??? ELSE ... something like that work?

Eric Petroelje Over a year ago

@lorsungcu - You can't really put an IF statement in a query (well, you can, but not to do what you want to do here). If a UNION query doesn't work for you, perhaps it would be best to write a stored procedure where you actually could have the type of IF ... THEN logic that you want.

Ben G Over a year ago

What you're gaining from one query is simpler/better code, including a single result to deal with instead of many. Many queries also has the issue that one of them may fail along the way..

Tommie · Accepted Answer · 2011-05-24 23:38:23Z

0

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!

answered May 24, 2011 at 23:38

Tommie

1,9652 gold badges14 silver badges23 bronze badges

1 Comment

tsz Over a year ago

Sort of. I think it's got me in the right direction, anyway. Thanks!

Collectives™ on Stack Overflow

Query for one value in multiple MSSQL tables sequentially in PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related