Using PHP variables inside SQL statements?

Question

For some reason I can't pass a var inside a mysql statement. I have a function that can be used for multiple tables. So instead of repeating the code I want to change the table that is selected from like so,

function show_all_records($table_name) {

     mysql_query("SELECT * FROM $table_name");

etc, etc...

}

And to call the function I use show_all_records("some_table") or show_all_records("some_other_table")

depending on which table I want to select from at the moment. But it's not working, is this because variables can't be passed through mysql statements?

Obligatory: if the table name is dynamic, you should ensure it matches /^\w+$/ or similar before interpolating it into SQL, else you may be vulnerable to SQL injection attacks. — rjh
– rjh, Commented Apr 1, 2010 at 2:54

Andreas Bonini · Accepted Answer · 2010-04-01 02:21:53Z

4

The code you pasted must work. The possible reasons it doesn't are:

You are using 'SELECT * FROM $table_name' (single quotes instead of double quotes)
You misspelled $table_name

Try die("SELECT * FROM $table_name"); and you'll see exactly what's wrong!

answered Apr 1, 2010 at 2:21

Andreas Bonini

45k31 gold badges126 silver badges158 bronze badges

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

Comments

Ben · Accepted Answer · 2010-04-01 02:25:08Z

2

Try

$results = mysql_query("SELECT * FROM $table_name") or die(mysql_error());

and tell us what does it says. In theory $table_name should be parsed as a string, as it is inside "" and not ''

answered Apr 1, 2010 at 2:25

Ben

16.6k9 gold badges47 silver badges65 bronze badges

Comments

Marc B · Accepted Answer · 2010-04-01 02:49:27Z

0

Your query looks fine. Can't see why that wouldn't work. Have you connected to the DB properly with mysql_connect() elsewhere in your code? Did the connection get established? Does the user you're connecting as have SELECT permissions on the table you're trying to access?

answered Apr 1, 2010 at 2:49

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

sçuçu · Accepted Answer · 2013-08-14 14:04:59Z

0

My recent experience with SQL, PHP, and MySQL showed me that I should write SQL statements like below to have them work, whether inside PHP scripts or from SQL interaction in phpMyAdmin. Otherwise my queries did not work. i just modify same code you have posted above to generalize a bit what i come up with. and give general rules that work.

beware single quotes around any value(record value not a db, table or column name) whether data representing itself or the variable name represent that data. i mean ' mark. also variable scopes in the language php should be regarded as always, that is another thing.

function show_all_records($table_name) {

     mysql_query("SELECT `Username` FROM `$table_name` WHERE `Username` = 'edwin'");

etc, etc...

}

In SQL I discovered I should follow the following syntax rules to have my queries work in general:

Double-quote all SQL query. i mean the " mark.
Backquote all database names and table names and column names(column names can be left non-quoted, as well). i mean the ` (option + comma on Macintosh).
Single-Quote all values. i mean the ' mark.
Quote the variables with the same quotation mark that would be done for their values regarding above three rules.

edited Aug 14, 2013 at 14:04

answered Aug 14, 2013 at 13:28

sçuçu

3,0902 gold badges37 silver badges66 bronze badges

1 Comment

sçuçu Over a year ago

could not escape the backquote ` mark in the 2nd one, someone who knows to do may want to edit appropriately.

Collectives™ on Stack Overflow

Using PHP variables inside SQL statements?

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related