Page 1 of 1

Mysql Limit question

Posted: Mon Jul 21, 2003 6:54 pm
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

...

Posted: Mon Jul 21, 2003 7:59 pm
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

Posted: Tue Jul 22, 2003 2:18 pm
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.

Posted: Tue Jul 22, 2003 5:28 pm
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.

...

Posted: Tue Jul 22, 2003 6:29 pm
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.

Posted: Tue Jul 22, 2003 7:56 pm
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 ");
?>

Posted: Tue Jul 22, 2003 8:00 pm
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)

Posted: Tue Jul 22, 2003 8:34 pm
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.

...

Posted: Tue Jul 22, 2003 9:39 pm
by kettle_drum
Oh crap sorry :( i didnt mean to attempt to teach you to do something wrong.....honest.

Posted: Tue Jul 22, 2003 10:05 pm
by Testor
:) I know you didn't mean to here's the proof:

viewtopic.php?t=11011

Don't be sad