What is the best loop to use

I have some code that needs to loop through some code, but I am not sure what would be the best loop to use. At the moment, the if statement is only returning 1 item and there are several. I would be grateful if someone could help me with this as I am quite new to this form of coding. Many thanks

$sql = "SELECT * FROM files WHERE department = '$dept' AND boxref = '$items'";
   $result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
     if($result = mysqli_query($conn, $sql)){
         if(mysqli_num_rows($result) > 0){
            while($row = mysqli_fetch_array($result)){
                $fileid = $row['id'];
                $fstatus = $row['filestatus'];
                $fitem = $row['custref'];
                $boxitem = $row['boxref'];
              }
            }
           }
          
          if($fstatus != '9') {
            echo 'ERROR:';
            echo ' File: ' . ' ' . $fitem . ' ' . ' In box: ' . $boxitem . '<br />';
          } else { 
            echo 'bla bla';
          }

Try this:

	$aFound = array();
	while($row = mysqli_fetch_array($result))
	{
	  if('9'===$row['filestatus'])
	  {
	    $aFound[]  = [
	      $fileid  => $row['id'],
	      $fstatus => $row['filestatus'],
          $fitem   => $row['custref'],
	      $boxitem => $row['boxref']
	    ];
	  }//endif
	}//endwhile
	
	if( count($aFound) ):
		echo '<pre>';
			print_r($aFound);
		echo '</pre>';
	else:
  	echo 'Unable to find any filestatus ???';
	endif;	
1 Like

Hi John
Thanks for reply. Do i use your code in place of mine or do I have to join it with mine. Thanks

So, for the record, what your problem is is not which loop to use (the answer to that is ‘the one that works’), but it’s what you’re doing with the data inside the loop that is the problem.

Consider your lines.

So. The first time through the loop, $fitem gets set to the current row’s custref. Great.
What happens the next time through?
And the next?

What John’s code does is stuff the rows into an array, which allows you to interact with all of the rows after you’re done.

Alternatively, refine your query properly (more WHERE conditions to exclude the ones with status 9, and only retrieve the fields you’re interested in instead of *), and fetch_all instead of fetch_array to avoid the loop altogether.

2 Likes

What I usually do to ensure I can always compare the two scripts is something like this:

$sql = "SELECT * FROM files WHERE department = '$dept' AND boxref = '$items'";
 $result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
 if($result = mysqli_query($conn, $sql))
 {
  if(mysqli_num_rows($result) > 0)
  {
    if( true ) // TOGGLE true/false
    {
      // NEW SCRIPT
    }else{ 
      // OLD SCRIPT 
    }//endif
  }// endif(mysqli_num_rows($result) > 0)
}//endif($result = mysqli_query($conn, $sql))

Once the NEW SCRIPT is OK then delete if(TRUE), OLD SCRIPT, else, endif, etc

1 Like

Why can I not just use a loop in the if statement to achieve result?


if($fstatus != ‘9’) {
loop start
echo ‘ERROR:’;
echo ’ File: ’ . ’ ’ . $fitem . ’ ’ . ’ In box: ’ . $boxitem . ‘
’;
loop end
}

You can, if you’re trying to find said errors. Or, you can just tack " AND filestatus != 9" onto the end of your query and not have those rows retrieved from the database in the first place.

2 Likes

Could you please show me how to code loop in my if statement please.

Further to @m_hutley’s post:

I would try and modify the query to only return the required items.

// using PHP Heredoc strings - only linefeed after ____TMP
$sql = <<< ____TMP
   SELECT 
      * 
   FROM 
     `files` 
   WHERE
      `department` = "$dept'" 
   AND 
     `boxref` = '$items'"
  AND
     `filestatus` ="9"
____TMP;

echo '<br>' .$sql;

// ONLY line feed after trailing ;

Try copying and pasting the $sql into phpMyAdmim’s SQL/Query option and use FORMAT to make the $sql easier to read.

Sorry john this has just gone over my head. As in my previous comment, can I just do a loop in my if statement? Thanks

Yes but make sure you read and understand @m_hutley’s post: #4

2 problems john. Firstly, I need to retrieve those items to display to user what files are not equal to 9. ie, demofile01 etc. Secnondly, I do not know how to code the if statement with a loop, hence my post. If I use what m_hutley suggested, that would eliminate the files altogether which is not what I wanted. Thanks

I took m_hutley advice and did it this way which works ok. Can anyone see any problems with doing it this way. Thanks

$sql = "SELECT * FROM files WHERE department = '$dept' AND boxref = '$items' AND filestatus != '9'";
   $result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
     if($result = mysqli_query($conn, $sql)){
       if(mysqli_num_rows($result) > 0){
         while($row = mysqli_fetch_array($result)) {
            $fileid = $row['id'];
            $fstatus = $row['filestatus'];
            $fitem = $row['custref'];
            $boxitem = $row['boxref'];       
            echo 'ERROR: ' . ' ' . 'File: ' . '[ ' . $fitem . ' ]' . ' ' . 'Box: ' . '[ ' . $boxitem. ' ]' . '<br />';                
            }
            exit;
           }
          }

It looks OK.

Three minor things:

Firstly:

it looks as though both $fileid and $fstatus are not being used?

Secondly:

Try adding these statements at the top of the file because it may highlight problems which could be a future problem.

<?php 
  declare(strict_types=1);
  error_reporting(-1);
  ini_set('display_errors', 'true');
  // DELETE ABOVE THREE LINES WHEN NO ERRORS OR WARNINGS ARE SHOWN

Thirdly:

I have recently become a big fan of PHP Heredoc Strings for numerous reasons but the most is far easier to format strings.

// Old style:
$sql = "SELECT * FROM files WHERE department = '$dept' AND boxref = '$items' AND filestatus != '9'";

// Using PHP HereDoc:
// ESSENTIAL - ONLY Linefeed after ____TMP
$sql = <<< ____TMP
  SELECT 
    * 
  FROM 
    files 
  WHERE 
    department = '$dept' 
  AND 
    boxref = '$items' 
  AND
    filestatus != '9'
  ;
____TMP;
// ESSENTIAL: ONLY ; after ____TMP

// Same for the other string:
$tmp = <<< ____TMP
  ERROR: File: $fitem  Box:  [{$boxitem}]
 <br />               
____TMP;

echo $tmp; 
            

Thanks very much for advice John. It has helped me move forward with my php knowledge. I would also like to thank m_hutley for his comments and suggestions. Thanks

1 Like

Now that you have it working, have a look at prepared statements, rather than just copying your variables directly into the query string. I can’t see what security checks you have done on those variables prior to putting them in the query, but prepared statements among other things will assist with that.

Not familiar with prepared statements. Do you have a good tutorial link I could use? Cheers

I use PDO, so the method is a little different, but if you scroll down this forum for a few topics you’ll find plenty of code that uses the technique.

ETA: here you go, from a couple of threads down:

Unfortunately an incorrect example was selected…

This later post in the same topic shows the correct script:

1 Like

Thank you very much