php mysql query with a loop in it

Question

I'm trying to retrieve the results of users in my database, and then if my checkbox is selected to retrieve only users who have photos to show those results, but I can't seem to figure out how to loop through those results or if I am even doing it in the right context. `

$photos = $_POST['pcbox'];
$basicsql = "SELECT * FROM users";
$basicsql .= "WHERE status > '1'";
if($photos=='1'){
    $sql = mysql_query("SELECT user_id FROM pictures GROUP BY user_id");
    while($row2 = mysql_fetch_assoc($sql))
    $options[] = " AND (users.user_id = '$row2[user_id]')";
    foreach($options as $key => $str){
        $basicsql .= $str;
    }
}
$basicsql .= " ORDER BY users.last_login DESC";
$pagesql = mysql_query($basicsql);

All works until the checkbox is selected

Answer 1

You can't use the same db handle for nested loops. You need to explicitly set the db handle for each one.

However, you can just do it in one sql query.

Observe:

Select * from users u inner join pictures p on u.id=p.user_id where status > 1

An INNER JOIN will give you only users that have rows in the pictures table.

This will give you users that have pictures only.

Edit

This will give the number of pitcures and only 1 user row.

Select name,email,address,status,count(*) as num_pics from users u inner join pictures p on u.id=p.user_id group by name,email,address,status where status > 1

Answer 2

Not exactly sure if this is what you're looking for. Untested as well.

$photos = $_POST['pcbox'];
$basicsql = "SELECT * FROM users WHERE status > 1";
$photoSql = "SELECT * FROM users, pictures WHERE status > 1 AND users.user_id = pictures.user_id GROUP BY users.user_id ORDER BY users.last_login DESC"

if($photos=='1')
{
    $result = mysql_query($photosSql);
}
else
{
    $result = mysql_query($photoSql);
}

Answer 3

$photos = isset($_POST['pcbox']) ? $_POST['pcbox'] : null;

$sql = 'SELECT U.* FROM users AS U LEFT JOIN pictures AS P ON P.user_id = U.id ' . ($photos == '1' ? 'WHERE P.id IS NOT NULL') . ' GROUP BY U.id ORDER BY U.last_login DESC';

$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
  // Do something with the user info, such as outputting it to a table
}

Answer 4

Furthermore, close while before start looping in options.