SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please help Mysql/PHP and <Select> tag

    Hi everyone! I have a ?simple? problem that i just cannot figure out, i have a table in my database called Categories, with the fields ID, Name, Parent_ID. Now my problem is that I want to put all the categories in a drop down list box (<select>) so that the user can choose one of the categories in the list, and edit or delete that particular category. I have all parts of the page done except for the displaying (edit done, delete done) The problem comes because i need to display the categories as nested like this:

    Category 1
    Subcat1 of Cat1
    Subcat2 of Cat1
    Category 2
    Subcat1 of Cat2
    etc
    etc

    the data is stored like this;
    ID (AUTO PRIMARY)
    Name (Category Name)
    Parent_ID (INT shows ID of parent category or 0 if it is top level)

    anyone have any ideas?? Please help!! (is the database designed poorly??)
    signature

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is what I came up with, didn't have time to figure out if its the fastest way but it works. Just change the table and db names to match your setup.

    PHP Code:
    <?
    $db 
    mysql_connect("localhost""""");
    mysql_select_db(test);
    $result mysql_query("select* from categories order by Parent_ID");
    while(
    $row mysql_fetch_array($result)) {
        
    extract($row);
            
    //If the parent id is 0 then this isa main cat
            
    if($Parent_ID == 0) {
                
    $tcats[$id][] = array("id" => $id,
                                
    "Name" => $Name,
                                
    "Parent_ID" => $Parent_ID
                                
    );
                }
            
    //Or else this is a subcat
            
    else {
                
    $tcats[$Parent_ID][] = array("id" => $id,
                                
    "Name" => $Name,
                                
    "Parent_ID" => $Parent_ID
                                
    );
                }
        }

    ?>
    <select name="categories">
    <option value="">SELECT CATEGORY</option>
    <?
        
    foreach($tcats as $key => $val) {
            if(
    is_array($tcats[$key])) {
                foreach(
    $tcats[$key] as $jey => $jal) {
                    if(
    $tcats[$key][$jey][Parent_ID] == 0) {
    ?>
                        <option value="<?=$tcats[$key][$jey][id]?>"><?=$tcats[$key][$jey][Name]?></option>
    <?
                        
    }
                    else {
    ?>
                        <option value="<?=$tcats[$key][$jey][id]?>">&nbsp;&nbsp;&nbsp;&nbsp;<?=$tcats[$key][$jey][Name]?></option>
    <?
                        
    }
                    }
                }
            }
    ?>
    </select>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Freddy -
    thank you soo much! I didnt get to try it yet, but looks real good...i will post later and tell you how it works/any changes!! thanks again!
    signature

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is turning in to way more than i could have imagined but guess what:

    Since PHP supports indexing into strings via offsets using the same syntax as array indexing, the example above leads
    to a problem: should $a become an array with its first element being "f", or should "f" become the first character of the
    string $a?
    For this reason, as of PHP 3.0.12 and PHP 4.0b3-RC4, the result of this automatic conversion is considered to be
    undefined. Fixes are, however, being discussed.

    ----taken from php manual

    i was getting an error Undefined index: battery in my output, so i checked the manual. i have to admit i have never used multiple dimensional arrays before, and so i pretty much have no idea what to do, i sort of (pretty much)understand what the code does, but i dont have a clue how to fix it, maybee tommorrow when i am not so tired....anyone have any ideas please post, maybee that excerpt has nothing to do with my error, i dont know---after all i have never used a multidemensional array before
    signature

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PLEASE HELP!

    could someone please explain to me how this works//why it isnt working?? i dont understand the multi-dimensionall part, not at all, please help!
    signature

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey can you show us the exact code you tried? Because the code works as advertised on my system. Please show the EXACT code you used as well as your db table structure please. Also what version of PHP you are running.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function ShowCatList()
    {
    Connect();
        $result = mysql_query("select id, Name, Parent_ID from categories order by Parent_ID");

        while($row = mysql_fetch_array($result))
        {
            extract($row); //loads the array $row into variables for each column(id becomes $id)
                    //same as $id=$row["ID"]; for each column

            if($Parent_ID == 0)  //If the parent id is 0 then this is a main cat
            {
                $tcats[$id][] = array("id" => $id,
                            "Name" => $Name,
                            "Parent_ID" => $Parent_ID);
            } else { //Or else this is a subcat
                $tcats[$Parent_ID][] = array("id" => $id,
                            "Name" => $Name,
                            "Parent_ID" => $Parent_ID);
            }
        }

    ?>

    <select name="categories">
    <option value="">SELECT CATEGORY</option>

    <?
        
    foreach($tcats as $key => $val)
        {
            if(
    is_array($tcats[$key]))
            {
                foreach(
    $tcats[$key] as $jey => $jal)
                {
                    if(
    $tcats[$key][$jey][$Parent_ID] == 0)
                    {
    ?>
        <option value="<?=$tcats[$key][$jey][$id]?>"><?=$tcats[$key][$jey][$Name]?></option>
    <?
                    
    } else {
    ?>
        <option value="<?=$tcats[$key][$jey][$id]?>">&nbsp;&nbsp;&nbsp;&nbsp;<?=$tcats[$key][$jey][$Name]?></option>
        
        
        
    <?
                    
    }
                }
            }
        }
    ?>
    </select>
    that is my code, i am using php 4.0.6 i didnt really change the code much except select ID, Name, Parent_ID instead of select * cause i was getting invalid result set....and i changed some stuff to variables because it said it was undefined....I dont really have a clue what i am doing, these multidimensional arrays are complicated...i dont understand what the code is doing...i get lost.. . .
    signature

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ooops sorry here is the table structure:


    mysql> describe categories;
    +-----------+-------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------+-------------+------+-----+---------+----------------+
    | ID | int(11) | | PRI | NULL | auto_increment |
    | Name | varchar(50) | YES | | NULL | |
    | parent_id | int(11) | YES | | NULL | |
    +-----------+-------------+------+-----+---------+----------------+
    3 rows in set (0.38 sec)

    mysql>

    signature

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so you were missing a closing bracket for your function and also

    PHP Code:
                        <option value="<?=$tcats[$key][$jey][id]?>"><?=$tcats[$key][$jey][$Name]?></option>
                        <option value="<?=$tcats[$key][$jey][id]?>">&nbsp;&nbsp;&nbsp;&nbsp;<?=$tcats[$key][$jey][$Name]?></option>
    See how you have $Name in the last brackets it hsould be Name

    PHP Code:
                        <option value="<?=$tcats[$key][$jey][id]?>"><?=$tcats[$key][$jey][Name]?></option>
                        <option value="<?=$tcats[$key][$jey][id]?>">&nbsp;&nbsp;&nbsp;&nbsp;<?=$tcats[$key][$jey][Name]?></option>




    This works on my machine
    PHP Code:
    <?
    function ShowCatList() {
    Connect();
        
    $result mysql_query("select id, Name, Parent_ID from categories order by Parent_ID");

        while(
    $row mysql_fetch_array($result)) {
            
    extract($row); //loads the array $row into variables for each column(id becomes $id)
                    //same as $id=$row["ID"]; for each column

            
    if($Parent_ID == 0) { //If the parent id is 0 then this is a main cat
                
    $tcats[$id][] = array("id" => $id,
                            
    "Name" => $Name,
                            
    "Parent_ID" => $Parent_ID);
                } 
            else { 
    //Or else this is a subcat
                
    $tcats[$Parent_ID][] = array("id" => $id,
                            
    "Name" => $Name,
                            
    "Parent_ID" => $Parent_ID);
                }
            }
    ?>

    <select name="categories">
    <option value="">SELECT CATEGORY</option>

    <?
        
    foreach($tcats as $key => $val) {
            if(
    is_array($tcats[$key])) {
                foreach(
    $tcats[$key] as $jey => $jal) {
                    if(
    $tcats[$key][$jey][Parent_ID] == 0) {
    ?>
                        <option value="<?=$tcats[$key][$jey][id]?>"><?=$tcats[$key][$jey][Name]?></option>
    <?
                        

                    else {
    ?>
                        <option value="<?=$tcats[$key][$jey][id]?>">&nbsp;&nbsp;&nbsp;&nbsp;<?=$tcats[$key][$jey][Name]?></option>
    <?
                        
    }
                    }
                }
            }
    ?>
    </select>
    <?
        
    }
    ShowCatList();
    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that sort of works, the field get put in the select thing, but along with them comes use of undefined constant Name assumed 'Name'...here is the whole thing --what version of php are you using?? i still dont understand what this thing is doing:




    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

    <html>
    <head>
    <title>Untitled</title>
    </head>

    <body>

    <select name="categories">
    <option value="">SELECT CATEGORY</option>

    <br>
    <b>Warning</b>: Use of undefined constant Parent_ID - assumed 'Parent_ID' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>38</b><br>
    <option value="<br>
    <b>Warning</b>: Use of undefined constant id - assumed 'id' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>40</b><br>
    6"><br>
    <b>Warning</b>: Use of undefined constant Name - assumed 'Name' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>40</b><br>
    Electrical</option>
    <br>
    <b>Warning</b>: Use of undefined constant Parent_ID - assumed 'Parent_ID' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>38</b><br>
    <option value="<br>
    <b>Warning</b>: Use of undefined constant id - assumed 'id' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>40</b><br>
    3"><br>
    <b>Warning</b>: Use of undefined constant Name - assumed 'Name' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>40</b><br>
    Control</option>
    <br>
    <b>Warning</b>: Use of undefined constant Parent_ID - assumed 'Parent_ID' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>38</b><br>
    <option value="<br>
    <b>Warning</b>: Use of undefined constant id - assumed 'id' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>45</b><br>
    4">&nbsp;&nbsp;&nbsp;&nbsp;<br>
    <b>Warning</b>: Use of undefined constant Name - assumed 'Name' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>45</b><br>
    Grips</option>
    <br>
    <b>Warning</b>: Use of undefined constant Parent_ID - assumed 'Parent_ID' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>38</b><br>
    <option value="<br>
    <b>Warning</b>: Use of undefined constant id - assumed 'id' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>45</b><br>
    7">&nbsp;&nbsp;&nbsp;&nbsp;<br>
    <b>Warning</b>: Use of undefined constant Name - assumed 'Name' in <b>C:\webroot\DHY\admin\tst.phtml</b> on line <b>45</b><br>
    Battery</option>
    </select>



    </body>
    </html>

    signature

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using 4.0.6 on win2k. Hey try putting ' ' single quotes around those array indexes the ones without $ signs so Name should be 'Name' etc...

    PHP Code:
    <?
    function ShowCatList() {
    Connect();
        
    $result mysql_query("select id, Name, Parent_ID from categories order by Parent_ID");

        while(
    $row mysql_fetch_array($result)) {
            
    extract($row); //loads the array $row into variables for each column(id becomes $id)
                    //same as $id=$row["ID"]; for each column

            
    if($Parent_ID == 0) { //If the parent id is 0 then this is a main cat
                
    $tcats[$id][] = array("id" => $id,
                            
    "Name" => $Name,
                            
    "Parent_ID" => $Parent_ID);
                } 
            else { 
    //Or else this is a subcat
                
    $tcats[$Parent_ID][] = array("id" => $id,
                            
    "Name" => $Name,
                            
    "Parent_ID" => $Parent_ID);
                }
            }
    ?>

    <select name="categories">
    <option value="">SELECT CATEGORY</option>

    <?
        
    foreach($tcats as $key => $val) {
            if(
    is_array($tcats[$key])) {
                foreach(
    $tcats[$key] as $jey => $jal) {
                    if(
    $tcats[$key][$jey]['Parent_ID'] == 0) {
    ?>
                        <option value="<?=$tcats[$key][$jey]['id']?>"><?=$tcats[$key][$jey]['Name']?></option>
    <?
                        

                    else {
    ?>
                        <option value="<?=$tcats[$key][$jey]['id']?>">&nbsp;&nbsp;&nbsp;&nbsp;<?=$tcats[$key][$jey]['Name']?></option>
    <?
                        
    }
                    }
                }
            }
    ?>
    </select>
    <?
        
    }
    ShowCatList();
    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did some more research, and sure enough it syour error reporting level that is causing this. Are you a shared server? So just put the single quotes around the array elements that aren't variables, like above and you should be fine.

    http://www.php.net/manual/en/language.types.array.php
    Look at the Araay dos and don'ts section.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well done freddy, that worked like a charm.,.....i guess its back to the manual for me now....i just cannot grasp this concept--maybee now that it works and i can just look at the code with its output....but thanks again freddy i really, really appreciate it!
    signature


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •