Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > Beginning PHP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Beginning PHP Beginning-level PHP discussions. More advanced coders should post to the Pro PHP forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning PHP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 25th, 2003, 03:39 PM
Authorized User
 
Join Date: Jun 2003
Location: San Jose, CA, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default MySQL Query Problem

Ok, I think my eyes are just too tired from staring at code too long, can someone look at this and tell me what the heck is wrong. I for the life of me can't figure it out...

$link_id_fmt2 = db_connect('there1');
        $result_fmt2 = mysql_query("SELECT idnum, format FROM $format_tablename ORDER BY format", $link_id_fmt2);

        while($query_data_fmt2 = mysql_fetch_row($result_fmt2)) {

        echo "<option value=\"$query_data_fmt2[0]\">$query_data_fmt2[1]</option>\n";

        }


And, here's the error:

Warning: Supplied argument is not a valid MySQL result resource in /user1/********/www/admin/edit_musictitles.php on line 265

And before anyone asks, I know the database is right and I know the table is right. I have even tested the query and it works fine. I know it's something stupid. Thanks in advance.

----------
~cmiller
__________________
----------
~cmiller
Reply With Quote
  #2 (permalink)  
Old August 25th, 2003, 03:50 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Just really quickly cause I'm on my way out of here...

Try the query without specifing the resource handle. Or post your user-defined function for connecting to the database. The problem seems to be not with the query but with the resource handler.

Also include:
echo mysql_error();
echo mysql_errno();

After each mysql function to see what error mysql is outputting.

: )
Rich


:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::
Reply With Quote
  #3 (permalink)  
Old August 25th, 2003, 04:06 PM
Authorized User
 
Join Date: Jun 2003
Location: San Jose, CA, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, now this is weird.

Thanks for the tip Rick! I tossed in the mysql_error checking stuff and that was returning an error, however it was just "error in SQL query near '' on line xxx" So that wasn't really telling me anything.

I did get it to work however by removing the variable as the tablename and replaced it with the actual tablename and it worked just fine. How friggan weird. Anyone have an explination for this, because it's very annoying.

Thanks again for your help.

----------
~cmiller
Reply With Quote
  #4 (permalink)  
Old August 25th, 2003, 05:42 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hmm, sounds like there's a problem with the variable. Have you double-checked that the query (after variable substitution) is what you expect?

$query = "SELECT idnum, format FROM $format_tablename ORDER BY format";
echo "Query is [[[$query]]]\n";

As far as the link resource ID, I don't expect that it would cause problems. A single script can have multiple connection resource links available and run queries on any of those resource links.


Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #5 (permalink)  
Old August 26th, 2003, 12:43 AM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Yes I was in a bit of a rush when I read your problem before because I was on my way out the door. Now that I have had a chance to look at your problem again it occurred to me that mysql will throw the blah blah blah resource error when there is a syntax error in your query and then attempting to fetch the result set, meaning that because the query was not successful, therefore mysql_fetch_row() is not a valid resource. I had for some reason thought that there might have been a typo there with the setting of the mysql resource... but I've got it now!

I find it helpful to make my queries in a user-defined function, which helps reduce some redundancy in code...

Code:
function query($query, $link) {

    $result = mysql_query($query, $link);

    if (empty($result)) {

        echo mysql_error().": ";
        echo mysql_errno()."\n";

        return false;

    }

    return $result;

}
Wrapping your query calls in a function makes sense for two key reasons...
1. Redundant tasks are best enclosed within functions or classes, meaning less time is spend on development!
2. Error checking should always be included in any task to create the best scenario for quickly and efficiently troubleshooting application failures.

How are you going to know what's going wrong and where if you don't plan for it?

Personally I only use one resource connection and store the value in a super-global array $GLOBALS["link"] -- I like this approach because then I don't have to pass it as an argument to my user-defined functions and classes. I like to have only one connection at the beginning of my whole application and then reuse that connection throughout the life of the script, because database connections are resource intensive, the fewer the better! Well when we're talking about connecting to the same database. I understand that PHP can have many connections to many databases --- but the more connections either way, the more resources consumed. As far as the super-global method is concerned, if version portability isn't an issue then you might consider this approach.

I have to agree with Nik (Its hard to ever disagree with Nik! He's right 99.9% of the time ;))... I would output the value of the variable to help track down where things are going awry.

Always always always plan a project with its failure in mind. If a variable's value is critical create a routine to check that its value absolutely exists and is what you intend it to be... isset() and empty() are IMO two of the most useful functions that exist. isset() checks for a variable's existence, empty() checks for null value... a subtle but very important difference.

Code:
#Don't do this...
if ($variable) {

    # Execute code relying on the existence of $variable

} else {

    echo "Error: failure, \$variable not set at... ";

}

#Do this...
if (isset($variable)) && !empty($variable)) {

   # If variable exists and isn't null 
   # Execute code relying on the existence of $variable

} else {

   echo "Error: \$variable not set at... <br />\n";

}
I would even take this one step further and wrap my error reporting within a function to control error output using a boolean variable or constant. Error output is very good in development, but unprofessional in a live production site. So you might set it up so that it outputs to the screen if ERROR_BOOLEAN is true and to a text file log if ERROR_BOOLEAN is false.

Also consider the error text from PHP's native error reporting...
Warning: Supplied argument is not a valid MySQL result resource in /user1/********/www/admin/edit_musictitles.php on line 265

Line 265 will correspond to the function call mysql_fetch_row()... which indicated an error with the argument being passed to it, which will in turn mean one of two things, a typo in the result variable or an error in SQL syntax... if you are getting that error when trying to fetch a result set then you can pretty much lay money on that being exactly the issue every time. (Just some words of wisdom for the future there!)

IMO, you can never have too much error reporting! Its certainly saved me from pulling out my hair several times. I'm glad to have been of help~!

: )
Rich


:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Query Problem w/ PHP rdmapes PHP How-To 5 August 14th, 2010 12:55 AM
php and mysql query ghari PHP Databases 1 February 12th, 2006 06:43 PM
MySQL query junemo MySQL 14 June 10th, 2004 10:11 PM
mySQL login query problem??? nick_roberts5 PHP Databases 4 March 3rd, 2004 05:35 PM
MySQL query desc order problem dssachdeva MySQL 3 September 18th, 2003 01:11 AM



All times are GMT -4. The time now is 11:21 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.