[SOLVED] Associating query result again

[adrianTNT]

Hello, this is probably easy but I dont know how to properly do it, I am not very familiar with mysql yet...

I have a query that looks like this:

<?php 
$query_Recordset_listing_comments = sprintf("SELECT * FROM comments WHERE comment_listing_id = %s ORDER BY comment_id DESC", GetSQLValueString($listing_id, "int"));
$query_limit_Recordset_listing_comments = sprintf("%s LIMIT %d, %d", $query_Recordset_listing_comments, $startRow_Recordset_listing_comments, $maxRows_Recordset_listing_comments);
$Recordset_listing_comments = mysql_query($query_limit_Recordset_listing_comments, $mmfiles) or die(mysql_error());
$row_Recordset_listing_comments = mysql_fetch_assoc($Recordset_listing_comments);
?>

And I use it's values like this:

<?php 
do {
echo $row_Recordset_listing_comments['comment_text'];
} while ($row_Recordset_listing_comments = mysql_fetch_assoc($Recordset_listing_comments));
;?>

 

I wanted to use this do .. mysql_fetch_assoc  in a second location in my page but second time it doesn't return results anymore. Can I only do mysql_fetch_assoc once ?

 

Thanks.

[gevans]

mysql_data_seek($Recordset_listing_comments,0);

 

Use that to reset the pointer in the mysql array

[adrianTNT]

Gevans, that seems to be what I need but I am confused about the zero.

I don't know what number to enter there.

If I use 0 then at second association it prints one blank comment before my valid ones.

If I enter 1 then it shows correct number of comments but first is still blank.

I hope I make sense. maybe you can tell me more about the zero, do I need to calculate it's value somehow or zero simply resets the association?

 

I use in this order:

 

- mysql_fetch_assoc

- mysql_data_seek($Recordset_listing_comments,0);

- mysql_fetch_assoc

 

Thank you.

[gevans]

The problem isn't the new code but your old do while loop...

 

<?php 
do {
echo $row_Recordset_listing_comments['comment_text'];
} while ($row_Recordset_listing_comments = mysql_fetch_assoc($Recordset_listing_comments));
;?>

 

This prints out a record before getting any data, stick with a regular while loop...

while($row_Recordset_listing_comments = mysql_fetch_assoc($Recordset_listing_comments)){
echo $row_Recordset_listing_comments['comment_text'];
}

[cooldude832]

The problem is we aren't thinking about what a mysql_query does properly

 

 

A MySQL query does 2 things for you

 

1) It runs the given query

2) It creates a resource node for that given query

 

 

#2 is what is important here

 

We can name our queries what ever we want

 

<?php
$r1 = mysql_query("Select UserID from `users` where Name like '%george%'");
$r2 = mysql_query("select UserID from `users` where Name like '%joe%'");

echo "Ppl with random names: <br />";
echo "George";
while($row = mysql_fetch_assoc($r1)){
echo $row['UserID']."<br />";
}
echo "<br />Joe";
while($row = mysqL_fetch_assoc($r2)){
echo $row['UserID']."<br />";
}
echo "<br />George again";
while($row = mysql_fetch_assoc($r1)){
echo $row["UserID']."<br />";
}
?>

[gevans]

I'm not sure how that helps him, he's only running one query, and he wants to use the data twice, so instead of re-writting all the code and adding his own array of the query data to re-use I just adviced him to reset the query array and run through it again. That wasn't working because he was using a do while instead of a while loop and getting the first line empty.

[adrianTNT]

@cooldude832: sry but I dont know what you meant

 

@gevans: Yes, it works ok if I change only second loop/assoc:

 

- do {//code} while mysql_fetch_assoc

- reset to zero

- while mysql_fetch_assoc {//code}

 

So second is different than first one, otherwise it doesnt work.

But it would be better to make both work with do{}while mysql_fetch_assoc as it is formated by default in Dreamweaver, otherwise when I work on this code again I might change it back by mistake.

 

so does it work to somehow shift the values/data when I do that reset? So that I can use same do{}while... on second association too.

 

[gevans]

it wont work with do while, do while loop will do something (output your data) before running a query (getting your data).

 

Usually a do while loop is used to run something at least once, then check to see if you want to keep looping. This would not be good here as you may not have any results.

[adrianTNT]

OK but if it works when I first use the results, then shouldn't it work the same on second use? :-\

[gevans]

I couldn't answer you without seeing the code. I can guarantee that the while loop is the best option here!

[adrianTNT]

Pfew... I finally did it 

 

I needed to add another association after the reset:

mysql_data_seek($Recordset_listing_comments,0);
$row_Recordset_listing_comments = mysql_fetch_assoc($Recordset_listing_comments);

Is this common usage?

 

I entered that there because I saw $row_Recordset_listing_comments = mysql_fetch_assoc($Recordset_listing_comments); right after the query (meaning before first use), so I thought to add it before second use too.

 

I dont know if I make sense but it works and thanks for your time