MySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

MySQL query

Post by mjseaden »

Dear All,

Bit of a while since I've been using these forums, nice to be back!

Can anyone tell me what query I can use to SELECT the n-th row from a data table (i.e. only dependent on the position of the row in the table, not the row's values).

Many thanks

Mark Seaden (mjseaden@hotmail.com)
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

if you are using an auto-incrementing row ID

Code: Select all

$query = "SELECT * FROM mytable WHERE ID ='3'";
dont know if this is what you mean
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Okay, change the query - the last one didn't make sense. How do I select a random row from the data table, given that I don't have an auto-incrementing row ID?

Many thanks

Mark
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

to select a random row, do this query

Code: Select all

$sql = "SELECT * FROM mytable ORDER BY rand() LIMIT 1";
Mark
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

ok, i can start it

Code: Select all

$query = "SELECT * FROM tablename";
$result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($result);
// now create a randomiser
$random_num = rand(1, $numrows);
$fetch_row = mysql_fetch_row($random_num)
print "$fetch_row";
now you can sort out the print statement
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Thanks to you both
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

to select a certain row in the table if you don't have an id do this

Code: Select all

$sql = "SELECT * FROM mytable LIMIT 5, 1";
This will return the 5th row. change the 5 to return whatever row you want.

Mark
Last edited by JayBird on Tue Apr 20, 2004 10:38 am, edited 1 time in total.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

more efficient as usual
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

Yea, he seems to do that ... ;)
Post Reply