Results 1 to 11 of 11
  1. #1
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Multiple query
    Here's the problem, I am modding a script and didn't design the db tables, I would have done them differently but it would be too much to try to change them.

    The table is setup basically like this categoryid, parentcategory and name.

    I need help with getting the category and parentcategory. Here is where I'm having an issue:
    eg.
    category parentcategory name
    1 0 widgets
    2 0 gadgets
    3 0 gizmos
    4 1 blue widgets
    5 1 green widgets
    6 1 brown widgets
    7 2 blue gadgets
    8 2 green gadgets
    9 2 brown gadgets
    10 3 blue gizmos
    11 3 green gizmos
    12 3 brown gizmos
    13 4 large blue widgets
    14 8 small green gadgets
    15 12 medium brown gizmos
    I can find all the category ids of the main categories by using where parentcategory ='0', simple enough.

    I can query for all subcategories of the main categories by using where main category id = parentcategory. Still not too complicated.

    Here we go...

    I hit a wall trying to find the next level. I need to find all sub-subcategories (medium brown gizmos) their (parent) subcategory (brown gizmos) and their (parent) category (gizmos) and have that in a $result set I can print.

    I thought I could do something like:
    PHP Code:
    $sql="SELECT categoryid FROM categories WHERE parent = '0' && category_id > '0' 
    $result = mysql_query($sql);
    while(
    $row= mysql_fetch_array($result)){
    $sql2="SELECT FROM categories WHERE categoryid '$row[categoryid]' || parent '$row[categoryid]'}
    $result2 mysql_query($sql2);
    while(
    $row2mysql_fetch_array($result2))
    $sql3="SELECT * FROM categories WHERE categoryid = '$row[categoryid]' || parent = '$row[categoryid]'}
    $result3 = mysql_query($sql3);
    while(
    $row3= mysql_fetch_array($result3))
    {print "
    $row3[name]" } 
    This is just an example of how I thought it could work....but I get an error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in... which appears to be in the last mysql_fetch_array.

    I was basically just trying to loop the queries, getting all the categories based on the parent category. I need to somehow tie the association of category 15 (medium brown gizmos) with the main category of 3 (gizmos) keeping in mind that I can't use any common term such as gizmos which would make it way too easy.
    Someday starts today
    Military Discounts

  2. #2
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Without digging into the actual query itself, which I will do here in a minute, I noticed that you have an underscore in only one of the categoryid field calls. It's in the first query, that would be throwing you the error if it supposed to be categoryid rather than category_id.

    Let me know if that helps, if not I will go through the query.

  3. #3
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    knight01,

    This first thing I saw is the ||. Why is that in the middle of your query? To do an OR in SQL you'd use:

    SELECT fields FROM table WHERE var=value OR var2 = value2.

    Try using OR not ||.

    also...
    This might not help this particular situation. But I saw your example and thought of some good practices you might want to implement:


    1. A suggestion for your debugging. Work on your query by itself *outside* of the PHP first. This is just a general good practice. Use phpmyadmin or even better sqlyog (free). Fine tune your query and get that working. Then drop it into PHP. When you do this you eliminate any questions as to where your issues are. If you have a perfectly working query and drop it into PHP then you know any issues end up being related to the PHP and not the sql. Obviously when working this way you can't use your PHP variables. But you can substitute those during testing.

    2. Try to really avoid using SELECT * at all costs. Very poor performing. Select the particular columns you want in the order that you want them. Even if you have a lot of columns and you select them all it is still faster than SELECT *

    Patrick
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  4. #4
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Quote Originally Posted by bettylou
    Without digging into the actual query itself, which I will do here in a minute, I noticed that you have an underscore in only one of the categoryid field calls. It's in the first query, that would be throwing you the error if it supposed to be categoryid rather than category_id.

    Let me know if that helps, if not I will go through the query.
    BettyLou, Thanks, but that is just a typo. I started to hand write out the code and got tired so copied and pasted and didn't correct it.
    Someday starts today
    Military Discounts

  5. #5
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Should print each of your queries after you build them. Usually it's very clear what's going on when you see them.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  6. #6
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Quote Originally Posted by patrick24601
    knight01,

    This first thing I saw is the ||. Why is that in the middle of your query? To do an OR in SQL you'd use:

    SELECT fields FROM table WHERE var=value OR var2 = value2.

    Try using OR not ||.
    Patrick, Thanks, I do || because that is how I learned to do it. I seem to recall having an occasional issue with using OR for some reason... but I changed it and tested and still have the error in the mysql_fetch_array.

    Quote Originally Posted by patrick24601
    also...
    This might not help this particular situation. But I saw your example and thought of some good practices you might want to implement:

    1. A suggestion for your debugging. Work on your query by itself *outside* of the PHP first. This is just a general good practice. Use phpmyadmin or even better sqlyog (free). Fine tune your query and get that working. Then drop it into PHP. When you do this you eliminate any questions as to where your issues are. If you have a perfectly working query and drop it into PHP then you know any issues end up being related to the PHP and not the sql. Obviously when working this way you can't use your PHP variables. But you can substitute those during testing.
    Not sure how to run this in phpmyadmin without using to php variables. I could do something like:
    SELECT * FROM categories WHERE categoryparent = '0' or categoryid = '$row[categoryid]'... but what mysql variable would I use in place of the $row that was gotten in the first query?

    Quote Originally Posted by patrick24601
    2. Try to really avoid using SELECT * at all costs. Very poor performing. Select the particular columns you want in the order that you want them. Even if you have a lot of columns and you select them all it is still faster than SELECT *
    Patrick
    Agreed, Typically in the initial testing jsut to keep things easier in my limited brain I use * but when I go to production I"ll name the actual fields in the Select statement.

    Any other ideas?
    Someday starts today
    Military Discounts

  7. #7
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Quote Originally Posted by Snib
    Should print each of your queries after you build them. Usually it's very clear what's going on when you see them.

    - Scott
    Hey Snib, thanks, I'm using a var_dump for $result and it shows something about Resource_id... I would think it would be printing the actual content of the table rows.

    As an update, I see I am likely var dumping the wrong variable. $result is what is showing the
    string(15) "Resource id #10" string(30) "Resource id #10Resource id #12" string(45) "Resource id #10Resource id #12Resource id #14" string(60) "Resource id #10Resource id #12Resource id #14Resource id #16" string(75) "Resource id #10Resource id #12Resource id #14Resource id #16Resource id #18"
    If I var_dump $row I do see the expected information...
    array(2) { [0]=> string(1) "4" ["category_id"]=> string(1) "4" } array(2) { [0]=> string(2) "37" ["category_id"]=> string(2) "37" } array(2) { [0]=> string(2) "38" ["category_id"]=> string(2) "38" } array(2) { [0]=> string(2) "39" ["category_id"]=> string(2) "39" } array(2) { [0]=> string(2) "40" ["category_id"]=> string(2) "40" }
    But still get the fetch error.
    Last edited by knight01; May 4th, 2008 at 08:44 PM.
    Someday starts today
    Military Discounts

  8. #8
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    What I meant was put each of these before your mysql_query() calls to see what you're sending to mySQL:

    Code:
    print "$sql<br>";
    print "$sql2<br>";
    print "$sql3<br>";
    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  9. #9
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    I think you want to use $row2[categoryid] in $sql3.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  10. #10
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Maybe I'm approaching this wrong.

    Is there a way to loop the select statement adding data to the $result variable?

    I tried something like
    $result. = mysql_query($sql);

    But it didn't like that at all. After doing the print $sql, it looks like all the exepcted categoryid information is there, but still not working as expected.
    Someday starts today
    Military Discounts

  11. #11
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    I don't think so. A single $result is a multi dimensional array. Don't know how you'd concatenate that.

    Try what Snib is saying and do an ECHO on some of your SQL before you execute it so we can see what you are attempting to execute. It seems to me you are not executing what you think is executing.

    [for the future]

    Consider moving this code into a function that you can call

    PHP Code:
    function get_subcategory_list($parentcategoryid)
    {

    $sql="SELECT categoryid, categoryname FROM categories WHERE parent = $parentcategoryid";

    $result mysql_query($sql);

    return 
    $result;


    You can the call it over and over:
    PHP Code:
    list1 get_subcategory_list(0);
    // loop through list1
    while looping list1 {
      
    list1category list1[0];
      
    list2 get_subcategory_list(list1category );
      while 
    looping list2 {
          
    list2category list2[0];
          
    list3 get_subcategory_list(list2category );
    // end of list2 
    // end of list1 
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  12. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Multiple products, multiple merchants
    By zendango in forum ShareASale - SAS
    Replies: 4
    Last Post: January 22nd, 2010, 07:28 AM
  2. Multiple products from multiple merchants?
    By JamesS in forum PopShops
    Replies: 3
    Last Post: October 2nd, 2007, 02:21 AM
  3. Query Multiple tables... help!!
    By Doug247 in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: June 4th, 2004, 04:47 PM

Posting Permissions

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