MySQL Query and PHP problems

MilesStandish · August 11, 2010

Hi all,

I have a query that runs just fine from the command line in MySQL, but when I try to run the exact same query using PHP to query the same database I get an error.

The query is:

SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close, lat, lng, ( 3959 * acos( cos( radians('41.757787') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-88.321416') ) + sin( radians('41.757787') ) * sin( radians( lat ) ) ) ) AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName AND OpHours.Close > '17:00:00' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30

Like I stated above, when I run this query from the MySQL command line it returns 12 rows, which is what it should.

But when I try executing the same query via PHP I get this error message:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''17:00:00' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30' at line 1

The PHP code is thus:

[pre]$connection=mysql_connect ($server, $username, $password);

if (!$connection)

{

echo 'Connection error';

die("Not connected : " . mysql_error());

}

$db_selected = mysql_select_db($database, $connection);

if (!$db_selected)

{

echo 'Database selection error' . '\n';

die ("Can\'t use db : " . mysql_error());

}

$query = sprintf("SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close,

lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) )

AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName %s HAVING distance < '%s' ORDER BY distance LIMIT 0 , 30",

mysql_real_escape_string($center_lat),

mysql_real_escape_string($center_lng),

mysql_real_escape_string($center_lat),

mysql_real_escape_string($hours),

mysql_real_escape_string($radius));

$result = mysql_query($query);

if (!$result)

{

die("Invalid query: " . mysql_error());

}[/pre]

I've also checked the MySQL logs and they don't report any errors. I'm running this on a Mac using Snow Leopard. MySQL version is 5.1.37 and the PHP version is 5.2.11. If it makes any difference both of these are from MAMP version 1.8.4.

trq · August 11, 2010

Can you post the code between

tags so we can actually read it?

MilesStandish · August 11, 2010

Sorry, when I previewed it, it showed up looking o.k.

Here it is:

$connection=mysql_connect ($server, $username, $password);
if (!$connection)
   {
      echo 'Connection error';
     die("Not connected : " . mysql_error());
   }

$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) 
   {
      echo 'Database selection error' . '\n';
      die ("Can\'t use db : " . mysql_error());
   }

$query = sprintf("SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close,
lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) 
AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName %s HAVING distance < '%s' ORDER BY distance LIMIT 0 , 30",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($hours),
  mysql_real_escape_string($radius));

$result = mysql_query($query);

if (!$result) 
   {
      die("Invalid query: " . mysql_error());
   }

AbraCadaver · August 11, 2010

Your PHP query is not the same as the one in the first post. You are missing what is in red:

SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close, lat, lng,

