PHP: Unable to run same MySQL query several times

Question

HTML FILE:

<form method="post" action="generate.php">
Product Reference(s): (if multiple, separate by ",")<br />
<input type="text" name="project_ref" value="REF123, REF124" />
<input type="submit" value="Generate" />
</form>

PHP FILE:

<?php

$ref_array = explode(',', $_POST['project_ref']);

foreach ($ref_array as &$ref) {

    // Create connection
    $conn = mysqli_connect($host, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "SELECT * FROM `inventory` WHERE reference = '$ref' LIMIT 1";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        // output data of each row
        while($row = mysqli_fetch_assoc($result)) {
            echo "Brand: " . $row["brand"]. "<br>";
        }
    } else {
        echo "0 results";
    }

    mysqli_close($conn);

}

?>

RESULTS:
Brand: Bose
0 results

But I actually wanted:
Brand: Bose
Brand: Beats

So the problem is that the MySQL query is not running for every array item. Its only executing the first item of the array.

It looks like the second "ref" code returns zero results. Maybe you need to trim() the whitespace of each $ref before executing the query? Incidentally, you only need to connect to the database once, not upon every iteration of the loop. — showdev
– showdev, Commented Feb 3, 2015 at 19:48
Is there a particular reason you need to pass ref as a &$ref? — Dave Goten
– Dave Goten, Commented Feb 3, 2015 at 19:48
Maybe this can help you. php.net/manual/en/mysqli.multi-query.php — Ciprian
– Ciprian, Commented Feb 3, 2015 at 19:49
I can't believe I missed the trim() function....! Thank you @showdev!! — Jose Andraos
– Jose Andraos, Commented Feb 3, 2015 at 19:50
Don't connect and disconnect in the loop. Connect, do loop, disconnect. — developerwjk
– developerwjk, Commented Feb 3, 2015 at 19:51

mopo922 · Accepted Answer · 2015-02-03 19:50:05Z

1

Your input value has a space between the different refs: REF123, REF124

You can either explode on a comma & space:

$ref_array = explode(', ', $_POST['project_ref']);

Or trim the values:

 $sql = "SELECT * FROM `inventory` WHERE reference = '" . trim($ref) . "' LIMIT 1";

It's also strongly recommended that you pass in $ref as a parameter, rather than a string literal:

http://php.net/manual/en/mysqli-stmt.bind-param.php

answered Feb 3, 2015 at 19:50

mopo922

6,3813 gold badges30 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dustin Ruckman · Accepted Answer · 2015-02-03 20:06:57Z

It looks like your issue is in your explode. The first value of the explode is correct the second (third, forth, etc.) will have a leading space. explode on ', ' instead of ',' or trim the results before using it.

But there are a few other things in this code. Don't create a new connection for each query, the single connection will work just reuse it. Second, use parameters or sanitize the value before you use it in the sql, this will help prevent sql injection attacks, a better way is to use PDO and use parameters. Last, change your query so that a single query returns all the results you need by using an IN clause.

<?php

$ref_array = explode(', ', $_POST['project_ref']);
$ref_IN = "";
foreach ($ref_array as $ref_val)
    $ref_IN .= "'{$ref_val}', ";
$ref_IN = rtrim($ref_IN , ",");

// Create connection
$conn = mysqli_connect($host, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM `inventory` WHERE reference IN ({$ref_IN}) GROUP BY reference";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "Brand: " . $row["brand"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);

?>

Collectives™ on Stack Overflow

PHP: Unable to run same MySQL query several times

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related