Sorting data while pulling from database

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
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Sorting data while pulling from database

Post by arunkar »

Hi,

I'm trying to pull the latest entry in the database using the auto-generated ID.

Below is the code.

Code: Select all

 
$result = mysql_query("Select subscriberid from SendStudio_list_subscribers where formid=5 order by subscriberid desc");
 
while($r=mysql_fetch_assoc($result))
{   
    $joomlaid = $r["subscriberid"];
}
 
the above sql statement dosen't pull the latest data but the last record data. How do I move the pointer to the first record?

Is there a way to do it?

Kindly advise...

thanks... :)
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Sorting data while pulling from database

Post by aceconcepts »

Use a timestamp?
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Sorting data while pulling from database

Post by arunkar »

Ya I'm using a time stamp... but won't that cause the same problem as this sorting the latest... autonumber in deciding order?

right now...

for this sql

Code: Select all

 
select *  from SendStudio_list_subscribers where formid = 5 order by subscriberid desc
 
I get an output:

2333
2332
2329

on the webpage when I display its showing me the output as 2329 but I want the number 2333...

any suggestions?

Thanks
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Sorting data while pulling from database

Post by aceconcepts »

how are you outputting the results on the page?
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Sorting data while pulling from database

Post by arunkar »

Code: Select all

 
$result = mysql_query("Select subscriberid from SendStudio_list_subscribers where formid=5 order by subscriberid desc");
 
while($r=mysql_fetch_assoc($result))
 {   
     $joomlaid = $r["subscriberid"];
 }
From the above $joomlaid stores the value from the database. this is how I display the value:

Code: Select all

<?=$joomlaid?>
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Sorting data while pulling from database

Post by aceconcepts »

Try echoing the variables. Also use mysql_fetch_array().
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Sorting data while pulling from database

Post by arunkar »

emm...

ya did it but still dont get any luck... I even sort by the date...

Code: Select all

 
$result = mysql_query("Select subscriberid from SendStudio_list_subscribers where formid=5 order by subscriberid desc, requestdate desc");
 
//while($r=mysql_fetch_assoc($result))
while($r=mysql_fetch_array($result))
{   
    $joomlaid = $r["subscriberid"];
}
echo "$joomlaid:  ".$joomlaid;
exit();
 
 
I get an output:

2329

when I run the same sql in the db I get:

2339
2333
2332
2329


any Ideas how to pick up the latest/ largest subscriberid as its autonumber...

:idea:
Li0rE
Forum Commoner
Posts: 41
Joined: Wed Jun 07, 2006 6:26 am

Re: Sorting data while pulling from database

Post by Li0rE »

you have to echo it within the loop, otherwise it just outputs the last value the variable was set to.

but what you want to do is only echo one i see,

So do what you just did, except use mysql_query("SELECT field FROM table ORDER BY value DESC LIMIT 1"); change value to that value you are talking about, and it will get you the latest one only. Since you arent limiting it to one, it is going through all the results and only echoing after it has finished looping. by the time it finishes the loop, it has set the variable to your desired value, but it has also proceeded to set it to the one less, one less, one less, etc.
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Sorting data while pulling from database

Post by arunkar »

Thanks Li0rE and aceconcepts,

It works as I wanted. LIMIT 1 did the trick...


cheers
:D
Li0rE
Forum Commoner
Posts: 41
Joined: Wed Jun 07, 2006 6:26 am

Re: Sorting data while pulling from database

Post by Li0rE »

no problem :)
Post Reply