Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jun 2002
    Location
    Central Ohio
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Single Record from DB

    Ok, I have enough knowledge to do quite a bit of basic stuff. But it seems every now & again a simple problem just stumps me and I am banging my head on the wall over this one.

    I have two tables.

    The first is a simple category table formatted as such:

    ID | Category (1|Pizzas, 2|Subs, etc)

    The second is each category with pertinent data...

    Category|Item|Description|Price (1|Large|18 inch, single topping|10.00, etc)

    The code I'm having trouble with takes a URL (i.e. menu.php?cat_name=Pizzas), reads the category table to find the 'id' for Pizzas, and then selects each item in the other table with an id of 1.

    PHP Code:
    $link mysql_connect($db_host$db_login$db_pass);
    if (!
    $link) {
        die(
    'Not Connected : ' mysql_error());
    }
     
    mysql_select_db($db_name$link) or die ("Can't open database!");

       
    $category $_GET['cat_name'];
        echo 
    "<td valign='top'>";
        if (
    $category null) {
            echo 
    "empty</td>\n";
    } else {
        
    $query "SELECT * FROM menu_cats where cat_name=$cat_name";
        
    $result mysql_query($query$link); 
        
    $row mysql_fetch_row($result);
            echo 
    $row['id']. " - </td>\n";

    It consistently throws an error on the $row = mysql_fetch_row($result); line, stating Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /public_html/menu/test.php on line 57

    I have tried several fetch statements, to no avail. I've looked up several tutorials, and cannot see my error. Any help would be greatly appreciated!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    You are missing a VERY IMPORTANT point:
    Code:
          ...
        $result = mysql_query($query, $link) or die( mysql_error() );
        $row = mysql_fetch_row($result);
        ...
    Your problem is that your query is illegal and so you are getting an error from mysql_query but then you are simply ignoring that error.

    Why is you query illegal? The best way to find out is DEBUG DEBUG DEBUG!!!
    Code:
    ...
        $query = "SELECT * FROM menu_cats where cat_name=$cat_name";
        echo "<hr/>DEBUG SQL: " . $query . "<hr/>\n";
        $result = mysql_query($query, $link) or die(mysql_error);
        $row = mysql_fetch_row($result);
        echo $row['id']. " - </td>\n";
    ...
    What does the DEBUG show you?

    I would *GUESS* it shows something like this:
    Code:
        SELECT * FROM menu_cats where cat_name=machinery
    Does that look like valid SQL, to you? If so, time to take a short refresher course.

    STRINGS in SQL *must* be designated by apostrophes. NUMBERS should not be.

    So what you almost surely want there is
    Code:
        SELECT * FROM menu_cats where cat_name='machinery'
    Which means you need to supply the apostrophes in your PHP code.
    Code:
        $query = "SELECT * FROM menu_cats where cat_name='$cat_name'";
    That should fix you up for now, but in the future remember to DEBUG DEBUG DEBUG. And to die when needed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Jun 2002
    Location
    Central Ohio
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OP:
    Like I said, something, something stupid I was overlooking. I can always get the "logic" worked out properly, and then overlook some minuscule detail that throws a wrench into things. Then I undoubtedly start tripping over myself trying to find my mistake.

    Thanks for pointing it out. Won't have an opportunity to sit down and correct it until Monday, but shall report back then. :-)

    Again, thanks, m8!

  • #4
    New Coder
    Join Date
    Jun 2002
    Location
    Central Ohio
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, my updated (relevant) code follows...

    PHP Code:
    $link mysql_connect($db_host$db_login$db_pass);
    if (!
    $link) {
        die(
    'Not Connected : ' mysql_error());
    }
     
    mysql_select_db($db_name$link) or die ("Can't open database!");

       
    $category $_GET["cat_name"];
        echo 
    "<td valign='top'>";
        
    //    echo $category;
        
    if ($category null) {
            echo 
    "empty</td>\n";
    } else {
        
    $query "SELECT * FROM menu_cats where cat_name='$category'";
        
    $result mysql_query($query$link) or die(mysql_error());
        
    $row mysql_fetch_row($result);
            echo 
    $row['id'];

    {edit}
    Updated a bit of code at the $query/$result lines. It's not dieing anywhere now, but isn't printing $row['id'], either?
    {/edit}

    It's finally accepting $category in the select statement, for a while it was not filling in properly, meaning it was reading as cat_name=''. But that is working now, evidenced by the reference to "Resource #7".


    Any advice would be greatly appreciated.

    Thanks!
    Last edited by MrJL; 05-01-2012 at 07:11 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    http://www.php.net/mysql_fetch_row

    mysql_fetch_row *ONLY* allows you to get the fields *BY NUMBER*.

    echo $row[0];

    Use mysql_fetch_assoc if you want fields by name.

    I don't use PHP, but it's all there in very clear language in the documentation. Time to start reading the docs, maybe?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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