Getting mysql value to variable?

[GlenRogers]

Hi

I have some code that has 2 querys to fill 2 dropdown boxes. I need to get an id from one depending on which selection is chosen, put it in a variable to use in the second query.

My code is below with comments of what I want to do.

//populate cat form dropdown box
   $dd = '';
   $r = mysql_query("SELECT cat_id, cat_label FROM categories");
   if (mysql_num_rows($r)){
      while ($d = mysql_fetch_assoc($r)){
         $dd .= "\n\t<option value='{$d['cat_id']}'>{$d['cat_label']}</option>";
         //I need to get cat_id into a variable($cat_id) from here and use it in the below query
      }
   }
   //populate subcat form dropdown box
   $op = '';
   $r = mysql_query("SELECT name, product_id FROM cat_images WHERE cat_id = $cat_id");
   if (mysql_num_rows($r)){
      while ($d = mysql_fetch_assoc($r)){
         $op .= "\n\t<option value='{$d['product_id']}'>{$d['name']}</option>";
      }
   }

Can anyone offer advice on this?

Thanks for looking

Glen

[diafol]

This could be done more effectively with javascript. In the method above, you seem to need a page load just because you choose a value from the first dropdown. With js, you have a choice - load all values from the DB into a js variable on page load and use those to populate the second dropdown on first dropdown select (good for static DB data and relatively small datasets) OR use ajax to retrieve live data from the DB to populate the second dropdown.

I recently wrote a blog post on the first method. The second method is pretty straightforward, especially with jQuery. Let me know if you're interested in that.

Will help with the question, if you still need a plain php solution.

[GlenRogers]

I'm always interested in learning new things! So the second method would be good if you could help me with that.

Thanks

[diafol]

Ok, here's some old stuff I cobbled together. Check the fieldnames etc. I'm using mysqli.

<?php
$mysqli = new mysqli('localhost', 'root', '', 'daniweb');
$query = 'SELECT cat_id, category FROM `cat`';
$result = $mysqli->query($query) or die('help');
$catOps = '';
if($result->num_rows){
    while ($row = $result->fetch_assoc()) {
        $catOps .= "\n\t<option value='{$row['cat_id']}'>{$row['category']}</option>";
    }
}
?>


<form method="post">
    <label for="cat"></label>
    <select name="cat" id="cat">
        <?php echo $catOps;?>
    </select>
    <label for="subcat"></label>
    <select name="subcat" id="subcat">
    </select>
    <input name="submit" id="submit" value="Submit" />
</form>


<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script>
//bind category dropdown change event
$('#cat').change(function(){
    cat = $(this).val();
    getSubCat(cat);

});
//change subcat dropdown
function getSubCat(cat){
    if(cat == null)cat = $('#cat').val();
    $.post("includes/subcat.php", { cat: cat }).done(function(data) {
        $('#subcat').html(data);
    });
}
//run on page load
getSubCat();
</script>

The ajax php script is like this:

<?php
if(isset($_POST['cat'])){
    $cat = intval($_POST['cat']);
    $mysqli = new mysqli('localhost', 'root', '', 'daniweb');
    $query = "SELECT subcat_id, subcat FROM `subcat` WHERE cat_id = $cat";
    $result = $mysqli->query($query) or die('error');
    $subcatOps = '';
    if($result->num_rows){
        while ($row = $result->fetch_assoc()) {
            $subcatOps .= "\n\t<option value='{$row['subcat_id']}'>{$row['subcat']}</option>";
        }
    }
    echo $subcatOps;
}
?>

So php basically the same as the main page code. As such, you could create a class and create methods for this, but may be overkill. Note that ajax page element update requires php to echo stuff out so that the function can pick it up. Note - this isn't production code - just an example of how it could be done. You'll need to include error trapping etc. See pritaeas's code snippet on mysqli for this.

[GlenRogers]

Thanks alot for that Al. It worked a treat!

Glen

[diafol]

No prob. I looked at this again and it looked a bit unclean:

$('#cat').change(function(){
    cat = $(this).val();
    getSubCat(cat);
});
//change subcat dropdown
function getSubCat(cat){
    if(cat == null)cat = $('#cat').val();
    $.post("includes/subcat.php", { cat: cat }).done(function(data) {
        $('#subcat').html(data);
    });
}

Could be:

$('#cat').change(function(){
    getSubCat();
});
//change subcat dropdown
function getSubCat(){
    cat = $('#cat').val();
    $.post("includes/subcat.php", { cat: cat }).done(function(data) {
        $('#subcat').html(data);
    });
}

That should clean up the need to check whether a value has been passed to the getSubCat() function. So, as you can see the js bit of an ajax call - especially if using jQuery - is trivial. The PHP ain't so bad either.