0

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>";
halfer
  • 19,824
  • 17
  • 99
  • 186
John Beasley
  • 2,577
  • 9
  • 43
  • 89
  • 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

1 Answers1

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
silversunhunter
  • 1,219
  • 2
  • 12
  • 32