mysql in while loop for a different mysql

MadLittleMods · March 10, 2011

So i have this php as shown below. It should make a list of comments with comment replies below their comment respectively.

The problem is that it only goes through and shows 1 comment and all the comment replies for that one comment. It should be showing all comments i have in the db for that article.

If i remove the second while then it shows all the comments correctly but no comment replies then...

How do i get this script to loop through the db for every comment but also loop through every comment reply for that $row[id]?

If anyone has a better / more efficient way of what I am trying to do, please explain or show example (i am open to anything)...

// what article are we showing?
$article_to_show_id = $_GET['article_id'];


$active_is_set_text = "1"; // Active Column text that makes it okay to show

// Finding the article
$search_for_article = mysql_query("SELECT * FROM articles WHERE id = '$article_to_show_id' AND active = '$active_is_set_text'");

while($row = mysql_fetch_array($search_for_article))
{
	// format the last updated date right
	$update_date_edit = $row[update_date];

	$update_date_edit = date('F j, Y \a\t h:ia', $update_date_edit);

	$row[update_date] = $update_date_edit;



	// format the submit updat date right
	$submit_date_edit = $row[submit_date];

	$submit_date_edit = date('F j, Y \a\t h:ia', $submit_date_edit);

	$row[submit_date] = $submit_date_edit;


	echo '
		<div>
			', $row[title] ,'
		</div>


		<div>
			by: ', $row[author] ,' on ', $row[submit_date] ,'
		</div>



		<div>
			', $row[content] ,'
		</div>


		<div>
			Last Updated: ', $row[update_date] ,'
		</div>

		<form action="article_reply.php" method="post">
			<input type="hidden" name="article_id" value="', $row[id] ,'" />
			<button name="article_reply" type="submit" value="submit">Reply</button>
		</form>

	';

}

$comment_count = 0;
$comment_reply_count = 0;

// Finding all of the comments
$search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '0'");

while($row_comment = mysql_fetch_array($search_for_article))
{
	// format the submit updat date right
	$comment_date_edit = $row_comment[comment_date];

	$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

	$row_comment[comment_date] = $comment_date_edit;

	echo '
		<br>
		<br>
		COMMENT:<br>

		<div>
			By: ', $row_comment[username] ,' on ', $row_comment[comment_date] ,'
		</div>

		<div>
			', $row_comment[comment] ,'
		</div>
	';
	$comment_count++;

	// Finding all of the comment replies if any
	$search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '$row_comment[id]'");

	while($row_two = mysql_fetch_array($search_for_article))
	{
		// format the submit updat date right
		$comment_date_edit = $row_two[comment_date];

		$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

		$row_two[comment_date] = $comment_date_edit;

		echo '
			<br>
			<br>
			COMMENT REPLY:<br>

			<div>
				By: ', $row_two[username] ,' on ', $row_two[comment_date] ,'
			</div>

			<div>
				', $row_two[comment] ,'
			</div>
		';
		$comment_reply_count++;
	}
}

jcbones · March 10, 2011

Run this as a test, this means DO NOT OVERWRITE YOUR ORIGINAL script.

// what article are we showing?
$article_to_show_id = $_GET['article_id'];


$active_is_set_text = "1"; // Active Column text that makes it okay to show

// Finding the article
$search_for_article = mysql_query("SELECT * FROM articles WHERE id = '$article_to_show_id' AND active = '$active_is_set_text'");

while($row = mysql_fetch_array($search_for_article))
{
	// format the last updated date right
	$update_date_edit = $row[update_date];

	$update_date_edit = date('F j, Y \a\t h:ia', $update_date_edit);

	$row[update_date] = $update_date_edit;



	// format the submit updat date right
	$submit_date_edit = $row[submit_date];

	$submit_date_edit = date('F j, Y \a\t h:ia', $submit_date_edit);

	$row[submit_date] = $submit_date_edit;


	echo '
		<div>
			', $row[title] ,'
		</div>


		<div>
			by: ', $row[author] ,' on ', $row[submit_date] ,'
		</div>



		<div>
			', $row[content] ,'
		</div>


		<div>
			Last Updated: ', $row[update_date] ,'
		</div>

		<form action="article_reply.php" method="post">
			<input type="hidden" name="article_id" value="', $row[id] ,'" />
			<button name="article_reply" type="submit" value="submit">Reply</button>
		</form>

	';

}

