MySQLi using limit and bind param problem?

[chrisjroyce]

<?php
$link = mysqli_connect("hostName", "user", "pass", "db");

/* check connection */
if (mysqli_connect_errno()) 
{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

$limit = 1;

/* create a prepared statement */
$stmt = $link->prepare('SELECT `id` FROM `proofImages` WHERE `clientId` = 1 LIMIT ?');

$stmt->bind_param('i', $limit);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($col1);

    /* fetch values */
    while ($stmt->fetch()) 
{
        printf("%s \n", $col1);
    }

    /* close statement */
    mysqli_stmt_close($stmt);

/* close connection */
mysqli_close($link);
?>

 

Whenever I try to bind a value to my query for the limit the query fails. I have no idea why, and if I remove the Limit everything works.

 

Any ideas? It's been driving me crazy for days!

[fenway]

Perhaps some error output would help?

[chrisjroyce]

I've read that it might be that MYSQL and PHP doesn't support prepared statement params for LIMITS

 

Fatal error: Call to a member function bind_param() on a non-object in /var/www/vhosts/domain.co.uk/subdomains/projects/httpdocs/imageViewer/limit.php on line 16

 

is the error message.

[Mchl]

Do var_dump() on $stmt. If it is bool(false), it means that preparing the statement failed.

[chrisjroyce]

I know the prepare statement is failing

 

But wondered if anyone knew why?

[Mchl]

I think you can't have 'LIMIT ?' in prepared statement.

 

[edit]

 

I just tested in console, and apparently it is possible to have ? in LIMIT...

 

Maybe some other error with your query?