Mysql Limit question

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
Testor
Forum Newbie
Posts: 12
Joined: Tue Jul 16, 2002 11:26 am

Mysql Limit question

Post by Testor »

Is it possible to do:

SELECT.........LIMIT $somthingdefined , 30

The 30 above is just a number to retun the first 30 entries etc. What is the correct syntax?

On mysql home, the limit() function is explained only with the numbers like 0, 30 and so on.

Thanks for your replies
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

...

Post by kettle_drum »

Yep ive done it before so i know its possible. If you get an error then just keep playing with your syntax.

SELECT * FROM blah LIMIT $var, 0

Im pretty sure that you can also do the following (somebody correct me if im wrong) :

$var = "50, 0";
SELECT * FROM blah LIMIT $var
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

that should work fine, the var indicating the starting row number with a limit of 30. I use this for next and previous page routines quite a bit.
Testor
Forum Newbie
Posts: 12
Joined: Tue Jul 16, 2002 11:26 am

Post by Testor »

Thank you both for replying.

xisle, could you explain a bit more? what did you mean a limit of 30?

I have the $var defined to present one question per page, but I'm trying to make it limit the whole exam for example, 50 or 100 questions out of say hundreds of questions in the db.

so, LIMIT $var, 100 doesn't work.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

...

Post by kettle_drum »

Well the syntax of limit is as follows:

LIMIT num_of_items_to_select, number_of_items_to_skip_before_selecting

Hope that makes sense.

Imagen that your showing 30 items of something per page. In order to know how many items you have already shown you would keep a record of it - this is usually done in the URL. You then make your sql statement including this var, e.g.

SELECT * FROM blah LIMIT 30, $shown

You pass $shown in the url so you know how many items have been shown.

So on mypage.php?shown=0 the sql statment will look like this:

SELECT * FROM blah LIMIT 30, 0

This would select the first 30 rows from blah.

On mypage.php?shown=4283 the sql statment will look like this:

SELECT * FROM blah LIMIT 30, 4283

This would select 30 rows, starting from the 4283rd result.
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post by Gen-ik »

kettle_drum has got that the wrong way around.

Code: Select all

<?php
$show = 10; // how many rows to show
$start = 50; // which row to start from

$info = mysql_query(" SELECT * FROM table LIMIT $start,$show ");
?>
qartis
Forum Contributor
Posts: 271
Joined: Sat Dec 14, 2002 4:43 pm
Location: BC, Canada
Contact:

Post by qartis »

Yea, so in plaintext, mysql thinks like this:

Code: Select all

LIMIT 50,10
I'm going to return 10 rows, and the first one is going to be number 50. (Would return rows 50-60)
Testor
Forum Newbie
Posts: 12
Joined: Tue Jul 16, 2002 11:26 am

Post by Testor »

WOW, this is much better than reading a text book :D

Thank you all, I think I got it now. So, if I wanted to show 100 questions starting from the beginning then:

Code: Select all

LIMIT 0, 100
I could also order by rand() before limit to randomize the questions :lol: This is cool.

I guess I can forget about the defined $var and find a way to present one question per page, much easier this way.

Once again, thank you all very much.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

...

Post by kettle_drum »

Oh crap sorry :( i didnt mean to attempt to teach you to do something wrong.....honest.
Testor
Forum Newbie
Posts: 12
Joined: Tue Jul 16, 2002 11:26 am

Post by Testor »

:) I know you didn't mean to here's the proof:

viewtopic.php?t=11011

Don't be sad
Post Reply