PHP & MySQL query value question.

Question

How can I use the first query's id value $row['id'] again after I run a second query inside the while loop statement? To show you what I mean here is a sample code below of what I'm trying to do.

I hope I explained it right.

Here is the code.

    $mysqli = mysqli_connect("localhost", "root", "", "sitename");
    $dbc = mysqli_query($mysqli,"SELECT users.*
                                 FROM users
                                 WHERE user_id = 4");

    if (!$dbc) {
        // There was an error...do something about it here...
        print mysqli_error($mysqli);
    }

    while($row = mysqli_fetch_assoc($dbc)) {

     echo '<div>User: ' . $row['id'] . '</div>';
     echo '<div>Link To User' . $row['id'] . '</div>';


            $mysqli = mysqli_connect("localhost", "root", "", "sitename");
            $dbc2 = mysqli_query($mysqli,"SELECT COUNT(cid) as num
                                          FROM comments
                                          WHERE comments_id = $row[id]");

            if (!$dbc2) {
                // There was an error...do something about it here...
                print mysqli_error($mysqli);
            }  else {
                while($row = mysqli_fetch_array($dbc2)){ 
                    $num = $row['num'];
                }
            }

     echo '<div>User ' . $row['id'] . ' Comments# ' . $num . '</div>';

    }

Answer 1

Uh.... why don't you just make your second "row" variable something else? $row2? I'm a bit confused by your question...

Answer 2

I think you are asking the wrong question here. Yes you could use $row['id'] in the nested query. You can concatenate it to the query string using the '.' operator.

$dbc2 = mysqli_query($mysqli,"SELECT COUNT(cid) as num
                                      FROM comments
                                      WHERE comments_id = ".$row['id']);

However that is horible. You should also use only one connection and store the variables in a central place.

You should change you query to something like this (not tested):

SELECT 
  users.*, 
  (SELECT count(*) 
  FROM comments 
  WHERE comments.user_id = users.user_id) AS 'comment_count'
FROM users
LEFT JOIN comments ON 
  comments.user_id = user.user_id
WHERE users.user_id = 4;

By adding a select statement as the count field you are getting everything you want in a single query. That should avoid all those ugly loops. If you are doing these queries inside loops it will be very slow and there is usually a way to do the same thing in one more complex query.

Answer 3

Personally, I avoid using generic variable names like $row and $result for this very reason - you are going to come unstuck when you are doing complicated loops like this, and it is generally good practice to have variables with descriptive names like $user or $comment.

Also - @Keyo's point about using a subquery will help significantly reduce the number of queries you have to make to the database.

Answer 4

Every time you see nested query - you know you'rе doing something wrong. No exceptions.

Your query must be like this (I am not sure about column names but hope you have got an idea):

$sql = "SELECT users.*, COUNT(cid) as num 
    FROM users LEFT JOIN comments ON comments_id = id 
    WHERE user_id = 4
    GROUP BY cid";
$dbc = mysqli_query($sql);
if (!$dbc) trigger_error(mysqli_error($mysqli).$sql);

(Note that I have assigned a query to a variable first. That's great for the debugging purposes. Always do it this way)

Though I don't understand the meaning of the first loop at all. Doesn't your first query return only one row? Why to loop then? just fetch a row and then use in the output.

And your main problem, as Jan noted in comments, is you reopen a database connection. While it must be opened only once. You don't have to connect to the database every time you run a query. Do it only once.
What is the book you're learning from?

Answer 5

I don't know what's the structure of your tables, so I will assume that id is actually user_id since I believe you are trying to associate the user with its comments. Maybe you made a mistake rewriting the code to post it here? Anyway, my last point will be based on these assumptions.

1. There is no need to reconnect to the same database inside the loop.

2. Your second loop shouldn't overwrite the $row array. Use $row2 instead to prevent headaches.

3. You can do all the work in a single query as stated above with LEFT JOIN or subqueries.

4. Your first query returns a single record, so, there is no need to loop through it.

5. Here is your fully rewritten code, not tested and assuming that you want to display the user data based on my assumptions in the first paragraph.

   $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT users.*, COUNT(cid) AS num FROM users LEFT JOIN comments ON comments_id = user_id WHERE user_id = 4 GROUP BY users.user_id");

   if (!$dbc) { // There was an error...do something about it here... print mysqli_error($mysqli); }

   if($row = mysqli_fetch_assoc($dbc)){ echo 'User: ' . $row['user_id'] . ''; echo 'Link To User' . $row['user_id'] . ''; echo 'User ' . $row['user_id'] . ' Comments# ' . $num . ''; }

Next time please provide your table structures so we can give a more accurate solution to your problem.

Quick Reference: Using Quotes

I will explain something here since I saw some of you make the same mistake with quotes a couple of times in your responses to this post.

This is what you tell PHP when you use quotes:

Single Quotes: Do not parse here.
Double Quotes: Parse the contents inside the quotes, please.
No Quotes: This is a constant or number, please parse if it's a constant only.

When you're working with arrays, the element inside the square brackets should be single quoted if there is nothing to be parsed there. If there is a number or a constant, you can drop the quotes. For instance:

// If your string is double quoted, you use regular brackets to encapsulate the array
$sql = "SELECT * FROM tableA WHERE id = {$myarray['id']}";

Or, there is another similar approach:

$sql = "SELECT * FROM tableA WHERE id = ".$myarray['id'];

Which can also be written like:

$sql = 'SELECT * FROM tableA WHERE id = '.$myarray['id'];

Another couple of examples:

// This is correct
echo $myarray[5];

// If you are using constants, this is correct too
define('MYCONSTANT', 5);  
echo $myarray[MYCONSTANT];

// This shouldn't be used, because 'name' isn't a constant...
echo $myarray[name];

// Use this instead...
echo $myarray['name'];

// Or, like I explained above, escape the array
echo "My name is {$myarray['name']}";

Hope it helps.