Problem with php and mysql_query() 1

[dwardio]

I have a problem with the mysql_query() function...

Consider the following code:

<?php
$db = mysql_connect(&quot;host&quot;, &quot;user&quot;, &quot;pass&quot;
$connect = mysql_select_db(&quot;webdemo&quot;,$db);

$query = &quot;SELECT COUNT(*) AS myCount, userType FROM survey GROUP BY userType&quot;;

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

// DEBUGGING CODE:
printf (&quot;<h3>Debug Results:</h3>&quot;
printf (&quot;db = $db <br>&quot;
printf (&quot;connect = $connect<br>&quot;
printf (&quot;query = $query<br>&quot;
printf (&quot;result = $result<br>&quot;

if(mysql_num_rows($result) >0) {
while($myrow = mysql_fetch_row($result)){
printf($myrow[myCount], $myrow[userType]);
}
} ELSE {
print &quot;<BR>ERROR: There are no rows!&quot;;
}
?>

When I run this, I get:
---
Debug Results:
db = Resource id #1
connect = 1
query = SELECT COUNT(*) AS myCount, userType FROM survey GROUP BY userType
result =

Warning: Supplied argument is not a valid MySQL result resource in c:/program files/abria merlin/apache/htdocs/report.php on line 25

ERROR: There are no rows!
---

Any ideas what can be causing this? The user in this case has both SELECT and INSERT privs in the mysql:user table, and can successfully insert new rows via another script...

I can successfully run the SELECT stmt in the mysql console and get the results that I want, so I'm satisfied that the SELECT is OK.

HELP!

 

[vcherubini]

You can get rid of the $db variable in the mysql_query() function, since all you need is the query.

And here:
[tt]
$connect = mysql_select_db(&quot;webdemo&quot;,$db);
[/tt]

You can get rid of the $db variable since mysql_select_db() only requires the name of the database.


Try that and see what happens.

Hope this helps.

-Vic vic cherubini
vikter@epicsoftware.com

http://www.epicsoftware.com

====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director
====

 

[dwardio]

Vic,

Thanks for the reply, but neither of your suggestions worked.

Dennis

 

[vcherubini]

Dennis:

Try this, I modified the code so that instead of using mysql_query(), you just use mysql() and pass the database name and query to it.

[tt]
<?php

$db = mysql_connect(&quot;localhost&quot;, &quot;cnunited&quot;, &quot;rtv@16&quot;
$connect = mysql_select_db(&quot;webdemo&quot;,$db);

$query = &quot;SELECT COUNT(*) AS myCount, userType FROM survey GROUP BY userType&quot;;

$result = mysql(&quot;webdemo&quot;,$query);

// DEBUGGING CODE:
print (&quot;<h3>Debug Results:</h3>&quot;
print (&quot;db = $db <br>&quot;
print (&quot;connect = $connect<br>&quot;
print (&quot;query = $query<br>&quot;
print (&quot;result = $result<br>&quot;

if(mysql_num_rows($result) >0) {
while($myrow = mysql_fetch_row($result)){
printf($myrow[myCount], $myrow[userType]);
}
} else {
print &quot;<BR>ERROR: There are no rows!&quot;;
}
?>
[/tt]

It worked fine on my machine. Win2k running PHP 4.02. And I got rid of the printf()'s since they weren't doing anything because you were not using any special formatting characters (i.e., %s).

Try it and tell me what happens.

Hope this helps.

-Vic vic cherubini
vikter@epicsoftware.com

http://www.epicsoftware.com

====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director
====

 

[dwardio]

I used you code (changing only the user and password), but got the exact same debug results and warnings...

I'm now wondering if there is a problem with my setup.

Thanks for your help!

Dennis

 

[ElgisRamon]

Are you sure you have compiled your PHP with MySQL support?
I always use
[tt]
$cx = mysql_pconnect( &quot;server&quot;, &quot;user&quot;, &quot;pass&quot; );
mysql_select_db( &quot;databasename&quot; );
$query = stripslashes( &quot;SELECT ... &quot; );
$rset = mysql_query( $query );
if ( $rset )
{
// display the result rows got with mysql_fetch
}
[/tt]
I hope it works...
Unix was made by and for smart people.

 

[dwardio]

As I said in my initial post, another page successfully performs an INSERT, so yes PHP has the MySQL support...

 

[spookie]

hi
try splitting the query .
$query = &quot;SELECT COUNT(*) AS myCount, userType&quot;;
$query .=&quot; FROM survey GROUP BY userType&quot;;
it works for me
spookie

 

[Guest_imported]

Someone has solved this problem? I can't solve it, and I'd try all the options here.
--John

 

[Sparhawk2]

I can't say this will be any different, but sometimes it helps to try different configs for your code instead of

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

if(mysql_num_rows($result) >0) {
while($myrow = mysql_fetch_row($result)){
printf($myrow[myCount], $myrow[userType]);
}
} ELSE {
print &quot;<BR>ERROR: There are no rows!&quot;;
}

try this:

if (($result = mysql_query($result,$db)) || die('Bad Query'.mysql_error())){
if (mysql_affected_rows() || die('No Results Returned'.mysql_error())){
while($myrow = mysql_fetch_array($result,MYSQL_ASSOC)){
print($myrow['myCount'] . $myrow['userType'] . '<br>');
}
}
}

You also might change

$connect = mysql_select_db(&quot;webdemo&quot;,$db);

to

if (!mysql_select_db('webdemo',$db)){
die('Unable to find the data base'.mysql_error());
}

The main difference here is that the script will exit and report the error as soon as it happens, and not try to execute a statment with a bad resource ID. I find this to be very helpful in tracking down a problem as it helps pin point where and when the error occurs.

You wanted all the options, I hope this helps shed some light on the problem.