[PHP-DB] SQL help
I'm using PHP with ADOdb ( and an MS Access 2000 db ) to write a simple SQL statement but was running into some case sensitivity issues. Here's my SQL currently: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE fldBody LIKE '%$strSearchFor%'; All I'm trying to do is have the users search string searched for in the fldBody field. However, I'm having problems trying to get it so that the search is case-insensitive. For instance: If I search on Airline, I get 1 record. If I search on airline, I get 0 records. I make the value of $strSearchFor lower case ( using strtolower() ), but I don't know how to get it so that the contents of the fldBody field is lower case also. I can't seem to find any functions or operators that remove the case-sensitivity. Any help would be much appreciated! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL help
I have tried that and I don't get an error, but I don't get any records returned either. And I have lowered the search string like you mentioned. Here's what I tried ( Access syntax ): SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE LCase(fldBody) LIKE '%$strSearchFor%'; Nicole Swan wrote: Have you tried lowering the fldBody as well? Like: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE LOWER(fldBody) LIKE '%$strSearchFor%'; And $strSearchFor has already been lowered, of course. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Gabe [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 8:59 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL help I'm using PHP with ADOdb ( and an MS Access 2000 db ) to write a simple SQL statement but was running into some case sensitivity issues. Here's my SQL currently: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE fldBody LIKE '%$strSearchFor%'; All I'm trying to do is have the users search string searched for in the fldBody field. However, I'm having problems trying to get it so that the search is case-insensitive. For instance: If I search on Airline, I get 1 record. If I search on airline, I get 0 records. I make the value of $strSearchFor lower case ( using strtolower() ), but I don't know how to get it so that the contents of the fldBody field is lower case also. I can't seem to find any functions or operators that remove the case-sensitivity. Any help would be much appreciated! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Help?
Hi, Here's another SQL query problem: How do I construct a query that returns 1 if a value is present and zero if it is not, for a list of values returned in another query? i.e. select a.id, a.name, c.selected from table-with-id-and-name a, (select 1 as selected from another-table b if a.id = b.id or select 0 as selected from another-table b if a.id != b.id ) c; Thx in Advance, Barry Prentiss -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] SQL help
Hi there folks, I have a small prob regarding SQL on MySQL 3.22.32 using php4.0.3pl1 : I want to select DISTINCT sports from one table WHERE a username and a password match a username and a password in another table - not sure how to construct my query. In an ideal world it's gonna look something like this: $sql = "SELECT DISTINCT sportID FROM $table_sport"; $sql .= "SELECT * FROM $table_users WHERE $table_users.usrName='$admin_username' AND $table_users.usrPswd=password('$admin_password')"; Just not sure how the query needs to be constructed... Can anyone with even slightly advanced (over me) SQL knowledge help me out?? Many thanks! Russ #---# "Believe nothing - consider everything" "Web Developers do it on-the-fly." Russ Michell Anglia Polytechnic University Webteam www.apu.ac.uk/webteam [EMAIL PROTECTED] +44 (0)1223 363271 ext 2331 www.theruss.com #---# -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] SQL help
Russ Michell wrote: Hi there folks, I have a small prob regarding SQL on MySQL 3.22.32 using php4.0.3pl1 : I want to select DISTINCT sports from one table WHERE a username and a password match a username and a password in another table - not sure how to construct my query. In an ideal world it's gonna look something like this: $sql = "SELECT DISTINCT sportID FROM $table_sport"; $sql .= "SELECT * FROM $table_users WHERE $table_users.usrName='$admin_username' AND $table_users.usrPswd=password('$admin_password')"; Just not sure how the query needs to be constructed... Can anyone with even slightly advanced (over me) SQL knowledge help me out?? Many thanks! Russ Depends... Looking at what you posted it'll be something like this: SELECT DISTINCT $table_sport.sportID FROM $table_sport, $table_users WHERE $table_users.usrName = '$admin_username' AND $table_users.usrPswd = password($admin_password) AND $table_sport.usrName = $table_users.usrName AND $table_sport.usrPswd = $table_users.usrPswd You can also make it a bit more readable, like this: SELECT DISTINCT S.sportID FROM$table_sport S, $table_users U WHERE U.usrname = '$admin_username' AND U.usrPswd = password($admin_password) AND S.usrName = U.usrname AND S.usrPswd = U.usrPswd; This way you don't have those variablenames in the query all the time. -- * RzE: *** ** Renze Munnik ** ** E: [EMAIL PROTECTED] ** M: +31 6 218 111 43 *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] SQL help
Sweet!! Many thanks! Russ #---# "Believe nothing - consider everything" "Web Developers do it on-the-fly." Russ Michell Anglia Polytechnic University Webteam www.apu.ac.uk/webteam [EMAIL PROTECTED] +44 (0)1223 363271 ext 2331 www.theruss.com #---# -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]