MySQL Not filtering SELECT statement properly

Question

$result->num_rows returns a value of 64(all records) when it should return a value of 29 whereas $counter returns a value of 0 suggesting no records. I'm thinking it's an issue with my prepared statement but I'm not entirely sure what it could be.

Ajax

$.ajax
({
  url: 'query.php',
  type: 'POST',
  data: {Category: cat},
  //dataType: 'json',  
  success: function(result) 
  {  
    if(result) 
    {   
      console.log(result);          
    }
  },
  error: function() 
  {
    alert("error");
  }      
}); // end ajax call

PHP

<?php
require '_conn/connection.php'; 

$stmt = $conn->prepare("SELECT * FROM portfoliotb WHERE Category = ?");
$stmt->bind_param('s', $cat); 
$cat = $_POST['Category'];
$stmt->execute();

$result = $stmt->get_result();


if ($result->num_rows > 0) 
{
    echo $result->num_rows.",";
} 
else 
{
    echo "0 results";
}

$records = [];
$counter=0;

while( $row = mysql_fetch_row( $result ) )
{
    $counter++;

    //$records[] = array('Id' => $row['Id'], 'Category' => $row['Category'],'Directory' => $row['Directory'],'Description' => $row['Description'],'Code' => $row['Code']);
    //array_push( $records, $row['Category'] );   
    //array_push( $records, array("Id"=>$row['id'],"Category"=>$row['Category'],"Directory"=>$row['Directory'],"Description"=>$row['Description'],"Code"=>$row['Code'])); 
}

echo $counter;
//echo json_encode( $records );

?>

Start with this: https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php. No mysql* functions! So use this: http://php.net/manual/en/mysqli-stmt.fetch.php (mysqli-stmt-fetch) instead of mysql_fetch_row. I am not 100% certain, but I seem to remember to mixing mysql* and mysqli* produces weird results. And mysql* are deprecated anyway, so... — Nic3500, Apr 22 '18 at 21:02

score 0 · Answer 1 · answered Apr 23 '18 at 14:35

0

The problem was solved by removing the line

while( $row = mysql_fetch_row( $result ) )

and replacing it with the line

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

Thanks for the help

answered Apr 23 '18 at 14:35

Ismail

25
5

MySQL Not filtering SELECT statement properly

1 Answers1