PHP - Loop through multiple tables and populate a multidimensional array

Question

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

what is the mysql error being returned?

Jeff Lambert
– Jeff Lambert

2014-07-11 13:44:01 +00:00
Commented Jul 11, 2014 at 13:44 — Jeff Lambert
– Jeff Lambert, Commented Jul 11, 2014 at 13:44
Edited the post with the error.

user1009573
– user1009573

2014-07-11 13:47:31 +00:00
Commented Jul 11, 2014 at 13:47 — user1009573
– user1009573, Commented Jul 11, 2014 at 13:47

Jeff Lambert · Accepted Answer · 2014-07-11 13:57:04Z

2

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:

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.

edited Jul 11, 2014 at 13:57

answered Jul 11, 2014 at 13:48

Jeff Lambert

24.7k4 gold badges72 silver badges98 bronze badges

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

5 Comments

user1009573 Over a year ago

Thanks! I will accept this answer when it lets me in a few minutes.

Daniel W. Over a year ago

Don't do this at all: $query = "SELECT * FROM '$databaseTables[$x]'";, properly concatenate variables with a dot: $query = "SELECT * FROM " . $databaseTables[$x];

smcjones Over a year ago

Why? When using double quotes you can use curly brackets {} and this is much cleaner than concatenating with a dot.

Jeff Lambert Over a year ago

@smcjones Interpolating variables inside of double quoted strings like that used to bring about a performance penalty. I don't think that is the case anymore, or at least it's not as big as it used to be

smcjones Over a year ago

@watcher Fair enough. My opinion is that the performance penalty is small, memory is cheap, and the biggest bottleneck for a PHP application is rarely the code. I understand the drive for efficiency but speed and ease of reading can also be important. If I were to eliminate inefficiencies in PHP it would be in looping and using wasteful functions. If I wanted to eliminate all inefficiencies, I would probably choose a different language.

smcjones · Accepted Answer · 2014-07-11 13:54:43Z

1

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.

answered Jul 11, 2014 at 13:54

smcjones

5,6411 gold badge25 silver badges43 bronze badges

Collectives™ on Stack Overflow

PHP - Loop through multiple tables and populate a multidimensional array

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related