Page 1 of 4 123 ... Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2

    Cant get a select list working with joined table. HELP


    i am going round in circles. Maybe a fresh set of eyes can help
    i have the following table joined

    prod table

    ID (prod)
    name
    CatID

    Size Table

    SizeID
    Size

    Stock Table

    StockID
    ID (prod)
    SizeID
    Stock

    i am trying to populate a select list with sizes from a product

    PHP Code:
    $var3_Recordset1 = "-1";
    if (isset($_GET['ProdID'])) {
      $var3_Recordset1 = $_GET['ProdID'];
    }
    mysql_select_db($database_poochie, $poochie);
    $query_Recordset1 = sprintf("SELECT * FROM poochieProd, poochieStock, poochieSizes WHERE poochieProd.ProdID = %s" AND poochieStock.sizeID = poochieSizes.SizeID, GetSQLValueString($var3_Recordset1, "text"));
    $Recordset1 = mysql_query($query_Recordset1, $poochie) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 = mysql_num_rows($Recordset1)

    <select name="os0" class="text" id="selectSize">
          <option value="Select Size">Select Size</option>
          <?php
                    $query2 
    sprintf("
                    SELECT DISTINCT
                        stock.stockID, size.Size 
                    FROM 
                        poochieProd AS prod 
                        LEFT JOIN pochieStock AS stock ON prod.ID = stock.ID 
                        LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
                    WHERE
                        prod.ID = '%s' AND stock.Stock > 0
                    ORDER BY
                        size.SizeID ASC"
    GetSQLValueString($var3_Recordset1"int"));
                    
    $results2 mysql_query($query2);
                    while(
    $row2 mysql_fetch_array($results2)){
                        
    ?>
          <option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
          <?php
                    
    }
                    
                    
    ?>
        </select>

    i dont know where i am going wrong,

    thanks in advance for any help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,501
    Rep Power
    594
    You need to be more specific. Post some output or something that will help us understand the problem. What is wrong or missing? What error messages are you getting? Did you echo $query2 and then copy and paste that into a command line session?

    P.S. You should consider changing to PDO rather than use the depreciated MySQL library.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    You need to be more specific. Post some output or something that will help us understand the problem. What is wrong or missing? What error messages are you getting? Did you echo $query2 and then copy and paste that into a command line session?

    P.S. You should consider changing to PDO rather than use the depreciated MySQL library.
    ok the Output i got from the select list was

    <b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/usr/users1/ort/public_html/ch/product-detail.php</b> on line <b>132</b>


    and on line 132 the code is

    while($row2 = mysql_fetch_array($results2)){
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    the full out from the select list is



    <select name="os0" class="text" id="selectSize">
    <option value="Select Size">Select Size</option>
    <br />
    <b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/usr/users1/ort/public_html/ch/product-detail.php</b> on line <b>132</b><br />
    </select>
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,501
    Rep Power
    594
    Try debugging the warning with this:
    PHP Code:
    $results2 mysql_query($query2) or die ("Error in query: $query2. ".mysql_error()); 
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    this is helping..it gave me an error regarding a spelling error

    pochieStock

    i corrected this to read poochieStock then it has given an error


    Error in query: SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON Prod.ID = stock.ID LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID WHERE Prod.ID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown table 'Prod' in where clause


    but i am unsure what table 'Prod' the error is referring to?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,501
    Rep Power
    594
    You must not have a table named 'Prod' in your database (show tables). What is that particular column (Prod.ID) supposed to be?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    the tables that i have and the joins are

    poochieCat
    CatID
    name

    poochieStock
    stockID
    ProdID
    sizeID
    stock

    poochieSizes
    SizeID
    Size

    poochieProd
    ProdID
    Prodname
    CatID
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    the tables i included in the first post are not the correct ones from this post, i incorrectly added them so the ones on my previous ones are the correct one
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,501
    Rep Power
    594
    I think you want to remove the dot (.).
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    I think you want to remove the dot (.).

    i have changes the query to

    <?php
    $query2 = sprintf("
    SELECT DISTINCT
    stock.stockID, size.Size
    FROM
    poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON Prod.ID = stock.ID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE
    prodID = '%s' AND stock.stock > 0
    ORDER BY
    size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int"));
    $results2 = mysql_query($query2);
    while($row2 = mysql_fetch_array($results2)){
    ?>

    and am now getting the results

    Error in query: SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON Prod.ID = stock.ID LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID WHERE prodID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Column 'prodID' in where clause is ambiguous

    so i changed this to prod.prodID and it returned

    Error in query: SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON Prod.ID = stock.ID LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID WHERE prod.prodID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown table 'Prod' in on clause
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,501
    Rep Power
    594
    Yes because table 'Prod' does not exist. What table do you want 'ProdID' to be taken from? It exists in more than 1.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    Yes because table 'Prod' does not exist. What table do you want 'ProdID' to be taken from? It exists in more than 1.
    i presume in need it to come from the poochieStock table.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    Yes because table 'Prod' does not exist. What table do you want 'ProdID' to be taken from? It exists in more than 1.

    i thought the poochieProd AS prod

    was telling the next part to see poochieProd as prod so

    WHERE prod.ProdID would see this?
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,501
    Rep Power
    594
    Nope. Use poochieProd.ProdID
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
Page 1 of 4 123 ... Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo