Query Help - Beginner Question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
setanta
Forum Newbie
Posts: 3
Joined: Wed Jan 30, 2008 9:39 am

Query Help - Beginner Question

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Query Help - Beginner Question

Post 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.
setanta
Forum Newbie
Posts: 3
Joined: Wed Jan 30, 2008 9:39 am

Re: Query Help - Beginner Question

Post 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
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Query Help - Beginner Question

Post 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.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Re: Query Help - Beginner Question

Post 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. :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Query Help - Beginner Question

Post 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.
setanta
Forum Newbie
Posts: 3
Joined: Wed Jan 30, 2008 9:39 am

Re: Query Help - Beginner Question

Post by setanta »

Thanks everybody - it works now.
Post Reply