Difficulty: Moderate
Knowledge: Intermediate/Advanced
My partner in crime and I have been working on a website. We’re hoping it becomes the next big evolution in _______. We are sworn to secrecy until the site is released. We started this several months ago and have been toiling away at making it completely dynamic. PHP/HTML/CSS/FLASH/MySQL are the main methods of madness. This project was undertaken before I started my Databasing class or my PHP/MySQL class. We still haven’t covered much of the material I find myself using. The latest mountain we had to conquer was dynamic SQL queries based upon a form with OPTIONS. For your imagination… it had 6 comboboxes, 2 of which are required, 4 of which are optional. Here’s the dilemma.
You have your typical database connection, which I won’t get into for this as it is fairly straightforward. The html form has passed the variables using POST. So we pull those in with the php $_POST feature and assign them to a PHP script variable:
$person1 = $_POST['person1'];
$person2 = $_POST['person2'];
$person3 = $_POST['person3'];
$language1 = $_POST['lanuage1'];
$language2 = $_POST['lanuage2'];
$language3 = $_POST['lanuage3'];
$criteria = $_POST['criteria'];
This is relatively easy stuff, but I will explain these variables and what they do. Within the form they MUST select at least 1 person and 1 language, and choose SPEAKS or DOESN’T SPEAK (the criteria). They optionally may search more than 1 person and more than 1 language.
Here’s the basic, first part of the query:
$sqlBeginning = “SELECT * FROM PERSONS WHERE (PERSON1_ID=$person1 OR PERSON2_ID=$person1 OR PERSON3_ID=$person1 “;
Notice this is only comparing the first (REQUIRED) variable to the 3 columns within the database. (PERSON1_ID, PERSON2_ID,PERSON3_ID). Also take note that there is an opening ‘(’ without a closing ‘)’, this will be addressed later. With me so far? Good.
Now to the fun part. We have to create dynamic SQL statement IF they have selected the other options.
if ($person2 == ‘0′)
{
$person2String = “”;
}
else{
$person2String = “OR PERSON1_ID=$person2 OR PERSON2_ID=$person2 OR PERSON3_ID=$person2 “;
}
This has checked to see if $person2 is set to ‘0′ and if it is the string is blank, otherwise it compares the $person2 variable to the 3 columns as we did with $person1 above. Repeat the same process for $person3.
Now if we wanted to combine all these our SQL string could go something like this…
$sqlStatement = $sqlBeginning . $person2String . $person3String . “)”;
Notice the closing “)” on the end. SWEET. The first part is over. Take a breather.
Now, because the languages can be a “true/false” or “speaks/doesn’t speak” we need an if statement to compare that.
if ($criteria == ‘1′){
}
else{
}
Make sense? Cool. Now, we will put more coding into there, because the SQL statements will change from = to <> depending on which option they selected.
if ($criteria == ‘1′){
//This line sets the $language1String to it’s value because it is a REQUIRED item so no if/else is needed. It uses AND to compare the PERSON that has been selected to the language options specified. It checks $language1 against all 3 columns in the DB for languages (LANGUAGE1_ID,LANGUAGE2_ID,LANGUAGE3_ID). It also must make sure that language 2 and 3 are not null.
$language1String = “AND (LANGUAGE1_ID<>$language1 AND ((LANGUAGE2_ID<>$language1) OR (LANGUAGE2_ID IS NULL)) AND ((LANGUAGE3_ID<>$language1) OR (LANGUAGE3_ID IS NULL))) “;
//NOW we check to see if language 2 option has been selected, much like the persons 2 above if it set to ‘0′ we assign the string to blank
if ($language2 == ‘0′){
$language2String = “”;
}
else{
//if it isn’t ‘0′, then we add another AND to the SQL statement and perform the same steps as above, but with $language2 variable. The same step is repeated for $language3.
$language2String = “AND (LANGUAGE1_ID<>$language2 AND ((LANGUAGE2_ID<>$language2) OR (LANGUAGE2_ID IS NULL)) AND ((LANGUAGE3_ID<>$language2) OR (LANGUAGE3_ID IS NULL))) “;
}
}
The very last part of this mess. Is the ELSE, which instead of making sure the person DOESNT speak the language’s specified, it checks to make sure they DO speak that language. You can copy and paste all of the above code and simply change the <> to = for all the options. ALSO, remove the IS NULL check. This isn’t necessary for this part.
The final SQL variable would look like this:
$sqlStatement = $sqlBeginning . $person2String . $person3String . “) ” . $language1String . $language2String . $language3String;
The benefit of coding it this way is to save on unnecessary queries to the database. Why check an option if they haven’t specified it? This will create extra work for the server and slow down your website. While small databases, it isn’t a big deal, but for thousands, or hundreds of thousands of records this could save a lot of server work.
In more detail, the code performs checks to verify what the user has selected, creates the SQL SELECT statement on the fly. So these quick IF ELSE statements are one TRUE/FALSE, but if you were to query all 6 possibilities to the 6 columns, it will be searching 3×3=9 for persons and 3×3=9 (18) times to the db for a TRUE/FALSE for each record, when it may only need to run 6 queries (1×3 for persons, 1×3 for languages).
Don’t forget to run your query with:
$results = mysql_query(”$sqlStatement”);
Hope this helps someone!
