tags:

views:

94

answers:

2
Q: 

using JQuery with mySQL confusion

HI, I have code like this. what I am doing is populating the first select with the MySQL data and based on the selection the first , using jQuery, I am populating the second one. Now, my question is, can I use the same combos_get.php to populate the another select based on user selection from the second select?

Is yes, then please look at the comment 'stuck at this point' where I am confused on how to get the data on the the third select.

    <html>
          <head>
            <link href="style23.css" rel="stylesheet" type="text/css" />
            <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"&gt;&lt;/script&gt;

            <title></title>

          </head>
        <body>

        <div align="left" style="position:absolute;top:10px;">

         <select name="select1"  id="select1" size="10px;">
        <?php 
        // Make a MySQL Connection
        mysql_connect("localhost", "root", "bob") or die(mysql_error());
        mysql_select_db("mydb") or die(mysql_error());

        $result = mysql_query("select * from results where ID NOT LIKE 'Ex%' ") or die(mysql_error());  

        // store the record of the "example" table into $row
        while($row = mysql_fetch_array( $result )) {
        // Print out the contents of the entry 
        ?>
         <option value="<?php echo $row['ID']; ?>"><?php echo $row['ID'] ?></option>

        <?php
            }
            ?>
            </select><br>

            <script type="text/javascript">
        $(document).ready(function() {
            $('#select1').change(getDropdownOptions);
     //     $('#select2').change(getDropdownOptions); // stuck at this point 

        });

        function getDropdownOptions() {
            var val = $(this).val();
            //alert(val);
            // fire a POST request to combos_get.php
            $.post('combos_get.php', { value : val }, populateDropdown, 'html');
            //alert('s');
        }

        function populateDropdown(data) {
            if (data != 'error') {
                $('#select2').html(data);
            }
        }
        </script>
        </div>  
        <div align="left" style="position:relative;left:250px;">
        <select name="select2"  size="10px;" id="select2">
        <option value="--">--</option>
        </select>
        </div>

        <div  style="position:relative;left:450px;top:10px">
        <select name="select3"  size="10px;" id="select3">
        <option value="--">--</option>
        </select>
        </div>

        </body>
        </html>

    **combos_get.php**

<?php
if (!empty($_POST['value'])) {

$val = $_POST['value'];
mysql_connect("localhost", "root", "bob") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());

$result = mysql_query("select ID2 from results where ID = \"$val\" ") or die(mysql_error());  

while($row = mysql_fetch_array( $result )) {
$html .= '<option value="1">'.$row['ID2'].'</option>';
}
    die($html);
    }


die('error');

?>
+1  A: 

You will more than likely want another handler for this case. It's up to you whether or not the same PHP file can handle the query, however:

$(document).ready(function() {
    $('#select1').change(getDropdownOptions);
    $('#select2').change(getSecondDropdownOptions);
});

function getSecondDropdownOptions() {
    var val = $(this).val();
    $.post('combos_get.php', { value : val }, populateSecondDropdown, 'html');
}

function populateSecondDropdown(data) {
    if (data != 'error') {
        $('#YOURNEXTSELECT').html(data);
    }
}

Common practice is to reuse as much code as possible. I don't have time to refactor since I just got to work but someone is more than welcome to clean this up for him.

cballou  2009-12-08 13:45:23
I created second handler like you stated , but when I select any option from the second select, the contents in the second select are getting erased and nothing is getting added in the third.
JPro  2009-12-08 13:52:22
everything is working now ONLY in FIREFOX, in IE, the contents are getting erased. Strange!!. any bug in handling jquery in IE?
JPro  2009-12-08 14:02:34
The contents of what? The select you intend to inject the opitions into? The incorrect select? All selects? the entire document?
prodigitalson  2009-12-08 14:42:25
It is fixed. thank you. compatability issue with IE8
JPro  2009-12-08 15:36:20
You should probably hook cballou up with the answer then :-)
prodigitalson  2009-12-08 16:46:28
+1  A: 

In order to do that you need to make populateDropdown use a dynamic target.

something like:

 function getDropdownOptions(event) {
            var e = $(this);
            var val = e.val();

            // fire a POST request to combos_get.php
            $.post('combos_get.php',{ value : val }, function(data){
                     populateDropdowns(data, e);
                }, 'html');

        }

        function populateDropdown(data, e) {
            // e is our originating select

            /* HERE You need to come up with a way to determin the target to populate based on the element that triggered it. for example assuming you only have 3 selects:*/
            switch(e.attr(id)){
              case 'select2':
                var targetSelector = '#select3';
                break;
              case 'select1':
                var targetSelector = '#select2';
                break;
              default:
                var targetSelector = null;
                break;
             }

            if (data != 'error' && targetSelector) {
                $(targetSelector).html(data);
            }
        }
prodigitalson  2009-12-08 13:52:23
interesting, but how do I declare $('#select1').change(getDropdownOptions);is it $('#select1').change(getDropdownOptions(event));
JPro  2009-12-08 13:56:23
no... the event is passed in automatically by jQuery. its a jquery.Event objet. you can actually not even use it.. i just included it for completeness. You can see http://docs.jquery.com/Events/jQuery.Event for more detail about the event object.
prodigitalson  2009-12-08 14:12:48

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL