1

For the life of me I cannot get this to work. I've looked at many articles on stackoverflow so if you could help that would be wonderful! I am working on a form submission for a client. They want to be able to select multiple values from a dropdown, which in turn I will pull from a database to get their query results. 

<form id="test" action="results.php" method="POST">
<select id="role" name="role[]" multiple>
<option value="Student">Student</option>
<option value="Faculty">Faculty</option>
<option value="Alumni">Alumni</option>
</select>

<?php
$query="SELECT City FROM Cities";
$result = mysqli_query($link, $query);
echo '<select name="city" id="city" multiple>';
while($r = mysqli_fetch_assoc($result)){
echo '<option value="'.$r['City'].'">'.$r['City'].'</option>'; }
?>
</select>
<input type="submit"/>
</form>

//results.php

$results=array();

$results[] = $_POST['role'];

$results[]= $_POST['city'];

echo "<pre>";
print_r($results);
echo "</pre>";

**How do I obtain all the values from the array and parse it into separate variables so I can use the variables in a SQL statement? Here is my output: **

Array
(
[0] => Array
    (
        [0] => Faculty
        [1] => Alumni
    )

[2] => Adams
)

Thanks so much for any help! :) And if there is a better way to do this, let me know.

Improve this question
3
  • 1
    It's not considered clean, but you can try extract. I haven't read too deep into your question, so it might not work for you. Commented Apr 24, 2017 at 0:51
  • Post the output that you want. Commented Apr 24, 2017 at 1:06
  • @AliRasheed : I would like each dropdown selection to be a variable, so that if the user selects two options from a dropdown, they would be split. Ex: $ faculty and $alumni. I am building a query based off these to get survey data from the database that matches the user selections. Commented Apr 24, 2017 at 14:17

2 Answers 2

Reset to default
2

[EDIT] : This code is wild open to SQL Injection , Please don't use it.

One submit options i already have in the question and one i created dummy submit options for city, run this code in the different file, than select different different options, and click on submit button, to check how our query is getting built Please read the note first and make sure you read the comment in the code, as they are more important than the code

Note 1-> in short you want to run the query, according to the selected options by the user, make sure you read the comment to understand the logic, comments are more important than the code it's self,

Note 2-> and more thing i did not realize, you may be storing your value in different different table, if that's the case, code will change little bit, but basic shell will remain the same

Note 3-> To achieve the out come which you want to achieve, you basically have to create your query according to the set options, and than use IN keyword and you are good go,

Note 4-> I added echo statement, so you can see stage by stage how our query is developing, i added the comment, if you want see just remove the comment, I did not add the comment in the last echo so you can see the ready to use query string

Note Again-> one submit options i already have, one i created by my self, so you can see what happening, and you it going to work out for you.

as you said in the comment you may have 12 field, in your form, if that's the case, use this code, because lets say if you have to change some thing in the future, and you have to change at tweleve places, you will make mistake like miss some thing, or use the wrong variable or some thing else, with this code, you have to change it one place, and it will get apply to 12 or 24 places, number of places does not matter,

and one more thing, it will better if you wrap this php code inside the function, the reason is lets say you have form on some other page, and you need same functionality only thing you have to do than, just call the function, and in the future if you have change some thing, just change the function code

I am giving you example on your code why it is better to wrap this in a function, lets say your table name are different than the given selected name in your form or you decided to hole values in different different table, than you have to change the code, if you wrote this twelve times or each form, and than you have to change it, than you are in big trouble, but if you use this code as function for different different form, you just have to do some changes in function or in here, and will get applied everywhere, in short chances of you screwing up some thing is just not their, so hope fully this will help you

SideNote -- one more thing i want to say, the reason this solution look big, is because of note, form and comment, if you count the php code line, with out the last echo statement, it actually only 10 lines of php code, so dont get afraid, becuase it's look big

    <form id="test" action="" method="POST">
<select id="role" name="role[]" multiple>
<option value="Student">Student</option>
<option value="Faculty">Faculty</option>
<option value="Alumni">Alumni</option>
</select>

<select id="city" name="city[]" multiple>
<option value="London">London</option>
<option value="Paris">Paris</option>
<option value="New York">New York</option>
</select>
    <input type="submit">
