0

Expected result:

Loop through all entries in the checkedout table, and select the entry from the game table where the barcode field is the same.

Actual behaviour / issue:

For the most part, this is working as intended. If I set the barcode field to a numerical value in the game table, and then "checkout" that barcode, everything works as intended. The barcodes I'll be using are in the format of ABC12345678. Once I change the values in the barcode field, in the game table to the alphanumeric version, it no longer runs the secondary select statement and displays this error: Fatal error: Call to a member function fetch_assoc() on boolean which refers to the following line: while ($row2 = $result2->fetch_assoc()) {

Oddly enough, if I run the exact same select statement SELECT * FROM game WHERE barcode = 'ABC12345678' on the MySQL instance, it returns the proper results.

Question

Do I need to be using a different method to select based on the value now being alphanumeric? Do I need to manipulate the data in some way?

Code:

$sql = "SELECT * FROM checkedout";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while ($row = $result->fetch_assoc()) {

        $userid  = $row["userid"];
        $barcode = $row["barcode"];

        echo "$userid </br>";
        echo "$barcode </br>";

        $sql2 = "SELECT * FROM game WHERE barcode = " . $barcode . "";
        $result2 = $conn->query($sql2);

    while ($row2 = $result2->fetch_assoc()) {

        $title = $row2["title"];
        $console = $row2["console"];

        echo "$title </br>";
        echo "$console </br>";
    }

checkedout table:

Checked Out table

game table:

Game table

SimplyAzuma
  • 25,214
  • 3
  • 23
  • 39
  • You are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). **This will take care of any pesky quoting issues that may occur, which issue what is happening here.** – aynber Mar 04 '20 at 17:38
  • 1
    Alphanumeric strings needs quotes around it, unless you're using prepared statements. Always use prepared statements when using user-defined values, which will eliminate a whole host of issues. – aynber Mar 04 '20 at 17:49
  • @aynber my apologies, you are correct! Any idea why it would work with just numbers, but the second that other characters were introduced, it would break? As it had quotes around it, that's where my confusion lies. – SimplyAzuma Mar 04 '20 at 17:55
  • Numbers are treated as numbers, but if it contains a letter, it's treated as a column name if it's not surrounded by quotes. – aynber Mar 04 '20 at 17:58

0 Answers0