Php variables as database table names

PHP
1

I would like to replace the table name in a database with a php variable so that it can be store in a variable file included on all pages, and then all the instances of the table name in the website can be changed simply by changing the value of the one variable.

I have never done this before (is it possible?), and would like to know what the proper syntax would be? For example, if I set $name = “user_name”, then could I use it in all mysql queries such as “SELECT … FROM $name WHERE …” ?

I tried the above code and it didn’t work.

Thanks

2

What are you using to connect to your database? mysql, mysqli, PDO?

You can substitute variables for table names.

Show us the full code you are using, maybe there is another issue you are hitting (using reserved words for table and field names is a notable one)

3

SQL queries are just strings. So you can build them however you like, and if the resulting string is correct it will work. Echo your query to debug.

If you are using some abstraction such as PDO then you won’t be building those strings yourself

4

I am not using PDO. The original table names were not reserved words, the two tables that I did this with were “locations” (which I gave the variable name $locations) and “user_info” which I gave the variable name $information. By the way, before I replaced the table names in my mySqL queries with the variables, everything worked fine. I have the following in my variables.php file (in the includes folder)

$locations = "locations";
$information = "user_info";

and I have the following at the top of the individual pages:

<?php
session_start();
require("inc/variables.php");
require_once("inc/connect_db.php");
?>

Thanks

5

Can you post the contents of connect_db.php please?

6 
$mysql_link = mysql_connect("localhost","root","jfisuela")
or die("Cannot make a connection to the database");
mysql_select_db("sampleDatabase",$mysql_link)
or die("Cannot select the database");

I had no issues with my queries before I replaced the table names with variables. :confused:

7

Well, you’re not using any of the variables you set in there.

8

Doh, well you wouldn’t would you! My bad, I’m an idiot.

Can you post the sections of code where you execute the queries please?

9

You’re probably going to be better off using constants too, PHP will stop you from accidentally reassigning them a new value.


<?php
define('USER_TABLE', 'user');

$sql 'SELECT id, name FROM ' . USER_TABLE . ' LIMIT 5;'
10

I think I know what my issue was, because in your example you are concatenating the variable to the rest of the query string. I was using the following:

$query = "SELECT user_name FROM $information WHERE ...";

.

Should it be

$query = "SELECT user_name FROM " . $information . " WHERE ...";

?

This is where my total lack of experience shows up!

11

If your string is double quoted then it will expand the variable name all the same.

Echo $query onto the page and inspect it,

12

Both of your examples are the same thing, and they both should work. :confused:

13

Got it. Thank you. Problem solved … and I am embarrassed to say it is because one of my header includes was missing a link to the variable file. :blush:

14

Excellent, I’m glad you nailed it. :wink:

15

That is exactly why you want to set error_level to E_ALL (or even -1) on development machines (not on production machines, it will just clutter the logs), so you get a warning for undefined variables. Makes it a lot easier to spot errors like these :slight_smile:

16

Also why I suggested echoing the SQL to see if it looked right. No one ever wants to do this though.

17

Yeah, with you on that - we seem to be banging out this simple message over and over ad infinitum.

It really is the starting place if you want to “divide and conquer” code errors when you are not sure if;

a) your html form is bust
b) your PHP is badly written
c) your SQL statement is badly written,
and my favourite…
d) your database does not even have any matching data, so always returns nothing no matter how much you fiddle with a) b) and c) above.

Is a carefully crafted message and “how to” worthy of a sticky I wonder?

18

If people read them it’s worth it. Basic debugging methodology is a good skill to learn; a lot of people make assumptions about where the problem isn’t.

19

Yeah, debugging is a skill you learn, but I wish someone had showed me, I had to work it out logically myself - mixed in with tricks I learned hanging around here.

Even if there was a sticky “how to debug your code”, ppl would not generally read it - but at least there’d be a link which would just need the intro “read this first, then tell us what errors you are still getting <link>”.

Overall though, its less about “how to debug your code”, its more about “how to progressively develop your code proving to yourself after each step that what you planned to happen actually did happen before you go off and complicate it even more” - which is an entirely different mindset.