Not a valid MySQL resource (Again!)

Anon

Please help the newbie!

I'm collecting the responses to a very simple survey form as text strings in a VARCHAR field in a table.

I can run the following SELECT statement on the console and get exactly the response I need:

SELECT COUNT(userType) AS myCount, userType FROM survey GROUP BY userType;

However, when I place this SELECT statement into my PHP page, I get the dreaded "Supplied argument is not a valid MySQL result resource" message. Here is my PHP code:

<?php
$db = mysql_connect("localhost", "user", "passwd");
$connect = mysql_select_db("webdemo",$db);

$query = "SELECT COUNT(userType) AS myCount, userType FROM survey GROUP BY userType";
$result = mysql_query($query);

while($myrow = mysql_fetch_row($result)) {
printf($myrow[0], $myrow[1]);
}
?>

The line that the error is reported on is the WHILE() line. If I replace the WHILE statement with "echo $result;" I don't get any sort of return or error message.

Help!

reagent

did you try using mysql from the command line and entering in the same sql statement? that will help debug syntax errors w/ your sql.

tgmdes

Hi dwardio,

I would create two sql statements. For example, the first to get your record count...SELECT COUNT(userType) AS myCount FROM survey ... and put that value into a variable for future use. Then run the query that you would like to loop through. select userType from survey... I'm not quite sure why your getting that error message, but there is no real reason to loop through a query that returns a record count... Hope this helps....

rrijnders

Your query string is fine. My guess is that you should check the $connect variable to see if you actually connected to the db.

if ($connect) {
//run your query
{
else {
print "ERROR: Couldn't connect to DB";
}

-- Rich Rijnders
-- Irvine, CA US

Anon

Rich,

Thanks for the help-- at least I know that the connect stmt is working...

I changed my code to the following, but I still get the same error at the WHILE stmt:

<?php
$db = mysql_connect("localhost", "user", "passwd");
$connect = mysql_select_db("webdemo",$db);

if ($connect){
print "Connect is working fine!";
$query = "SELECT COUNT(*), userType FROM survey GROUP BY userType";
$result = mysql_query($query,$db);
while($myrow = mysql_fetch_row($result)){
printf($myrow[myCount], $myrow[userType]);
}
} ELSE {
print "ERROR: Couldn't connect to DB";
}
?>

I am really puzzled by this one!

Anon

My table is collecting a series of pre-defined text strings stored in one field, for example:

apple
apple
orange
apple
plum
orange

I need to determine the number of apples, oranges, and plums entered, so I use

SELECT COUNT(fruit) AS entered, fruit GROUP BY fruit;

in order to get the listing

entered fruit
3 apple
2 orange
1 plum

I can run this query at the command line and get the exact results I need. The problem seems to be in my PHP coding, specifically the WHILE stmt that tries to do a row-by-row readout of the results.

Anon

Yes! Quoting from my original msg:

I can run the following SELECT statement on the console and get exactly the response I need:

SELECT COUNT(userType) AS myCount, userType FROM survey GROUP BY userType;

This works fine, the problem seems to be in the PHP, not the SQL.

Anon

hi there!!

i'm a newbie too! can u tell me how u fixed your problem, 'cos i seem to be experiencing your pain!!

here's my problem:

if ( strlen(trim($login)) > 0 )
{
$sql= "select field1, field2, field3 from my_table where name = '$mylogin' and field1 = '1';
$result = mysql_query( $sql );

while ( $row = mysql_fetch_array($result) ) {
$count=$count+1;
if ( trim($row[1]) == 2 ) {$flag1="checked";}
if ( trim($row[1]) == 3 ) {$flag2="checked";}
if ( trim($row[1]) == 4 ) {$flag3="checked";}

}//end-while
}//end-if

help!!!

rrijnders

Well, if this is a cut-and-paste of your code, then you need an ending double quote at the end of your <b>$sql=</b> statement, before the final semi-colon.

-- Rich

Anon

hi again

my actual statement in my code has an ending quote. i didn't cut and paste properly.

however, the problem is still persisting, and its driving me insane. the error is where my WHILE loop is, and when i echoed out my $result, it was empty.

what do u think is causing this?? i double-checked my connections to my datase, and that's not the problem. i also cut-and-pasted the sql-statement into my database, and i got data!! my database is not empty, so why is $result empty????

help!?!??!

Anon

did u ever get it working?

coz i'm having the same problem!

Anon

Hi Mark

Yup, i got it working. For the connection to my database, i used an include file that basically did the connection.

However, for the script causing the problem, i left the include file as is. I just manually connected to my database, and did something like this:

$db = mysql_connect("your_machine_name", "your_user_name", "your_user_passwd");
$connect = mysql_select_db("your_db_name",$db);

if ($connect)
{
your $sql statement goes here, along with whatever loops u need to run to check for 'stuff'...
}
else
{
print "ERROR: Couldn't connect to DB";
}

This worked for me. I don't know if its the most elegant way to go about it, I am a newbie, so if you find out a better method, let me know.

Sorry I took so long to reply. I yesterday was a public holiday... :-)

Tasneem