SQL Error?

Curtis782

I am trying to allow people to search for events. They can search by event name/keyword, by industry or type of show. For some reason my query is not displaying results correctly. Classification1, Classification2, Classification3, Classification4 refer to industries and Classification5 refers to the type of show (Options are Trade, Public, Trade/Public). Without the AND Classification5 = 'Public' piece, it works fine. Here is the SQL:

SELECT DISTINCT id, Page_Num, Classification1, Company_Event, SM_Name, TSW_SMID, SM_Mgr, SM_Mgr_Title, SM_Mgr_Phone, SM_Mgr_Email, Classification2, Classification3, Classification4, Classification5, Event_Type, Show_Owner1, Show_Owner2, Show_Owner3, Show_Owner4, Show_Description, SF_Dates_Location, SF_Profile_Exhibitors, SF_Profile_Attendees, SF_Promo_Opp, SF_EstNetSqFt, SF_EstNumExhibitingComp, SF_EstNumProfAttendees, SF_EstNumHotelRoomNights, SF_EstPeakRoomNightReq, SF_PreRegFee, SF_AtShowRegFee, SF_ExhibitSpaceCost, SF_ShowAuditSurvey, SF_DeptCommCert, ConfSemMeet_DatesLocation, ConfSemMeet_NumConfSeminars, ConfSemMeet_SubjectAreasCovered, ConfSemMeet_Fees, SH_Freq, SH_FirstYearShow, SH_ShowHeldSameCityEveryYear, SH_ShowHeldConcurrentlyWith, SH_Rotation, LastShowStats_DatesLocation, LastShowStats_NetSqFt, LastShowStats_NumExhibCompanies, LastShowStats_NumProfAttendees, LastShowStats_GeoAnalysis, Event_Status, SM_Address, SM_City, SM_State, SM_Zip, SM_Co_Phone, SM_Co_Toll, SM_Co_Fax, SM_Co_Web, SM_Mgr_TollFree, SM_Mgr_Fax, SM_Exhibit_Sales, SM_Exhibit_Ttl, SM_Exhibit_Email, SM_Exhibit_Ph, SM_Exhibit_TollFree, SM_Exhibit_Fax, SS_Sponsor1, SS_Address1, SS_City1, SS_State1, SS_Zip1, SS_Contact1, SS_Contact_Ttl1, SS_Contact_Ph1, SS_Contact_Fax1, SS_Contact_Email1, SS_Sponsor2, SS_Address2, SS_City2, SS_State2, SS_Zip2, SS_Contact2, SS_Contact_Ttl2, SS_Contact_Ph2, SS_Contact_Fax2, SS_Contact_Email2, SS_Sponsor3, SS_Address3, SS_City3, SS_State3, SS_Zip3, SS_Contact3, SS_Contact_Ttl3, SS_Contact_Ph3, SS_Contact_Fax3, SS_Contact_Email3, SS_Sponsor4, SS_Address4, SS_City4, SS_State4, SS_Zip4, SS_Contact4, SS_Contact_Ttl4, SS_Contact_Ph4, SS_Contact_Fax4, SS_Contact_Email4, SS_Sponsor5, SS_Address5, SS_City5, SS_State5, SS_Zip5, SS_Contact5, SS_Contact_Ttl5, SS_Contact_Ph5, SS_Contact_Fax5, SS_Contact_Email5, SS_Sponsor6, SS_Address6, SS_City6, SS_State6, SS_Zip6, SS_Contact6, SS_Contact_Ttl6, SS_Contact_Ph6, SS_Contact_Fax6, SS_Contact_Email6, SS_Sponsor7, SS_Address7, SS_City7, SS_State7, SS_Zip7, SS_Contact7, SS_Contact_Ttl7, SS_Contact_Ph7, SS_Contact_Fax7, SS_Contact_Email7, SF_Contractor_Name, SF_Contractor_Address, SF_Contractor_City, SF_Contractor_State, SF_Contractor_Zip, SF_Contact, SF_Contact_Ph1, SM_Contact_Toll, SM_Contact_Fax, SF_Contact_Email, SF_Contractor_Name2, SF_Contractor_Address2, SF_Contractor_City2, SF_Contractor_State2, SF_Contractor_Zip2, SF_Contact2, SF_Contact_Ph2, SF_Contact_Toll2, SF_Contact_Fax2, SF_Contact_Email2 FROM bookdata WHERE Company_Event LIKE '%%' AND Classification1 = 'Transportation' OR Classification2 = 'Transportation' OR Classification3 = 'Transportation' OR Classification4 = 'Transportation' AND Classification5 = 'Public' ORDER BY id

laserlight

Err... I suspect that you should start by reading this article on Database Normalization And Design Techniques.

Curtis782

