Advanced query above my head, trying to add COUNT(*) as num to a working query

[Presto-X]

Hey guys,

 

I'm working on a google map for our site, the system will return a list of locations, my first query is working great:

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM $tbl_name WHERE approved = '1' AND status = '1' HAVING distance < '%s' ", mysql_real_escape_string($center_lat), mysql_real_escape_string($center_lng), mysql_real_escape_string($center_lat), mysql_real_escape_string($radius));

 

Now I want to add in a paging system, so I have to add a bit more code to my query the code calls for the following to be added to my query but I can not get it working with the above query:

COUNT(*) as num 

[Presto-X]

We are running mysql 5.0.90

 

The following is our table layout:

CREATE TABLE `jos_locations` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) unsigned zerofill NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(50) NOT NULL,
  `zip` varchar(25) NOT NULL,
  `phone` varchar(25) default NULL,
  `fax` varchar(25) default NULL,
  `email` varchar(255) default NULL,
  `website` varchar(25) default NULL,
  `description` text,
  `approved` tinyint(1) unsigned zerofill NOT NULL default '0',
  `latitude` float NOT NULL default '0',
  `longitude` float NOT NULL default '0',
  `created` datetime NOT NULL,
  `modified` datetime default NULL,
  `status` tinyint(1) NOT NULL default '1',
  `hours_sun` varchar(255) default NULL,
  `hours_mon` varchar(255) default NULL,
  `hours_tues` varchar(255) default NULL,
  `hours_wed` varchar(255) default NULL,
  `hours_thur` varchar(255) default NULL,
  `hours_fri` varchar(255) default NULL,
  `hours_sat` varchar(255) default NULL,
  `about` longtext,
  `products` longtext,
  `promotions` longtext,
  `services` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

[Mchl]

How did you try to add it?

[Presto-X]

How did I not try to add it lol, ok really here are a few ways I went about it:

 

$query = sprintf("SELECT COUNT(*) as num, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM $tbl_name WHERE approved = '1' AND status = '1' HAVING distance < '%s' ", mysql_real_escape_string($center_lat), mysql_real_escape_string($center_lng), mysql_real_escape_string($center_lat), mysql_real_escape_string($radius));

$query = sprintf("SELECT ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance, COUNT(*) as num FROM $tbl_name WHERE approved = '1' AND status = '1' HAVING distance < '%s' ", mysql_real_escape_string($center_lat), mysql_real_escape_string($center_lng), mysql_real_escape_string($center_lat), mysql_real_escape_string($radius));

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance, COUNT(*) as num FROM $tbl_name WHERE approved = '1' AND status = '1' HAVING distance < '%s' ", mysql_real_escape_string($center_lat), mysql_real_escape_string($center_lng), mysql_real_escape_string($center_lat), mysql_real_escape_string($radius));

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM $tbl_name WHERE approved = '1' AND status = '1' HAVING distance < '%s' ", mysql_real_escape_string($center_lat), mysql_real_escape_string($center_lng), mysql_real_escape_string($center_lat), mysql_real_escape_string($radius).", COUNT(*) as num");

 

I do remember that the first examples does not return any errors but the problem with that one was it codes all of the rows in the table, in effect showing more pages at the bottom then what there are, I’m guessing because of the latitude and longitude search after is limiting the results down quite a bit.

[Presto-X]

So I went at this from another angle I ended up leaving that bit of code out of my query and just using mysql_num_rows($results);