( 3959 * acos( cos( radians('%s') ) * cos(radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) )

AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName AND OpHours.Close > %s

HAVING distance < '%s' ORDER BY distance LIMIT 0 , 30"

MilesStandish · August 11, 2010

The "%s" contains the entire phrase "AND OpHours.Close > '17:00:00'", so the two queries are the same.

I have a commented out section at the end of the $query statement:

echo $query;
exit();

I've been uncommenting it to show me what the query looks like that is submitted via this PHP page. That's what I copied and pasted in the beginning of my initial post, i.e., this is exactly what gets build by the PHP code.

And, like I said, it works fine from the command line, not so when I let the PHP do the query.

:'(

MilesStandish · August 11, 2010

Here's the entire PHP page so there's no confusion.

<?php  
require('db_info.php');


// Get parameters from URL
// $center_lat = $_GET["lat"];
// $center_lng = $_GET["lng"];
// $radius = $_GET["radius"];
// $services = $_GET["services"];
// $days = $_GET["days"];
// $hours = $_GET["hours"];

$center_lat = "41.757787";
$center_lng = '-88.321416';
$radius = '10';
$services = 'PrimaryCare,Referrals';
$days ='Weekdays,Saturdays';
$hours = 'After5PM';

$services = substr_replace($services, "AND services.", 0, 0);
$services = str_replace(",", "=1 AND services.", $services);
$services = $services . "=1";



$hoursArray = explode(",", $hours);

foreach ($hoursArray as &$queryElement)
{
switch ($queryElement)
	{
		case "BusinessHours":
			$queryElement = htmlentities(" AND OpHours.Open <> '0:00:00' ", ENT_QUOTES);
			break;
		case "BFor8AM";
			$queryElement = htmlentities(" AND OpHours.Open < '8:00:00' ", ENT_QUOTES);
			break;
		case "After5PM";
			$queryElement = htmlentities(" AND OpHours.Close > '17:00:00' ", ENT_QUOTES);
			break;
		case "Lunch";
			$queryElement = htmlentities(" AND OpHours.LunchOpen <> '0:00:00' ", ENT_QUOTES);
			break;
		case "DropIn";
			$queryElement = htmlentities(" AND OpHours.DropIn = 1 ", ENT_QUOTES);
			break;
		default:
			$queryElement = htmlentities(" AND OpHours.Open <> '0:00:00' ", ENT_QUOTES);
	}
}

$hours = implode (" ", $hoursArray);

$daysArray = explode(",", $days);

foreach ($daysArray as &$queryElement)
{
switch ($queryElement)
	{
		case "Weekdays":
			$queryElement = " AND (OpDays.Mon = 1 OR OpDays.Tues = 1 OR OpDays.Wed = 1 OR OpDays.Thurs = 1 OR OpDays.Fri = 1) ";
			break;
		case "Saturdays";
			$queryElement = " AND OpDays.Sat = 1 ";
			break;
		case "Sundays";
			$queryElement = " AND OpDays.Sun = 1 ";
			break;
		case "Holidays";
			$queryElement = htmlentities(" AND OpDays.Holidays <> ''", ENT_QUOTES);
			break;
		case "ByAppointment";
			$queryElement = " AND OpDays.ByAppointment = 1 ";
			break;
		default:
			$queryElement = "  AND (OpDays.Mon = 1 OR OpDays.Tues = 1 OR OpDays.Wed = 1 OR OpDays.Thurs = 1 OR OpDays.Fri = 1 OR OpDays.Saturday = 1 OR OpDays.Sunday = 1) ";
	}
}

$days = implode (" ", $daysArray);


// Start XML file, create parent node
$dom = new DOMDocument("1.0");
$node = $dom->createElement("markers");
$parnode = $dom->appendChild($node);

//Opens a connection to a mySQL server
$connection=mysql_connect ($server, $username, $password);
if (!$connection)
{
	echo 'Connection error';
  	die("Not connected : " . mysql_error());
}

// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) 
{
	echo 'Database selection error' . '\n';
	die ("Can\'t use db : " . mysql_error());
}


$query = sprintf("SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName %s HAVING distance < '%s' ORDER BY distance LIMIT 0 , 30",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($hours),
  mysql_real_escape_string($radius));

// echo $query;
// exit();



$result = mysql_query($query);

if (!$result) 
{
	die("Invalid query: " . mysql_error());
}

header("Content-type: text/xml");

// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  $node = $dom->createElement("marker");
  $newnode = $parnode->appendChild($node);
  $newnode->setAttribute("name", $row['bName']);
  $newnode->setAttribute("address", $row['Street'].", ".$row['City']);
  $newnode->setAttribute("lat", $row['lat']);
  $newnode->setAttribute("lng", $row['lng']);
  $newnode->setAttribute("distance", $row['distance']);
  $newnode->setAttribute("bWebpage", $row['bWebpage']);
}

echo $dom->saveXML();
?>

AbraCadaver · August 11, 2010

It's probably because mysql_real_escape_string() is escaping the quotes in AND OpHours.Close > '17:00:00'. There's really no reason to escape strings that you have built unless you have actual quotes in the column data.

PFMaBiSmAd · August 11, 2010

You are also using htmlentities() on pieces of your SQL syntax. Why?

You escape the data values put in a query. You don't escape the actual SQL syntax that makes up the query.

AbraCadaver · August 11, 2010

You are also using htmlentities() on pieces of your SQL syntax. Why?

You escape the data values put in a query. You don't escape the actual SQL syntax that makes up the query.

Haha, yeah I missed that. Take a look at the view source of the page where you echo the query. It probably looks something like this:

OpHours.Close > &#039;17:00:00&#039;

MilesStandish · August 12, 2010

Thanks all, I'll make the suggested changes and see if that fixes things.

Thanks again!

MilesStandish · August 12, 2010

If I don't use htmlentities() I get error messages like this because of mysql_real_escape_string:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'17:00:00\' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30' at line 1

If I remove both the htmlentities() and the quotes then I get error messages like this:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':00:00 HAVING distance < '10' ORDER BY distance LIMIT 0 , 30' at line 1

I get the same error if I remove htmlentities(), the quotes, and mysql_real_escape_string.

If I remove just the quotes around 17:00:00 and leave htmlentities() and mysql_real_escape_string then I get an error message like this:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '17:00:00 HAVING distance < '10' ORDER BY distance LIMIT 0 , 30' at line 1

Am I just incorrectly setting up an SQL time query? I don't think so because the built query from the original PHP executes just fine from the MySQL command line.

MilesStandish · August 12, 2010

It's probably because mysql_real_escape_string() is escaping the quotes in AND OpHours.Close > '17:00:00'. There's really no reason to escape strings that you have built unless you have actual quotes in the column data.

BTW, I should point out that the distance data is also escaped with mysql_real_escape_string() and it works just fine if it is the only variable in the query that the PHP executes.