I agree, too many selections for what is needed to display. But I selected everything in the table to play with the data first and then I plan on removing what I don't need in the "SELECT". I guess I'm primarily concerned with how to best write the WHERE clause to accomplish what I am trying to display? WHERE Company_Event LIKE '%%' AND Classification1 = 'Transportation' OR Classification2 = 'Transportation' OR Classification3 = 'Transportation' OR Classification4 = 'Transportation' AND Classification5 = 'Public' ORDER BY id

laserlight

Curtis782 wrote:
I agree, too many selections for what is needed to display.

That may be a problem, but it is the underlying problem that I hinted that you should address: your table is not even in the first normal form. You should get it to at least the third normal form, and only then write your SQL statement. When you do this, it may well be the case that this "too many selections" problem would also be solved.

Curtis782 wrote:
I guess I'm primarily concerned with how to best write the WHERE clause to accomplish what I am trying to display?

You probably want this as the WHERE clause:

WHERE (Classification1 = 'Transportation' OR Classification2 = 'Transportation' OR Classification3 = 'Transportation' OR Classification4 = 'Transportation') AND Classification5 = 'Public'

Note the use of parentheses for grouping to avoid precedence issues. I left out Company_Event LIKE '%%' because it appears to be quite useless.

Curtis782

Thank you for this info. I forgot to mention the "Company_Event" would be needed because it pulls from an input field but was unsure how I could enter this into my WHERE clause? Here is my PHP as it is now (produces an error)

$whereclause = " WHERE Company_Event LIKE '%" . $_REQUEST["Company_Event"] . "%'
	AND (Classification1 = '" . $_REQUEST["Industry"] . "'
	OR Classification2 = '" . $_REQUEST["Industry"] . "'
	OR Classification3 = '" . $_REQUEST["Industry"] . "'
	OR Classification4 = '" . $_REQUEST["Industry"] . "')
	AND Classification5 = '" . $_REQUEST["ShowType"] . "'
	";

$orderby = " ORDER BY id ";

laserlight

Curtis782 wrote:
Here is my PHP as it is now (produces an error)

What is the error?

Before we continue, please normalise your database.

Curtis782

I don't recall what the error was but I moved some of the SQL around to remove the error but I still had an incorrect data display (the event type did not display correctly). I am following your advice to restructure my database (from what I understand) and was curious if you believe this is an OK way to go about doing that? I tried creating a table ("primary") but that resulted in an error inside phpMyAdmin when I pasted the SQL in (error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary( id INT NOT NULL AUTO_INCREMENT, Page_Num VARCHAR(10) NOT NULL, Compa' at line 1")

This is my planned new structure:

CREATE TABLE primary(
id INT NOT NULL AUTO_INCREMENT,
Page_Num VARCHAR(10) NOT NULL,
Company_Event VARCHAR(255) NOT NULL,
TSW_SMID VARCHAR(200) NOT NULL,
Classification1 VARCHAR(100) NOT NULL,
Classification2 VARCHAR(100) NOT NULL,
Classification3 VARCHAR(100) NOT NULL,
Classification4 VARCHAR(100) NOT NULL,
Classification5 VARCHAR(100) NOT NULL,
Event_Type VARCHAR(100) NOT NULL,
Show_Owner1 VARCHAR(100) NOT NULL,
Show_Owner2 VARCHAR(100) NOT NULL,
Show_Owner3 VARCHAR(100) NOT NULL,
Show_Owner4 TEXT NOT NULL,
Show_Description TEXT NOT NULL,
ConfSemMeet_DatesLocation VARCHAR(100) NOT NULL,
ConfSemMeet_NumConfSeminars VARCHAR(255) NOT NULL,
ConfSemMeet_SubjectAreasCovered VARCHAR(255) NOT NULL,
ConfSemMeet_Fees VARCHAR(100) NOT NULL,
LastShowStats_DatesLocation VARCHAR(20) NOT NULL,
LastShowStats_NetSqFt VARCHAR(10) NOT NULL,
LastShowStats_NumExhibCompanies VARCHAR(100) NOT NULL,
LastShowStats_NumProfAttendees VARCHAR(100) NOT NULL,
LastShowStats_GeoAnalysis VARCHAR(10) NOT NULL,
Event_Status VARCHAR(10) NOT NULL,
PRIMARY KEY ( id )
);

CREATE TABLE sh(
id INT NOT NULL AUTO_INCREMENT,
SH_Freq VARCHAR(10) NOT NULL,
SH_FirstYearShow VARCHAR(10) NOT NULL,
SH_ShowHeldSameCityEveryYear VARCHAR(255) NOT NULL,
SH_ShowHeldConcurrentlyWith VARCHAR(10) NOT NULL,
SH_Rotation VARCHAR(255) NOT NULL,
PRIMARY KEY ( id )
);

