PHP mysql query is not working in PHP but works fine in MYSQL

Question

I have a mysql query that does not work in PHP but works fine in MySQL. Below is partial code for the HTML form that processes POST variables and processes it for mysql query.

See below.

<?php

$codes = $_POST['codes'];

// get as array like so
$barcodes = explode("\n", $codes);

// build up string of barcodes
$barcode_str = "";
$prefix = '';
foreach ($barcodes as $barcode){
    $barcode_str .= $prefix . "'" . $barcode . "'";
    $prefix = "," ;
}
$dbhost = 'localhost:3036';
$dbuser = 'xxxx';
$dbpass = 'xxxx';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db('disks');
$sql = "SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result
       FROM hdds WHERE serial IN ( $barcode_str) ;";

echo $sql;


$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}

$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
echo $row['manu'];
}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{

/** Lets remove 20000000000000 bytes values from capacity array */
preg_match('/\[(.*?)\]/', $row['capacity'], $matches);
if (isset($matches[1])) {
    $row['capacity'] = $matches[1];
}
echo '<tr>';
echo
     "<td> {$row['manu']} </td>".
     "<td> {$row['model']} </td>".
     "<td> {$row['serial']} </td>".
     "<td> {$row['capacity']} </td>".
     "<td> {$row['firmware']} </td>".
     "<td> {$row['method']} </td>".
     "<td> {$row['date']} </td>".
     "<td> {$row['stime']} </td>".
     "<td> {$row['etime']} </td>".
     "<td> {$row['wks']} </td>".
     "<td> {$row['result']} </td>".
     "<td> <a href=\"certificate.php?Customer={$row['cust']}&serial={$row['serial']}&manu={$row['manu']}&capacity={$row['capacity']}&method={$row['method']} \">Print</a>";


/**                     foreach($row as $key=>$value) {
                                echo '<td>',$value,'</td>';
                        }*/
                        echo '</tr>';
}
mysql_close($conn);


    ?>

As you can see above I have echo the $sql query and I get following on the screen.

SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result FROM hdds WHERE serial IN ( '5MQ3DJPM ','5MQ3DJPM ','5MQ3DJPM ','') ;

If I run this query I am getting rows back.

But with PHP nothing is returned, there are no errors.

But if I replace the php query with static query then it works, but I want the query to work with the variable.

Please [don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1); the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure. Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead. — elixenide, Jan 08 '17 at 01:49
Also, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — elixenide, Jan 08 '17 at 01:49
You should fix the above issues before doing anything else with your code. This code is extremely insecure. — elixenide, Jan 08 '17 at 01:50
This page is not exposed to the internet, this is just a little internal page, I just need your help to fix what I asked above — user2107349, Jan 08 '17 at 01:52
Writing bad code is still not a good idea, no matter how you intend to use it. You should know that questions and answers here are intended to be helpful to future users, not just the person asking the question. Demonstrating insecure code doesn't help anyone. — elixenide, Jan 08 '17 at 01:58
Also, you're running two `while` loops over your result set without resetting the cursor. That doesn't work. When the first loop ends, that means `mysql_fetch_array()` isn't returning additional rows because you're at the end. You can't just call it again and expect to get more data. — elixenide, Jan 08 '17 at 02:01
Please, leave literal query in your code, test with it. Test same query in MySQL command line interface. Output results in very raw form first (dump variable). Read thoroughly how results should be read into variables. Questions like "here are 50 lines of code, help me to locate error" are discouraged. — George Sovetov, Jan 08 '17 at 11:51

Phil · Accepted Answer · 2017-01-08T02:10:04.310

<?php

$codes = $_POST['codes'];

// get as array like so
$barcodes = explode("\n", $codes);

// build up string of barcodes
$barcode_str = "";
$prefix = '';
foreach ($barcodes as $barcode){
    $barcode_str .= $prefix . "'" . $barcode . "'";
    $prefix = "," ;
}
$dbhost = 'localhost:3036';
$dbuser = 'xxxx';
$dbpass = 'xxxx';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db('disks', $conn);
$sql = "SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result
       FROM hdds WHERE serial IN '$barcode_str') ;";

echo $sql;


$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}

$result = mysql_query($sql, $conn);
while($row = mysql_fetch_array($result)) {
echo $row['manu'];
}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{

/** Lets remove 20000000000000 bytes values from capacity array */
preg_match('/\[(.*?)\]/', $row['capacity'], $matches);
if (isset($matches[1])) {
    $row['capacity'] = $matches[1];
}
echo '<tr>';
echo
     "<td> {$row['manu']} </td>".
     "<td> {$row['model']} </td>".
     "<td> {$row['serial']} </td>".
     "<td> {$row['capacity']} </td>".
     "<td> {$row['firmware']} </td>".
     "<td> {$row['method']} </td>".
     "<td> {$row['date']} </td>".
     "<td> {$row['stime']} </td>".
     "<td> {$row['etime']} </td>".
     "<td> {$row['wks']} </td>".
     "<td> {$row['result']} </td>".
     "<td> <a href=\"certificate.php?Customer={$row['cust']}&serial={$row['serial']}&manu={$row['manu']}&capacity={$row['capacity']}&method={$row['method']} \">Print</a>";


/**                     foreach($row as $key=>$value) {
                                echo '<td>',$value,'</td>';
                        }*/
                        echo '</tr>';
}
mysql_close($conn);


    ?>

You're using a deprecated function, meaning it will show an error on PHP. Try converting to MySQLi.

I've modified your structure so you use the connection in all queries, and modified the query so it definately runs. You weren't using the connection when running a query.

an explanation for this would be beneficial and would avoid others from having to compare with both codes as to what's different/changed/added. — Funk Forty Niner, Jan 08 '17 at 02:03
I've edited with a new query. Try using this, however: ```sql = "SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result FROM hdds WHERE serial = '$barcode_str'";``` @user2107349 — Phil, Jan 08 '17 at 02:10
Phil, query must have Where serial IN clause because there are mutiple serials to match in the DB and return the matching ones, I have tried your query it does not work. — user2107349, Jan 08 '17 at 02:14
Phil, in PHP If I try static query sql = SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result FROM hdds WHERE serial IN ( '5MQ3DJPM ','5MQ3DJPM ','5MQ3DJPM ','') ; It works fine, but with sql = "SELECT cust, manu, model, serial, capacity, firmware, method, date, stime, etime, wks, result FROM hdds WHERE serial IN ($barcode_str) ;"; does not work — user2107349, Jan 08 '17 at 02:19
Phil, I have managed to fix it but I had to remove empty spaces from barcode_str and also remove last 3 three ,'' characters, now it works like a treat, special thanks to you mate :) — user2107349, Jan 08 '17 at 16:40

PHP mysql query is not working in PHP but works fine in MYSQL

1 Answers1