How to deal in a PHP script with a SQL query within a function failing to find any results

[wake689]

Hi,
I have a function as below which works well when the query $queryItems finds a barcode but when the barcode does not exist in the barcode table the PHP script throws a

Warning: Invalid argument supplied for foreach() on line *** where the line no. corresponds to

foreach ($fromBarcode as $value){

$fromBarcode=getBarcode($barcode, $pdo);   
          
foreach ($fromBarcode as $value){
  echo $value.'<br>';
} 

          
function getBarcode($barcode, $pdo){
  
    $queryItems="
    SELECT 

    commodities.commodityName as Food, 
    genre.genreName as Genre,
    manufacturer.manufacturerName as Manufacturer, 
    packaging.packagingName as Packaging   

    FROM barcodes

    LEFT JOIN commodities
    ON barcodes.food=commodities.commodity_ID

    LEFT JOIN genre
    ON barcodes.genre=genre.genre_ID

    LEFT JOIN manufacturer
    ON barcodes.manufacturer=manufacturer.manufacturer_ID

    Left JOIN packaging
    ON barcodes.packaging=packaging.packaging_ID

    WHERE barcodes.barcode='$barcode'";
  
    //Prepare and execute the query       
    $stmtItems=$pdo->prepare($queryItems);
    $stmtItems->execute();

    //Cycle through the query result 
    foreach ($stmtItems as $value){

      $food=$value['Food'];
      $packaging=$value['Packaging'];
      $manufacturer=$value['Manufacturer'];
      $genre=$value['Genre'];
      
      return array($food, $packaging, $manufacturer, $genre);
          }       //close foreach ($stmtItems as $value){
           }     //close function

What is the best way to deal with this? I have tried checking the result and Googling but I can’t figure it out.
Thank you.

[m_hutley]

fetchAll your statement into a variable, so that it’s an array.

[droopsnoot]

Your function should really check whether or not the barcode was found, and return false if it was not. You could then just surround your foreach() loop with a if ($fromBarCode) { clause. At the moment if it doesn’t find anything, your function just silently completes without returning anything.

Your function only ever returns one barcode, though, so I don’t see the need for a foreach() in the calling code in any case. There’s no need to have it in the function either, as surely a given barcode will only ever return either zero or one row?

ETA as this is a PHP-related question, I’ll move it over there for more input.

[wake689]

Is the below what you mean? I haved probably misunderstood something. Because I now get
Fatal error: Uncaught Error: Call to undefined function fetch_all() referencing the line

$resultArray=fetch_all($stmtItems);

I have hard coded a barcode ‘ppp’ that I know exists in the database table barcodes (I know it’s not a valid barcode but I just inserted it into the database for testing purposes.

$barcode='ppp';
                  
$fromBarcode=getBarcode($barcode, $pdo);  
        
foreach ($fromBarcode as $value){
  echo $value.'<br>';
} 
          
function getBarcode($barcode, $pdo){
   
    $queryItems="
    SELECT 

    commodities.commodityName as Food, 
    genre.genreName as Genre,
    manufacturer.manufacturerName as Manufacturer, 
    packaging.packagingName as Packaging   

    FROM barcodes

    LEFT JOIN commodities
    ON barcodes.food=commodities.commodity_ID

    LEFT JOIN genre
    ON barcodes.genre=genre.genre_ID

    LEFT JOIN manufacturer
    ON barcodes.manufacturer=manufacturer.manufacturer_ID

    Left JOIN packaging
    ON barcodes.packaging=packaging.packaging_ID

    WHERE barcodes.barcode='$barcode'";
  
    //Prepare and execute the query       
    $stmtItems=$pdo->prepare($queryItems);
    $stmtItems->execute();
  
    $resultArray[]=fetch_all($stmtItems);
  
    //Cycle through the query result 
    foreach ($resultArray as $value){

      $food=$value['Food'];
      $packaging=$value['Packaging'];
      $manufacturer=$value['Manufacturer'];
      $genre=$value['Genre'];
      
      return array($food, $packaging, $manufacturer, $genre);
          }       //close foreach ($stmtItems as $value){
           }     //close function

Thank you for taking the time to look at this and to help me out.

[wake689]

By checking that the barcode is found do you mean eg

  if (!$stmtItems){
    echo 'No barcode found';
  }

in the code as below

    $queryItems="
    SELECT 

    commodities.commodityName as Food, 
    genre.genreName as Genre,
    manufacturer.manufacturerName as Manufacturer, 
    packaging.packagingName as Packaging   

    FROM barcodes

    LEFT JOIN commodities
    ON barcodes.food=commodities.commodity_ID

    LEFT JOIN genre
    ON barcodes.genre=genre.genre_ID

    LEFT JOIN manufacturer
    ON barcodes.manufacturer=manufacturer.manufacturer_ID

    Left JOIN packaging
    ON barcodes.packaging=packaging.packaging_ID

    WHERE barcodes.barcode='$barcode'";
  
    //Prepare and execute the query       
    $stmtItems=$pdo->prepare($queryItems);
    $stmtItems->execute();

  if (!$stmtItems){
    echo 'No barcode found';
  }
  
    //Cycle through the query result 
    foreach ($resultArray as $value){

      $food=$value['Food'];
      $packaging=$value['Packaging'];
      $manufacturer=$value['Manufacturer'];
      $genre=$value['Genre'];
      

      return array($food, $packaging, $manufacturer, $genre);
          }       //close foreach ($stmtItems as $value){
           }     //close function

This was just to test if I have correctly understood what you said. The above gives the notice
Notice: Undefined variable: resultArray in and references the line
foreach ($resultArray as $value){

The barcode ‘ppp’ does exist in the barcode table.

I am now trying also to implement the rest of your answer.
Thank you for taking the time to look at this and to help me out.

[droopsnoot]

You need to check whether any rows are found, and only attempt to retrieve the array and return it if there were some. If there were not, that’s when you return false.

So the pseudo-code for that part of the function would be:

execute query
any rows found? 
  yes - return an array with the results
  no - return false

Your calling code could then be something like

if ($barcode = getBarcode($barcode, $pdo)) {
  // display the product information
} else {
  // display a message to say it wasn't found
}

The notice you’re getting there is because you’ve removed the line that actually retrieves the results from the database - you execute the query, then you try to use the results, but you didn’t retrieve them.

In your previous post, you have a syntax error. This line

$resultArray[]=fetch_all($stmtItems);

should read

$resultArray = $stmtItems->fetchAll();

You should also read up on Prepared Statements, instead of concatenating the barcode into your query like that. You use prepare(), but then don’t take advantage of it. Make the last line of the query read

WHERE barcodes.barcode= ? ";

and change the execute call to read

$stmtItems->execute(array($barcode));

[m_hutley]

@wake689

Is the below what you mean?

@wake689

$resultArray=fetch_all($stmtItems);

No, when i said fetchAll, i meant fetchAll.

$resultArray = $stmtItems->fetchAll();
Then $resultArray will be an array unless there was a flat-out error in your query. Having no rows returned is not an error, it is a successful return of 0 records.

If $resultArray is an array, foreach will work on it (if there are 0 records returned, $resultArray is an empty array, and foreach will simply do nothing and proceed to the next code line).

As Droop says, have a read on Prepared statements; that would help prevent problems with your input crashing the query, as well as improve security.

[Gandalf]

Probably the best place to learn about PDO is https://phpdelusions.net/pdo

[anon321]

The problem is that if the query doesn’t return any results, the foreach loop in your function doesn’t get entered, and therefore, the operation doesn’t return anything. When you later try to iterate over the result of the process, you’re attempting to iterate over null , leading to the warning.

A simple fix is to return an empty array when no results are found. This way, the outer foreach loop won’t enter when there are no results, and you won’t encounter a warning.

Made some changes to your code

function getBarcode($barcode, $pdo){

    $queryItems = "
    SELECT 

    commodities.commodityName as Food, 
    genre.genreName as Genre,
    manufacturer.manufacturerName as Manufacturer, 
    packaging.packagingName as Packaging   

    FROM barcodes

    LEFT JOIN commodities
    ON barcodes.food=commodities.commodity_ID

    LEFT JOIN genre
    ON barcodes.genre=genre.genre_ID

    LEFT JOIN manufacturer
    ON barcodes.manufacturer=manufacturer.manufacturer_ID

    Left JOIN packaging
    ON barcodes.packaging=packaging.packaging_ID

    WHERE barcodes.barcode='$barcode'";
  
    //Prepare and execute the query       
    $stmtItems = $pdo->prepare($queryItems);
    $stmtItems->execute();

    $results = array();
    //Cycle through the query result 
    foreach ($stmtItems as $value) {
      $food = $value['Food'];
      $packaging = $value['Packaging'];
      $manufacturer = $value['Manufacturer'];
      $genre = $value['Genre'];
      
      $results[] = array($food, $packaging, $manufacturer, $genre);
    }
    
    // Return the results, or an empty array if no results are found
    return $results;
}

Modify the outer foreach loop because the function now returns an array of arrays, even when results are found:

$fromBarcode = getBarcode($barcode, $pdo);   

foreach ($fromBarcode as $result) {
  foreach ($result as $value) {
    echo $value . '<br>';
  }
}

So, when no results are found, getBarcode will return an empty array, and the outer foreach loop won’t be entered.

I have not tested the code, but I hope it works and solves your problem. good luck.

[m_hutley]

@anon321

    $results = array();
    //Cycle through the query result 
    foreach ($stmtItems as $value) {
      $food = $value['Food'];
      $packaging = $value['Packaging'];
      $manufacturer = $value['Manufacturer'];
      $genre = $value['Genre'];
      
      $results[] = array($food, $packaging, $manufacturer, $genre);
    }

    // Return the results, or an empty array if no results are found
    return $results;

Having looked at that again… and a little bit more awake now… this whole thing can be reduced to a single line.

return $stmtItems->fetchAll(PDO::FETCH_NUM);

NOTE: The array results will be in the order your query specifies them; so if you want packaging before genre, put that in the right order in the query.

[Thallius]

@anon321

$results[] = array($food, $packaging, $manufacturer, $genre);

Such “Returns” from backends are 1970th style. You should not return plain values. If you some time change your query you might get completely confused why your code is not longer working because the order of the values might change or there might be more or less values.

Fetch your rows as objects and return the object as a JSON string. Then work on the object in the frontend by parsing the JSON string back to an object.

[m_hutley]

@Thallius

Such “Returns” from backends are 1970th style

Slightly hyperbolic there.

@Thallius

You should not return plain values.

Not sure what you mean by ‘plain’ values.

@Thallius

why your code is not longer working because the order of the values might change or there might be more or less values.

fetchAll (operating in FETCH_NUM) will return a full (dense) array regardless of potential NULL states; but order of values may indeed change, which is why I dont like FETCH_NUM, and would FETCH_ASSOC instead.

@Thallius

Fetch your rows as objects and return the object as a JSON string. Then work on the object in the frontend by parsing the JSON string back to an object.

You lost me here. So I fetch my database result as an associative array; then json encode it, to hand a string back to my other PHP function (not sure what you mean by ‘frontend’ here), which has to json decode it to… get the original array.
Why?

[Thallius]

@m_hutley

You lost me here. So I fetch my database result as an associative array; then json encode it, to hand a string back to my other PHP function (not sure what you mean by ‘frontend’ here), which has to json decode it to… get the original array.
Why?

Ok, I thought the output of this function is returned from a frontend call. Sorry I am too deep in my apps where backend is always only REST API used to fetch data.

But even if you just return the values to another php function I would always fetch as PDO::FETCH_OBJ as this is much better to handle and to read then an array of values.

If you use your result in the calling function it will look like

xxx = $result[0];

No one will know what this is. So he needs to look at the called function to know what it is returning.

With my result if will look like

xxx = $result->food;

Easier or?

[wake689]

Thank you everyone for all your interest and input and patient explanations.

And thank you for catching that I had not done the prepared statements correctly (ie not in a secure way). The SQL query is for me a complicated one. I got so excited when I found out that it worked (in php MyAdmin) that I got carried away and messed up the security.

I have hurriedly corrected that and will now go through the above posts and implement them to get the code to work.

[m_hutley]

@Thallius

Easier or?

I absolutely agree that it’s better to have named output for tracking - whether you do so as an associative array ($result['food']) or an object ($result->food), to me at least, is personal preference. The only reason I did it with NUM before was because that was how the OP’s function was returning.