SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile want php/mysql advice

    Hello, I'm a beginner looking for a better way to do something that I seem to be doing a lot of.

    Here's some common code:
    PHP Code:
    $subcatagoryresult mysql_query("select superID, subID, subcategoryMain from subcategory");
    while ( 
    $row mysql_fetch_array($subcatagoryresult)) {
        
    $superID $row["superID"];
        
    $subID $row["subID"];
        
    $subcategoryMain $row["subcategoryMain"];
        ...
    other stuff......
        } 
    I've got a pretty good handle on this much. Here's the problem. $superID and $subID both represent ID numbers of categories from another table. In the output, I'd like to match up the ID numbers with the category names from another table.

    My current method for doing this seems inefficient: basically I step all the way through the table with the category names and echo the category name if the the ID numbers match.

    Example:
    PHP Code:
    mysql_data_seek($subcatagoryresult0); 
    while ( 
    $row mysql_fetch_array($subcatagoryresult)) {
        
    $superID $row["superID"];
        
    $subID $row["subID"];
        
    $subcategoryMain $row["subcategoryMain"];

        
    mysql_data_seek($catagoryresult0); 
        while ( 
    $row mysql_fetch_array($catagoryresult)) {
            
    $categoryID $row["categoryID"];
            
    $categoryName $row["categoryName"];
            
    $categoryDescription $row["categoryDescription"];
            if (
    $superID==$categoryID) {
            echo(
    "$categoryName<br>
                 
    $categoryDescription<br>
                 <p></p>"
    )
            }
            }
        } 
    Hope that gives you some idea of what I want to achieve. Is there an easier way to do it?

    Thanks,
    Sam
    Last edited by samsm; Mar 8, 2002 at 09:47.

  2. #2
    SitePoint Enthusiast jsegura's Avatar
    Join Date
    Feb 2002
    Location
    Barcelona (Catalonia-Spain)
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join the tables

    Hello samsm,

    it seems you need to do a join between the two tables. To do this, simply rewrite the query as:

    select subcategory.superID, subcategory.subID, subcategory.subcategoryMain, category.categoryID,
    category.categoryName,category.categoryDescription
    from subcategory,category
    where subcategory.superID=category.categoryID;

    Now you can get the results opening as usual and doing:

    mysql_fetch_row and getting it with $row[0]...

    or

    mysql_fetch_array and $row["subcategory.superID"]...

    Hope this helps.

    Regards,
    Juan.

  3. #3
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That does help. I've read about joins but have not become fluent yet... now would be an excellent time to start.

    I keep thinking that there's something that I am not clear about.... then I come up with an answer so I think I just need to work on this tonight.

    Thanks!
    Sam


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
  •