SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)

    MySQL In-valid Result Resource Error... Why??

    Aloha.

    I have a database table with the following fields...


    Product_ID
    Product_Name
    Product_Zone
    Product_ShortStory
    Product_Price


    And this function in my PHP code to selectively output that information based on which "Product_Zone" is chosen.

    PHP Code:
    // Define Function to Return Product Listing
    function writeProductListing($chosenZone)
        {
        
    // Connect to Database
        
    include("../Vault/LoginInfo.txt");

        @
    mysql_connect(localhost,$username,$password);
        @
    mysql_select_db($database)or die("Unable to select database");

        
    // Initialise HTML Table
        
    $output[] = '<table id="TinyMarketerProductsTable">';
        
    $output[] = '<tr><td><h1>'.$chosenZone.'</h1></td></tr>';

        
    // Load Viewer Data from Database
        
    $query "SELECT * FROM $table WHERE Product_Zone = $chosenZone ORDER BY Product_ID ASC";
        
    $result mysql_query($query);
        
    $num '1';
        
    $i 0;

        while(
    $i $num)
            {
            
    $product_ID mysql_result($result,$i,"Product_ID");
            
    $product_Name mysql_result($result,$i,"Product_Name");

            
    $output[] = '    <tr>';
            
    $output[] = '        <td>$product_Name</td>';
            
    $output[] = '    </tr>';

            
    $i++;
            }

        
    // Dis-connect from Database
        
    mysql_close();

        
    // Finalise HTML Table
        
    $output[] = '</table>';

        
    // Implode and Return the $output Array
        
    return implode('',$output);
        } 
    I keep getting that old "Warning: mysql_result(): supplied argument is not a valid MySQL result resource" error by the lines...
    $product_ID = mysql_result($result,$i,"Product_ID");
    $product_Name = mysql_result($result,$i,"Product_Name");

    and I have no idea why.

    Does anybody see something that I don't ??

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Debugging MySQL 101:
    Change the line
    PHP Code:
    $result mysql_query($query); 
    to
    PHP Code:
    $result mysql_query($query) or die(mysql_error()); 
    Also, make sure you turn on debugging output. If you follow these suggestions, you'll likely see exactly what the problem is.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that tip.

    The error I'm getting is "Unknown column 'Books' in 'where clause' ".
    "Books" is the parameter of the function... The value of "$chosenZone".

    PHP Code:
    <?php echo writeProductListing('Books'); ?>
    When I remove the WHERE part of the function it runs perfectly, so I know the problem is somewhere there.

    I still can't see what's wrong, though. It all looks correct to me.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if $chosenZone is Books, then this --

    ... WHERE Product_Zone = $chosenZone

    produces this --

    ... WHERE Product_Zone = Books

    but what you really want is this --

    ... WHERE Product_Zone = 'Books'

    see?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    eenteresting... yeees...



    Okay so I tried...


    $query = "SELECT * FROM $table WHERE Product_Zone = '$chosenZone' ORDER BY Product_ID ASC";
    ...which of course didn't work becuase it was looking for the row "$chosenZone" rather than "Books".

    So then I tried...

    $query = "SELECT * FROM $table WHERE Product_Zone = \'$chosenZone\' ORDER BY Product_ID ASC";
    ...which was a syntax error.

    THEN I tried changing that other line to...

    <?php echo writeProductListing('&#39 ;Books&#39 ;'); ?>
    ...but that produced a syntax error too.

    What else is there??

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    $query = "SELECT ... WHERE Product_Zone = '" . $chosenZone . "' ORDER... ";
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)


    Holy sh*t!!... Wow that's clever.

    Thanks, man.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shaun(OfTheDead) View Post
    Wow that's clever.
    thank you

    and i don't even do php!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT * FROM $table WHERE Product_Zone = '$chosenZone' ORDER BY Product_ID ASC"
    This code most certainly does not produce what you say it does. The string is delimited by double quotes, and thus variable expansion occurs regardless of how many single quotes you have within it. In fact, if you compare this string with the one produced by Rudy's code, you will find them identical.

    Don't believe me? Run it and see for yourself:
    Code PHP:
    	$table = "table1";
    	$chosenZone = "Books";
    	$query = "SELECT * FROM $table WHERE Product_Zone = '$chosenZone' ORDER BY Product_ID ASC";
    	$query2 = "SELECT * FROM $table WHERE Product_Zone = '" . $chosenZone . "' ORDER BY Product_ID ASC";
    	var_dump($query);
    	var_dump($query2);
    	echo ($query===$query2?"identical":"not identical");
    This produces the following output:
    Code:
    string(73) "SELECT * FROM table1 WHERE Product_Zone = 'Books' ORDER BY Product_ID ASC"
    string(73) "SELECT * FROM table1 WHERE Product_Zone = 'Books' ORDER BY Product_ID ASC"
    identical
    I'm sorry, I really don't mean to sound harsh or to beat on you, but this is such a common misconception and in almost every case seems to be based upon people not understanding and then not even trying to test code. When a string is delineated by double quotes (i.e. when it starts and ends with "), then variable expansion will occur within it for every variable, even those that are enclosed in single quotes within the string. This is something that is clearly laid out in the manual, and the most basic of testing (such as the test code I just provided) clearly illustrates and proves it.
    Off Topic:


    Rudy, you're spending too much time in the PHP forum I think!
    Last edited by kromey; Jun 25, 2007 at 12:18. Reason: Adding code output
    PHP questions? RTFM
    MySQL questions? RTFM

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kromey View Post
    Rudy, you're spending too much time in the PHP forum I think!
    only the sql threads
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey
    This code most certainly does not produce what you say it does. The string is delimited by double quotes, and thus variable expansion occurs regardless of how many single quotes you have within it. In fact, if you compare this string with the one produced by Rudy's code, you will find them identical.

    Don't believe me? Run it and see for yourself:
    If you say so, dude, but I can tell you for a fact that it didn't work.

    It was the first thing I tried and it didn't work (see above).

    I don't know what other factors could come into play, but I know for a fact I didn't change any other parts of the code other than that one line.

  12. #12
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you get when you var_dump that string as opposed to Rudy's that uses string concatenation?
    PHP questions? RTFM
    MySQL questions? RTFM

  13. #13
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where does $table come from in this query ?
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  14. #14
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    Where does $table come from in this query ?
    PHP Code:
    include("../Vault/LoginInfo.txt"); 
    Although, I'd give a tip to Shaun that even though that directory is probably protected, it's a good idea to keep the extension .php; just in case, and well, to be consistent.
    Saul

  15. #15
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shaun(OfTheDead) View Post
    Thanks for that tip.

    "Books" is the parameter of the function... The value of "$chosenZone".

    PHP Code:
    <?php echo writeProductListing('Books'); ?>
    When I remove the WHERE part of the function it runs perfectly, so I know the problem is somewhere there.

    I still can't see what's wrong, though. It all looks correct to me.
    Ok so select all from a table will return a valid resultset, but if you are using the WRONG TABLE NAME, and it does not contain a column called chosen zone then you will get an invalid resultset.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  16. #16
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    Ok so select all from a table will return a valid resultset, but if you are using the WRONG TABLE NAME, and it does not contain a column called chosen zone then you will get an invalid resultset.
    No, the error was not quoting the value. The table name is right, but not quoted value was considered a column name, non-existent column name.
    Saul

  17. #17
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    Of course the table name was right... If not then even when I removed the WHERE part (as I mentioned earlier) it still wouldn't have worked.

    To tell you the honest truth, I'm not in the mood to muck around with this anymore... it works and it's not too much more trouble to type an extra ' and a .



    Thanks for that tip, php_daemon (about the file extention). It makes a lot of sense. I'll implement that when I do my next batch of updates.


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
  •