MilesStandish

I was looking over some code that someone else had written when I noticed a line with "$this->entity" in it. "Entity" is recognized as PHP syntax in my text editor but I cannot find any reference to it in any documentation that I've looked through. Where can I find more information about the "entity" class? Thanks in advanced!

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.

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 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. 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.

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? :'(

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)

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.

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.

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.

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

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(); ?>

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. :'(

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()); }

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.

O.K., I found the error, I shouldn't have put in the curly braces. However, now I know I am not connecting to the database. I know the username, password, and db name are all correct. Any suggestions as to what else to check?

Hi, Newbie here, I'm trying to retrieve data from a database that I set up using MAMP Pro. Right now I'm just trying to verify that I'm even connecting to the DB at all. I'm using the below code to do that: <?php require("dbinfo.php"); $connection=mysql_connect ($server, $username, $password); if (!$connection) { echo "Could not connect to database!"; else echo "Connected to database successfully!"; } ?> The file "dbinfo.php" contains the database name, username, and password. I know that that information is being passed to the script because I set up some echo statements and they showed the correct information is being passed. However, the above code doesn't produce any output at all. Shouldn't it echo one or the other statements in my browser? Instead all I get is a completely blank page. Sorry if this is obvious, as I stated, I am a newbie.