MySQL | Dynamically Accessing a Table

**reci** · 06-05-2010 09:23 PM

I have single database with multiple tables named, c1, c2, c3, and so on. I'm trying to access a certain table with the following query:

PHP Code:


$query="SELECT * FROM c'$id'";

in which $id determines which table to access.

This throws "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource".

I've also tried it two other ways:

PHP Code:


$query="SELECT * FROM c.'$id'";

PHP Code:


$query="SELECT * FROM c+'$id'";

Is it possible to do this, or is there another way to get around it?

Thanks.

**exdreamer** · 06-05-2010 10:10 PM

PHP Code:


//concatenate the strings

$cid = 'c'.$id;

//use the string as the table name

$query = "SELECT * FROM $cid";

If you were going to do it in a line, you could do:

PHP Code:


$query = 'SELECT * FROM c'.$id';

**khh** · 06-06-2010 11:21 AM

Pretty sure this would work too:

PHP Code:


$query = "SELECT * FROM c$id";

Generally, when you meet a problem like this, it can be a good idea just to print the string or whatever, so you can look at it and see why it's failing. Here it'd be because it read "SELECT * FROM c'1'" or somthing like that :p

**ninja9578** · 06-06-2010 02:48 PM

khh is corrent.

**reci** · 06-09-2010 04:52 PM

Thanks everyone! This thread can be locked

**reci** · 06-09-2010 08:51 PM

Never mind about locking it! I have another problem.

PHP Code:


$query="INSERT INTO c$p ('id', 'Date', 'Name', 'Email', 'Entry') VALUES (NULL, NULL, '{$_POST[fname]}', '{$_POST[femail]}', '{$_POST[fentry]}')";



if(!mysql_query($query))

{

    die('Error: ' . mysql_error());

}

The error report is:
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 ''id', 'Date', 'Name', 'Email', 'Entry') VALUES (NULL, NULL, 'sample name', 'samp' at line 1

Help?