Basic Mysql Query Problem Just need a quick bit of help here [solved]

[8ennett 0]

Right, haven't got time to finish debugging this query because it's late and need sleep, coding is not a good idea when tired lol Basically I want this query to pull a maximum of 10 records from the database where either the user column OR the other column equals the users id (stored in $_SESSION['dm']['id'] obviously) but it appears to pull 10 records where the user column contains the data and then a seperate 10 from the other column as well. It's part of a pagination system I've built in to financial transaction sheet for my game. Here's the full set: if (!isset($_GET['page']) || ($_GET['page'] == 1) || ($_GET['page'] < 1)){ $_GET['page'] = 1; $amounta = 0; $amountb = 10; } else { $page = CleanUp(round($_GET['page']), true); if ($page > 1){ $amounta = (10 * $page) - 9; $amountb = (10 * $page); } } $tranhistory = mysql_query("SELECT * FROM banklog WHERE user='".$_SESSION['dm']['id']."' OR other='".$_SESSION['dm']['id']."' ORDER BY time DESC LIMIT ".$amounta.", ".$amountb); I can probably figure this out when I wake up in the morning, but you know what it's like, it's better if someone has given you the answer straight off to save you time! Definately sure it's the query that's the problem though.

[Quatrux 4]

I think in MySQL the LIMIT works quite differently, if you have 100 rows in the result, the limit works something like this:

Limit 0,10 - it will take 10 rows starting from 0 row.

Limit 10,10 - it will take 10 rows starting from 10th row

Limit 20,10 - it will take 10 rows starting from 20th row

in your situation you use the second parameter $amountb for it to be 10 * number, which means you can get a result on lets say page 5 like this:

Limit 41, 40 - it will take 40 rows starting from 41th row? Is this really what you want to do or I'm missing something?

I've done a paging system before, but I'm not at home to look at the code, but basically you need to do something like this:

// Get the amount of Pages$Pages = ceil( getNumberOfRows($Query) / ROWS_ON_A_PAGE );// Check amount of Pagesif ($Pages == 0) $Pages = 1;

This is not a working code, but an idea.. To continue:

if (!isset($Page) || !is_numeric($Page) || $Page < 1 || $Page > $Pages) {  $Page = 1;} elseif (!is_int($Page)) {  $Page = (int) $Page;}

So I guess your $amounta needs to be something like this:

$amounta = (($Page * ROWS_ON_A_PAGE) - ROWS_ON_A_PAGE);

So if you're on page 1, you'll get ((1 * 10) - 10) = (10 - 10) = 0 [Just what the LIMIT wants! in MySQL]
So if you're on page 5, you'll get ((5 * 10) - 10) = (50 - 10) = 40 [Just what the LIMIT wants! in MySQL]

And your $amountb is the ROWS_ON_A_PAGE, so:

$amountb = ROWS_ON_A_PAGE;

and your SQL Query: ... LIMIT $amounta, $amountb;

Maybe this is what you were looking for?

[8ennett 0]

Yes, I can see where the problem is now, I've used the LIMIT parameter wrong. There wasn't any need for the amountb variable, instead I should have just put in 10. Query has been fixed and working perfectly. Thanks for the help!