prepared statement causing while loop error

Question

Hello,

I have the following simple mysqli php application, which should work fine. $pk is accepted perfectly and is a valid ARTICLE_NO, and the query works perfectly when executed directly by mysql. I have put output statements after every event and all except tetsing while executes. The while loop is never entered, and I am unsure why.

edit: I have narrowed the problem down to the fact that 0 rows are returned, but I have no idea why as the same query in phpmyadmin gives the right result.

edit2: if I get rid of the while loop and just have

    if (!$getRecords->fetch()) {
    printf("<p>ErrorNumber: %d\n", $getRecords->errno);
}

It shows that the errno is 0. So no records are fetched, and there is no error, yet it is a valid query.

<?php
ini_set('display_errors', '1');
error_reporting(E_ALL);
$pk = $_GET["pk"];
$con = mysqli_connect("localhost", "", "", "");
if (!$con) {
    echo "Can't connect to MySQL Server. Errorcode: %s\n". mysqli_connect_error();
    exit;
}
$con->set_charset("utf8");
echo "test outside loop";
if(1 < 2) {
    echo "test inside loop";
    $query1 = 'SELECT ARTICLE_NO FROM AUCTIONS WHERE ARTICLE_NO = ?';
    if ($getRecords = $con->prepare($query1)) {
echo "inside second loop";
     $getRecords->bind_param("i", $pk);
echo "test after bind param";
     $getRecords->execute();
echo "test after bind execute";
     $getRecords->bind_result($ARTICLE_NO);
echo "test after bind result";
     while ($getRecords->fetch()) {
      echo "test inside while";
      echo "<h1>".$ARTICLE_NO."</h1>";
     }
    }
}

edit:

I tried with this code:

<?php
$mysqli = new mysqli("localhost", "", "", "");
$pk = $_GET["pk"];
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT ARTICLE_NAME, WATCH FROM AUCTIONS WHERE ARTICLE_NO = ? LIMIT 5")) {
    $stmt->bind_param("i", $pk);
    $stmt->execute();
    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);
    /* fetch values */
    while ($stmt->fetch()) {
     printf("%s %s\n", $col1, $col2);
    }
    /* close statement */
    $stmt->close();
}
/* close connection */
$mysqli->close();
?>

This works without $pk, if I take away the parameters it works fine. It is not a problem with getting pk via GET, because if I assign $pk = 1; instead it still fails. 1 is a valid ARTICLE_NO, and SELECT ARTICLE_NAME, WATCH FROM AUCTIONS WHERE ARTICLE_NO = 1 LIMIT 5 works fine in phmyadmin.

edit: the problem was that mysqli could not handle bigint, I am now using k as a string and it works fine.

Answer 1

I'm not sure if you've modified that code, but you don't seem to be selecting the database you want to connect to there.

Use mysqli_select_db(...) for that if that is the problem.

EDIT: It also looks like you're using uppercase for the column, table name etc.

Get case sensitivity right, it could be that you're presuming case insensitivity because it works from the command line. As far as I know the mysqlI driver in PHP is case sensitive about at least column names.

Answer 2

Check the value of:

$getRecords->num_rows

which should help reveal whether the earlier SELECT is actually returning any data

You may need to also add:

$getRecords->store_result()

first to ensure that you've the whole query has completed before asking for the number of rows in the result set.

Also - make sure you cast $pk to an integer! It's possible that the value being passed in is getting mangled.