Page 1 of 1
Strange looking queries
Posted: Thu Mar 08, 2007 4:46 am
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,
Posted: Thu Mar 08, 2007 5:08 am
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.
Posted: Thu Mar 08, 2007 5:37 am
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'";