Skip to main content
D
dips045
Known Participant
August 22, 2011
Question

PHP MySQL Search two keywords

  • August 22, 2011
  • 2 replies
  • 1219 views

Hi

I have set up a room style selector. Each of these rooms have two keywords that explains the type of style it is designed in - hope this makes sense

The database has been set up like this:

RoomName     Keyword1     Keyword2

room1                abc              xyz 

room2                efg               abc

room3                xyz              rst

I have put the keywords in one checkbox group from which people can select two checkboxes. When they click submit the matching room will appear. (matching both keywords)

I can search for one keyword and display the results......

How can I search for both of the keywords and display the results? ....how can I do this with one checkbox group?

I hope this makes sense! hope someone help!

Many thanks...

This topic has been closed for replies.

2 replies

D
dips045Author
Known Participant
August 24, 2011

Many thanks for your reply guys this was really helpful....

sorry about the dely... I am waiting on the client to get back to me. I never really agreed with two keywords associated with the room style so I am going to discuss the topic further with the client.

thought it was worth the ask though. However I am about to set up the database as suggested by Bregent... but I am having trouble figuring out how I would search for both keywords?

Lon_Winters
Lon_Winters
Inspiring
August 24, 2011

Evaluate your desired user experience to help you in how you set up the database. The term keywords generally indicate a search where the user enters something into a text box, and your database setup and SQL  to determine what the user is actually looking for and retrun results as close as possible to the intent. This would involve a list of keywords like bregent said.if, on the other hand, the user is to select one or more attributes related to the main entity, then this would call for a different way to set up the database and SQL queries. . So I suggest, what you should discuss with the client is what you want the user experience to be when performing a search. There are many options, including entering text into one or more text boxes, selecting specific values from list menus, check boxes. Radio button groups, or various combinations.  I've found that clients usually have a pretty good idea of what they want there because they've used different types of search forms. Sometimes they need direction in order to ensure all bases are covered, and that in turns gives you direction in the best way to configure the database and actually carry out the searches.

D
dips045Author
Known Participant
August 26, 2011

The search will definitely be done using the checkbox group.

I have gone with the checkbox group so the user can select two checkboxes

Still struggling with searching for both keywords?

Lon_Winters
Lon_Winters
Inspiring
August 22, 2011

It's just a matter of adding to the WHERE clause of your SQL. If you created the recorder in simple mode, view it in advanced mode and it should read something like "WHERE keyword1=column1". Below that, the value for column1 is defined. add the the clause so it's: "WHERE keyword1 = column1 AND keyword2 = column2. Below that, enter the definition for column 2 using the existing column 1 info as a guide. It will be the same except the value will come from the second checkbox. Using AND in the where clause means that both keywords must be present to return a result. You can use OR instead of AND if you want a result to be returned if not only both keywords match, but either one or the other.

B
bregent
Participating Frequently
August 22, 2011

Lon, I think it's a bit more complicated because either keyword can occur in either column. So you would need to compare both keywords against both columns. This looks like a case of a database that is not properly normalized. It might be better for the OP to create a keyword table with the following columns:

roomID

keyword

This would link back to the room table by room_id. This design is much more flexible as it easily allows the addition of more keywords and also makes it simple to code for cases where the user only selects a single keyword.

Lon_Winters
Lon_Winters
Inspiring
August 23, 2011

Thank you for pointing that out. I was looking at it as if each keyword column were a specific property of the room, which may still be the case. If Keyword1 actually meant something like "Room size" and keyword 2 was perhaps "Room color", then each column would be distinct in terms of what values they contained.  Even in that scenario, there may be a better way to set up the database. On the other hand, if the columns represent just keywords and may exist in either column, then I to ally agree with your solution.  Even though the SQL statement could be written to compare both fields, twice, it would be un-necessarily complex when a table with a list of keywords would be clearly the better solution as you pointed out.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices