PHP: While loop only running once, should run many times

Question

First time tackling a project where i'm needing to pull data from one table (lets say 200 records/results), and based on the results of a certain column within that result set, i need to query one of my 5 other tables (which table i need to query isnt defined until i've made the first query)

I'm currently under the impression there is no way for me to use a JOIN of some kind to do this as i cannot know which table i need to join before the first set of results have come back.

so the solution i came up with was as follows (example code for simplicity sake)

$FirstTableVals = array();
$sql = ("SELECT * FROM TABLE_A");
$run = $con->query($sql);

if($run->num_rows > 0)
{
  while($row = $run->fetch_assoc())
  {
      foreach($row as $key => $value)
        {
            $FirstTableVals[$key] = $value;
        }

      $valueToSwitch = $FirstTableVals["VAL_TO_SWITCH"];
      //$SecondTable can be 1 of 5 different table names
      $SecondTable = $FirstTableVals["SECOND_TABLE_TO_QUERY"];

      switch ($valueToSwitch)
      {
        case"1":
              $sql = ("SELECT * FROM $SecondTable WHERE SOME_COLUMN = SOME_VALUE");
              $run = $con->query($sql);
              if($run->num_rows > 0)
              {
                  while($row = $run->fetch_assoc())
                  {
                     //save some values from the second table 
                  }
              }
                    //echo the results of TABLE_A and second table
             break;

        case"2":
              $sql = ("SELECT * FROM $SecondTable WHERE SOME_OTHER_COLUMN = SOME_OTHER_VALUE");
              $run = $con->query($sql);
              if($run->num_rows > 0)
              {
                  while($row = $run->fetch_assoc())
                  {
                     //save some values from the second table 
                  }
              }
                     //echo the results of TABLE_A and second table
             break;
        default:
              break;
      }
  }
 }

Now, the problem i'm running into is that once one of the "Second" sql queries is executed, after performing everything within the "Second" While loop, it will break out of the while loop its in and echo my values but stops there without breaking out of the switch statement and then running again, due to the "First" sql queries loop.

Essentially, this only seems to run for the first record inside of "TABLE_A" as opposed to looping again and executing the switch statement with "Second" sql queries for each record inside of "TABLE_A".

If any of this doesn't make any sense, please let me know and i'll do my best to elaborate on anything that may be confusing.

Really stumped with this one as it seems to me that should run as i've intended.

@IshtiyaqHusain I dont understand? could you please elaborate? — JakeMcclay
– JakeMcclay, Commented Feb 4, 2018 at 13:29
Which mysql driver are you using? I.e. how are you creating $con — Adam
– Adam, Commented Feb 4, 2018 at 13:38
@Adam the following is used to create my connection, $con = mysqli_connect("localhost","root","","xxxxxx"); which is in a file called db.php, that is included into all of my other php files. — JakeMcclay
– JakeMcclay, Commented Feb 4, 2018 at 13:40

Ishtiyaq Husain · Accepted Answer · 2018-02-04 13:39:58Z

3

You are overridding the run variable, thats why it breaks the loop. Please change your code like this:

$FirstTableVals = array();
$sql = ("SELECT * FROM TABLE_A");
$run1 = $con->query($sql);

if($run1->num_rows > 0)
{
 while($row = $run1->fetch_assoc())
 {
     foreach($row as $key => $value)
    {
        $FirstTableVals[$key] = $value;
    }

  $valueToSwitch = $FirstTableVals["VAL_TO_SWITCH"];
  //$SecondTable can be 1 of 5 different table names
  $SecondTable = $FirstTableVals["SECOND_TABLE_TO_QUERY"];

  switch ($valueToSwitch)
  {
    case"1":
          $sql = ("SELECT * FROM $SecondTable WHERE SOME_COLUMN = SOME_VALUE");
          $run2 = $con->query($sql);
          if($run2->num_rows > 0)
          {
              while($row = $run2->fetch_assoc())
              {
                 //save some values from the second table 
              }
          }
                //echo the results of TABLE_A and second table
         break;

    case"2":
          $sql = ("SELECT * FROM $SecondTable WHERE SOME_OTHER_COLUMN = SOME_OTHER_VALUE");
          $run3 = $con->query($sql);
          if($run3->num_rows > 0)
          {
              while($row = $run3->fetch_assoc())
              {
                 //save some values from the second table 
              }
          }
                 //echo the results of TABLE_A and second table
         break;
    default:
          break;
  }
 }
}

answered Feb 4, 2018 at 13:39

Ishtiyaq Husain

4244 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ishtiyaq Husain Over a year ago

Please make it as answer, if it solved your problem.

JakeMcclay Over a year ago

That worked, thanks so much!! i'll keep this in mind for future, i didnt realise if i overwrote it inside of itself that it would matter as its not executing the first query more than once. Never the less, This works!! Thank you thank you!!!!

Collectives™ on Stack Overflow

PHP: While loop only running once, should run many times

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related