php variable to mysql where

Question

I defined PHP variable $id with a value that is the current Facebook user's ID, extracted from an array returned from a Facebook API call.

$id = $userInfo[id];

I'm confident that the variable is declared correctly because I can echo it's value and have successfully stored the correct value in a database.

I'm trying unsuccessfully to use it in the where clause of a mysql query to return records from the database table the ID and other data is stored in. The query returns the expected results until I add the where clause so I think it's my syntax pertaining to $id. The column I'm filtering by is also named id. None of the syntax I've tried has worked, including the following:

WHERE id = $id
WHERE id = '$id'
WHERE id = "$id"

I've spent the last few days searching for a solution and tried every combination of single quotes, double quotes, and decimals I've seen suggested anywhere.

Edit (Code taken from comments made by author)

mysql_select_db($database_YOUR_giftbox, $YOUR_giftbox); 
$query_user_data = "SELECT date, event FROM events WHERE id = $id ORDER BY date ASC"; 
$user_data = mysql_query($query_user_data, $YOUR_giftbox) or die(mysql_error()); 
$row_user_data = mysql_fetch_assoc($user_data); 
$totalRows_user_data = mysql_num_rows($user_data);

$id = $userInfo[id]; is a bit risky: you should use $id = $userInfo["id"]; instead. — Waleed Khan
– Waleed Khan, Commented Aug 19, 2012 at 2:11
Hello arxanas, Thank you very much for your feedback! What is the significance of surrounding the array key with double quotes? Do you have any suggestions that will allow me to use $id in the mysql where clause? — Keith Burk
– Keith Burk, Commented Aug 19, 2012 at 2:15
I think it would help if we saw how you implemented your database query (that is, the PHP method used). — Waleed Khan
– Waleed Khan, Commented Aug 19, 2012 at 2:15
you need to show more of your code, my guess is $sql = '... WHERE id = '.addslashes($id); — i--
– i--, Commented Aug 19, 2012 at 2:16

tptcat · Accepted Answer · 2012-08-19 02:29:02Z

2

I'm going to assume something things and expand your example. Here's what a full query would look like including a PHP var (very basic example):

$query = "SELECT name, another_field FROM users WHERE id = " . $id;

That's if id is an integer in your database. If it's some sort of string for some reason, then:

$query = "SELECT name, another_field FROM users WHERE id = '" . $id . "'";

You can also enclose the PHP variable in curly braces like this (for an integer):

$query = "SELECT name, another_field FROM users WHERE id = {$id}";

or this (for a string):

$query = "SELECT name, another_field FROM users WHERE id = '{$id}'";

But if you use that method then just beware that the entire $query declaration needs to be wrapped in " and not '.

answered Aug 19, 2012 at 2:29

tptcat

3,9712 gold badges36 silver badges52 bronze badges

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

4 Comments

tadman Over a year ago

If you like juggling chainsaws while drinking, this is how you write SQL in PHP.

tptcat Over a year ago

@tadman: It's a very basic example to illustrate how what he may or may not be doing correctly (since he didn't provide real sample code we could only guess). I wasn't trying to educate him on better practices. Provide a better alternative. Your example does nothing to answer his question either. This doesn't deserve a down vote.

tadman Over a year ago

If you put that code into production, a company could lose their entire business if it gets hacked. This is the sort of thing you must take seriously.

Marc B Over a year ago

{} notation in double-quoted strings is only necessary for quoted variable keys, multidimensional array dereferences, or object chains. But it's still a good idea to use them anyways, regardless of the circumstances.

tadman · Accepted Answer · 2012-08-19 03:12:11Z

1

Since it seems like you're just getting started with PHP and MySQL, DO NOT USE mysql_query in any new applications. Your query should look like something like:

SELECT * FROM something WHERE id=?

It could also look like:

SELECT * FROM something WHERE id=:id

This depends on how you're using mysqli or PDO to do your SQL escaping. There's several ways to do it, so find one that suits your style best and try to apply it consistently. You won't regret learning this very important skill. You will regret it if you are oblivious to what kind of dangers you're exposing yourself to without it.

It terrifies me that people give "answers" with zero care to making sure the query will always be valid and will never be a gigantic gaping hole in your application just waiting for someone to use an automatic SQL injection tool on.

You should be able to compose the sort of query you want, test it on the mysql> command line, and then make a template version in your application that does exactly the same thing. MySQL is generally pretty casual when it comes to numbers, they can be quoted or not, it converts as necessary, but strings must always be escaped correctly or there can be serious consequences.

