So lately i have been working on a way to post values from a database through checkbox selecting. Thanks to a previous question: How to use checkboxes to retrieve specific data in a database, i managed to get it working!
Although i got it working, i wanted to make my queries a bit more effective when multiple checkboxes are selected instead of writing queries with if/else for every checkbox possibility.
Through some research on stack overflow, this topic in particular: Run a query based on multiple checkboxes, i created the code for my own project. Though, for some reason it just wont output the data correctly, it even wont output anything at all.
So i looked in my code to see where stuff goes wrong. I passed in several echo's to get array information etc, but it all seems ok EXCEPT the last echo where i am echoing the query results (which returns 0).
So i am kinda stuck now since i cant figure out what goes wrong. NOTE: It is for wordpress so that explains the little difference in querying.*
The code is as follows: The HTML Code to display the checkboxes
<form method="post">
<div id="list1" class="dropdown-check-list">
<span class="anchor">Select Authors</span>
<ul class="items">
<li><input type="checkbox" name="columns[]" value="Barry" />Barry</li>
<li><input type="checkbox" name="columns[]" value="Henk" />Henk</li>
<li><input type="checkbox" name="columns[]" value="Nicolas" />Nicolas</li>
</ul>
</div>
<input type="submit" name="go" value="Submit"/>
</form>
The ID is for a Jquery dropdown effect.
The PHP code is as follows:
*The code below defines the column titles that are being displayed/echo'd in a table
$column_names = array(
"Authorss" => "Authorss",
"Research_Source" => "Research_Source",
"Research_Title" => "Research_Title"
);
$sql_columns = array();
foreach($column_names as $i) {
$sql_columns[] = $column_names[$i];
}
The next lines of code checks the checked checkbox values and queries the selected values:
if(!empty($_POST['columns'])) { // empty() checks if the value is set before checking if it's empty.
foreach($_POST['columns'] as $key=>$value){
// Runs mysql_real_escape_string() on every value encountered.
$clean_criteria = array_map('mysql_real_escape_string', $_REQUEST['columns']);
// Convert the array into a string.
$criteria = implode("','", $clean_criteria);
}
$tmp = $wpdb->get_results("
SELECT"
.implode(",", $sql_columns)."
FROM
wp_participants_database
WHERE
authorss IN ($criteria)
ORDER BY
authorss ASC
");
}
If non is selected the query selects all possible results (this still has to be created tho since i want the specific select query to work first):
else {
echo 'still needs to be edit to show everything here';
//$tmp = $wpdb->get_results( "SELECT ".implode(",", $sql_columns)." FROM wp_participants_database;");
}
Now comes the part where the queried data is being showed/put in tables:
echo "<table>
<tr>";
foreach($column_names as $k => $v) {
echo "<th>$v</th>";
}
echo "</tr>";
if(count($tmp)>0){
for($i=0;$i<count($tmp);$i++){
echo "<tr>";
foreach($tmp[$i] as $key=>$value){
echo "<td>" . $value . "</td>";
}
echo "</tr>";
}
}
echo '</table>';
*****UPDATE***** Got it fixed myself! First i deleted:
$column_names = array(
"Authorss" => "Authorss",
"Research_Source" => "Research_Source",
"Research_Title" => "Research_Title"
);
$sql_columns = array();
foreach($column_names as $i) {
$sql_columns[] = $column_names[$i];
}
This above code was unnecessary as the columns were all preset, so i just echo'd them by typing them out.
The problem was the following:
$tmp = $wpdb->get_results("
SELECT"
.implode(",", $sql_columns)."
FROM
wp_participants_database
WHERE
authorss IN ($criteria)
ORDER BY
authorss ASC
");
The $criteria variable should give a string to the IN, though in my code it was parsed as an literal (so no string). It seemed very easy, i just had to add the '' so it would be parsed as a string. Result: WHERE authorss IN ('$criteria')
