PHP code to correctly handle an HTML query

Question

For weeks on end I have worked on MYSQL and PHP. I have built a music database with approx 15 data columns. MANUALLY - I am able to QUERY database with great results:

SELECT * FROM $tableName WHERE category RLIKE ('option-choice') and
genre RLIKE ('option-choice') and Sub_genre RLIKE ('option-choice')
and lyrical_theme RLIKE ('option-choice') and lead RLIKE
('option-choice') and tempo RLIKE ('option-choice'); "

My available HTML FORM has NINE drop down buttons which I want to perform as well, each containing several choices. No radio buttons, check boxes or multiple choices. Each button name is like this:

<select id="category" name="category"><option value="" class="header">Category</option>

Through my HTML Form and _POST, I am able to get any 1 field choice result, output is correctly table formatted with correct colors, etc. GREAT!

When I add a 2nd or a 3rd or 4th form choices, I still get ALL the records but NOT Filtered results as I get above (manually). Visitor choices are NOT 1,2,3, in order, but could be any combination thereof like 2,5,7 or 6,7 or 9 etc....

I need the correct PHP handling statement to produce the correct SELECT statement, (POST ARRAY / IGNORE BLANK CHOICE OPTIONS) to see my efforts become reality. ANY help is truly appreciated. After weeks, I am exhausted from reading on my own and not produce correct results - Hundreds of trial and errors, but at least I have given it my best efforts.. I need help.... ! THANK YOU!!!!

Can you post the php code you use that is not working for you please? Its difficult to debug something that we can't see... — FluffyKitten
– FluffyKitten, Commented Feb 19, 2012 at 17:31
This is the current query - not from a FORM - that I am using to obtain a result - $query1 = "SELECT * FROM $tableName WHERE $category='' OR $categoryIS NULL LIMIT $start, $limit"; $result = mysql_query($query1); What I really need is the correct handling of the form query - Thanks !!!!! - ALSO, I am using MAC SEQUEL PRO to play around with my SELECT STATEMENTs - Thank you ! — 4world2c
– 4world2c, Commented Feb 19, 2012 at 17:53
This code only returns results where the field called $category is empty... I thought you wanted to filter on a particular result? — FluffyKitten
– FluffyKitten, Commented Feb 19, 2012 at 17:56
My code does not work I have used MANUALLY: $query1= "SELECT * FROM $tableName WHERE category= '$category' or genre='$genre' or sub_genre= '$sub_genre' LIMIT $start, $limit"; //$result = mysql_query($query1); - Which works, but this is a MANUAL ENTRY for me to play with - not as a PHP code handling... — 4world2c
– 4world2c, Commented Feb 19, 2012 at 17:59

FluffyKitten · Accepted Answer · 2012-02-19 19:10:57Z

1

Based on your comments I see a few problems: You are using OR instead of AND in your query which is your main problem. AND will select the records that have ALL off the specified options; OR selects any records that have any one of the options even if it doesn't have the rest.

$query1= "SELECT * FROM $tableName 
WHERE category= '$category' AND genre='$genre' AND sub_genre= '$sub_genre' 
LIMIT $start, $limit";

The second thing if I understand correctly is that you want to build the statement dynamically depending on what options are selected?

Check the value of each dropdown list and if it has a value, add to the WHERE clause

$query1 = "SELECT * FROM $tableName WHERE [some condition thats always required]";
if (!empty ($category))
    $query1 .= "AND category = '$category' ";
if (!empty ($genre))
    $query1 .= "AND genre= '$genre' ";
[etc...]
$query1 .= "LIMIT $start, $limit;"

If the only conditions you have in your WHERE clause are the options passed in then you will need to check first if there are any options passed in to determine if you need to add the "WHERE" keyword into your query; and then check which is the first option to be included so you don't include the "AND" keyword when there is no previous condition to add it to. Or you could cheat and put in an arbitrary condition that is always true, e.g. " WHERE $table_id > 0 " (assuming you have an id field in your table)

You also should use numerical ids for the options instead of doing text comparisons on your database which is more inefficient but thats another matter entirely...

edited Feb 19, 2012 at 19:10

answered Feb 19, 2012 at 18:27

FluffyKitten

14.4k10 gold badges44 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FluffyKitten Over a year ago

Glad it woks for you @4world2c. To make it appear in the same page, you can include the processing code in your page with the form and post the page to itself essentially. hen simply display the results in a new div underneath the search.

4world2c Over a year ago

FluffyKitten - Can you please e-mail me - Private consultation pertaining future questions regarding above? - Thank you in advance - [email protected]

FluffyKitten Over a year ago

Sorry @4world2c, I don't give out my personal email :-) But feel free to ask any questions here or in the chat rooms - when I'm offline there will always be someone else around who can help!

Abhay · Accepted Answer · 2012-02-19 18:38:06Z

0

Thank you for pointing to the website. I think the problem you have is that you are using AND for your query conditions. Therefore when from the HTML form, the user selects only one or two fields, say Category and Genre, the values in all other fields is "" (an empty string). When these empty values are put in the query, there are no matching entries found. An example of what the query might look like is:

SELECT * FROM $tableName
WHERE category RLIKE 'Indie'
and genre RLIKE 'Blues'
and Sub_genre RLIKE ''
and lyrical_theme RLIKE ''
and lead RLIKE ''
and tempo RLIKE '';

The solution is to check what non-empty fields are passed from the form and accordingly add the WHERE conditions to the query. Something like:

<?php
$arrConditions = Array();
if (isset($_POST['category']) && !empty($_POST['category'])) {
    $arrConditions[] = 'category RLIKE "' . $_POST['category'] . '"';
}
if (isset($_POST['genre']) && !empty($_POST['genre'])) {
    $arrConditions[] = 'genre RLIKE "' . $_POST['genre'] . '"';
}
if (isset($_POST['sub_genre']) && !empty($_POST['sub_genre'])) {
    $arrConditions[] = 'Sub_genre RLIKE "' . $_POST['sub_genre'] . '"';
}
/* ADD SIMILAR IF STATEMENTS FOR ALL OTHER FORM FIELDS */

$qryWhere = '';
if (!empty($arrConditions)) { // assuming that there is at least one condition
    $qryWhere = ' WHERE ' . implode(' AND ', $arrConditions);
}
$query = 'SELECT * FROM ' . $tableName . ' ' . $qryWhere;
?>

Hope the above makes sense.

answered Feb 19, 2012 at 18:38

Abhay

6,6852 gold badges26 silver badges17 bronze badges

2 Comments

4world2c Over a year ago

I will work with all your answers and post back the results thereof. I thank each one of you for your answers !!!!! - THANK YOU...

4world2c Over a year ago

Stop the Presses. It's working! - I added the following: $result = mysql_query($query); so my rows and results would actually show up... Again Thank you. Now, If results would only show up in the same page through a <div> target? ---- THANK YOU I have 150 hrs into this project !....You answered it in 1 hour !!!!!!!!

Collectives™ on Stack Overflow

PHP code to correctly handle an HTML query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related