PHP - SQL while in a foreach statment

Orddie

2[H]4U
Joined
Dec 20, 2010
Messages
3,368
Back in my day.... we used mysql_query. now its some mysqli shit.

as far as I can tell, i can only use a single DB connection for a single query unless i free the query result from memory. if a free the result inside a foreach, the whole thing dies. the way how i USED to be able to do this would be as follows

$db = 'localhost';
$usr = 'Kyle';
$pwd = 'kim';
if(!$resrouce = mysql_connect($db, $usr, $pwd, 'forum') die "error: ".mysql_error();

if(!$q1 = mysql_query($resrouce, "select `admin` from `new` where admin == ''")) die "Error: ".mysql_error();
$r1=mysql_result($q1);

foreach($r1 as $v)
{
if($v['admin'] == null)
{
if(!$q2 = mysql_query($resrouce, "select `user` from `other` where admin == 'True'")) die "Error: ".mysql_error();
$r2=mysql_result($q2);
}
}

where i run into an error in during the $q2 query.
 
Last edited:
Though, looking at the queries and the limited snippet, you should be able to combine them in one call.

SQL:
SELECT admin, user
FROM [new]
WHERE admin == '' or admin == 'true'

Then your loop would go about checking one condition or the other.
 
Though, looking at the queries and the limited snippet, you should be able to combine them in one call.

SQL:
SELECT admin, user
FROM [new]
WHERE admin == '' or admin == 'true'

Then your loop would go about checking one condition or the other.
i corrected my first posting. i need to query another table based on key info from the existing row im working on.
 
Code:
if(!$q1 = mysqli_query($db, "Select `DeviceName`,`extension`,`User` from sep"))
    {
    mysqli_close($db);
    die("Query error :".mysqli_error($db));
    }
foreach($q1 as $v)
    {
    if($v['User'] == null)
    {
        
        if(!$q2 = mysqli_real_query($db, "SELECT `ID` from users where `TELEPHONE` = '".$v['extension']."'"))
        {
            mysqli_close($db);
            die("Query error: ".mysqli_error($db));
        }
        else
        {
            if($debug){Echo "SELECT `ID` from users where `TELEPHONE` = '".$v['extension']."' and the result was ".$q2;}
        
        $v['User'] = $q2;
        
        $warn = True;
        }
        echo '<tr class="myDiv">';
    }
    else echo "<tr>";
    echo"   
      
        <td>".$v['DeviceName']."<t/d>
        <td>".$v['extension']."</td>
        <td>".$v['User']."</td>
      </tr>";
    }
 
Hmm, my php is a little rusty to try that off the cuff on a local dev setup, but my sql-spidy-sense is tingling.

I feel you could accomplish that with a left join and use of 'isnull'.

Ex:
SQL:
create table #sep(devicename varchar(10), extension varchar(5), myuser varchar(10))
create table #users(id int, telephone varchar(5))


insert into #sep(devicename, extension, myuser) values ('d1', '1234', 'u1')
insert into #sep(devicename, extension, myuser) values ('d2', '2345', null)
insert into #sep(devicename, extension, myuser) values ('d3', '3456', 'u2')


insert into #users(id, telephone) values(1, '2345')


select *
from #sep


select a.devicename
,a.extension
,ISNULL(myuser, b.id) as myuser
from #sep a
left join #users b on a.extension = b.telephone




drop table #users
drop table #sep

SQL:
devicename extension myuser
---------- --------- ----------
d1         1234      u1
d2         2345      NULL
d3         3456      u2




devicename extension myuser
---------- --------- ----------
d1         1234      u1
d2         2345      1
d3         3456      u2
 
Putting aside the nature of your SQL for a moment, this is my old mysql pattern ( which should be replaced with prepares and such... )
Code:
$c = mysqli_connect( 'localhost', 'user', 'password', 'db' );
if( !$c )
throw new Exception( 'mysql connect failed' );

$q1 = sprintf( 'SELECT * FROM `table1` WHERE id=%d', (int)$id );
$r1 = mysqli_query( $c, $q1 );
if( !$r1 )
throw new Exception( 'Bad query: %s', $q1 );

while( $d1 = mysqli_fetch_assoc( $r1 ) )
{
$q2 = sprintf( 'SELECT * FROM `table2` WHERE `string1`="%s"', mysqli_real_escape_string( $c, $d1['string1'] ) );
$r2 = mysqli_query( $c, $q2 );
if( !$r2 )
throw new Exception( 'bad query: %s', $q2 );

while( $d2 = mysqli_fetch_assoc( $r2 ) ) {
...magic happens here...
}
}

mysqli_close( $c );

I should reiterate; this is not the ideal pattern as it introduces sql injection vulnerabilities ( if you aren't careful ). Prepares and executes should be used instead, but this is simpler and what I recall off the top of my head.

As far as the sql, modi123 nails it. The only thing I might add is to ensure your indexes are setup correctly so you aren't doing table scans when you try to join the tables. In fact if possible I'd redesign your tables so you can reference row IDs instead of strings for your join parameters ( so in this case you might use a `users_id` field in `sep` which references the `id` in the users table ).

( note; the above was typed from memory, so it's gonna have bugs )
 
if(!$q2 = mysql_query($resrouce, "select `user` from `other` where admin == 'True'")) die "Error: ".mysql_error();
$r2=mysql_result($q2);
doood. you need to set $r2 ONLY if the condition doesnt meet. so probably in an else. the condition is saying, if $q2 is not set, do something. But then its using the $q2 in the mysql_result method EVERY TIME, that wont work if $q2 is not defined.

*ALSO, as a code organization tip. create your vars above the condition and then just do if(!var)some_method
 
Last edited:
I second modi123 and grasshopper... join it in SQL (cleaner than running multiple queries) and join on a unique user id, if you're going to have a user identifier in your sep table anyway.
 
Back
Top