formatting mysql data into array

[mbarandao]

hello:

I'm pulling mysql data and want to load into an array. I getting the data in like manner:

 $result = mysql_query("SELECT DISTINCT item2 FROM products");
        echo "var option_array = new Array(num_of_cats);\n";
        $count=1;
        echo"option_array[0] = new Array(\"Please Select a Merchandise\");";
        while($row = mysql_fetch_array($result))
          {
        echo"option_array[".$count."] = new Array(\"--select One--\",\"\",\"\");";
        $count++;
        }

I need my array as reflected within the echo to output like this

option_array[1] = new Array(\"-- Select One --\",\"CNN\",\"ABC News\");

for the echo, I have tried, with no luck:

 echo"option_array[".$count."] = new Array(\"".$row['item2']."\");";
        $count++;

Any help, please!

[vsolute]

do you want to insert them into database?

[diafol]

Sorry, but this looks like a strange setup. how's this?

$list = "\n<select id=\"products\">\n\t<option>--select One--</option>";
while($row = mysql_fetch_array($result)){
    $list.= "\n\t<option value=\"{$row['item2']}\">{$row['item2']}</option>"; 
}
$list .= "\n</select>\n";
//...then finally
echo $list;

[mbarandao]

Thank you both for chiming in. Basically, I'm simply trying to get the data from mysql to be put in an array in this format:

option_array[1] = new Array("-- Select One --\",\"CNN\",\"ABC News\");  

CNN, ABC News are the values from db.

I'm working on a chained dropdown boxes which are populated from mysql. the challenge is that I'm adding a description box to the selection.

At the moment I'm able to get to it work if I hard code the arrays in this manner:

option_array[1] = new Array("-- Select One --\",\"CNN\",\"ABC News\");

So I need to be able to format the array in that structure to get it to work. The array is then executed with the following js, to populate the second box and display the correct description for the each item in list. here is the js:

  function switch_select()

{
  for (loop = window.document.PaymentForm.select_2.options.length-1; loop > 0; loop--)
  {
    window.document.PaymentForm.select_2.options[loop] = null;
  }

  for (loop = 0; loop < option_array[window.document.PaymentForm.select_1.selectedIndex].length; loop++)
  {
    window.document.PaymentForm.select_2.options[loop] = new Option(option_array[window.document.PaymentForm.select_1.selectedIndex][loop]);
  }

  window.document.PaymentForm.select_2.selectedIndex = 0;
}

function switch_text()

{
window.document.PaymentForm.mytextarea.value = text_array[window.document.PaymentForm.select_1.selectedIndex][window.document.PaymentForm.select_2.selectedIndex];
}

function set_orig()
{
  window.document.PaymentForm.select_1.selectedIndex = 0;
  window.document.PaymentForm.select_2.selectedIndex = 0;
}

diafol, Your sugguest, is how my first dropdown box is populated, but I have to create an array as mentioned above in order to populate the next box.

I appreciate any further thoughts on this!
Mossa

[diafol]

Ok I need more info - what data are you retrieving for the 2nd dropdown? How is it dependent on the value of the first dropdown?

In other words, give the structure of your data (DB)
which fields need to be retrieved
how should they be displayed
what's this description box?

You will require some ajax or just one-shot js (json) for this, depending on the total amount of data you could retrieve via all combinations - easier than it sounds.

[mbarandao]

Data structure: (bold --> field heading)

**id |  items   | item2 | invt  | product_desc  |  quantity |   unit_cost| **   
    1 | CAR CARE SERVICES|  Chassis Lube|   n/a|    Injected grease between moving joints and into ‘ze...|  0 | 0.00

All fields are to be retrieved and are displayed in the sequence outlined below

items ---> this field represent the main category of product/services (content of 1st list box)
item2 ----> this field represent the subcategory of services dependent upon the services from the first box

I may be able to best explain what's happening here with the js with the hard coding of data into the array:
content of primary box:

<select name="select_1" onChange="switch_select(); switch_text();">
        <option>-- Categories --</option>
        <option>CAR CARE SERVICES</option>
        <option>BRAKE SYSTEMS</option>
        <option>COOLING SYSTEM</option>
        <option>ect.. dependent of DB content</option>
      </select>

Content of secondary box

 var option_array = new Array(num_of_cats);
    option_array[0] = new Array("You need to select a category"); 
    option_array[1] = new Array("-- Select One --", "Chassis Lube",  "Oil & Filter Changes", "Transmission Service");
    option_array[2] = new Array("-- Select One --", "Brake Pads", "Calipers", "etc...");
    option_array[3] = new Array("-- Select One --", "Radiator", "etc...");

Description boxes and other linked boxes

 var text_array = new Array(num_of_cats);
    text_array[0] = new Array(" before anything is selected from main box this is displayed in description box. "); 
    text_array[1] = new Array(" "Chassis lubrication.","Oil Changes.","New transmission.");//
    text_array[2] = new Array("Description for option_array2, again formated in quotes and separated with comma for each descriptoom");
    text_array[3] = new Array("Description for option_array3, again formated in quotes and separated with comma for each descriptoom");

Does this give any clarity at all?

I appreciate your help on this

[diafol]

Yep fine. BUT this isn't a very good data setup.

You should have a categories table which is then linked to the items table :

cat_id | category_label
1 | CAR CARE SERVICES
item_id | cat_id | item2 | invt ...etc
3 | 1 | Lube | 0 | ....

In this way you can use integers to relate tables and stop all those problems with text fields, e.g. mistyping etc.
Also consider a better method of storing NULL or 0 than n/a
I'll get back to you after you consider this change.

[mbarandao]

Thanks, Diafol!

Ok, I have considered the new table structure. I think as you write, would be a much more efficient way. The n/a actually would represent just that but I need to come up with a different value for the category that will not need inventory.

[diafol]

OK!

You have the option now of using ajax or vanilla php. To be honest, facing a page reload on every selection from dropdown #1 is tedious, so perhaps it's best to ajaxify. However, it could be possibl, if you just want to read data, to read the entire table(s) into json format, so you don't involve the server at all for subsequent dropdown calls. It depends what you want to do with the data.

$r = mysql_query("SELECT cat_id, category_label FROM categories ORDER BY category_label");
if(mysql_num_rows($r)){
    $dd1 = "\n<select id=\"categories\">\n\t<option value=\"0\">--select One--</option>";
    while($d = mysql_fetch_assoc($r)){
        $dd1.= "\n\t<option value=\"{$d['cat_id']}\">{$d['category_label']}</option>"; 
    }
    $dd1 .= "\n</select>\n";
}

$r = mysql_query("SELECT item_id, cat_id, item2... FROM items ORDER BY cat_id, item2");
if(mysql_num_rows($r)){
    while($d = mysql_fetch_assoc($r)){
        $array[$d['cat_id']][] = array('id'=>$d['item_id'],'item2'=>$d['item2'],...);
    }
    $json = json_encode($array);
}

//HEAD OR BOTTOM OF PAGE - if HEAD consider using document.ready - BTW using jQuery
<script type="text/javascript">
    var data = <?php echo $json;?>;
    var id = 1; //default value on startup
    function getData(id){
        //call data and apply it to the second dropdown
        //also populate the description box
    }
    getData(id);
    $('#categories').click(function(){
       id = $('#categories').val();
       //do check here that it's not 0
       getData(id);
    });


</script>



//BODY / FORM

<form...>
    <label for="categories">Category:</label>
    <?php echo $dd1;?>
    <label for="items">Item:</label>
    <select id="items"></select>
    <div id="descbox"></div>
</form>

Sorry I rushed this - it's gone bed time. Will come back sometime tomorrow if you need any more help.

[mbarandao]

I appreciate the work. I'm going throught it now. I'll advise shortly.

Thanks again!

[mbarandao]

Looks great! It seems that I'm missing something with the second box. it is not populating...The id of the second select box should be its value in the php --is that correct?

$r = mysql_query("SELECT item_id, cat_id, subcat_label, invt, product_desc, qty, cost FROM service_subcat ORDER BY cat_id, subcat_label");
if(mysql_num_rows($r)){
while($d = mysql_fetch_assoc($r)){
    $array[$d['cat_id']][] = array('id'=>$d['item_id'],'subcat_label'=>$d['subcat_label'], 'invt' =>$d['invt'], 'product_desc' =>$d['product_desc']);
}

in the above, php/sql I have what should be populated in the second box as subcat_label. I'm understanding this correctly. I'm able to see that the data is being pulled correctly in safari console.

[diafol]

I think I made a mistake on this :)

$r = mysql_query("SELECT item_id, cat_id, subcat_label, invt, product_desc, qty, cost FROM service_subcat ORDER BY cat_id, subcat_label");
if(mysql_num_rows($r)){
while($d = mysql_fetch_assoc($r)){
    $array[$d['cat_id']][$d['item_id']] = array('subcat_label'=>$d['subcat_label'], 'invt' =>$d['invt'], 'product_desc' =>$d['product_desc']);
}

THat will allow you to access the json data via item id.

Sorry, I didin't have enough time to write the js to create the 'insert into div' js function. Am going to work now, so probably won't get another look-in till later.

I should point out that this is not my usual approach and that ajax may be a better alternative. I provided this in order to reduce the load on the server. If you don't care for this approach, say so and I or some other contributor may come back with ideas for ajax.

[diafol]

OK, for js(on) solution - I'm rubbish as js, so bear with me:

<?php
    $link = mysql_connect('localhost', 'root', '');
    if (!$link) {
        die('Not connected : ' . mysql_error());
    }
    $db_selected = mysql_select_db('myDB', $link);
    if (!$db_selected) {
        die ('Can\'t use foo : ' . mysql_error());
    }
    $r = mysql_query("SELECT c.cat_id, c.cat_label, i.item_id, i.item_name, i.`desc` FROM cats AS c INNER JOIN items AS i ON c.cat_id = i.cat_id ORDER BY c.cat_label, i.item_name");
    if(mysql_num_rows($r)){
        $dd1 = "\n<select id=\"categories\">\n\t<option value=\"0\">--select One--</option>";
        $catname = '';  
        while($d = mysql_fetch_assoc($r)){
            if($catname != $d['cat_label'])$dd1.= "\n\t<option value=\"{$d['cat_id']}\">{$d['cat_label']}</option>"; 
            $array[$d['cat_id']][$d['item_id']] = array('item_name'=>$d['item_name'], 'desc' =>$d['desc']);
            $catname = $d['cat_label'];
        }
        $dd1 .= "\n</select>\n";
    }
    $arr = json_encode($array);
    //print_r($arr); //testing purposes

?>
<!DOCTYPE HTML>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    var data = <?php echo $arr;?>;
    var id;
    function getData(passCatID){
        var content = '';
        var desc = '';
        id = passCatID;
        $.each(data[id], function(key,value) {
            if(desc == '')desc = value['desc'];
            content += '<option value="' + key + '">' + value['item_name'] + '</option>';
        });
        $('#items').html(content);
        $('#descbox').html('<p>' + desc + '</p>');
    }
    function getDesc(passItemID){
        var itemID = passItemID;
        var desc = data[id][itemID]['desc'];
        $('#descbox').html('<p>' + desc + '</p>');    
    }
    $('#categories').change(function(){
       id = $('#categories').val();
       getData(id);
    });
    $('#items').change(function(){
       var itemID = $('#items').val();
       getDesc(itemID);
    });
});

</script>
</head>
<body>
<form>
    <label for="categories">Category:</label>
    <?php echo $dd1;?>
    <label for="items">Item:</label>
    <select id="items"></select>
    <div id="descbox"></div>
</form>
</body>
</html>

[mbarandao]

Good day Diafol:

I appreciate the second round at this. I'm implementing now. Will advise shortly!

Thank you,
Mossa

[mbarandao]

Here is what I have. On test, I'm getting an Invalid UTF-8 sequence in argument error (I'm searching the web for familiarity). It's pointing to:

$arr = json_encode($array);

I have printed the array, it looks fine.

complete code...

$r = mysql_query("SELECT sc.cat_id, sc.category_label, ss.item_id, ss.subcat_label, ss.invt, ss.product_desc, ss.invt, ss.qty, ss.cost FROM service_cat AS sc INNER JOIN service_subcat AS ss ON sc.cat_id = ss.cat_id ORDER BY sc.category_label, ss.subcat_label");
    if(mysql_num_rows($r)){
        $dd1 = "\n<select id=\"categories\">\n\t<option value=\"0\">--select One--</option>";
        $catname = '';  
        while($d = mysql_fetch_assoc($r)){
            if($catname != $d['category_label'])$dd1.= "\n\t<option value=\"{$d['cat_id']}\">{$d['category_label']}</option>"; 
            //$array[$d['cat_id']][] = array('item_id'=>$d['item_id'],'subcat_label'=>$d['subcat_label'], 'invt' =>$d['invt'], 'product_desc' =>$d['product_desc'], 'qty'=>$d['qty'], 'cost'=>$d['cost']);
            $array[$d['cat_id']][$d['item_id']] = array('subcat_label'=>$d['subcat_label'], 'invt' =>$d['invt'], 'product_desc' =>$d['product_desc'], 'qty'=>$d['qty'], 'cost'=>$d['cost']);
            $catname = $d['category_label'];
        }
        $dd1 .= "\n</select>\n";
    }
    $arr = json_encode($array);
    //print_r($arr); //testing purposes

?>
<html>
<title></title>
<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    var data = <?php echo $arr;?>;
    var id;
    function getData(passCatID){
        var content = '';
        var product_desc = '';
        id = passCatID;
        $.each(data[id], function(key,value) {
            if(product_desc == '')product_desc = value['product_desc'];
            content += '<option value="' + key + '">' + value['subcat_label'] + '</option>';
        });
        $('#service_subcat').html(content);
        $('#descbox').html('<p>' + product_desc + '</p>');
    }
    function getDesc(passItemID){
        var itemID = passItemID;
        var product_desc = data[id][itemID]['product_desc'];
        $('#descbox').html('<p>' + product_desc + '</p>');    
    }
    $('#categories').change(function(){
       id = $('#categories').val();
       getData(id);
    });
    $('#service_subcat').change(function(){
       var itemID = $('#service_subcat').val();
       getDesc(itemID);
    });
});

