CODEWITHSUNDEEP
phpmysqlmysqlilimitoffset
vijay9066
vijay9066

Reputation: 11

Using $variable in PHP MySQL LIMIT

This is my current code which i'm using inside a php script.

$prodid = $_GET['id'];
$query = mysql_query("SELECT * FROM table1 WHERE productid = '$prodid' 
ORDER by amount DESC LIMIT 3");

I've set the LIMIT as 3. But I want to define something like '$count' and the value for $count should be fetched from a MySQL query i.e 

$count = mysql_query("SELECT count FROM table1 WHERE productid = '$prodid'");

The LIMIT must change with respect to the '$prodid'. Like this, 

mysql_query("SELECT * FROM table1 WHERE productid = '$prodid' 
ORDER by amount DESC LIMIT '$count'");

Upvotes: 1

Views: 1451

Answers (1)

O. Jones
O. Jones

Reputation: 108776

A few things.

First, you are using this query to find the number of rows to fetch.

 "SELECT count FROM table1 WHERE productid = '$prodid'"

That should say

 "SELECT COUNT(*) FROM table1 WHERE productid = '$prodid'"

in order to get a count of rows.

And, it should say

 "SELECT COUNT(*) FROM table1 WHERE productid = " . intval($prodid)

to avoid sql injection (assuming your prodid values are integers).

Second, the query above gives back a one-row result set. You need to actually read that row. I'm not going to tell you how to do that using mysql_ because only a fool uses mysql_ these days. Please, please use PDO or mysqli_.

Third, this query

"SELECT * FROM table1 WHERE productid = " . intval($prodid) . " ORDER by amount DESC"

always returns the number of rows your previous query wanted. So your LIMIT clause is entirely unnecessary in the example you've given.

Upvotes: 1

Related Questions