1

I am trying to run the following query, but I am not sure if my 's should be `s or not, $form_id = the record's column , $user_id is the primary key of the record called cf_id .

$querydate is going to be echo'd later on in the script, as it pulls the date from the record that equals to $form_id and $user_id .

$querydate = mysql_query("SELECT '$form_id' FROM email_history WHERE cf_id = '$user_id'") or die(mysql_error());

EDIT >>>>>>

After trying some of the solutions below, it seems to work ok, but instead of getting the date stored under the form name, I am getting this echo'd instead, so im not sure whats happening now : :Resource id #120 :Resource id #121 :Resource id #122 :Resource id #123

The table is setup like the followng:

[USER_ID] [FORM_ID1212212]  [FORM_ID1212112]  
 [1]      [2-1-2012]        [2-1-2012]       
 [2]      [1-1-2012]        [1-1-2012]
Iain Simpson
  • 441
  • 4
  • 13
  • 29
  • You shouldn't be performing MySQL queries like this; use prepared statements instead. – Oliver Charlesworth Jan 02 '12 at 19:30
  • possible duplicate of [What are the differences between backtick and single quote? Can I use IF statement in a query as above?](http://stackoverflow.com/questions/2122721/what-are-the-differences-between-backtick-and-single-quote-can-i-use-if-stateme) -and- [Using backticks around field names](http://stackoverflow.com/questions/261455/using-backticks-around-field-names) – mario Jan 02 '12 at 19:40
  • re: Edit... the tutorials you're using should show how to fetch each result from the $querydate resultset using mysql_fetch_row() – Mark Baker Jan 02 '12 at 19:50

3 Answers3

7

You use backticks (`) for table and column names, single quotes (') for strings.

$querydate = mysql_query("SELECT `$form_id` FROM email_history WHERE cf_id = '$user_id'");

Backticks are only needed when your table name or column name is a MySQL reserved word... best practise is to avoid reserved words

But also consider switching to PDO and using prepared statements, or at least to mysqli rather than mysql

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • FWIW, you can't bind table or column names with a prepared statement. – Alnitak Jan 02 '12 at 19:31
  • 1
    True, you can't bind table/column names; but with a well-defined data model you shouldn't need to... and that doesn't mean that you can't still use $variables in the string... I'm just recommending a general best practise rather than the continued use of the mysql extension, and the horrendous "or die" should also be consigned to history – Mark Baker Jan 02 '12 at 19:32
  • Im just trying to learn the basics at the moment as most of the snippets for examples seem to be in oldskool mysql queries opposed to pdo , I will get onto pdo once I learn the basics :-) – Iain Simpson Jan 02 '12 at 19:36
  • @Alnitak True enough, the problem with the web is that all those out-of-date tutorials are still available for all eternity, but I'll still try to recommend better practises when asked. – Mark Baker Jan 02 '12 at 19:37
  • @IainSimpson please do yourself a favour and ignore any PHP DB tutorial that isn't using PDO. It's really almost just as easy to use. – Alnitak Jan 02 '12 at 19:38
  • @Iain - good for you, SO is a good source for help once you have worked through those oldskool tutorials, and people here will appreciate your efforts to learn how to do things using better methods once you've outgrown the basics... though learning the wrong way first isn't always the best approach to learning if you can locate any better tutorials – Mark Baker Jan 02 '12 at 19:39
  • @MarkBaker I agree completely - PDO is the way to go. I was just pointing out that for the OP's purposes, he should be aware that binding doesn't work for column names which were the specific case of backticks he was asking about. – Alnitak Jan 02 '12 at 19:39
  • I have updated my question above, as I am not sure if I explained it right, I am probably using the wrong array name in the wrong place, poss why I am getting the wrong thing back :-S – Iain Simpson Jan 02 '12 at 19:47
  • FWIW, you should also use delimited identifiers if your identifiers contain whitespace, special/international characters, or significant case. See my answer to [Do different databases use different name quote?](http://stackoverflow.com/questions/214309/do-different-databases-use-different-name-quote/214344#214344) – Bill Karwin Jan 02 '12 at 19:50
1

Best practice would be:

"SELECT `$form_id` FROM `email_history` WHERE `cf_id` = '$user_id'"

Backticks should be used around field names and table names (and DB names), and quotes should be used around values.

Ben D
  • 14,321
  • 3
  • 45
  • 59
  • :) I'd agree with that... this is "best practice" assuming you're just writing a mysql query "manually" – Ben D Jan 02 '12 at 19:32
1

You should:

  1. ensure that $form_id is a legal table name, especially if it's generated from user-supplied input.

  2. use a bound parameter for $user_id

e.g.:

$sql = "SELECT `$userid` FROM `email_history` WHERE `cf_id` = ?"
$res = $db->query($sql, array($user_id));
while ($row = $res->fetchRow()) {
   ...
}

Back-ticks are appropriate for all table and column names. Unfortunately you can't use variable column names in a parameterised query, so you do need to construct that part of the query by hand.

Alnitak
  • 334,560
  • 70
  • 407
  • 495