$comment_count = 0;
$comment_reply_count = 0;

// Finding all of the comments
$query = "SELECT 
							a.*,
							b.comment_date AS reply_date,
							b.username AS reply_name,
							b.comment AS reply,
							b.reply_id AS reply_to
				FROM 
							article_comments AS a
				JOIN
							article_comments AS b
				ON
					(a.id = b.reply_id)
				WHERE
					article_id = '$article_to_show_id'";
$search_for_article = mysql_query($query) or trigger_error($query . ' has an error: <br />' . mysql_error());
$last_id = NULL;
while($row_comment = mysql_fetch_array($search_for_article))
{
	if($last_id == NULL || ($last_id != $row_comment['id'])) {
		// format the submit updat date right
		$comment_date_edit = $row_comment['comment_date'];

		$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

		$row_comment['comment_date'] = $comment_date_edit;

		echo '
			<br>
			<br>
			COMMENT:<br>

			<div>
				By: ', $row_comment['username'] ,' on ', $row_comment['comment_date'] ,'
			</div>

			<div>
				', $row_comment['comment'] ,'
			</div>
		';
		$comment_count++;
	}

	if(!empty($row_comment['reply_to']) && $row_comment['reply_to'] == $row_comment['id']) {
		// format the submit updat date right
		$comment_date_edit = $row_comment['reply_date'];

		$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

		$row_comment['reply_date'] = $comment_date_edit;

		echo '
			<br>
			<br>
			COMMENT REPLY:<br>

			<div>
				By: ', $row_comment['reply_name'] ,' on ', $row_comment['reply_date'] ,'
			</div>

			<div>
				', $row_comment['reply'] ,'
			</div>
		';
		$comment_reply_count++;
	}
             $last_id = $row_comment['id'];
}

MadLittleMods · March 10, 2011

Get warning

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in

on this line:

while($row_comment = mysql_fetch_array($search_for_article))

jcbones · March 10, 2011

If you had your error level set for development, you would see what the error was.

change:

or trigger_error($query . ' has an error: <br />' . mysql_error());

To:

or die($query . ' has an error: <br />' . mysql_error());

This should give us valuable info.

MadLittleMods · March 10, 2011

ahh good call, here is the output:

never used that a. and b. stuff so no idea. Could you provide a reference or tut on how these work?

SELECT a.*, b.comment_date AS reply_date, b.username AS reply_name, b.comment AS reply, b.reply_id AS reply_to FROM article_comments AS a JOIN article_comments AS b ON (a.id = b.reply_id) WHERE article_id = '1' has an error: 
Column 'article_id' in where clause is ambiguous

jcbones · March 10, 2011

Easy fix.

Change:

WHERE
       article_id = '$article_to_show_id'";

to:

WHERE
     a.article_id = '$article_to_show_id'";

Let me know if that fixed it.

MadLittleMods · March 10, 2011

Alright man, how did you do that black magic?

Was there a reason my way wasn't working?

It works great but i want to learn from this mistake and problem so that i don't have to ask you guys again... Would you be so kind to give me a rundown?

jcbones · March 10, 2011

Sure, any time you run mysql queries inside of another queries while loop, it will most likely hit a snag. So, by joining the table to itself, we can get all the info we need with one query. This does 2 things.

1. Keeps us from running into the coding nightmare that you encountered.

2. Is more efficient because we interact with the database with one query, instead of many.

Commented code below.

//We build our query, joining the table to itself on the reply id to the id.
$query = "SELECT 
							a.*,
							b.comment_date AS reply_date,
							b.username AS reply_name,
							b.comment AS reply,
							b.reply_id AS reply_to
				FROM 
							article_comments AS a
				JOIN
							article_comments AS b
				ON
					(a.id = b.reply_id)
				WHERE
					a.article_id = '$article_to_show_id'";
