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

    Join Date
    Aug 2011
    Posts
    170
    Rep Power
    55
    Originally Posted by jonnyfreak
    ok thanks i have changed that and it is now giving me another error

    Error in query: SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON prod.ProdID = 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 column 'stock.ID' in 'on clause'
    Looking at the table structure you posted earlier, that seems pretty self explanatory. Look at your error message and you tell me what's wrong with it.

    This forum is for helping you learn how to code on your own. If you don't make an attempt to fix it on your own, no one is going to do it for you.
  2. #32
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    Originally Posted by SecurityDavid
    Looking at the table structure you posted earlier, that seems pretty self explanatory. Look at your error message and you tell me what's wrong with it.

    This forum is for helping you learn how to code on your own. If you don't make an attempt to fix it on your own, no one is going to do it for you.
    thanks, yes i dont expect the code to be done for me. i am trying to figure out whats wrong with it.

    Unknown column 'stock.ID' in 'on clause'

    obviously i need to now look at this error so i take it i need to add the table name to where this stock.ID sits?
  4. #33
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    Originally Posted by jonnyfreak
    thanks, yes i dont expect the code to be done for me. i am trying to figure out whats wrong with it.

    Unknown column 'stock.ID' in 'on clause'

    obviously i need to now look at this error so i take it i need to add the table name to where this stock.ID sits?
    ok. i have done the following

    <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 poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.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)){
    ?>
    <option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
    <?php
    }

    ?>
    </select>

    the following is no longer showing an error

    <?php $results2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error()); ?>

    however no sizes are shown in the drop down list even though there are sizes in the DB

    i have also looked in the source code to see any error occurring in drop down but this is just blank. can you help
  6. #34
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    Originally Posted by jonnyfreak
    ok. i have done the following

    <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 poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.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)){
    ?>
    <option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
    <?php
    }

    ?>
    </select>

    the following is no longer showing an error

    <?php $results2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error()); ?>

    however no sizes are shown in the drop down list even though there are sizes in the DB

    i have also looked in the source code to see any error occurring in drop down but this is just blank. can you help
    these are the query's

    $var1_rsProdList = "-1";
    if (isset($_GET['recordID'])) {
    $var1_rsProdList = $_GET['recordID'];
    }
    mysql_select_db($database_poochie, $poochie);
    $query_rsProdList = sprintf("SELECT * FROM poochieProd, poochieCat WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = %s", 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);

    if (isset($_GET['totalRows_rsProdList'])) {
    $totalRows_rsProdList = $_GET['totalRows_rsProdList'];
    } else {
    $all_rsProdList = mysql_query($query_rsProdList);
    $totalRows_rsProdList = mysql_num_rows($all_rsProdList);
    }
    $totalPages_rsProdList = ceil($totalRows_rsProdList/$maxRows_rsProdList)-1;

    $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 poochieStock.sizeID = poochieSizes.SizeID AND poochieProd.ProdID = %s", 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);

    are these correct for what i am trying to call?
  8. #35
  9. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,114
    Rep Power
    487
    Originally Posted by jonnyfreak
    are these correct for what i am trying to call?
    I don't know ... have you tried echo the sql to screen then copy/paste that into something like PHPMyAdmin to see what results you get?


    Also, why in this thread DOESN'T ANYONE USE PHP TAGS?!
    Argh ...
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  10. #36
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    Originally Posted by badger_fruit
    I don't know ... have you tried echo the sql to screen then copy/paste that into something like PHPMyAdmin to see what results you get?


    Also, why in this thread DOESN'T ANYONE USE PHP TAGS?!
    Argh ...
    yes i have I copied the below query to the phpMyAdmin
    PHP Code:
    <?php
    $query2 
    sprintf(
            SELECT DISTINCT stock.stockID, size.Size 
            FROM poochieProd AS prod 
            LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID 
            LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID 
            WHERE prod.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 it returned the following

    PHP Code:
    Error
    There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

    ERROR: Unknown Punctuation String @ 7
    STR: <?
    SQL
    :       <?php
    $query2 
    sprintf(
            SELECT DISTINCT stock.stockID, size.Size 
            FROM poochieProd AS prod 
            LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID 
            LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID 
            WHERE prod.ProdID = '%s' AND stock.stock > 0 
            ORDER BY size.SizeID ASC"
    GetSQLValueString($var3_Recordset1"int"));      <?php
    $query2 
    sprintf(
            SELECT DISTINCT stock.stockID, size.Size 
            FROM poochieProd AS prod 
            LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID 
            LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID 
            WHERE prod.ProdID = '%s' AND stock.stock > 0 
            ORDER BY size.SizeID ASC"
    GetSQLValueString($var3_Recordset1"int"));      <?php
    $query2 
    sprintf(
            SELECT DISTINCT stock.stockID, size.Size 
            FROM poochieProd AS prod 
            LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID 
            LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID 
            WHERE prod.ProdID = '%s' AND stock.stock > 0 
            ORDER BY size.SizeID ASC"
    GetSQLValueString($var3_Recordset1"int"));      <?php
    $query2 
    sprintf(
            SELECT DISTINCT stock.stockID, size.Size 
            FROM poochieProd AS prod 
            LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID 
            LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID 
            WHERE prod.ProdID = '%s' AND stock.stock > 0 
            ORDER BY size.SizeID ASC"
    GetSQLValueString($var3_Recordset1"int"));

    SQL query

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

    MySQL said

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php
    $query2 sprintf(
            SELECT DISTINCT stock.stockID, size.Size 
    ' at line 1

    i dont know what this means but it doesnt look good!
  12. #37
  13. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,114
    Rep Power
    487
    It means that you've copied the PHP into your SQL query; you need to echo to screen the SQL and copy that, not your PHP code which produces the SQL query ...
    You copied and pasted:


    <?php
    $query2 = sprintf("
    SELECT DISTINCT stock.stockID, size.Size
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.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 tried to run that as a query ... when you should have only copied this bit:-

    SELECT DISTINCT stock.stockID, size.Size
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.ProdID = '%s' AND stock.stock > 0
    ORDER BY size.SizeID ASC

    And replaced %s with the actual value you're wanting to narrow your query resultset down with.

    When I am working/debugging PHP / SQL I would do something like this:-

    PHP Code:
    $mydate "01/01/2012";
    $sql "SELECT `calldate`, `calltime` FROM `calldata` WHERE `calldate` <  {$mydate}";
    echo 
    $sql
    When I run that code, the SQL is sent to my browser:-
    Code:
    SELECT `calldate`, `calltime` FROM `calldata` WHERE `calldate` <  01/01/2012
    and it's THAT that I copy and paste into MySQL, NOT my actual PHP code otherwise, it gives a large error like you got.
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  14. #38
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    Originally Posted by badger_fruit
    It means that you've copied the PHP into your SQL query; you need to echo to screen the SQL and copy that, not your PHP code which produces the SQL query ...
    You copied and pasted:


    <?php
    $query2 = sprintf("
    SELECT DISTINCT stock.stockID, size.Size
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.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 tried to run that as a query ... when you should have only copied this bit:-

    SELECT DISTINCT stock.stockID, size.Size
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.ProdID = '%s' AND stock.stock > 0
    ORDER BY size.SizeID ASC

    And replaced %s with the actual value you're wanting to narrow your query resultset down with.

    When I am working/debugging PHP / SQL I would do something like this:-

    PHP Code:
    $mydate "01/01/2012";
    $sql "SELECT `calldate`, `calltime` FROM `calldata` WHERE `calldate` <  {$mydate}";
    echo 
    $sql
    When I run that code, the SQL is sent to my browser:-
    Code:
    SELECT `calldate`, `calltime` FROM `calldata` WHERE `calldate` <  01/01/2012
    and it's THAT that I copy and paste into MySQL, NOT my actual PHP code otherwise, it gives a large error like you got.
    thanks for your help, i added the following (like you said) and replaced the %s with a 5 because i know there is a product id in the DB with the value of 5

    i got these results

    MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)

    Code:
    SELECT DISTINCT stock.stockID, size.Size
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.ProdID =  '5'
    AND stock.stock >0
    ORDER BY size.SizeID ASC 
    LIMIT 0 , 30
    so its saying its empty even though there is a record in there?
  16. #39
  17. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,114
    Rep Power
    487
    OK, at least we can work from that ... I presume that this simple query returns something ?

    Code:
    SELECT *
    FROM poochieProd AS prod
    WHERE prod.ProdID =  '5'
    ... if that works then try this :-

    Code:
    SELECT DISTINCT *
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    WHERE prod.ProdID =  '5'
    ... keep slowly building the query from the ones above until you finally get the 0 rows and when that happens, you can identify a little clearer whereabouts the query is no longer giving results and can, hopefully, change it so it does!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  18. #40
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    170
    Rep Power
    55
    Code:
    SELECT DISTINCT stock.stockID, size.Size
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
    WHERE prod.ProdID = '%s' AND stock.stock > 0
    ORDER BY size.SizeID ASC
    It looks like you may still have an instance where the field you reference doesn't exist in the fields you listed earlier. Everything is case sensitive. (This may have been a typo earlier and correct now, only you can know that).
  20. #41
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    SELECT *
    FROM poochieProd AS prod
    WHERE prod.ProdID = '5'

    returned

    Showing rows 0 - 0 (1 total, Query took 0.0003 sec)

    and showed the record containing the ProdID5

    and the headers
    ProdID Prodname ProdDesc ProdPrice ProdImageLarge ProdPayPayID CatID
  22. #42
  23. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,114
    Rep Power
    487
    Originally Posted by jonnyfreak
    SELECT *
    FROM poochieProd AS prod
    WHERE prod.ProdID = '5'

    returned

    Showing rows 0 - 0 (1 total, Query took 0.0003 sec)
    OK, so it found a row, so now expand the query but just a little otherwise you won't know what breaks it ...
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  24. #43
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    3
    Originally Posted by badger_fruit
    OK, so it found a row (1 total) ... isn't that right, should there be more? Row 0 is still a row ... I just saw your edit, OK, so now expand the query but just a little otherwise you won't know what breaks it ...

    i have then run the next
    SELECT DISTINCT *
    FROM poochieProd AS prod
    LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
    WHERE prod.ProdID = '5'

    and it has given me
    ProdID Prodname ProdDesc ProdPrice ProdImageLarge ProdPayPayID CatID stockID ProdID sizeID stock sold
  26. #44
  27. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,114
    Rep Power
    487
    All good so far then by the looks ... just keep on expanding it until you either have the "golden" query (i.e. the one that does what you need correctly), or until something breaks and you stop getting results
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  28. #45
  29. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,114
    Rep Power
    487
    Wait a second, you only have 1 product with product ID 5 so then your SQL needs to be totally different ... if you're wanting to show all available sizes for a certain product then try this instead ...

    SELECT *
    FROM `poochieSizes` AS `sizes`
    RIGHT JOIN `poochieStock` AS `stock` ON `stock`.`SizeID` = `sizes`.`SizeID`
    WHERE `stock`.`stockID` = 5;

    I mean, that's just a stab in the dark but if you're selecting FROM Products and you only have 1 that will match your WHERE then you're only going to get 1 result.

    I think .... although I'm pretty sure that's right

    :S

    Try it, your code doesn't work now so it's not going to make it any worse lol !



    EDIT > If that SQL above works then you can RIGHT JOIN the products table to it if need be
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984

IMN logo majestic logo threadwatch logo seochat tools logo