lundi 26 octobre 2015

SELECT query for multiple checkbox in HTML (PHP, Postgresql)

I need to make dynamic SQL query that returns several columns from DB, depending on what data was checked in checkbox html form. In this code it only returns last column of data that was checked, not all. I can only think of examining all combination with IF clause, but since in practice I have much more checkboxes then 3, I'm wondering if someone can suggest some dynamic solution.

<?php
// Connecting to a Postgresql DB
...
// Values sent from HTML form
$checked = $_POST["check_list"];
$submit = $_POST["submit"];
?>

<form action="" method="POST">  
Choose:
<input type="checkbox" name="check_list[]" value="5"> 5h    
<input type="checkbox" name="check_list[]" value="6"> 6h    
<input type="checkbox" name="check_list[]" value="7"> 7h    
<br><br>    
<input type="submit" name="submit">
</form>

 <?php
// Checking if something is checked
// If it is do something
if(isset($submit)){
    if(empty($checked)){
        echo "Please choose something";     
    } else {        
        foreach($checked as $selected){
            // Adding 0 in front of one dimensional numbers because of DB column name
            if ($selected < 10){
                $selected = "0$selected";
            }
            $db_colum_name = "a_b$selected";

            $query = "SELECT table1. name,
                             table1. latitude / CAST(10 AS FLOAT(6)) AS lat,
                             table1. longitude / CAST(10 AS FLOAT(6)) AS lng,
                             EXTRACT(YEAR FROM table2. date) AS year,
                             table2. $db_colum_name AS $selected
                      FROM table1
                      JOIN table2 
                      ON table1.id=table2.id
                      WHERE
                      name = 'New York'
                      AND
                      EXTRACT(YEAR FROM table2. date) = '2000'
                      ORDER BY date DESC";

            $result = pg_query($dbcon, $query) or die("Error: {$query} <br>");
        }

        // Printing data
        ...
        // Closing DB connection
        pg_close ($dbcon);

    }
}
?>




Aucun commentaire:

Enregistrer un commentaire