write query correctly php pdo

Question

So I want to write the following query in PHP using PHP but I am wasting a ton of time trying to figure out something that should be simple.

Query in SQL

SELECT * 
FROM `table_name`
WHERE `column1` = 0560

Now it has to be exactly that way, the values can't have ' ', or " ", or even back ticks around them. I tried it with those around the values and it just keeps failing even in SQL if its not exactly like the above.

Now I have tried the following, but none of which are successful and it's annoying me to no end

$a_query = "SELECT * FROM `".$table_name."` WHERE `".$column_name."` = `".$store_num."`";
$a_query = "SELECT * FROM `table_name` WHERE `column1` = `0560`";

I know I've tried a few other variations I just can't recall them right now. I've been at this for a while this evening.

Anyways I get this error every time

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0560' in 'where clause'

Thanks for the help, I realize I'm probably missing something simple, my eyes are just fried

@zerkms I am using PDO, I had been told that if I wanted to pass the table name in PDO as a variable I had to write the query before I prepared it. Is this wrong? — i_me_mine
– i_me_mine, Commented Jun 28, 2013 at 0:40
normally you don't parameterize table names (it's bad, mkay), but if you do - you have to use white lists — zerkms
– zerkms, Commented Jun 28, 2013 at 0:52
@zerkms yes, its terrible, I have no choice in the matter, this db is a nightmare and I did not design it. Thank you for the "white lists" option I'll review it. — i_me_mine
– i_me_mine, Commented Jun 28, 2013 at 0:53
white list is just an array with all possible table names hardcoded. So before you put the table name (to the query) accepted from outside - you just check if it's in the allowed list, throw error otherwise. — zerkms
– zerkms, Commented Jun 28, 2013 at 0:58

John Woo · Accepted Answer · 2013-06-28 00:21:19Z

3

String literals should be enclosed in a single quotes '

Backticks ` are used to enclose identifiers (column name, table name, alias, etc)

Double quotes " behaviour depends on a correspondent sql_mode

edited Jun 28, 2013 at 0:21

John Woo

265k70 gold badges509 silver badges500 bronze badges

answered Jun 28, 2013 at 0:12

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

7 Comments

i_me_mine Over a year ago

you're right, and I can query using backticks ` around the identifiers but I need the string to be without ' ' or " " and when I try and use backticks to accomplish this it isn't working. Is there another way? Ultimately it must be in variable form

zerkms Over a year ago

@i_me_mine: I don't understand your question. Strings SHOULD BE enclosed in single quotes. Regardless of if you like that or not - it's an SQL syntax. "I need the string to be without" -- elaborate that

i_me_mine Over a year ago

if my String is passed with ' ' or " " around it , it does not work. It needs to be read as 0560, not '0560' or "0560". So at the top where I have my query written from SQL that gives me results, when it is passed from PHP it must look identical.

zerkms Over a year ago

@i_me_mine: so change your code so that it passed data in a correct form. If you want a string passed to be 0560 not '0560' - then just don't add quotes. I don't see how this question is related to sql syntax.

i_me_mine Over a year ago

AHHHH! I literally wan't to punch something from my stupidity.... I am taking a break. I solved it. You were right, as I knew you were. I didn't notice that I had surrounded my variable in back ticks

|

Collectives™ on Stack Overflow

write query correctly php pdo

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related