$search_for_article = mysql_query($query) or die($query . ' has an error: <br />' . mysql_error()); //die if there is an sql error <- debugging.  Change to trigger_error() for logging to the error file.
$last_id = NULL; //define our last id variable.
while($row_comment = mysql_fetch_array($search_for_article)) // query loop, only one needed.
{
	if($last_id != NULL && ($last_id != $row_comment['id'])) { //because the original comment will be returned for every reply to it, we use our last_id to find out if we need to show the original comment.
		// format the submit updat date right
		$comment_date_edit = $row_comment['comment_date'];

		$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

		$row_comment['comment_date'] = $comment_date_edit;

		echo '
			<br>
			<br>
			COMMENT:<br>

			<div>
				By: ', $row_comment['username'] ,' on ', $row_comment['comment_date'] ,'
			</div>

			<div>
				', $row_comment['comment'] ,'
			</div>
		';
		$comment_count++;
	}

	if(!empty($row_comment['reply_to']) && $row_comment['reply_to'] == $row_comment['id']) {  //we check to see if the reply_to column is empty, if it is, there is no reply to the comment.  As an added check, we also make sure it matches the id.
		// format the submit updat date right
		$comment_date_edit = $row_two['reply_date'];

		$comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit);

		$row_two['reply_date'] = $comment_date_edit;

		echo '
			<br>
			<br>
			COMMENT REPLY:<br>

			<div>
				By: ', $row_comment['reply_name'] ,' on ', $row_comment['reply_date'] ,'
			</div>

			<div>
				', $row_comment['reply'] ,'
			</div>
		';
		$comment_reply_count++;
	}
	$last_id = $row_comment['id'];  //assign the id, to our last_id variable.  This will decide if we need to show our original comment on the next loop.
}

Joining tables is very powerful in MySQL. Learning how to do this will help you greatly improve the efficiency of your code.

MySQL JOIN

MadLittleMods · March 10, 2011

Thanks a BUNCH for the explanation. :-*

I see your join but i am getting a little dazed on exactly what is being joined and on etc when i look at this code below:

Why do you select things like a.*(assuming everything) and then b. individual stuff.

Can you explain what you joined into that query and how the comment replies get the id of the actual comment.

Awesome info already

$query = "SELECT 
							a.*,
							b.comment_date AS reply_date,
							b.username AS reply_name,
							b.comment AS reply,
							b.reply_id AS reply_to
				FROM 
							article_comments AS a
				JOIN
							article_comments AS b
				ON
					(a.id = b.reply_id)
				WHERE
					a.article_id = '$article_to_show_id'";

jcbones · March 10, 2011

a.* refers to article_comments AS a. The a refers to this table throughout the query. Being that we are joining the same table to itself, we need to create this reference so MySQL will know which columns we are talking about. (reference the error, when I missed the table identifier on the article_id column).

Next I use the individual columns in the b identifier so that I can re-name those columns for less ambiguous returns.

So, this is how the logic of the query works.

SELECT

a.*, <-select every column from the table referenced as 'a'

b.comment_date AS reply_date, <-select the comment_date column from table referenced as b, but output it as the reply_date column.

b.username AS reply_name, <- select the username column from table referenced as b, but output it as the reply_name column.

b.comment AS reply, <- select the comment column from table referenced as b, but output as reply column.

b.reply_id AS reply_to <-select the reply_id column from the table referenced as b, but output as reply_to column.

FROM

article_comments AS a <-table article_comments REFERENCE this table as 'a'.

JOIN <-simple join, this will return results from 'a' even if there are no results from 'b'.

article_comments AS b <- table article_comments REFERENCE this table as 'b'.

ON <-the ON statement tells MySQL which column to join the tables on.

(a.id = b.reply_id) <-we want the table reference as 'a' to join based on it's 'id' column matching the table referenced as 'b's reply_id column.

WHERE <- now we keep going with the standard WHERE clause.

a.article_id = '$article_to_show_id' <-matching the column article_id from the table referenced as 'a'.

Sign In

mysql in while loop for a different mysql

Recommended Posts

MadLittleMods

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

MadLittleMods

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

MadLittleMods

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

MadLittleMods

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

MadLittleMods

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information