This all helps. I think I need to spend some time experimenting.
Thanks for your help!
- Ron
On 3/19/2010 3:56 PM, Bastien Koert wrote:
[snip]
Here's an example of a tough query (there are lots of Smiths!).
SELECT voter1.County, voter1.`First Name`, voter1.`Middle Name`,
voter1.`Last Name`, voter1.`Residential ZipCode`,voter1.`House Number`,
voter1.`Mailing Address`, voter1.`Street Name`, voter1.`Residential City`,
voter1.`Voter ID`, voter1.`Congressional Distri`,voter1.idVoter,
voter1.County FROM voter1 WHERE voter1.`County` LIKE '%' AND voter1.`Last
Name` LIKE 'Smith%' AND voter1.`First Name` LIKE 'John%' AND
voter1.`Residential ZipCode` LIKE '%' LIMIT 0, 10;
Do you think the LIKE '%' hurts for the fields we don't have search
criteria? We do that to keep the query definition simple and flexible.
[/snip]
Yes, those hurt and will cause you endless grief. A much better course
of action is to build the SQL dynamically to only query on the fields
where you have values to start with. If I alter your example
SELECT voter1.County, voter1.`First Name`, voter1.`Middle Name`,
voter1.`Last Name`, voter1.`Residential ZipCode`,voter1.`House Number`,
voter1.`Mailing Address`, voter1.`Street Name`, voter1.`Residential City`,
voter1.`Voter ID`, voter1.`Congressional Distri`,voter1.idVoter,
voter1.County FROM voter1
WHERE
voter1.`Last Name` LIKE 'Smith%' AND voter1.`First Name` LIKE 'John%'
LIMIT 0, 10;
This produces a much cleaner SQL and simpler query for the DB to use.
Also, I tend to prefer not using spaces in the field names. I prefer
to have an underscore to avoid any issues if I ever have to move
databases
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
