CODEWITHSUNDEEP
phpmysqlarraysloopsmultidimensional-array
user1009573
user1009573

Reputation: 113

PHP - Loop through multiple tables and populate a multidimensional array

I need to loop through multiple tables in my database and then populate multidimensional arrays with their contents. The code I have at the moment is returning a MySQL error. I've tried searching around but everything I find does not match my needs.

Code

// The arrays that will hold the data we wish to collect
$ids = array();
$data = array();

// The database tables we want to search through
$databaseTables = array("table1", "table2", "table3", "table4");

for ($x = 0; $x < 4; $x++) {
    // Create a new array for this table's data
    $ids[] = array();
    $data[] = array();

    // Query the database
    $query = "SELECT * FROM '$databaseTables[$x]'";
    $result = @mysql_query($query) or die(mysql_error());

    // Collect the data
    while ($row = mysql_fetch_array($result)) {
        $ids[$x][] = $row['id'];
        $data[$x][] = $row['data'];
    }
}

Thank you for your help.

Edit

The MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table1'' at line 1

Upvotes: 0

Views: 863

Answers (2)

Jeff Lambert
Jeff Lambert

Reputation: 24661

Dont quote table names, use the backtick instead:

$query = "SELECT * FROM `$databaseTables[$x]`";

If you have the mode ANSI_QUOTES turned on you can double quote table names as that causes quotes to be identifier quote characters (vs a string quote character), but by default this is not set.

Sources:

  1. http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
  2. http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html
  3. http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_ansi_quotes

As an aside, I would advise you to not use the mysql_* functions any longer. They are deprecated and will be removed in future versions of PHP. See the introduction page to mysql.

Instead, I would use PDO or mysqli.

Upvotes: 2

smcjones
smcjones

Reputation: 5600

You are using single quotes. In MySQL you need to use back-ticks. You can also generally leave back-ticks out so long as you do not anticipate any conflicts via a MySQL reserved word.

Further, if you have or die at the end you really don't need the @ error suppressor. MySQL functions don't print out errors but if you add or die at the end you probably want to see what happens. Either remove or die or remove the @ error suppressor.

Upvotes: 1

Related Questions