returning a mysql query from a selection in a drop down box

Question

Hi I have created a drop down combo box on a form using a mysql query of 3 tables and this is working fine, now I want to perform another query on the datasabase when I select an item in the drop down combo with this running a query on the database and returning the values required to populate the form with additional data in defined fields. below is how far I have got to date....

$query3 = "SELECT customers.id, customers.email, customers_customfields.customer_id, customers_customfields.field_value, orders.sign_date, orders.sub_id, orders.domain_name, orders.cust_status 
FROM customers, customers_customfields, customers_orders, orders 
WHERE customers_orders.order_id = orders.sub_id
AND customers.id = customers_orders.customer_id 
AND customers.id = customers_customfields.customer_id
AND customers_customfields.field_id = 1";

$result = mysql_query ($query3);
echo "<select name=orders value='Select from the list provided '>";


while($drop=mysql_fetch_array($result)){

//data stored in $drop
echo "<option value=$drop[id]>$drop[sub_id]&nbsp;&nbsp;&nbsp;&nbsp;$drop[id]&nbsp;&nbsp;$drop[field_value]&nbsp;&nbsp;$drop[sign_date]&nbsp;&nbsp;$drop[domain_name]&nbsp;&nbsp;&nbsp;&nbsp;$drop[cust_status]</option>";

}
echo "</select>";

Try searching for `ajax` calls. And please. don't use `mysql_*` it's deprecated. — Bk Santiago, Sep 09 '13 at 01:17

score 0 · Answer 1 · answered Sep 09 '13 at 01:18

0

PHP is strictly a server-side language. If you want to have fields from a database dynamically update the application for the user (without having to submit and refresh), you're going to have to use AJAX.

Read through some AJAX tutorials to get the general idea.

Also, it would be a good idea to use a database abstract layer such as PDO.

It's helpful in almost any context, allowing for the use of prepared statements, as well as other goodies. Also, use MySqli in place of MySql, which is deprecated.

Ajax tutorials: http://www.smashingmagazine.com/2008/10/16/50-excellent-ajax-tutorials/

PDO: http://php.net/manual/en/book.pdo.php

Prepared statements: http://php.net/manual/en/pdo.prepared-statements.php

MySqli: http://php.net/manual/en/book.mysqli.php

answered Sep 09 '13 at 01:18

LoganEtherton

495
4
12

hi i am ok with including a submit button on the form to run a query which should keep it all inside the form??? – user2759987 Sep 09 '13 at 01:21
I don't think you're going to want the user to have to submit the form each and every time they select something on a dropdown menu. Each time you communicate with the database, the page is going to have to submitted and refreshed. It'll be disruptive to the user. – LoganEtherton Sep 09 '13 at 01:24
http://stackoverflow.com/questions/15415571/ajax-php-drop-down-list gives a good example of what you are trying to do – skv Sep 09 '13 at 02:33

score 0 · Answer 2 · answered Sep 12 '13 at 08:49

You should indeed work with AJAX and PHP.

For example, first set a jQuery.change trigger on your dropdown doing like this:

$(document).ready(function () {

    $('#yourDropDown').change(function (e) {
        yourFunction($(this).val());
    });
});

function yourFunction(inputString) {
        $.ajax({

            url: "../folder/yourPHP.php",
//The inputString will contain the value of your DropDown, you will give this parameter in the querystring
            data: 'queryString=' + inputString,
            success: function (msg) {
//MSG will be the "message" from the query
//As you see you'll put the MSG in your HTML of your #yourFormTag
//This message can contain everything you want
                if (msg.length > 0) {
                    $('#yourFormTag').html(msg);

                }
            }
        });
}

Just AN Example of A .PHP file:

<?php
if (isset($_REQUEST['queryString'])) {

    include '../yourConnection.php';



    $inputString = $_GET['queryString'];

    $select_query = "SELECT <yourColumns>

            FROM <yourTable>
            WHERE <Where you want to check if inputString is the same> LIKE CONCAT('%', :inputString, '%')
            ORDER BY <order if you want? ASC";

    $get = $db->prepare($select_query);

    $get->execute(array(':inputString' => $inputString);

    //For example to fill a select
    echo '<select>';

    while ($row = $get->fetch(PDO::FETCH_ASSOC)) {
        echo "<option value='" . $row['yourColumn'] . "'>" . $row['yourColumn'] . "</option>";
    }

    echo '</select>';

}
?>

As I said the .PHP file is an example.

Hope this helps, Cheers!

returning a mysql query from a selection in a drop down box

2 Answers2