CREATE TABLE ss(
id INT NOT NULL AUTO_INCREMENT,
SS_Sponsor1 VARCHAR(200) NOT NULL,
SS_Address1 VARCHAR(100) NOT NULL,
SS_City1 VARCHAR(100) NOT NULL,
SS_State1 VARCHAR(2) NOT NULL,
SS_Zip1 VARCHAR(10) NOT NULL,
SS_Contact1 VARCHAR(200) NOT NULL,
SS_Contact_Ttl1 VARCHAR(100) NOT NULL,
SS_Contact_Ph1 VARCHAR(40) NOT NULL,
SS_Contact_Fax1 VARCHAR(40) NOT NULL,
SS_Contact_Email1 VARCHAR(100) NOT NULL,
SS_Sponsor2 VARCHAR(200) NOT NULL,
SS_Address2 VARCHAR(100) NOT NULL,
SS_City2 VARCHAR(100) NOT NULL,
SS_State2 VARCHAR(2) NOT NULL,
SS_Zip2 VARCHAR(10) NOT NULL,
SS_Contact2 VARCHAR(200) NOT NULL,
SS_Contact_Ttl2 VARCHAR(100) NOT NULL,
SS_Contact_Ph2 VARCHAR(40) NOT NULL,
SS_Contact_Fax2 VARCHAR(40) NOT NULL,
SS_Contact_Email2 VARCHAR(100) NOT NULL,
SS_Sponsor3 VARCHAR(200) NOT NULL,
SS_Address3 VARCHAR(100) NOT NULL,
SS_City3 VARCHAR(100) NOT NULL,
SS_State3 VARCHAR(2) NOT NULL,
SS_Zip3 VARCHAR(10) NOT NULL,
SS_Contact3 VARCHAR(200) NOT NULL,
SS_Contact_Ttl3 VARCHAR(100) NOT NULL,
SS_Contact_Ph3 VARCHAR(40) NOT NULL,
SS_Contact_Fax3 VARCHAR(40) NOT NULL,
SS_Contact_Email3 VARCHAR(100) NOT NULL,
SS_Sponsor4 VARCHAR(200) NOT NULL,
SS_Address4 VARCHAR(100) NOT NULL,
SS_City4 VARCHAR(100) NOT NULL,
SS_State4 VARCHAR(2) NOT NULL,
SS_Zip4 VARCHAR(10) NOT NULL,
SS_Contact4 VARCHAR(200) NOT NULL,
SS_Contact_Ttl4 VARCHAR(100) NOT NULL,
SS_Contact_Ph4 VARCHAR(40) NOT NULL,
SS_Contact_Fax4 VARCHAR(40) NOT NULL,
SS_Contact_Email4 VARCHAR(100) NOT NULL,
SS_Sponsor5 VARCHAR(200) NOT NULL,
SS_Address5 VARCHAR(100) NOT NULL,
SS_City5 VARCHAR(100) NOT NULL,
SS_State5 VARCHAR(2) NOT NULL,
SS_Zip5 VARCHAR(10) NOT NULL,
SS_Contact5 VARCHAR(200) NOT NULL,
SS_Contact_Ttl5 VARCHAR(100) NOT NULL,
SS_Contact_Ph5 VARCHAR(40) NOT NULL,
SS_Contact_Fax5 VARCHAR(40) NOT NULL,
SS_Contact_Email5 VARCHAR(100) NOT NULL,
SS_Sponsor6 VARCHAR(200) NOT NULL,
SS_Address6 VARCHAR(100) NOT NULL,
SS_City6 VARCHAR(100) NOT NULL,
SS_State6 VARCHAR(2) NOT NULL,
SS_Zip6 VARCHAR(10) NOT NULL,
SS_Contact6 VARCHAR(200) NOT NULL,
SS_Contact_Ttl6 VARCHAR(100) NOT NULL,
SS_Contact_Ph6 VARCHAR(40) NOT NULL,
SS_Contact_Fax6 VARCHAR(40) NOT NULL,
SS_Contact_Email6 VARCHAR(100) NOT NULL,
SS_Sponsor7 VARCHAR(200) NOT NULL,
SS_Address7 VARCHAR(100) NOT NULL,
SS_City7 VARCHAR(100) NOT NULL,
SS_State7 VARCHAR(2) NOT NULL,
SS_Zip7 VARCHAR(10) NOT NULL,
SS_Contact7 VARCHAR(200) NOT NULL,
SS_Contact_Ttl7 VARCHAR(100) NOT NULL,
SS_Contact_Ph7 VARCHAR(40) NOT NULL,
SS_Contact_Fax7 VARCHAR(40) NOT NULL,
SS_Contact_Email7 VARCHAR(100) NOT NULL,
PRIMARY KEY ( id )
);


