Dynamic table names in PHP-SQL commands?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
MattSharp
Forum Commoner
Posts: 62
Joined: Wed Apr 24, 2002 2:25 pm

Dynamic table names in PHP-SQL commands?

Post by MattSharp »

Is it possible to use a variable name as the table name in an SQL command with PHP.

Like:

SELECT * from $table.......

When I tried this I get an error and I am wondering if there is something special I need to do to make this work.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Could you give us a bit more of your code as if $table is defined it should work.

Mac
toms100
Forum Contributor
Posts: 119
Joined: Wed Feb 26, 2003 10:29 am
Location: Bristol,UK

Post by toms100 »

the sql code you execute upon a mysql database is merely a text string szo can contain variables. try doing something like this

Code: Select all

<?php
$sql = "SELECT * FROM ".$table
echo $sql //just to check that the string is as intended
mysql_query($sql)

?>
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

Yes, this is very possible. I do it all the time. MattSharp just forgot to close his variable, so to speak.

To do what you want, I'd do this:

Code: Select all

<?php

$table = " ";  //Whatever table is.  I'm assuming you'll be using either $_GET or $_POST

$sql = "select * from ".$table."";
$act = mysql_query($sql);

?>
Basically, just enclose the variable in ". ." :D
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Be aware of possible security risks if you use variables. It might allow a site visitor to perform queries on any table in your database - set access level = 'admin' in a user table?

You could tighten things up by defining an array of allowed table names (or a text string - whatever) then check that $table is a value in the list before proceeding with the query.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Jim wrote:Yes, this is very possible. I do it all the time. MattSharp just forgot to close his variable, so to speak.
He forgot the semi-colon, which would of course cause a parse error so you can just do:

Code: Select all

$sql = "select * from ".$table;
instead of

Code: Select all

$sql = "select * from ".$table."";
'cause you don't need that empty string at the end.

But if that isn't working then there's obviously something else going on in the code, so the full error and a bit more code would make solving the problem possible.

Mac
MattSharp
Forum Commoner
Posts: 62
Joined: Wed Apr 24, 2002 2:25 pm

Post by MattSharp »

Thanks for all the help guys. I determined the problem to be that I am using it in a function and I forgot about making variables global.

But now upon 2nd thought, this may open up a greater security whole. A global variable use to access a database.
Post Reply