SQL/PHP search results problem and questions

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
charman
Forum Newbie
Posts: 2
Joined: Mon Sep 18, 2006 5:50 pm

SQL/PHP search results problem and questions

Post by charman »

I have two problems that are both SQL related using PHP.

1.) The following SQL statement that returns NO RESULTS:

SELECT * FROM mytable WHERE (typeNum IS NOT NULL) AND (type LIKE '%Night Club%') AND (food LIKE '%American Contemporary%' OR food LIKE '%American Traditional%') ORDER by spot ASC LIMIT 14, 15

I have records where they typeNum is filled in, the type field has "Night Club" listed, and the food field contains "American Traditional"). However, the results contain no results.

Why does it return an empty set?


2.) I have a simple keyword search textbox that returns results in a field named spot. There are about 9,000 records, alphanumeric. Anyway, if I type in "de" in the keyword searchbox and hit search, it'll return things like:

Deer
depend
always decent

However, if I type "dee", the Deer record above won't show up; instead it'll return an empty set.

SELECT * FROM mytable WHERE (typeNumIS NOT NULL) AND (spot LIKE '%deer%' OR description LIKE '%deer%') ORDER by name ASC LIMIT 14, 15

Why is it returning an empty set for the search term, "dee"?

Am I using OR wrong?

Thank you all for your help ... I am at wits end... :(
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Can you provide sample data?

Best if given as valid sql statments like

Code: Select all

CREATE TABLE `time_zone_name` (
  `Name` char(64) NOT NULL default '',
  `Time_zone_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Name`)
);

INSERT INTO `time_zone_name` VALUES ('Africa/Abidjan',1);
INSERT INTO `time_zone_name` VALUES ('Africa/Accra',2);
INSERT INTO `time_zone_name` VALUES ('Africa/Addis_Ababa',3);
INSERT INTO `time_zone_name` VALUES ('Africa/Algiers',4);
INSERT INTO `time_zone_name` VALUES ('Africa/Asmera',5);
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Also post your database server details (version, OS, etc).
Post Reply