#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Brisbane, Australia
    Posts
    19
    Rep Power
    0

    2-dimensial array / mysql_fetch_array database problem


    Happy festive season, all. I have a problem which may have a very obvious solution to those with a better knowledge of PHP/MySQL than myself. I am trying to list items in MySQL database in a special way. Some items in the database are multi-part items, that is, the item in the database has multiple parts. Most items do not have multiple parts, but the ones that do have a multipart ID and multipart order field which indicates they are part of a whole item.

    e.g.
    item_id - title - multipart_id - multipart_order
    1 apple null(or "") null(or "") - has no multiparts, standalone item
    2 bananas 1 1 - item has multiple parts, because it is the first part, it has multipart_order of 1
    3 banana1 1 2 - second part of multipart item (multipart_id = 1)
    4 banana2 1 3 - last part of multipart item (multipart_id = 1), but there is no obvious way to tell this
    5 cherries 2 1 - item has multiple parts, because it is the first part, it has multipart_order of 1
    6 cherry1 2 2 - last part of multipart item (multipart_id = 2), but there is no obvious way to tell this

    Items that have multipart_id's are appear in an unordered list in the outputted HTML. e.g.
    <ul>
    <li>bananas</li>
    <li>banana1</li>
    <li>banana2</li>
    </ul>

    Items that have no(null or blank) multipart_id are simply listed. I have changed the code below a little for simplicity.

    The code I use to do this below works, but no doubt it is excessively complicated and slow to run. I guess that my knowledge of 2-dimensional arrays has let me down and there is a much simpler way of doing things. The process I am using in the following code is thus:
    1. Need to find unique multipart id's pulled from database
    2. Then need to create new arrays for each unique multipart id
    3. Then meed to check each item pulled out of database in content listing to see if it is the last element in the array related to it's multipart id. Will need use ($)multipart_id in the array while I am in the "while" loop.

    If anyone can detail a better solution for what I want above I would be most appreciative.

    PHP Code:
    <?php

    // fetch an array containing multipart_id's and multipart_order's for all items
    $result mysql_query("SELECT multipart_id, multipart_order FROM items WHERE multipart_id IS NOT NULL AND multipart_id != ''");
    $num_fields mysql_num_fields($result); 
    $j=0;
    $x=1;
    while(
    $row=mysql_fetch_array($result)){  
        for(
    $j=0;$j<$num_fields;$j++){
            
    $name mysql_field_name($result$j);
            
    $object[$x][$name]=$row[$name];
        }
    $x++;
    }

    // function to get unique multipart_id's
    function unique_multi_array($array$sub_key) {
       
    $target = array();
       
    $existing_sub_key_values = array();
       foreach (
    $array as $key=>$sub_array) {
           if (!
    in_array($sub_array[$sub_key], $existing_sub_key_values)) {
               
    $existing_sub_key_values[] = $sub_array[$sub_key];
               
    $target[$key] = $sub_array;
           }
       }
       return 
    $target;


    // get unique multipart_id's
    $a unique_multi_array($object,"multipart_id");

    // cycle through unique multipart ids and get their corresponding multipart_order's
    foreach($a as $key => $b) {
        
    $tt implode($b,",");
        
    $mi $tt[0];
        
    $result2 mysql_query("SELECT multipart_order FROM items WHERE multipart_id = '".$mi."' AND multipart_order IS NOT NULL AND multipart_order != ''") OR DIE( "Error: ".mysql_error());
        while (
    $a_array mysql_fetch_array($result2MYSQL_NUM)) {
            
    $a_arrays[$mi][] = $a_array;
        }
    }

    // pull the items out of the database
    $items mysql_query("SELECT item_id, title, multipart_id, multipart_order FROM items ORDER BY multipart_id, multipart_order, title"$link);
    $num mysql_num_rows($items);
    if (
    $num 0) {
        
    $i=0;
        while (
    $i $num) { 
            
    $item_id mysql_result($items,$i,"item_id");
            
    $title mysql_result($items,$i,"title");
            
    $multipart_id mysql_result($items,$i,"multipart_id");
            
    $multipart_order mysql_result($items,$i,"multipart_order");
            if (
    is_null($multipart_id) || $multipart_id == "") {
                echo 
    "normal text";
            }
            else if (
    $multipart_order == 1) {
                echo 
    "<ul>\n";
                echo 
    "<li>first list item</li>\n";
            }
            else if (
    $multipart_order == end(end($a_arrays[$multipart_id]))) {
                echo 
    "<li>last list item</li>\n";
                echo 
    "</ul>\n";
            }
            else {
                echo 
    "<li>list item</li>\n";
            }
        }
    }
    ?>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Stevens Point, Wisconsin
    Posts
    623
    Rep Power
    19
    I wonder if everyone got scared off just from looking at your code or something =P

    It seems all you need to do is run 1 query!
    SELECT *
    FROM items
    ORDER BY multipart_id, multipart_order
    From that, I get:

    For me that returns:
    PHP Code:
    id    title     multipart_id     multipart_order 

    1    apple      0    0
    2    bananas    1    1
    3    banana1    1    2
    4    banana2    1    3
    5    cherres    2    1
    6    cherry1    2    2 
    You can have 2 while loops and print out everything =D
    some if's too.. like if ($row->multipart_id)
    //check if it's the same id as last time or not
    else// just echo it out

    Does that solve your problem?
    If not, what kind of recordset do u need to produce the output are you looking for?
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Brisbane, Australia
    Posts
    19
    Rep Power
    0
    Hi wordracr, thanks for your reply. I've nearly got it out using two while loops like you suggested. I also wanted the items (both normal and multipart) in alphabetical order, but I forgot to mention that last time. Only the title of the first part of a multipart item is to be used when sorting alphabetically with the other normal items. The unordered lists I am using are actually "javascript trees", so that when you click on the title of first part of a multi-part item the rest of the parts are shown. You have probably seen them before.

    I actually want two types of sorting. The normal item titles' and the titles of the first part of each multi-part item are to be sorted alphabetically by title, but each part of a multi-part item is to be sorted by it's multipart_order

    So for this data:

    PHP Code:
    item_id title multipart_id multipart_order

    1    C                                
    (i am using varchar at the moment)
    2    PHP tutorial       1     1
    3    Variables          1     2
    4    Arrays             1     3
    5    XML            
    6    MySQL              2     1
    7    Functions          2     2
    8    Reference          2     3 
    I would like the ouputted HTML to look something like:

    C

    <ul>
    <li>MySQL</li>
    <li>Functions</li>
    <li>Reference</li>
    </ul>

    <ul>
    <li>PHP tutorial</li>
    <li>Variables</li>
    <li>Arrays</li>
    </ul>

    XML


    The trouble I am having now is doing both types of sorting. Here is my code so far:

    PHP Code:
    $items mysql_query("SELECT item_id, title, IF((multipart_order != '' AND multipart_order != 1),'a',title) AS mp_order, multipart_id, multipart_order FROM test ORDER BY mp_order, multipart_id, multipart_order");
    $num mysql_num_rows($items);
    if (
    $num 0) {
        
    $i=0;
        while (
    $i $num) { 
            
    $item_id mysql_result($items,$i,"item_id");
            
    $title mysql_result($items,$i,"title");
            
    $multipart_id mysql_result($items,$i,"multipart_id");
            
    $multipart_order mysql_result($items,$i,"multipart_order");
            if (
    is_null($multipart_id) || $multipart_id == "") {
                echo 
    "$title<br/>";
            }
            else if (
    $multipart_order == 1) {
                echo 
    "<ul>\n";
                echo 
    "<li>$title</li>\n";
                
    $j 0;
                while (
    $j $num) {
                    
    $mp_title mysql_result($items,$j,"title");
                    
    $mp_multipart_id mysql_result($items,$j,"multipart_id");
                    
    $mp_multipart_order mysql_result($items,$j,"multipart_order");
                    if (
    $mp_multipart_id == $multipart_id && $mp_multipart_order != $multipart_order) {
                        echo 
    "<li>$mp_title</li>\n";
                    }
                
    $j++;
                }
                echo 
    "</ul>\n";
            }
            
    $i++;
        }

    Apparently, I can't do this:
    IF((multipart_order != '' AND multipart_order != 1). That doesn't matter because I forgot what I was doing with it anyway.

    So I can either take out the mp_order field and output this:

    C
    XML

    <ul>
    <li>PHP tutorial</li>
    <li>Variables</li>
    <li>Arrays</li>
    </ul>

    <ul>
    <li>MySQL</li>
    <li>Functions</li>
    <li>Reference</li>
    </ul>

    or sorting alphabetically by title:

    C

    <ul>
    <li>MySQL</li>
    <li>Functions</li>
    <li>Reference</li>
    </ul>

    <ul>
    <li>PHP tutorial</li>
    <li>Arrays</li>
    <li>Variables</li>
    </ul>

    XML

    Both the above ways are not exactly what I want. Do you have any idea about what I could do? Thanks again.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Brisbane, Australia
    Posts
    19
    Rep Power
    0
    Yay. I had that piece of code wrong
    IF((multipart_order != '' AND multipart_order != 1)
    should have been
    IF((multipart_order != '' AND multipart_order != ''1)
    It seems to work ok now, but I'll do some more testing and find out for sure

IMN logo majestic logo threadwatch logo seochat tools logo