Run a mysql query in a mysql query

Question

Hello I'm trying to query my database table "shout_out", during the while loop I have an if statement to check to if the "convers_id" column has been change from no_reply. if it has I want another query to run and get all the rows from shout_out_reply table that have the same "convers_id" column. I'm trying to get a similar lay out as a facebook "wall". Some one make a comment and others can reply to that comment with the reply being under the original comment.

I keep getting that the mysqli_query($dbc, $query_shout_out_reply) has failed. but there are reply's with the same "convers_id"

$query_shout_out = "SELECT * FROM shout_out WHERE sent_to = '$username' ";
$shout_out_query = mysqli_query($dbc, $query_shout_out);

if (mysqli_num_rows($shout_out_query) != 0) {
    while ($row = mysqli_fetch_array($shout_out_query)) {
        echo '<td class="shout_out_display" width="550">';
        echo $row['message'] . ' <br/><br/> From ' . $row['sent_by'] . ' at ' . $row['date_sent'];
        echo '</td></tr>';
        echo $row['convers_id'];
        if ($row['convers_id'] != "no_reply") {
            $query_shout_out_reply = "SELECT * FROM shout_out_reply WHERE convers_id = " . $row['convers_id'];
            $shout_out_reply_query = mysqli_query($dbc, $query_shout_out_reply);

            while ($reply_row = mysqli_fetch_array($shout_out_reply_query)) {
                echo ' <tr width="550"><td width="125" > &nbsp </td>';
                echo '<td width="425"  class="shout_out_reply_display">';
                echo $reply_row['message'] . '<br/><br/>Reply From ' . $reply_row['sent_by'] . ' at ' . $reply_row['date_sent'];
                echo '</td></tr>';
            }
        }

    }
}

I don't have time to answer, but typically running queries in loops is a bad idea. — Levi Morrison
– Levi Morrison, Commented Dec 15, 2011 at 21:44
do you really need that loop? maybe you can join the tables first then run only one loop - no nested query needed! — reox
– reox, Commented Dec 15, 2011 at 21:47
That would be good but I don't fully understand join, and if I could get this to work I'd rather. — Cjueden
– Cjueden, Commented Dec 15, 2011 at 21:48

Prpl_Ppl_Etr · Accepted Answer · 2011-12-15 21:49:30Z

1

SELECT * FROM shout_out AS so LEFT JOIN shout_out_reply AS sor ON sor.convers_id = so.convers_id WHERE so.sent_to = '$username' AND so.convers_id != 'no_reply'

The key here is to use an SQL JOIN statement. It'll give you the results from both tables in one statement. ...then you don't have to go through the loop. making all those sql requests.

answered Dec 15, 2011 at 21:49

Prpl_Ppl_Etr

1411 silver badge7 bronze badges

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

4 Comments

Cjueden Over a year ago

Prpl_Ppl_Etr Over a year ago

...by using the aliases defined in the statement I supplied. But, are they not the same values? If not, you can do this: SELECT so.convers_id AS so_con, sor.convers_id as sor_con FROM... ...or am I misunderstanding you?

Cjueden Over a year ago

I think this is on the write track but I need to get multiple "comments" like OG post then replies then start over with the next OG post and however many replies

Prpl_Ppl_Etr Over a year ago

So, you are wanting to group the results? ...so that you have the original post, then all of the replies to that post, then the next original post, then all of the replies to that one, etc?

Alexander Gharibashvili · Accepted Answer · 2011-12-15 21:52:12Z

0

you mast design tables such structure, that will easy you create joins and complex querys. first of all, you need to understood how works joins and when they must use. check google for this.

answered Dec 15, 2011 at 21:52

Alexander Gharibashvili

98311 silver badges16 bronze badges

Collectives™ on Stack Overflow

Run a mysql query in a mysql query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related