Need Help with Database Query

[PHPBear]

I just did a massive hardware/software upgrade - first new computer (a Mac) in 6 1/2 years, first MAMP upgrade in several years, etc. I'm now using these software versions: Apache 2.2.21, PHP 5.3.6, MySQL 5.5.9

 

Just about everything seems to be working fine except my database connections. I appear to be making a simple mistake, but I can't figure out the solution. I've checked out all sorts of online help pages, but I'm only getting more confused.

 

Rather than ask what the problem is, it might be better to go back to square one and ask how you would write this query from scratch. I pasted my current, somewhat convoluted code below, to give you an idea of how I was doing it.

 

But here's what I'm trying to do:

 

Imagine a website with a static page at World: MySite/World. It displays dynamic pages, like these: MySite/World/France, MySite/World/Spain...just as long as "France" or "Spain" match values stored in a database table (gw_geog). In my query, I represent France, Spain and other place names with the variable $MyURL.

 

So I want to query that database table. If $MyURL = 'Japan' (e.g. MySite/World/Japan), then $result should = 1, fetching a web page. If $result = 0 (e.g. a misspelled URL, like MySite/World/Japax), then it fetches a 404 error page.

 

If $result = 2 or more (e.g. MySite/World/Georgia - the name of a country and a U.S. state), then it fetches a duplicate note.

 

I wrote this script years ago, when I didn't know much about PHP/MySQL (I still don't), and I've upgraded my software, so I need to start fresh. Can anyone tell me how YOU would a script to accomplish this task?

 

Thanks.

 

* * * * *

 

<?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 gw_geog
  WHERE URL = '$MyURL'
";
$sql_result = mysql_query($sql,$conn);
$result = $sql_result; 

// DO SOMETHING WITH THE RESULTS
switch ($result)
{
case 1:
echo "\n";
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 "\n";
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 "\n";
break;
}
?>

[scootstah]

We're not going to write it for you. So you'd be better off posting what the problem is and we can help to resolve it.

[PHPBear]

We're not going to write it for you. So you'd be better off posting what the problem is and we can help to resolve it.

 

The current incarnation (the one I posted) eliminated the error messages. However, if the URL is MySite/World/Spain, $result should = 1. Instead, it echoes Resource id #13.

[scootstah]

No it shouldn't. mysql_query() returns a resource. To return a result you need to use one of the fetch functions.

 $sql = "
  SELECT COUNT(URL) AS count
  FROM gw_geog
  WHERE URL = '$MyURL'
";
$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_assoc($sql_result);

// DO SOMETHING WITH THE RESULTS
switch ($result['count']) 

[PHPBear]

Hmmm, now I get this error message...

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /Users/myname/Sites/...

 

If I change mysql_fetch_assoc() to mysql_fetch_result(), I get this error message...

 

Fatal error: Call to undefined function mysql_fetch_result()

 

I Googled some of those error messages a little more, and I found one discussion where they concluded that the individual had a problem with his PHP or MySQL installation; they wanted him to modify his php.ini file.

 

Do you think that might be my problem? I'd like to verify it before I start searching for hidden files. I did think it was kind of strange that database queries that worked fine suddenly stopped working after I upgraded MAMP. I though I just had to update my code somehow.

 

Thanks.

 

[PHPBear]

Yikes - I must have been doing something wrong; in fact, your script works fine.

 

Thanks!