CODEWITHSUNDEEP
phpmysqlcodeigniter
Mr Sorbose
Mr Sorbose

Reputation: 777

Mysql confusion with LIMIT function

I am using the following SQL query in MySQL. 

"SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month, 
        account.name AS accountName, 
        account.id AS accountId, 
        invoices.invId AS invoiceId, 
        productType.title AS productTitle, 
        sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue,
        sum(invoiceItems.quantity) AS totalQuantity
 FROM account LEFT JOIN invoices ON invoices.accountId = account.id
          LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId
      LEFT JOIN productType ON invoiceItems.productTypeId = productType.id
 WHERE invoices.statusId != 'S62FD452B1D4'
 GROUP BY invoiceItems.productTypeId, invoices.invId   
 ORDER BY month DESC, accountName ASC, invoices.id ASC    
 LIMIT ".$start_limit.", ".$records_per_page.";"

This works fine with out the section to limit the query. However when I add the limit section, the variables $start_limit & $records_per_page have no value, whereas if i jig the lines around the variables are making it into the query. this is also the case when I change the quotation marks around the variables e.g to '.$start_limit.'. However the query does not seem to function with this.

Any advice or help as to what I am doing wrong would be greatly appreciated.

The values for the two variables are 100% definitely being passed in. and in this example they are simply, start limit = 0 and end limit (per page) = 50. I have checked and 50 is well below the limit.

The problem is that when I see the SQL error pop up it says:

SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month, 
       account.name AS accountName, 
       account.id AS accountId, 
       invoices.invId AS invoiceId, 
       productType.title AS productTitle, 
       sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue,
       sum(invoiceItems.quantity) AS totalQuantity 
FROM account LEFT JOIN invoices ON invoices.accountId = account.id 
             LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId 
             LEFT JOIN productType ON invoiceItems.productTypeId = productType.id 
WHERE invoices.statusId != 'S62FD452B1D4' 
GROUP BY invoiceItems.productTypeId, invoices.invId 
ORDER BY month DESC, accountName ASC, invoices.id ASC 
LIMIT , ;

and if i change things around to see if the variables are there we do get:

SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month, 
       account.name AS accountName, 
       account.id AS accountId, 
       invoices.invId AS invoiceId, 
       productType.title AS productTitle, 
       sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue, 
       sum(invoiceItems.quantity) AS totalQuantity 
FROM account LEFT JOIN invoices ON invoices.accountId = account.id 
             LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId 
             LEFT JOIN productType ON invoiceItems.productTypeId = productType.id 
WHERE invoices.statusId != 'S62FD452B1D4' 
GROUP BY invoiceItems.productTypeId, invoices.invId 
ORDER BY month DESC, accountName ASC, invoices.id ASC 
LIMIT '.0.', '.50.' ;

neither of these values are user supplied. so thats not too much of a problem. I am unsure as to why the variables are behaving unusually in this 1 query from hundred similar 1s ive have done that do the same thing fine. the only difference in this one is the number of groupings and orderings. would this make a difference?

Upvotes: 1

Views: 356

Answers (3)

Resh32
Resh32

Reputation: 6590

The values of $start_limit and $records_per_page are probably invalid or out of range. Perhaps try logging them to be sure.

Upvotes: 0

Pedigree
Pedigree

Reputation: 2594

Can you use the PDO Extension for this?

Your code is prone to SQL Injection. Use the PDO or MySQLi Extensions.

Example using PDO extension:

<?php

    $query = "SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month, account.name AS accountName, account.id AS accountId, invoices.invId AS invoiceId, productType.title AS productTitle, sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue, sum(invoiceItems.quantity) AS totalQuantity
             FROM account
             LEFT JOIN invoices ON invoices.accountId = account.id
             LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId
             LEFT JOIN productType ON invoiceItems.productTypeId = productType.id
             WHERE invoices.statusId != 'S62FD452B1D4'
             GROUP BY invoiceItems.productTypeId, invoices.invId   
             ORDER BY month DESC, accountName ASC, invoices.id ASC    
             LIMIT ?, ?;"


    $stmt = $dbh->prepare($query);
    $stmt->bindParam(1, $start_limit, PDO::PARAM_INT);
    $stmt->bindParam(2, $records_per_page, PDO::PARAM_INT);

    $stmt->execute();
    // other codes here

?>

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101604

If $start_limit and/or $records_per_page are unpopulated you're going to have issues. However, if you default them to standard values before placing them in the query if you wish:

// default them values to 0 and 50
$start_limit = empty($start_limit) ? 0 : $start_limit;
$records_per_page = empty($records_per_page) ? 50 : $records_per_page;
// may also want to check (empty(...) || $var < 0 || $var > $threshold) as well.

Then go about your business:

$sql = "SELECT " . /* ... */ " LIMIT " . $start_limit . "," . $records_per_page;

I should note though that if either (or both) are these are user-supplied (or the user has any opportunity to change these values) I would sanitize them first before placing them in the query. e.g.

$start_limit = (int) $_REQUEST['start_limit'];
if ($start_limit < 0) // can't be <0
  $start_limit = 0;

$records_per_page = (int) $_REQUEST['records_per_page'];
if ($records_per_page < 10) // can't be <10
  $records_per_page = 10;
else if ($records_per_page > 100) // can't be >100
  $records_per_page = 100;

Then you're making sure $start_limit doesn't contain anything threatening like ;SELECT password FROM admin_table;. (SQL Injection)

Your question is a bit ambiguous though, so if I'm off track please update the question and I will do the same with my answer

Upvotes: 1

Related Questions