Page 1 of 1
Multible rows mysql_fetch_array($result)
Posted: Sun Jul 19, 2009 5:26 am
by gth759k
So, I have a have a simple mysql table of photos with three columns photo_id, user_id, and upload_date. What I want to do is select all the rows in the table with a particular user_id, which I know how to do, but next I want to create an array of the photo ids, sorted by upload date (most recent first).
$sql = "SELECT photo_id, user_id, upload_date FROM photos WHERE user_id='$_COOKIE[userid]'";
$result = mysql_query($sql) or die(mysql_error());
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$num = mysql_num_rows($result);
I know if there were only one photo in the table with the particular user id, I could get the photo id of that photo with
$data['photo_id'].
My question is how do I get the data for multiple photos into an array like:
array('$photoid1', '$photoid2', '$photoid3', etc.);
The date comes back in the format YXXX-MX-DX. Any help would be appreciated. Thanks.
Re: Multible rows mysql_fetch_array($result)
Posted: Sun Jul 19, 2009 6:02 am
by gth759k
For starters, I can't get the following code to return all the ids. Everywhere else I've looked, this is supposed to work, but it isn't for some reason.
$sql = "SELECT photo_id, user_id, upload_date FROM photos WHERE user_id='$_COOKIE[userid]'";
$result = mysql_query($sql) or die(mysql_error());
$data = mysql_fetch_array($result, MYSQL_ASSOC);
$num = mysql_num_rows($result);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $row['photo_id'];
}
echo $num; gives me the correct number of photos belonging to the user, but the above while loop only echos the first photo_id it comes to. Does anyone know why this isn't working?
Re: Multible rows mysql_fetch_array($result)
Posted: Sun Jul 19, 2009 6:54 am
by jackpf
I don't think you can run mysql_fetch_array twice on one result set. You don't need to anyway.
Try this
Code: Select all
$sql = "SELECT photo_id, user_id, upload_date FROM photos WHERE user_id='".mysql_real_escape_string($_COOKIE['userid'])."'";
$result = mysql_query($sql) or die(mysql_error());
$num = mysql_num_rows($result);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $row['photo_id'];
}
Re: Multible rows mysql_fetch_array($result)
Posted: Sun Jul 19, 2009 2:01 pm
by gth759k
Ok that solves that problem. I also figured out how to store the photo ids in an array, but I'm still having trouble sorting them by date. Any help would be appreciated. Thanks.
$sql = "SELECT photo_id, user_id, upload_date FROM photos WHERE user_id='$_COOKIE[userid]'";
$result = mysql_query($sql) or die(mysql_error());
$num = mysql_num_rows($result);
$i = 0;
$photos = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$photos[$i] = $row['feed_id'];
$i = $i + 1;
}
This creates the array of photo ids that I want, but they're sorted by the order they are encountered. Does anyone know the key to sorting them by date?
Re: Multible rows mysql_fetch_array($result)
Posted: Sun Jul 19, 2009 2:25 pm
by jackpf
Just put ORDER BY date in the query.
Re: Multible rows mysql_fetch_array($result)
Posted: Mon Jul 20, 2009 12:49 am
by gth759k
Well, that wasn't too hard. Thank you.
Re: Multible rows mysql_fetch_array($result)
Posted: Mon Jul 20, 2009 2:03 am
by McInfo
jackpf wrote:I don't think you can run mysql_fetch_array twice on one result set.
Actually, you can (and usually do).
This loop calls mysql_fetch_array() N+1 times where N is the number of rows in the result:
Code: Select all
while ($row = mysql_fetch_assoc($result))
(Edited) gth759k wrote:Code: Select all
$i = 0;
$photos = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$photos[$i] = $row['photo_id'];
$i = $i + 1;
}
That code can be simplified to
Code: Select all
$photos = array();
while ($row = mysql_fetch_assoc($result)) {
$photos[] = $row['photo_id'];
}
Edit: This post was recovered from search engine cache.
Re: Multible rows mysql_fetch_array($result)
Posted: Mon Jul 20, 2009 3:42 am
by jackpf
Aha!
You're right, McInfo. I've just figured something out. Every time you use mysql_fetch_array(), it moves the record pointer along by one.
In my case, when I tested this out, I had only one record i was fetching, so by the time it came to the second one, there was nothing left.
Yeah, you're right. However, by doing what gth795k said originally, he'd miss out the first record. Yeah, my bad, but I was kind of right
