8

Possible Duplicate:
PHP PDO bindValue in LIMIT

I could not display the data when using LIMIT and/or OFFSET in the prepare statement, but I can show "Lei Lei" if I don't use the LIMIT and OFFSET, does the code look wrong?

$statement = $conn->prepare("SELECT id,username FROM public2 WHERE username = :name LIMIT :sta OFFSET :ppage");
$name = "Lei Lei";
$statement->execute(array(':name' => $name,':sta' => $start,':ppage' => $per_page));

This have been change from the original code which worked:

$query_pag_data = "SELECT id,username from public2 LIMIT $start, $per_page";
$result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());
Community
  • 1
  • 1
proyb2
  • 321
  • 1
  • 2
  • 6
  • Any reason you're running `$statement->execute()` twice? And are you using `$statement->fetch()` or `$statement->fetchAll()` after execute to retrieve the results? – chriso Apr 01 '11 at 04:05
  • Update my post, I'm using $statement->fetch() – proyb2 Apr 01 '11 at 04:12

3 Answers3

23

I found the answer! 

$statement->bindValue(':sta1', (int) $start, PDO::PARAM_INT);

does work

kapa
  • 77,694
  • 21
  • 158
  • 175
proyb2
  • 321
  • 1
  • 2
  • 6
  • Thank you. I was wondering why my LIMIT :count wasn't working. It looks like it was being cast as a string. – taco Apr 17 '13 at 20:23
10
$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

will let you bind variables without being bothered of them type

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
1

Edit: Fixed

$statement = $conn->prepare("SELECT id,username FROM public2 WHERE username = :name LIMIT :limit OFFSET :offset");
$name = "Lei Lei";
$statement->bindValue(':name', $name);
$statement->bindValue(':limit', (int) $start, PDO::PARAM_INT);
$statement->bindValue(':offset', (int) $per_page, PDO::PARAM_INT);
$statement->execute();
chriso
  • 2,552
  • 1
  • 20
  • 16
  • @proyb2 try without the OFFSET like in your original query: `SELECT id, username FROM public2 WHERE username = :name LIMIT $start , $per_page` – chriso Apr 01 '11 at 05:06
  • I see, so how can I add the LIMIT into the prepare statement? – proyb2 Apr 01 '11 at 05:25
  • I found the answer! $statement->bindValue(':sta1', (int) $start, PDO::PARAM_INT); does work – proyb2 Apr 01 '11 at 05:32
  • you are wrong. doing this is against the idea of prepared statements – Your Common Sense Apr 01 '11 at 05:42
  • @Col totally agree, didn't realise LIMIT was supported – chriso Apr 01 '11 at 05:43
  • Limit statements certainly can be part of a prepared statement, the values just need to be bound as Integers. – Stephen Apr 01 '11 at 05:47
  • 3
    Hate to necro a thread... but the edited version binds the parameters to the wrong names... Given the parameters in the SQL statement "... LIMIT :limit OFFSET :offset" you should be binding $start to offset and $per_page to limit – Shadow Radiance Jun 12 '13 at 04:31