Retreive data from database by highest ID number

[mlsbraves]

I'm trying to figure out how to make a script that will connect to my mySQL database and select the three highest ID number from the database. I am creating a news script for my site, it will connect to my forums database and give me the last three post in my forum. I created its own forum and its ID is 12. I've been able to connect to the datbase and retrieve the data, but since the forum is updated regularly, I need it to check in forum 12 the latest post.

Heres what I got so far:

<?
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("database", $db);
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12",$db);  

$myrow = mysql_fetch_array($result);
?>

<? echo $myrow ["title"]?>

Now:
<? echo $myrow ["title"]?>

Will echo the data from the title field. But if I update the forum, there will be more than one title with the same forum ID. So I need it to check the ID of each field, and select the three highest. I've looked at php.net but I didnt see anything for selecting the highest number from the database.

Can someone help me out here? How can I get php to check for the highest ID number in a mySQL database.

[stezz]

well.. if your ID field is set to auto_increment then it's obvious that the last three items in your table will have the highest ID numbers.. therefore all you need to do is this..

$result = mysql_query("SELECT * FROM table ORDER BY ID ASC LIMIT 3;");

This query selects all fields in your table and orders them by the ID field in acesding order. Basically all it does it turn your table upside down so the highest ID number will be at the top and limits the results to 3, thus giving you the three highest ID's in ur table.

Hope it helps!

Regards, Stezz.

[mlsbraves]

Something still isnt working

<?
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("database", $db);
$result = mysql_query("SELECT * FROM ib_topics ORDER BY ID ASC LIMIT 3;");
$myrow = mysql_fetch_array($result);
?>

I need it to only check data in field "forum_id" #12.
How am I suppose to echo this?

<? echo $myrow ["title"]?>

[reidme]

You need to combine the two sql statements, the one you started with and the one you were shown with the ASC LIMIT. I suggest you go brush up on SQL structure, it will answer this question and many questions you are bound to need answered in the future:
http://www.sqlcourse.com/
http://www.w3schools.com/sql/
It's worth having a look.

[mlsbraves]

me banging my head agienst the monitor

I read that and I still cant get it to work 🙁

<?
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("ib", $db);
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12",$db);  

$myrow = mysql_fetch_array($result);
?>

<? echo $result = mysql_query("SELECT * FROM ib_topics ORDER BY ID ASC LIMIT 3;"); ?>

The only thing I see different from the reading is:

select title from ib_topics
       where forum_id > 12; 

But thats not working.

Can someone show me what I'm doing wronge here?

[diego25]

When reidme said to combine both SQL statements, he literally meant put them both into one SQL statement. Try this:

SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY ID ASC LIMIT 3

Diego

[mlsbraves]

Parse error on line 8:

<?
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("ib", $db);
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY ID ASC LIMIT 3);  

$myrow = mysql_fetch_array($result);
?>

<? echo $myrow ["title"]?>

Am I echoing it wronge?

[diego25]

Look at your message above, and use the syntax highlighted colours to catch your error...

Diego

[mlsbraves]

🙁 I'm sorry, I'm just so fed up with this. The things that use to make since don't anymore.

<? echo $myrow ["title"]?>

this works with my other scripts to echo data...

is this my problem with the code or is there more.

[milind24]

try this :

<?
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("ib", $db);
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY ID ASC LIMIT 3") die (" Query not excuted ".mysql_error());  

$myrow = mysql_fetch_array($result);

 echo $myrow ["title"];
?>

[mlsbraves]

Parse error: parse error on line 4

[milind24]

sorry man , correct the syntax or die stament as follows we missied || or word ther

$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY ID ASC LIMIT 3",$db) || die(" Query not excuted".mysql_error());  

[mlsbraves]

Query not excutedUnknown column 'ID' in 'order clause'

Something tells me I'm doing something wronge now...

[mlsbraves]

The database is "ib"
The table is "ib_topics"
The field it should be looking in is "forum_id"
and it should be looking for the value 12.

Then everyone with the value 12, should then be checked from the field tid and retrieve the 3 higest numbers.

[milind24]

what man , if ther is no any filed with name ID in the table how it will work , i think your id field name is forum_id so replace ID by forum_id and check it

[mlsbraves]

Ok, I copied a view of my table so you can see what I'm talking about.
http://myweb.cableone.net/atlantachi/tid.htm

This is what I want the script to do:

Connect to the databae(ib) and then go into table(ib_topics). Then it will go to the field "forum_id" and find the the rows
with the value 12 in it. If the row has a value 12, then it should check filed "id" and select the three highest numbers from the group. Then echo the data in the "title" field.

So in this case, it would echo:
News1
Tesy
please

I'm sorry if this doesnt make since, I'm frusterated and its 2am.

[milind24]

ok ,

checked the table , try this and tell me what output is coming :

$db = mysql_connect("localhost", "user", "pass") || die ("unable to make connection to MySQL".mysql_error());
mysql_select_db("ib", $db) || die("Unable to slect databse".mysql_error());
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY forum_id ASC LIMIT 3",$db) || die(" Query not excuted".mysql_error());

while ( $myrow = mysql_fetch_array($result))
{
    echo $myrow["title"]."<br>";
}

[mlsbraves]

Warning: Supplied argument is not a valid MySQL-Link resource on line 3
Unable to slect databse

The database name is ib so I don't know why it isnt connecting.

[mlsbraves]

Oh yeah. I had to add the <? to it or it would just display the text.

<?
$db = mysql_connect("localhost", "user", "pass") || die ("unable to make connection to MySQL".mysql_error());
mysql_select_db("ib", $db) || die("Unable to slect databse".mysql_error());
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY forum_id ASC LIMIT 3",$db) || die(" Query not excuted".mysql_error());

while ( $myrow = mysql_fetch_array($result))
{
    echo $myrow["title"]."<br>";
}

THis is the code, and I still get the same error.

[mlsbraves]

I got it to work with the following code:

<?
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("ib", $db);
$result = mysql_query("SELECT * FROM ib_topics WHERE forum_id=12 ORDER BY forum_id ASC LIMIT 3",$db);

while(list($forum_id, $title) = mysql_fetch_row($result)) 

{
    echo "$title";
}
?>

That will give me the three parts of data in the title field. But how do I echo lets say the highest numer? I want to echo one at a time.

Example:
Our latest entry was: echo1
Before that: echo 2
and before that: echo 3