Re: [PHP-DB] most popular places
Micah Stevens [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] With mysql 4.1 you could union and then subSelect maybe? I'm not super-familiar with sub selects, so this code won't likely run.. There is a unwritten rule that states: If your db needs the use of unions the db is set up poorly. You can union just about everything, that's the whole problem of a union. I would not and I do mean not ever use a union in my code. I woudl need more detail on the schema to give a more abt answer to the problem. But then, you're runn 4.0x so it won't run anyway. :) select city_id, count(city_id) as num from ((select city_id from members) UNION (select city_id from pictures) UNION (select city_id from reports)) order by num On Friday 25 February 2005 07:08 am, Merlin wrote: Hi there, I am trying to do a tricky task with PHP and MySQl 4.0.x There are 3 content tables where each of them contains a city_id and there is a 4th table which holds the city_id and the acording city_name Now I would like to find out the most used city names. For example: members pictures reports 3 4 3 2 9 8 9 8 8 So the most used city_ids are 8 3 9 To find this out would be a chelenging task, but to merge them with the city name is even more difficult I am working on this quite a while now and cant find a way. Maybe someone of you guys could give me a smart push. Thank you for any help, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] most popular places
the only way i can think of off the top of my head would not be
intense as you would have to do a foreach for each of the cities such
as
$qry = select * from table
$total = mysql_num_rows( $qry );
foreach( $cities as $city ) {
// do db query such as
// $qry = mysql_query(SELECT id FROM table where members=$city OR
pictures=$city OR reports=$city);
//$cities[$city] = $total / mysql_num_rows($qry);
with something like this you would get an array, you could then sort
the array based on highest to lowest value, you could then display the
cities which are used the most, if you really want to get into the
details you could do seperate queries for the members, pictures,
reports and store them as such
$citites[$city]['members'] etc...
--
Joseph Crawford Jr.
Codebowl Solutions
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] most popular places
Use a join to get the city name Use grouping to group the most common cities select a.*, b.* from table1 a inner join table2 b on a.id = b.id [where clause] group by a.id Would need a little more data about the structure of the tables to give a more complete answer bastien From: Merlin [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] most popular places Date: Fri, 25 Feb 2005 16:08:54 +0100 Hi there, I am trying to do a tricky task with PHP and MySQl 4.0.x There are 3 content tables where each of them contains a city_id and there is a 4th table which holds the city_id and the acording city_name Now I would like to find out the most used city names. For example: members picturesreports 3 4 3 2 9 8 9 8 8 So the most used city_ids are 8 3 9 To find this out would be a chelenging task, but to merge them with the city name is even more difficult I am working on this quite a while now and cant find a way. Maybe someone of you guys could give me a smart push. Thank you for any help, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] most popular places
Merlin wrote: I am trying to do a tricky task with PHP and MySQl 4.0.x There are 3 content tables where each of them contains a city_id and there is a 4th table which holds the city_id and the acording city_name Now I would like to find out the most used city names. For example: memberspictures reports 343 298 988 So the most used city_ids are 8 3 9 To find this out would be a chelenging task, but to merge them with the city name is even more difficult I am working on this quite a while now and cant find a way. Maybe someone of you guys could give me a smart push. [snip] SELECT COUNT(m.city_id), other.city_name FROM members as m, other GROUP BY m.city_id WHERE m.city_id = other.city_id; can't think off the top of my head any easy way to do that together with all the tables in one query, seems like it should be possible though. This should get you pointed in the right direction if you aren't already (note - sql is untested, and I always hose my GROUP BY's on the first try - but it should be close :) ) Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] most popular places
With mysql 4.1 you could union and then subSelect maybe? I'm not super-familiar with sub selects, so this code won't likely run.. But then, you're runn 4.0x so it won't run anyway. :) select city_id, count(city_id) as num from ((select city_id from members) UNION (select city_id from pictures) UNION (select city_id from reports)) order by num On Friday 25 February 2005 07:08 am, Merlin wrote: Hi there, I am trying to do a tricky task with PHP and MySQl 4.0.x There are 3 content tables where each of them contains a city_id and there is a 4th table which holds the city_id and the acording city_name Now I would like to find out the most used city names. For example: members pictures reports 3 4 3 2 9 8 9 8 8 So the most used city_ids are 8 3 9 To find this out would be a chelenging task, but to merge them with the city name is even more difficult I am working on this quite a while now and cant find a way. Maybe someone of you guys could give me a smart push. Thank you for any help, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
