CODEWITHSUNDEEP
phpmysqlforms
4world2c
4world2c

Reputation: 31

PHP code to correctly handle an HTML query

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!!!!

Upvotes: 1

Views: 234

Answers (2)

FluffyKitten
FluffyKitten

Reputation: 14312

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...

Upvotes: 1

Abhay
Abhay

Reputation: 6645

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.

Upvotes: 0

Related Questions