To execute this with mysqli is pretty easy:

$stmt = $db->prepare('SELECT * FROM something WHERE id=?');
$stmt->bind_param($id);
$stmt->execute();

That's probably the closest to the mysql_query family of functions.

edited Aug 19, 2012 at 3:12

answered Aug 19, 2012 at 2:51

tadman

212k23 gold badges237 silver badges266 bronze badges

3 Comments

AKS Over a year ago

Took off my odd answer that got a downvote. I think the OP needs clarification in whether use single or double quotes. That's why I mentioned print and sql are same - not in terms of security but when using variables in quotes.

tadman Over a year ago

I like to look for MySQL related questions to answer, but all I find are people doing w3schools inspired 1990s vintage PHP that's full of SQL injection bugs. The problem here is partially related to quoting, but mostly related to how to compose a SQL query that works. Sorry to be so harsh, but the answers some people provide here are so dangerous they should never, ever end up in a production application under any circumstances. People need to know the risks.

AKS Over a year ago

I completely agree, @tadman. There is a reason why PDO is built.

Keith Burk · Accepted Answer · 2012-08-19 03:39:27Z

0

Thank you all very much for your suggestions!

As it turns out the solution I was looking for is the one I started with, WHERE id = $id. For some reason Dreamweaver's database test returned all results correctly but not results filtered by my where clause. I uploaded it to my remote server and it worked perfectly.

Thanks again!

answered Aug 19, 2012 at 3:39

Keith Burk

311 silver badge2 bronze badges

1 Comment

tadman Over a year ago

If you're doing that, you're doing it wrong. Please do not use mysql_query in new applications, and ALWAYS escape any and all values injected into your SQL. You're creating a maintenance nightmare for someone, and that someone could be you in the future, as well as learning some very bad habits.

Zeke Nierenberg · Accepted Answer · 2012-08-20 13:17:54Z

0

$query = "SELECT * FROM `table_name` WHERE `id` = " . mysql_real_escape_string($id) . " ;"

Would be the most correct. Make sure table_name and id are correct.

Using ` left tick marks makes sure you don't run over protected mysql words.

Also a good thing to note about facebook IDs is that they require the BIGINT mysql type. The numbers are too big for int! That has screwed me up in the past.

Good luck!

edited Aug 20, 2012 at 13:17

answered Aug 19, 2012 at 2:29

Zeke Nierenberg

2,1961 gold badge20 silver badges30 bronze badges

5 Comments

tadman Over a year ago

If $id is a parameter from $_GET or $_POST, this is an exceptionally bad idea.

Zeke Nierenberg Over a year ago

Because of security? You can always use mysql_real_escape_string(). Also, the question says nowhere that this is from get or post.

tadman Over a year ago

If you assume it is a safe variable, that's where you run into trouble. ALWAYS correctly escape anything and everything you're putting directly into your SQL statements.

Zeke Nierenberg Over a year ago

Fiar point. I would quibble a little with that. Sometimes you are using an internal app... something with low security stakes. Or some sort of utility script that you only run once, then comment out... But I edited my answer to reflect that you're right.

tadman Over a year ago

It could be your grandmother's recipe application, it doesn't matter. Escape EVERYTHING. Sometimes these internal-only applications have a way of ending up public or being abused by hostile employees. You never know. It is your responsibility to do it the correct way the first time and every time.

Hugo Dozois · Accepted Answer · 2012-08-19 02:22:54Z

-2

SELECT * WHERE 'ID' = $id;

Also, make sure that your $id is an actual string variable and not something like a string array where your ECHO returns index 0.

edited Aug 19, 2012 at 2:22

Hugo Dozois

8,49812 gold badges56 silver badges59 bronze badges

answered Aug 19, 2012 at 2:15

amovitz

1

4 Comments

tptcat Over a year ago

If $id is a string, then what you wrote should be: id = "'" . $id . "'"; OR `id = "'{$id}'";

i-- Over a year ago

that is wrong, field names can't be wrapped in ' single quotes, only in `

Keith Burk Over a year ago

If it matters I'm using Dreamweaver CS6, and I get error codes when I try a lot of the suggestions, especially those that include a single ' or end with ;

Zeke Nierenberg Over a year ago

This is def wrong. You don't want to wrap your column names in single quotes. You can (and should) wrap them in ` left ticks.

Collectives™ on Stack Overflow

php variable to mysql where

5 Answers 5

4 Comments

3 Comments

1 Comment

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

1 Comment

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related