MilesStandish · August 12, 2010

Haha, yeah I missed that. Take a look at the view source of the page where you echo the query. It probably looks something like this:

Code: [select]

OpHours.Close > '17:00:00'

Nope, it looks like this:

SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close, lat, lng, ( 3959 * acos( cos( radians('41.757787') )
* cos( radians( lat ) ) * cos( radians( lng ) - radians('-88.321416') ) + sin( radians('41.757787') ) * sin( radians( lat ) ) ) )
AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName
AND OpHours.Close > '17:00:00' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30

As you can see the time value looks no different than the distance value so (theoretically) it should work, right?

Just banging my head against a brick wall.

I know it's probably something really simple that I've overlooked.

MilesStandish · August 12, 2010

Oh, and to show that the query that the PHP builds works fine from the command line, see below:

mysql> SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close, lat, lng, ( 3959 * acos( cos( radians('41.757787') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-88.321416') ) + sin( radians('41.757787') ) * sin( radians( lat ) ) ) ) AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName AND OpHours.Close > '17:00:00' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30;
+-----------------------------------------+--------------+---------+----------------------------------------------+----------+----------+-----------+------------+-------------------+
| Street                                  | City         | ZipCode | bWebpage                                     | Open     | Close    | lat       | lng        | distance          |
+-----------------------------------------+--------------+---------+----------------------------------------------+----------+----------+-----------+------------+-------------------+
| 101 S. Broadway                         | Aurora       | 60505   | http://www.auntmarthas.org/                  | 08:00:00 | 21:00:00 | 41.754951 | -88.315521 | 0.361541206683723 | 
| 101 S. Broadway                         | Aurora       | 60505   | http://www.auntmarthas.org/                  | 08:00:00 | 21:00:00 | 41.754951 | -88.315521 | 0.361541206683723 | 
| 101 S. Broadway                         | Aurora       | 60505   | http://www.auntmarthas.org/                  | 08:00:00 | 21:00:00 | 41.754951 | -88.315521 | 0.361541206683723 | 
| 101 S. Broadway                         | Aurora       | 60505   | http://www.auntmarthas.org/                  | 08:00:00 | 21:00:00 | 41.754951 | -88.315521 | 0.361541206683723 | 
| 101 S. Broadway                         | Aurora       | 60505   | http://www.auntmarthas.org/                  | 08:00:00 | 19:00:00 | 41.754951 | -88.315521 | 0.361541206683723 | 
| 400 N. Highland Ave.                    | Aurora       | 60506   | http://www.vnafoxvalley.org                  | 08:00:00 | 20:00:00 | 41.764843 | -88.330696 |  0.68300014571547 | 
| 400 N. Highland Ave.                    | Aurora       | 60506   | http://www.vnafoxvalley.org                  | 08:00:00 | 20:00:00 | 41.764843 | -88.330696 |  0.68300014571547 | 
| 400 N. Highland Ave.                    | Aurora       | 60506   | http://www.vnafoxvalley.org                  | 08:00:00 | 20:00:00 | 41.764843 | -88.330696 |  0.68300014571547 | 
| 400 N. Highland Ave.                    | Aurora       | 60506   | http://www.vnafoxvalley.org                  | 08:00:00 | 20:00:00 | 41.764843 | -88.330696 |  0.68300014571547 | 
| 317 East Indian Trail                   | Aurora       | 60505   | http://www.auntmarthas.org/                  | 12:00:00 | 20:30:00 | 41.782120 | -88.306419 |   1.8504534473313 | 
| 245 W. Roosevelt Rd. Bldg. 14, Ste. 150 | West Chicago | 60185   | http://accesscommunityhealth.net/westchicago | 08:00:00 | 19:00:00 | 41.868275 | -88.209610 |  9.56239219868299 | 
| 245 W. Roosevelt Rd. Bldg. 14, Ste. 150 | West Chicago | 60185   | http://accesscommunityhealth.net/westchicago | 09:00:00 | 20:00:00 | 41.868275 | -88.209610 |  9.56239219868299 | 
+-----------------------------------------+--------------+---------+----------------------------------------------+----------+----------+-----------+------------+-------------------+
12 rows in set (0.01 sec)

PFMaBiSmAd · August 12, 2010

If I remove both the htmlentities() and the quotes

No one told you to remove the quotes.

However, you were told that you are using mysql_real_escape_string() on parts of your SQL syntax. It is only used on string data that is put into a query, i.e. the data that is put between single-quotes in the query.

The single-quotes around string data in your query are part of the sql syntax that makes up the query. They are not escaped as that would break the sql syntax.

As to the htmlentities(), if you removed the htmlentities() from your code, then yes the resulting output is correct. If you echoed the query, even with htmlentities() in your code, it will display correctly and you can copy/paste it and it will work, but the actual string that php uses is not valid sql syntax and AbraCadaver wrote in his post that if you look at the view source of the page you would see the html entities.

MilesStandish · August 12, 2010

If you read the entirety of my reply you'll see that I was trying all combinations of htmlentities(), including/excluding the quote marks, and mysql_real_escape_string() just to show that I've gone through every combination. Not that anyone suggested that I do that.

But in all cases I get some sort of error returned.

So, if I remove htmlentities() from my code as you and AbraCadaver suggest then I still get an error, this one:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'17:00:00\' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30' at line 1

As I noted in the post you are replying to.

As you can see mysql_real_escape_string() is escaping the ' marks but this still causes an error. Furthermore the query created will not work from the command line. In fact the command line gives the same error as returned by the PHP code, see:

mysql> SELECT Providers.bName, Street, City, ZipCode, providers.Webpage, OpHours.Open, OpHours.Close, lat, lng, ( 3959 * acos( cos( radians('41.757787') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-88.321416') ) + sin( radians('41.757787') ) * sin( radians( lat ) ) ) ) AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName AND OpHours.Close > \'17:00:00\' HAVING distance <  '10' ORDER BY distance LIMIT 0 , 30;
ERROR: 
Unknown command '\''.
ERROR: 
Unknown command '\''.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'17:00:00\' HAVING distance <  '10' ORDER BY distance LIMIT 0 , 30' at line 1

This is not the case with my original code. It works from the command line returning 12 rows (see my other post above) as it should.

And yet when I use the exact same query (that works from the command line) by loading the PHP code page into my browser I get the error message that I originally posted.

See how perplexing it is?

:'(

PFMaBiSmAd · August 12, 2010

I was trying all combinations of ...

That's not how you write code. You write code by learning what the functions do and what the syntax means and use them to produce the code and query that accomplishes what you want.

The full code you finally posted in reply #5, will produce a query that displays correctly when echo'ed and could be copy/pasted and executed, but the actual variable $query does not contain valid sql due to how the mysql_real_escape_string() and htmlentities() are being applied to the sql syntax in it. Until you form your query by only using mysql_real_escape_string() on string data values that could contain sql special characters and by not using htmlentities() at all, you won't get this to work.

MilesStandish · August 13, 2010

That's not how you write code. You write code by learning what the functions do and what the syntax means and use them to produce the code and query that accomplishes what you want.

Yes, you are absolutely correct about that. However in my experience I have found that when something isn't working that it is helpful to make sure that you are addressing the correct point in the code by selectively adding and removing sections until you've narrowed it down to what is causing the problem.

In a perfect world where everyone had perfect memories and never suffered from "brain farts" overlooking otherwise obvious errors your assertion would be the best way of doing things.

We don't live in that world, at least I don't.

The full code you finally posted in reply #5, will produce a query that displays correctly when echo'ed and could be copy/pasted and executed, but the actual variable $query does not contain valid sql due to how the mysql_real_escape_string() and htmlentities() are being applied to the sql syntax in it.

The query is not echoed until after the mysql_real_escape_string() and htmlentities() have been applied. Unless there are changes that take place with those two functions that never show up when echoed I should be getting the exact same string that is being passed to the MySQL server, correct?

Or are you stating that the string that is echoed and the string passed by the PHP code without being echoed are somehow different despite the only difference being that one execution of the code contains:

echo $query;

And the other does not. That is hard to believe.

Until you form your query by only using mysql_real_escape_string() on string data values that could contain sql special characters and by not using htmlentities() at all, you won't get this to work.

I don't want to sound rude because I greatly appreciate the time that you are spending looking over my code and replying. But you really need to read my posts more carefully.

I removed the htmlentities() functions and left the rest of the code untouched, i.e., only used mysql_escape_string. It still won't work, I get an error. A different error to be sure, but an error none the less.

And I posted that error in my previous response, it's the code section of that post. So I have done exactly what you have suggested and it does not work the MySQL server complains about the "\" that mysql_escape_string inserts.

Merely removing htmlentities() does not solve the problem, it simply changes the type of error returned.

If I'm misunderstanding what you are saying then I apologize. Like I said I don't want to be rude, I really do appreciate the help. I'm just getting frustrated by this.

MilesStandish · August 13, 2010

I figured out what i was doing wrong.

Yes, I didn't need to use htmlentities(), but removing those wasn't enough by itself.

I also needed to remove mysql_real_escape_string from the variable that was being populated with SQL statements at the top of the page (the switch section) and that removed the escaping which solved the whole problem.

Sorry if this was obvious to all from the beginning. Like I said, sometimes I can be looking right at a problem and not recognize it.

Old age is hell.

Sign In

MySQL Query and PHP problems

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information