#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2

    Show item size sold out or ignore form list. not working


    i have a product table and a stock table aswell as a few more. to show the item is out of stock there are two ways. i would like to know both so can decide.

    one would be to just the size that has run out just not have it in the list. (this is the easier) i have the code but cant get it to work

    SELECT *
    FROM poochieProd, poochieCat, poochieSizes, poochieStock
    WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = poochieStock.ProdID AND poochieSizes.SizeID = poochieStock.sizeID AND poochieProd.ProdID = var1 AND poochieStock.sold !=1

    then if a value of 1 is in the poochieStock.sold it will not show.

    this isnt however working for me.

    the other is to have the words "soldout" nest to the size that is sold out. With this option however i would need to stop the user selecting this size with either an alert say please choose another size or if they do still choose this size send the to a contact page...

    any help would be greatly appreciated
  2. #2
  3. No Profile Picture
    Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    0
    Hi,

    what does "not work" mean? Do you get an error? If so, what does it say? Or do you get the "wrong" result? We need specific information.

    In any case, this is not proper SQL. Use explicit joins instead of misusing the WHERE clause for join conditions. Specify the columns you want to select. And what is "var1"?
  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 Jacques3
    Hi,

    what does "not work" mean? Do you get an error? If so, what does it say? Or do you get the "wrong" result? We need specific information.

    In any case, this is not proper SQL. Use explicit joins instead of misusing the WHERE clause for join conditions. Specify the columns you want to select. And what is "var1"?
    when i say doesnt work i mean it is still showing the size that has a value of 0 in the DB

    how do you mean specify columns you want to select, i though i was

    the var1 is

    $var1_rsProdList = "-1";
    if (isset($_GET['recordID'])) {
    $var1_rsProdList = $_GET['recordID'];
  6. #4
  7. No Profile Picture
    Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    0
    Originally Posted by jonnyfreak
    when i say doesnt work i mean it is still showing the size that has a value of 0 in the DB
    What value? The "sold" value? But your query selects exactly those sizes with sold != 1?



    Originally Posted by jonnyfreak
    how do you mean specify columns you want to select, i though i was
    I mean a specific column list instead of the wildcard "*". This is considered bad practice, because it selects all columns, even if you don't need them or if they are purely for internal use (or even secret).



    Originally Posted by jonnyfreak
    the var1 is

    $var1_rsProdList = "-1";
    if (isset($_GET['recordID'])) {
    $var1_rsProdList = $_GET['recordID'];
    Your query contains the word "var1" without any quotes or dollar signs. That's neither valid SQL nor valid PHP.

    Anyway, I assume you use another query that's syntactically correct, since you say that you do get results.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by Jacques3
    What value? The "sold" value? But your query selects exactly those sizes with sold != 1?





    I mean a specific column list instead of the wildcard "*". This is considered bad practice, because it selects all columns, even if you don't need them or if they are purely for internal use (or even secret).





    Your query contains the word "var1" without any quotes or dollar signs. That's neither valid SQL nor valid PHP.

    Anyway, I assume you use another query that's syntactically correct, since you say that you do get results.

    sorry the actual sql is below

    PHP Code:
    $var1_rsProdList "-1";
    if (isset(
    $_GET['recordID'])) {
      
    $var1_rsProdList $_GET['recordID'];
    }
    mysql_select_db($database_poochie$poochie);
    $query_rsProdList sprintf("SELECT * FROM poochieProd, poochieCat, poochieSizes, poochieStock WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = poochieStock.ProdID AND poochieSizes.SizeID = poochieStock.sizeID AND poochieProd.ProdID = %s  AND poochieStock.sold !=1"GetSQLValueString($var1_rsProdList"int"));
    $query_limit_rsProdList sprintf("%s LIMIT %d, %d"$query_rsProdList$startRow_rsProdList$maxRows_rsProdList);
    $rsProdList mysql_query($query_limit_rsProdList$poochie) or die(mysql_error());
    $row_rsProdList mysql_fetch_assoc($rsProdList); 

    the one i posted previously wasnt from the source code
  10. #6
  11. No Profile Picture
    Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    0
    OK. But it's still unclear what you want. Which rows do you want in the result set? In other words: Why is your current query not doing what you want?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by jonnyfreak
    sorry the actual sql is below

    PHP Code:
    $var1_rsProdList "-1";
    if (isset(
    $_GET['recordID'])) {
      
    $var1_rsProdList $_GET['recordID'];
    }
    mysql_select_db($database_poochie$poochie);
    $query_rsProdList sprintf("SELECT * FROM poochieProd, poochieCat, poochieSizes, poochieStock WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = poochieStock.ProdID AND poochieSizes.SizeID = poochieStock.sizeID AND poochieProd.ProdID = %s  AND poochieStock.sold !=1"GetSQLValueString($var1_rsProdList"int"));
    $query_limit_rsProdList sprintf("%s LIMIT %d, %d"$query_rsProdList$startRow_rsProdList$maxRows_rsProdList);
    $rsProdList mysql_query($query_limit_rsProdList$poochie) or die(mysql_error());
    $row_rsProdList mysql_fetch_assoc($rsProdList); 

    the one i posted previously wasnt from the source code
    thats what i cant find out. I if the value of poochieStock.sold is 0 not to show that field
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    fixed it, there seemed to be two of the same query. delete one now all good

    PHP Code:
    $var1_rsProdList "-1";
    if (isset(
    $_GET['recordID'])) {
      
    $var1_rsProdList $_GET['recordID'];
    }
    mysql_select_db($database_poochie$poochie);
    $query_rsProdList sprintf("SELECT * FROM poochieProd, poochieCat, poochieSizes, poochieStock WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = poochieStock.ProdID AND poochieSizes.SizeID = poochieStock.sizeID AND poochieProd.ProdID = %s AND poochieStock.sold !=1"GetSQLValueString($var1_rsProdList"int"));
    $rsProdList mysql_query($query_rsProdList$poochie) or die(mysql_error());
    $row_rsProdList mysql_fetch_assoc($rsProdList);
    $totalRows_rsProdList mysql_num_rows($rsProdList); 

IMN logo majestic logo threadwatch logo seochat tools logo