Multible rows mysql_fetch_array($result)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gth759k
Forum Commoner
Posts: 76
Joined: Mon Jun 15, 2009 3:04 am

Multible rows mysql_fetch_array($result)

Post 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.
gth759k
Forum Commoner
Posts: 76
Joined: Mon Jun 15, 2009 3:04 am

Re: Multible rows mysql_fetch_array($result)

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multible rows mysql_fetch_array($result)

Post 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'];
}
gth759k
Forum Commoner
Posts: 76
Joined: Mon Jun 15, 2009 3:04 am

Re: Multible rows mysql_fetch_array($result)

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multible rows mysql_fetch_array($result)

Post by jackpf »

Just put ORDER BY date in the query.
gth759k
Forum Commoner
Posts: 76
Joined: Mon Jun 15, 2009 3:04 am

Re: Multible rows mysql_fetch_array($result)

Post by gth759k »

Well, that wasn't too hard. Thank you.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Multible rows mysql_fetch_array($result)

Post 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.
Last edited by McInfo on Wed Jun 16, 2010 3:27 pm, edited 1 time in total.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multible rows mysql_fetch_array($result)

Post 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 :D
Post Reply