Database Query Error (Parameter 1...resource, boolean)

I just upgraded EVERYTHING - new computer with new operating system, MAMP upgrade with new versions of PHP, ySQL and Apache - and I have to iron out a few kinks. To my surprise, my websites appear to be working almost perfectly. However, I’ve encountered a problem with my database queries.

When I try to display a dynamic page, I get the following error message:

Warning: mysql_result() expects parameter 1 to be resource, boolean given in /Users/myname/Sites/MySite/inc/B/DB/Child/World.php on line 38

This is the offending code:


$result = mysql_result(mysql_query("SELECT COUNT(Name)
 FROM gw_geog
 WHERE Name = '$MyHomeL'"),0);

Can anyone tell me how to fix it? I’ve found some information about this error via Google, but I don’t really understand the explanations.

For whatever it’s worth, these are the versions I’ve been using:
Apache 2.0.59, PHP 4.4.7, MySQL 5.0.41

And these are the versions I just upgraded to:
Apache 2.2.21, PHP 5.3.6, MySQL 5.5.9

Thanks!

I checked a similar database query on another website and replaced the echo value with a value from the database table (Cuba)…


 $result = mysql_result(mysql_query("SELECT COUNT(URL)
 FROM gs
 WHERE URL = 'Cuba'"),0);

I still get the same error message.

I pasted it into MySQL’s SQL window, and it said I have a syntax problem. So I guess I need to upgrade my query to adapt it to the newer PHP/MySQL versions.

In other words, how should I write a database query of this nature?

Rewrite your code like this:


$query = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$queryresult = mysql_query($query, 0) or die ('mysql error ' . mysql_error() . ' in query ' . $query);
$result = mysql_result($queryresult);

And see what shows up.

SELECT COUNT(`URL`) AS count FROM gs WHERE `URL` = 'Cuba';

The above query should work, what error is MySQL giving you? Also, nice work on testing the query outside of PHP. :wink:

it’ll work just fine without those gratuitous and horrendous backticks you added, too

:cool:

So, we’re agreed - it’ll work. :stuck_out_tongue:

I thought I’d be a little cautious, last thing I wanted was to supply the OP with a non-working query.

Thanks for the kick though Rudy. :slight_smile:

Anthony.

yeah, we’re agreed, the original problem had nothing to do with the mysql query

as for post #2, “it said I have a syntax problem” makes sense if you paste the entire php statement instead of just the mysql part

you need connection var

Thanks for all the tips, but I get the same error message with Anthony’s code. However, I probably didn’t write it correctly…


$result = mysql_result(mysql_query("SELECT COUNT(URL) AS count FROM gs WHERE URL = 'Cuba';"),0);

I’ll try Guido’s solution, too. Also, I discovered someone else asked a similar question on this forum and was referred to http://www.php.net/manual/en/function.mysql-fetch-assoc.php I copied the code from Example #1, and it works. I can’t figure out why my original code stopped working, though.

OK, Guido’s code gives me another clue (e.g. error message):

Warning: mysql_query() expects parameter 2 to be resource, integer given in /Users/myname/Sites/inc/B/DB/Child/World.php on line 26
mysql error in query SELECT COUNT(URL) FROM gs WHERE URL = ‘Cuba’

You’ll need to post the code again, as that error is a PHP error, and not a MySQL error.

Oops…


$query = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$queryresult = mysql_query($query, 0) or die ('mysql error ' . mysql_error() . ' in query ' . $query);
$result = mysql_result($queryresult);

http://www.php.net/mysql_query

The second parameter can be omitted. From the manual

If the link identifier is not specified, the last link opened by mysql_connect() is assumed.

If you specify it, it has to be a mysql connection. 0 is not a mysql connection.

I’m confused. You’re saying I should remove the 0 from the script you gave me, right?

Original script…


 $query = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$queryresult = mysql_query($query, 0) or die ('mysql error ' . mysql_error() . ' in query ' . $query);
$result = mysql_result($queryresult);

After deleting the 0…


 $query = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$queryresult = mysql_query($query) or die ('mysql error ' . mysql_error() . ' in query ' . $query);
$result = mysql_result($queryresult);

Now I get an error message saying MySQL expects at least two parameters. I don’t really understand what it means by parameters.

Thanks.

When I do this with php I use code like this
$sql_result = mysql_query($sql,$connection)

two parameters, the $sql and the $connection. My $sql == your $query

Do you have a connection string somewhere that you are not using?

where it said in your first post
Warning: mysql_result() expects parameter 1 to be resource, boolean given in /Users/myname/Sites/MySite/inc/B/DB/Child/World.php on line 38

you had a 0, which is a boolean, not a resource.

I suspect this thread belongs in the PHP forum

Hmmm…Could the problem be caused by TWO database connections? For some reason, my original DB connection (on a file automatically included on all my pages) stopped working after I upgraded everything, so I added a new, working connection on THIS page.

However, I deleted the original DB connection just now, and I still get this error message:

Warning: mysql_query() expects parameter 2 to be resource, integer given in /Users/myname/Sites/MySite/inc/B/DB/Child/World.php on line 40
mysql error in query SELECT COUNT(URL) FROM gs WHERE URL = ‘Cuba’

Is this how I implement the code you posted?


 $sql = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$sql_result = mysql_query($sql,$conn);
$result = $sql_result;

That seems to work (no error message), except that there are apparently multiple results for a single value (it fetches Dupe.php).

In my original script, $result = 1 (fetching a normal page), 0 (fetching a 404 error page) or 2 or more (fetching Dupe.php). Now when I echo $result, it displays “Resource id #13.”

I don’t know what I’m doing, but it does look like I’m on the right track. Thanks.

I copied my file and deleted everything except the code focusing on just one website, making it easier to understand. This is what the entire file looks like now:


<?php
// DATABASE CONNECTION
 $conn = mysql_connect("localhost", "Username", "Password");
 
 if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("db_general")) {
    echo "Unable to select db_general: " . mysql_error();
    exit;
}

// DATABASE QUERY
 $sql = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$sql_result = mysql_query($sql,$conn);
$result = $sql_result; 

// DO SOMETHING WITH THE RESULTS
switch ($result)
{
 case 1:
 echo "\
";
 include_once($BaseINC."/$MyPHP/inc/B/DB2/Child/World.php");
 include_once($BaseINC."/$MyPHP/inc/B/Child2.php");
 include_once($BaseINC."/$MyPHP/inc/D/Child.php");
 echo "\
";
 break;

 case 0:
 include_once($BaseINC."/404.php");
 break;  

 default:
 // More than one results, as in Georgia (state and republic)
 include_once($_SERVER['DOCUMENT_ROOT']."/Dupe.php");
 echo "\
";
 break;
}
?>

Yes, which is the script you posted, rewritten. I just didn’t take notice of that 0 when I rewrote it.

Did you check out the page in the manual I linked to? As you can see, the second parameter is optional. I never specify it, because (so far) I’ve always used only 1 database connection.
It also says that if you omit it, and there is no active database connection, it will try to create one, and if that fails, it’ll give a warning. You didn’t post the exact text of the error you got, but my guess is there was no active database connection.

By the way, I moved the thread to the PHP forum.

Yes, I checked out your link, though I still don’t understand what I’m doing wrong.

I’m not really getting an error message now. The problem is that $result should = 1. But instead of displaying a normal web page, my script displays a duplicate notice. When I echo $result, it displays “Resource id #13.”

If the value for $MyURL is ‘Spain,’ then $result should = 1. If the value is ‘Georgia’ (both a country and a U.S. state), then $result should = 2. If a name is misspelled (e.g.Europ), $result should = 0, fetching a 404 error page.

// DATABASE QUERY
 $sql = "
  SELECT COUNT(URL)
  FROM gs
  WHERE URL = '$MyURL'
";
$sql_result = mysql_query($sql,$conn);
$result = $sql_result; 

$sql_result (and also $result because you simply assign the value of $sql_result to $result) is the result of the query, a mysql result set.
You need to get the values in that result set with mysql_fetch_assoc() for example. The result of mysql_fetch_assoc is an array with all the column names (key) and values (value) of the row.

// DATABASE QUERY
 $sql = "
  SELECT COUNT(URL) AS numberofurls
  FROM gs
  WHERE URL = '$MyURL'
";
$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_result($sql_result);
switch ($result['numberofurls'])
{ 

As you can see, I also gave an alias to the COUNT() column, so it will be easier to use that column in the rest of the script.