</form>
<?php
//creating variable and saying all the post request is equal to this variable
$selected_options=$_POST;
foreach($selected_options as $key=>$option){
    $countValue = count($option);
    for($i=0; $i<$countValue; $i++){
        /*
         * start adding the value seperated by coma, remember again it going to
         *  be on extra coma so we have to remove it.
         */

        $queryString_start_with_coma .= ",$option[$i]";
    }
    /*
     * come out of loop, and now remove that extra coma
     */
    $queryString_remove_extra_come= preg_replace("/,/", "", $queryString_start_with_coma, 1);
    /*
     * start building your query, use variable $key, just check the line below,
     * you will understand where and why i am using variable $key.
     */
    $query_string_with_and .= " AND $key IN($queryString_remove_extra_come)"; 

    /*
     * now unset the variable, this line is very important, so please also check
     * your out come without this line, 
     * what i am simply  doing is emptying the variable, if you dont 
     * do it, it will add the value in the existing value, which i dont want, what 
     * i want when the loop run for the second selected options, i want my variable
     * to be empty, so i can create new string
     * you will understand more if you remove this line and compare your two outcome
     * Note: you dont have to unset if you dont want to, but you have empty the 
     * variable, you can also do by creating a empty string, do what ever you want
     * to do, just make sure the variable is empty for the second loop
     */
    unset($queryString_start);
}

$query_string_second_part_ready = preg_replace("/AND/", "", $query_string_with_and, 1);
//echo "$query_string_second_part_ready<br>";
$query_string= "SELECT * FROM table_name WHERE ".$query_string_second_part_ready;
//see how your query look like
echo $query_string;
Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

@tadman Here's some clarification: I would like each dropdown selection to be a variable so that if a user selects multiple values from the dropdown, they can be used for a database query. The database has survey data and the user wants to be able to pull in anyone who matches the selections. Ex: a student who has been placed for an internship at a medical clinic and lives in a certain city.
@Francesca what you trying to achieve it is not that complicated, just give me column name for like student, internship medical clinic i am guessing your are storing city in the city column as i saw in your question, or if i am note here, just check my profile, i posted a answer for similiar question a week ago, get your clue from their, or just give me the column, for example, like you are student, alumni and faculty, in which column you are storing role name, or just post a picture of your database, i hope you understand what i am asking
I have twelve columns - I'll just give you the first six. I have role(student, alumni or faculty), internship placement, city (generated from database), congressional district (integer), target population and state. What I'm stuck on is how to pass these values to the mySQL query if the user selects more than one.
tried to add you to this previous comment, user2860957 - not working on my end.
@Francesca i edited my answer, and one more thing when last i commented i made two mistakes, i did not realize you are using two submit options with multiple, and one thing are you using different differnt table for different value, if that's the case in the given answer, code will change little bit, but the basic shell will remain the same, let me know if any confusion let me know that as well
|
0

It sounds like you want to be able to build a query based on the data submitted by the user. This may be a little more complex if you have multiple tables, but the basic idea is to use the input names with the fields, assemble the query from them, prepare the statement and bind the parameters.

Name the inputs the same as the database fields they match to

// Identify which database fields can be searched
// These names must match the names of the inputs
// Each name has a type which will be used later
$databaseFields = [ 'city' => 's', 'name' => 's', 'grade' => 'i' ];
$databaseFieldNames = array_keys($databaseFields);

// Set up the beginning of the query
$query = 'SELECT * FROM some_table WHERE ';

// Initialize an array to use to store fields to be searched
$where = [];

// Loop through all the post data
foreach ($_POST as $name => $value) {

    // If the name is in the database fields list, add it to the query
    if (in_array($name,$databaseFieldNames)) {
        $where[] = $name;
    }
}

// Add all the requested columns to the where
if (!empty($where)) {
    $query .= ' '.$where[0].'=?';
    array_pop($where);
    foreach ($where as $name) {
        if (is_array($_POST[$name])) {
            // Use any to check for multiple possible values
            $query .= ' AND '.$name.' = ANY (?)';
        } else {
            $query .= ' AND '.$name.'=?';
        }
    }
} else {
    // Avoid an empty WHERE which will cause an error
    $query .= ' TRUE';
}

$stmt = mysqli_prepare($query);

/* Bind parameters */
foreach ($where as $name) {
    // $_POST should be validated here
    if (is_array($_POST[$name])) {
        // Arrays are imploded to work in an ANY
        $value = "'".implode("','",addslashes($_POST[$name]))."'";
    } else {
        // Other values are used as sent
        $value = $_POST[$name];
    }
    $type = $databaseFields[$name];
    $stmt->bind_param($type,$value);
}

$stmt->execute();
Improve this answer

5 Comments

@tadman - $databaseFieldNames limits the fields accepted, providing both sanitization and validation.
Ah, right you are. Should be made more clear by wrapping that in a function.
@user2182349 To clarify -- will I need a foreach loop for each database field? (ex: City, Name, Placement, etc. ) ? Or does $name represent all the posted data?
$name will be the name of the inputs.
Thank you so much for your response! It helped me to figure out where I need to go with my sql query. If I could up vote this, I would :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.