PHP run query off each array variable and return results in table

Question

I am trying to run a query off multiple array variables and display the results in a table.

The user selects 1 or more records, which includes BOL and CONTAINER. These selections are put in their own arrays and they are always an equal amount.

 <?php
   $bolArray = explode(',', $_POST['BOL']);
   $containerArray = explode(',', $_POST['CONTAINER']);

   $count = count($bolArray);  // to get the total amount in the arrays

I use a FOR loop to separate each value from the 2 arrays:

   for($i = 0; $i < $count; $i++)
   {
     $bol = $bolArray[$i];
     $container = $containerArray[$i];
   }

Here is the part where I'm stuck and probably where I am messing up.

I need to take each variable from the FOR loop and run query using both variables.

First, I'll start the table:

 echo "<table><thead><tr><th>BOL</th><th>Container</th></thead><tbody>";

Here is where I tried a FOREACH loop:

 foreach($containerArray as $container) // I am not sure if I am using this FOREACH correctly
 {

And now, the query. Please take note of the variables from the first FOR loop:

   $preQuery = "SELECT * FROM mainTable WHERE CONTAINER = '".$container."' AND BOL = '".$bol."'";
   $preRes = mysql_query($preQuery) or die(mysql_error());
   $preNum = mysql_num_rows($preRes);

I use a WHILE loop with a mysql_fetch_assoc:

   while($preRow = mysql_fetch_assoc($preRes))
   {
     echo '<tr>'
     echo '<td>'.$preRow[BOL_NUMBER].'</td>';
     echo '<td>'.$preRow[CONTAINER_NUMBER].'</td>';
     echo '<td>'.$preRow[ANOTHER_COLUMN].'</td>';
     echo '</tr>'
   }
 }
 echo '</tbody></table>';

 ?>

The query actually works. Problem is, it only returns 1 record, and it's always the last record. The user could select 4 records, but only the last record is returned in the table.

I tried to use the same query and paste it inside the first FOR loop. I echoed out the query and it displayed the same amount of times as the number of array values, but will only return data for the last record.

I do not understand what I am doing wrong. I just want to display data for each value from the array.

Edit

Here is what the code looks like when I throw the query in the first FOR loop:

 echo "<table class='table table-bordered'><thead><tr><th>BOL</th><th>Container</th></tr></thead><tbody>";
 for($i = 0; $i < $count; $i++)
 {
  $bol = $bolArray[$i];
  $container = $containerArray[$i];

  $preQuery = "SELECT BOL_NUMBER, CONTAINER_NUMBER FROM `intermodal_main_view` WHERE BOL_NUMBER = '". $bol ."' AND CONTAINER_NUMBER = '".$container."'";
  $preRes = mysql_query($preQuery) or die();
  $preNum = mysql_num_rows($preRes);

  while($preRow = mysql_fetch_assoc($preRes))
  {
    echo '<tr>';
    echo '<td>'.$preRow[BOL_NUMBER].'</td>';
    echo '<td>'.$preRow[CONTAINER_NUMBER].'</td>';
    echo '</tr>';
  }
 }
 echo "</tbody></table>";

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). You will also want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 19 '14 at 20:40
You need to do it all in the first `for`-loop. Right now in that loop you are overwriting the first value with the second, the second with the third and so on. Only the last value will stick. — RST, Nov 19 '14 at 20:46
@JayBlanchard I should have stated that I was just using the basic of PHP and MySql. I have taken steps to prevent SQL injections and have also switched to Mysqli. Now back to my issue at hand... — John Beasley, Nov 19 '14 at 20:50
You are missing semicolons when you echo your opening and closing `` tags. There are cases when you can omit semicolons but it is good practice to always include them. — zack.lore, Nov 19 '14 at 20:54
@zack.lore minor typo. Nothing that prevents the query from running. — John Beasley, Nov 19 '14 at 20:59
@RST - Please see my edits. I attempted what you suggested, however, I am still only able to return data for the last record. Any thoughts? — John Beasley, Nov 19 '14 at 21:00
What does the post value look like? (value, value, value...) — silversunhunter, Nov 19 '14 at 21:02
@silversunhunter If you're referring to the values in the array, the first set in $bolArrary would be (value1, value2, value3). The containers in the second array $containerArray would be (valueA, valueB, valueC). — John Beasley, Nov 19 '14 at 21:05
try my answer and see if that returns the expected data. You would forgo the conversion to array and for loop. — silversunhunter, Nov 19 '14 at 21:07
is for($i = 0; $i < $count; $i++) { $bol = $bolArray[$i]; $container = $containerArray[$i]; } really in your code> if so you are overwriting those variables with eaach iteration. you need to build your sql statements in that loop. — Athens Holloway, Nov 19 '14 at 21:37
Are you sure `$_POST['BOL']`and `$_POST['CONTAINER']` are `arrays`? Did you setup the html code name like `name=BOL[]` and `name=CONTAINER[]`? — RST, Nov 19 '14 at 21:55

score 0 · Answer 1 · edited Feb 13 '20 at 19:51

0

I think you can use "IN" if your POST vars are comma separated.

   $preQuery = "
       SELECT * FROM mainTable
       WHERE CONTAINER IN ($_POST['CONTAINER'])
       AND BOL IN ($_POST['BOL'])
   ";

   $preRes = mysql_query($preQuery) or die(mysql_error());

   $preNum = mysql_num_rows($preRes);

Then go to your while loop....

This would omit the need for creating an array and looping it.

Also, you need to switch to PDO for your query, and switch to parameter binding. It will take all of an hour to learn.

edited Feb 13 '20 at 19:51

halfer

18,701
13
79
158

answered Nov 19 '14 at 21:03

silversunhunter

1,112
2
8
26

Edited the $_POST keys to match yours. – silversunhunter Nov 19 '14 at 21:06
Should I apply this in the first FOR loop? – John Beasley Nov 19 '14 at 21:07
I tried to use IN before and was unsuccessful. I don't PHP likes me using IN. – John Beasley Nov 19 '14 at 21:16
You would omit the for loop and use the comma separated values directly – silversunhunter Nov 19 '14 at 21:16
I would try it in your PHPMyAdmin if you are using that. run the query directly: SELECT * FROM mainTable WHERE CONTAINER IN (12,33,55,6) AND BOL IN (3,4,5,6). Are there words in the values? – silversunhunter Nov 19 '14 at 21:27
your suggestion will not produce the same set of data as his original query. your query will result in records with container and bol pairs in a record that do not exist post data – Athens Holloway Nov 19 '14 at 21:50

PHP run query off each array variable and return results in table

Edit

1 Answers1