Multible rows mysql_fetch_array($result)
Moderator: General Moderators
Multible rows mysql_fetch_array($result)
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.
$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)
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?
$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)
I don't think you can run mysql_fetch_array twice on one result set. You don't need to anyway.
Try this
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)
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?
$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)
Just put ORDER BY date in the query.
Re: Multible rows mysql_fetch_array($result)
Well, that wasn't too hard. Thank you.
Re: Multible rows mysql_fetch_array($result)
Actually, you can (and usually do).jackpf wrote:I don't think you can run mysql_fetch_array twice on one result set.
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))That code can be simplified to(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; }
Code: Select all
$photos = array();
while ($row = mysql_fetch_assoc($result)) {
$photos[] = $row['photo_id'];
}
Last edited by McInfo on Wed Jun 16, 2010 3:27 pm, edited 1 time in total.
Re: Multible rows mysql_fetch_array($result)
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
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