CREATE TABLE sf(
id INT NOT NULL AUTO_INCREMENT,
SF_Dates_Location TEXT NOT NULL,
SF_Profile_Exhibitors TEXT NOT NULL,
SF_Profile_Attendees TEXT NOT NULL,
SF_Promo_Opp VARCHAR(100) NOT NULL,
SF_EstNetSqFt VARCHAR(100) NOT NULL,
SF_EstNumExhibitingComp VARCHAR(100) NOT NULL,
SF_EstNumProfAttendees VARCHAR(100) NOT NULL,
SF_EstNumHotelRoomNights VARCHAR(100) NOT NULL,
SF_EstPeakRoomNightReq VARCHAR(100) NOT NULL,
SF_PreRegFee VARCHAR(100) NOT NULL,
SF_AtShowRegFee VARCHAR(200) NOT NULL,
SF_ExhibitSpaceCost VARCHAR(200) NOT NULL,
SF_ShowAuditSurvey VARCHAR(200) NOT NULL,
SF_DeptCommCert VARCHAR(200) NOT NULL,
SF_Contractor_Name VARCHAR(200) NOT NULL,
SF_Contractor_Address VARCHAR(100) NOT NULL,
SF_Contractor_City VARCHAR(100) NOT NULL,
SF_Contractor_State VARCHAR(2) NOT NULL,
SF_Contractor_Zip VARCHAR(10) NOT NULL,
SF_Contact VARCHAR(200) NOT NULL,
SF_Contact_Ph1 VARCHAR(40) NOT NULL,
SF_Contact_Email VARCHAR(100) NOT NULL,
SF_Contractor_Name2 VARCHAR(200) NOT NULL,
SF_Contractor_Address2 VARCHAR(100) NOT NULL,
SF_Contractor_City2 VARCHAR(100) NOT NULL,
SF_Contractor_State2 VARCHAR(2) NOT NULL,
SF_Contractor_Zip2 VARCHAR(10) NOT NULL,
SF_Contact2 VARCHAR(200) NOT NULL,
SF_Contact_Ph2 VARCHAR(40) NOT NULL,
SF_Contact_Toll2 VARCHAR(40) NOT NULL,
SF_Contact_Fax2 VARCHAR(40) NOT NULL,
SF_Contact_Email2 VARCHAR(100) NOT NULL,
PRIMARY KEY ( id )
);

CREATE TABLE sm(
id INT NOT NULL AUTO_INCREMENT,
SM_Name VARCHAR(200) NOT NULL,
SM_Mgr VARCHAR(200) NOT NULL,
SM_Mgr_Title VARCHAR(100) NOT NULL,
SM_Mgr_Phone VARCHAR(40) NOT NULL,
SM_Mgr_Email VARCHAR(100) NOT NULL,
SM_Address VARCHAR(100) NOT NULL,
SM_City VARCHAR(100) NOT NULL,
SM_State VARCHAR(10) NOT NULL,
SM_Zip VARCHAR(10) NOT NULL,
SM_Co_Phone VARCHAR(40) NOT NULL,
SM_Co_Toll VARCHAR(40) NOT NULL,
SM_Co_Fax VARCHAR(40) NOT NULL,
SM_Co_Web VARCHAR(100) NOT NULL,
SM_Mgr_TollFree VARCHAR(40) NOT NULL,
SM_Mgr_Fax VARCHAR(40) NOT NULL,
SM_Exhibit_Sales VARCHAR(100) NOT NULL,
SM_Exhibit_Ttl VARCHAR(100) NOT NULL,
SM_Exhibit_Email VARCHAR(100) NOT NULL,
SM_Exhibit_Ph VARCHAR(40) NOT NULL,
SM_Exhibit_TollFree VARCHAR(40) NOT NULL,
SM_Exhibit_Fax VARCHAR(40) NOT NULL,
SM_Contact_Toll VARCHAR(40) NOT NULL,
SM_Contact_Fax VARCHAR(40) NOT NULL,

PRIMARY KEY ( id )
);

laserlight

Curtis782 wrote:
I am following your advice to restructure my database (from what I understand) and was curious if you believe this is an OK way to go about doing that?

Unfortunately, no. There are two general ways to approach database design: you can look at entities and relationships from the ground up, or you can apply normalisation rules. Either way, you should end up with tables that are at least in third normal form.

In your case, you talk about events. Why not have an Event table? You say that "Classification1, Classification2, Classification3, Classification4 refer to industries". Why not have an Industry table? There is a many to many relationship between Events and Industries. So, create a join table to record each case of an event for an industry. Classification5 can just be renamed classification.

Likewise, you have a bunch of SS_* tables, numbered from 1 to 7. Move them to a new table, with a more descriptive name than SS. But don't stop there: look at where else you can make your entities and relationships more distinct, without having to repeat so many things. When you think you are ready, go through the normalisation rules I linked you to and see if your resulting tables are at least in third (or in this case, possibly fourth) normal form.