PHP/MYSQL add column with nested foreach

Question

I have 2 databases, SYSTEM and CUSTOMER. In SYSTEM there is a stafflist table with usernames. Each table in CUSTOMER is a customers name.

What I am trying to do is foreach table in Customer, foreach 'username in system.stafflist', ALTER TABLE '$table' ADD '$username'

Here is my code:

$sql3 = "SELECT username from system.stafflist";
$result3 = $conn->query($sql3);
$sales = array();
    while($row3 = $result3->fetch_array()) {
        $sales[]="x".$row3['username'];
    }   

$sql = "SHOW TABLES from customer";
$result = $conn2->query($sql);
$table = array();
    $i=0;
    while($row = $result->fetch_array()) {
        $table[] = $row[$i];
    }
var_dump ($table);
var_dump ($sales);

foreach ($table as $table){
    foreach ($sales as $sales){
        $sql2 = "ALTER TABLE $table ADD $sales VARCHAR( 12 );";
        if ($conn2->query($sql2) === TRUE) {} else {echo "Error: " . $sql2 . "<br>" . $conn2->error;}
    }
}

The var_dump shows two proper arrays:

array (size=62)
  0 => string 'rita' (length=4)
  1 => string 'sadk' (length=4)
  2 => string 'sand' (length=4)
  3 => string 'sany' (length=4)
  4 => string 'sbdk' (length=4)
  5 => string 'sdkg' (length=4)

array (size=54)
  0 => string '0007' (length=4)
  1 => string '0006' (length=4)
  2 => string '0005' (length=4)
  3 => string '0004' (length=4)
  4 => string '0003' (length=4)

But there are two errors:

Error: ALTER TABLE rita ADD x2304 VARCHAR( 12 ); Duplicate column name 'x2304'

And

Warning: Invalid argument supplied for foreach()  <--the $sales foreach

If I comment out the $sales foreach loop and change my ALTER statement column name to 'abc' instead of $sales then I can successfully add 'abc' to each table.

What is causing the $sales foreach to give errors?

Louis Loudog Trottier · Accepted Answer · 2017-02-09T02:21:00.790

in the foreach loop you are overwrting your array

foreach ($sales as $sales)

should read

foreach ($sales as $sale)

You will have to change your $sale varaible in the query to reflect that change

  $sql2 = "ALTER TABLE $table ADD $sale VARCHAR( 12 );";
                                  ^^^^^

test that to see if it fixe you sql error at once.

EDIT: i saw you are foing the same thing with tables

foreach ($table as $table){

find another name for your array. ei $tables.

Also, this looks funny to me since you are not incremating your counter but you can just add a 's' to your array here:

$tables = array(); //table changed to tables
       $i=0;
        while($row = $result->fetch_array()) {
            $tables[] = $row[$i]; //table changed to tables
        }

... and so on. Little mistake, big conseuences :P

unless you wanted to only get the 1st col of the row. in that case you can use reset() like so:

$tables[] = reset($row);

or simply calling the array index 0, no need for th $i.

$tables[]=$row[0];

See php manual for proper way to define foreach loop : http://php.net/manual/en/control-structures.foreach.php

I wouldn't be surprised if my code looks funny to people, I take no claim to being a good coder, but give it my best and learn as I go. You were right about the $sales as $sales and $table as $table, after adjusting this everything works. Strange thing is that the $table loop never gave me issues. Anyways it is working. Also thank you for the aditional information. — mochaMame, Feb 09 '17 at 02:28
Well you are on a good start, PHP.net is a great source of information. I did'nt bother telling about sql injection because you are using internal data but before jumping into databse from user input it is imperative you learn about prepared statement. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 for the rest, keep it up. :) And the way you posted you question was , in my opinion, well formed. bravo! — Louis Loudog Trottier, Feb 09 '17 at 02:30

PHP/MYSQL add column with nested foreach

1 Answers1