</head>


<body>
<form name=product_services>
<table border=1px><tr>
<td>Services/Product</td><td>Sub Category</td><td>Availability</td><td>Description</td><td>Quantity</td><td>Cost</td></tr>
<tr>
<td>

    <!--label for="categories">Category:</label-->
    <?php echo $dd1;?></td>
   <td><select id="service_subcat" name="service_subcat"><option>--Select One</option></select></td>
    <td><div id="invt"></div></td>
    <td><div id="descbox" style="border-top: 1px solid red"></div></td>
    <td><input type=text id="qty" style="border-top: 1px solid red" size=4></td>
    <td><div id="unit_cost" style="border-top: 1px solid red" ></div></td>
    </tr></table>

</form>
</body>
</html>

[mbarandao]

I think I see my problem. I'm missing the closing </script> tag. So far so good. Still testing....

[mbarandao]

That missing script closing tag was the issue. everything is working as conceptualized. Thanks Diafol!
Please, have your next beer or meal on me. Let me know how I can make that happen...

The very best!

Here is the complete working code....

<?php

include '../datalogin.php';
$r = mysql_query("SELECT sc.cat_id, sc.category_label, ss.cat_id, ss.item_id, ss.subcat_label, ss.invt, ss.product_desc, ss.invt, ss.qty, ss.cost FROM service_cat AS sc INNER JOIN service_subcat AS ss ON sc.cat_id = ss.cat_id ORDER BY sc.category_label, ss.subcat_label");
//$result = mysql_query($r);   
  if(mysql_num_rows($r)){
        $dd1 = "\n<select id=\"categories\">\n\t<option value=\"0\">--select One--</option>";
        $catname = '';  
        while($d = mysql_fetch_array($r)){
            if($catname != $d['category_label'])$dd1.= "\n\t<option value=\"{$d['cat_id']}\">{$d['category_label']}</option>"; 
            $array[$d['cat_id']][$d['item_id']] = array('subcat_label'=>$d['subcat_label'], 'invt' =>$d['invt'], 'product_desc' =>$d['product_desc'], 'qty'=>$d['qty'], 'cost'=>$d['cost']);
            $catname = $d['category_label'];
        }
        $dd1 .= "\n</select>\n";
    }

 $arr = json_encode($array);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<title></title>

