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
RE: [PHP-DB] SQL help
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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[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?
Hi Barry-- First, I want to see if I understand what you mean, then I'll take a shot at some SQL to solve the problem. I take it you have two tables, "a" and "b". They may look something like this: Table: a id name --- -- 001 Bob 002 Ted 003 Mary 004 Sandy Table: b id --- 002 003 And you want your result table to look something like: id nameselected --- -- 001 Bob 0 002 Ted 1 003 Mary1 004 Sandy 0 What you're trying to do is not very set-oriented (SQL is very set-oriented), so how about gathering the two sets you're talking about and returning the union of the two? Something like: select a.id as id, name, 1 as selected from a, b where a.id = b.id union select a.id as id, name, 0 as selected from a where a.id NOT IN (select id from b); There are undoubtedly other ways to do this, but the above query works on an old PostgreSQL installation I mess around with sometimes. Hope this helps. Doug At 02:30 PM 8/16/01 -0700, Barry Prentiss wrote: >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, 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]
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]
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.
--
* R&zE:
***
** 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]
[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]
