Strange looking queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Strange looking queries

Post by impulse() »

I have taken over somebodies project, which doesn't work correctly, and I'm wondering if it's anything to do with the unfamiliar syntax in the queries. Could somebody tell me if the following is a valid query, or if it affects results compared to a "normal" looking query.

Code: Select all

SELECT 'Surname', 'Forename`, `customerID`, `email` from `contact_email`, `contacts` where `contactID`=".$contact_id." and `roles_contactID`=".$contact_id." and `default_email`='Y'
All the column names are wrapped in ' and I'm not sure if there's any special reason as queries in other files don't have them.

Regards,
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

coulmn names and table names should be enclosed in backticks ` `
This way you could use mysql reserved words. For example naming a table `order`.

In your case as this is select statement, you can try it with no worries if it will work. I guess parse error.

Also join is not very clear because:
1. Not clear which column comes from which table. In feature db change it is easy to break such a query. e.g adding same column name in two tables.
2. could produce cortesian product - each row of one table mached with other table.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

I will rephrase jmut's answer

If your field names are mysql reserved words or your field/table names have spaces in them then you need to use backticks ` (its the key usually to the left of the number 1 key).

I would say never use backticks as that will stop you using reserved words as part of your table structure, you just shouldn't do it.

As jmut says the join between the tables is non existant, potential for a cartesian product being returned.

should be rewritten something like

Code: Select all

SELECT contacts.surname, contacts.forename, contacts.customerid, contact_email.email 
FROM contacts
INNER JOIN contact_email ON (contacts.contact_id = contact_email.contact_id) 
WHERE contacts.contact_id = $contact_id 
AND default_email = 'Y'

Code: Select all

<?php
//if you enclose the query string in double quotes ", you do not need to come out and concatenate in the variable
//just put it in the normal flow (this does not work if you enclose in single quotes ')
$Query = "SELECT contacts.surname, contacts.forename, contacts.customerid, contact_email.email 
FROM contacts
INNER JOIN contact_email ON (contacts.contact_id = contact_email.contact_id) 
WHERE contacts.contact_id = $contact_id 
AND default_email = 'Y'";
Post Reply