<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    var data = <?php echo $arr;?>;
    var id;
    function getData(passCatID){
        var content = '';
        var product_desc = '';
        var invt = '';
        var qty = '';
        var cost = '';
        id = passCatID;
        $.each(data[id], function(key,value) {          
            if(invt == '')invt = value['invt'];
            if(product_desc == '')product_desc = value['product_desc'];
            if(cost == '')cost = value['cost'];
            content += '<option value="' + key + '">' + value['subcat_label'] + '</option>';

        });
        $('#service_subcat').html(content);
        $('#descbox').html('<p>' + product_desc + '</p>');
        $('#invtbox').html('<p>' + invt + '</p>');
        $('#qtybox').html('<p>' + qty + '</p>');
        $('#costbox').html('<p>' + cost + '</p>');
    }
    function getDesc(passItemID){
        var itemID = passItemID;
        var invt = data[id][itemID]['invt'];
        $('#invtbox').html('<p>' + invt + '</p>');  

         var product_desc = data[id][itemID]['product_desc'];
        $('#descbox').html('<p>' + product_desc + '</p>');  

        var cost = data[id][itemID]['cost'];
        $('#costbox').html('<p>' + cost + '</p>');   
    }
    $('#categories').change(function(){
       id = $('#categories').val();
       getData(id);
    });
    $('#service_subcat').change(function(){
       var itemID = $('#service_subcat').val();
       getDesc(itemID);
    });
});
</script>
</head>

<body>
<form name="services">
<table border=1px style="visibility:visible"><tr>
<td>Services/Product</td><td>Sub Category</td><td>Availability</td><td>Description</td><td>Quantity</td><td>Cost</td></tr>
<tr>
<td>

    <?php echo $dd1;?></td>
   <td><select id="service_subcat"></select><option>Select Category</option></td>
    <td><div id="invtbox"></div></td>
    <td><div id="descbox" style="border-top: 1px solid red"></div></td>
    <td><input type=text id="qtybox" style="border-top: 1px solid red" size=4></td>
    <td><div id="costbox" style="border-top: 1px solid red" ></div></td>
    </tr></table>

</form>
</body>

</html>

[diafol]

Yipee! Ok, mark as solved. I'm so pleased - I actually wrote some js that worked!!! Perhaps it's not the best in the world, but at least there aren't any yellow hazard signs throwing two fingers up at me :)

[mbarandao]

Indeed! Indeed! Indeed!!!

Do let me know how you may have your next beer or meal on me!

PS: I'm attempting some other configuration with the code, which involve full integration into a form. I will need the entire script to be dynamically added as an option should the user like to add other products to the form. May I call upon you for some additional guidance?

Best!

[diafol]

Sure. But as long as it's on the forum. No PMs.