Page 1 of 1
Query Help - Beginner Question
Posted: Wed Jan 30, 2008 9:47 am
by setanta
Everah | Please use the [code] bbCode tag when posting code in the forums. For PHP code, use [code=php] or [php]. Thanks.
I am trying to pull a user's contact ID from a table using the following query:
Code: Select all
$query = "SELECT CONTACT_INTERNAL_KEY FROM contacts_keys WHERE index = 1";
$result =mysql_query($query);
This returns a syntax error, but when I use:
Code: Select all
$query = 'SELECT `CONTACT_INTERNAL_KEY` FROM `contacts_keys` WHERE `index` = 1';
$result =mysql_query($query);
It works correctly.
Also, I am trying to substitute the variable $i so the query would read:
Code: Select all
$query = "SELECT CONTACT_INTERNAL_KEY FROM contacts_keys WHERE index = $i";
$result =mysql_query($query);
I know the issue has to do with the quotation marks, but I am copying pretty closely out of a PHP book- is there something I am missing?
I am using PHP5.2.3
Everah | Please use the [code] bbCode tag when posting code in the forums. For PHP code, use [code=php] or [php]. Thanks.
Re: Query Help - Beginner Question
Posted: Wed Jan 30, 2008 9:55 am
by aceconcepts
First of all get some error feedback:
Code: Select all
$result =mysql_query($query) or die(mysql_error());
Secondly, in order to use a variable in a query write your query like this:
Code: Select all
$query = "SELECT CONTACT_INTERNAL_KEY FROM contacts_keys WHERE index = '$i' ";
When writing a query you must write all table names and field names exactly as they are written in the database - they are case-sensitive too.
Re: Query Help - Beginner Question
Posted: Wed Jan 30, 2008 10:09 am
by setanta
Thanks - I had a feeling I was being sloppy.
I have updated my query to reflect case sensitivity; it now reads:
Code: Select all
$query = "SELECT CONTACT_INTERNAL_KEY FROM contacts_keys WHERE INDEX = '$i' ";
$result =mysql_query($query)
or die(mysql_error());
The error message that I am getting is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDEX = '5'' at line 1
Re: Query Help - Beginner Question
Posted: Wed Jan 30, 2008 10:16 am
by aceconcepts
I wouldn't use "INDEX" as a field name as you will no doubt run into conflicts with sql's INDEX used to create an index within a table.
When creating a table I find it useful to name fields like this:
String field for first name would be named "strFirstName" - where str stands for string
Numercial field for age would be named "intAge" - where int is the integer data type
Try using naming conventions like this and you will hopefully not run into any conflicts.
Re: Query Help - Beginner Question
Posted: Wed Jan 30, 2008 10:24 am
by rsmarsha
I tend to write variables into query in the following form:
Code: Select all
Line number On/Off | Expand/Contract | Select all
$query = "SELECT CONTACT_INTERNAL_KEY FROM contacts_keys WHERE INDEX = '".$i."' ";
$result =mysql_query($query) or die(mysql_error());
The extra ". and ." around the variable make it easier to read in your code.
Also I'd agree with the previous poster on naming policies.

Re: Query Help - Beginner Question
Posted: Wed Jan 30, 2008 10:59 am
by RobertGonzalez
All of your queries should look like this:
Code: Select all
<?php
$query = 'SELECT `CONTACT_INTERNAL_KEY` FROM `contacts_keys` WHERE `index` = 1';
?>
It is always the best idea to backtick your database, table and field names in MySQL queries. Also, when comparing integers versus strings remember that numbers do not take quotes around them, stings do, so your query above is the best typed query you have. In PHP it might look something like:
Code: Select all
<?php
$query = "SELECT `CONTACT_INTERNAL_KEY` FROM `contacts_keys` WHERE `index` = $id";
?>
Also make sure to handle errors in these, and try to stay away from die(mysql_error) in production.
Re: Query Help - Beginner Question
Posted: Wed Jan 30, 2008 4:01 pm
by setanta
Thanks everybody - it works now.