logic to convert this array to a string using loop

I want to create a database query string which should be like this,

(SELECT COUNT(*) FROM states WHERE country_id =开发者_Python百科 121 ) + 
(SELECT COUNT(*) FROM cities WHERE country_id = 121 ) + 
(SELECT COUNT(*) FROM areas WHERE country_id = 121) + 
(SELECT COUNT(*) FROM states WHERE country_id = 122) + 
(SELECT COUNT(*) FROM cities WHERE country_id = 122) + 
(SELECT COUNT(*) FROM areas WHERE country_id = 122)

for that i have used the code like this.

$id = array('121','122');
$table = array('states','cities','areas');
$loopCount = count($id) * count($table);
$queryString = array();
for($i=0;$i<$loopCount;$i++)
{
    $queryString[] = "(SELECT COUNT(*) FROM $table[$i] WHERE country_id = $id[$i] ) + ";
}

i know the above code is totally wrong, what will be the correct way to implement the code to get desired result?

Update :

i would like to get the total number of counts From country_id not individual, that can exist in 1 to three tables, the below query seems to work fine for me, but please do let me know if you have a better solution

SELECT( 
      (SELECT COUNT(*) FROM states WHERE country_id IN(121,122)) + 
      (SELECT COUNT(*) FROM cities WHERE country_id IN(121,122) ) +
      (SELECT COUNT(*) FROM areas WHERE country_id IN(121,122) )
      );

---

You can do it with JOIN:

$id = array('121', '122');

jon_darkstar's count (Thank you!):

"SELECT COUNT(DISTINCT(state_id)) + COUNT(DISTINCT(city_id)) + COUNT(DISTINCT(area_id)) as desired_sum

JOIN another table called cities with the same country_id

LEFT JOIN cities ON states.country_id = cities.country_id

JOIN another table called areas with the same country_id

LEFT JOIN areas ON states.country_id = areas.country_id

Filter results for country_id's you provided in your array, implode them with a php function, called implode().

WHERE states.country_id IN (" . implode(', ', $id) . ")"

OR, within a foreach loop where $cid will be your country_id (see jon_darkstar's comment below)

WHERE states.country_id = " . $cid . ""

A single one query:

"SELECT * FROM states, 
        COUNT(DISTINCT(state_id)) + 
        COUNT(DISTINCT(city_id)) + 
        COUNT(DISTINCT(area_id)) AS desired_sum
        JOIN `cities` ON states.country_id = cities.country_id
        JOIN `areas` ON states.country_id = areas.country_id
        WHERE states.country_id IN (" . implode(', ', $id) . ")";

---

Just noticed the last few comments, give this a try

$countryCodes = array(121, 122);
$countryCodeString = implode($countryCodes, ', ');

$sql = 
  "SELECT COUNT(DISTINCT(S.id)) + COUNT(DISTINCT(C.id)) + COUNT(DISTINCT(A.id)) as desired_sum 
   FROM states S
        JOIN cities C ON S.country_id = C.country_id
        JOIN areas A ON A.country_id = S.country_id
   WHERE s.country_id in ($countryCodes)";

$res = mysql_query($sql);
$arr = mysql_fetch_assoc($res);
$val = $arr['desired_sum'];

So this will give a single integer, the total count of any 'unit' (city, state, or area) within any of the countries specified in $countryCodes. It does assume that there is at least one state in each country. (ie - if country X has cities and areas within it but no states, those cities and areas wont be counted). Also assumes that the primary keys of states, cities, areas are state_id, city_id and area_id (not simply id)

---

Try this code:

<?php
$a=array();
$a[]=array('states','121');
$a[]=array('cities','121');
$a[]=array('areas','121');
$a[]=array('states','122');
$a[]=array('cities','122');
$a[]=array('areas','122');

$r=array();
foreach($a as $v)
    $r[]='(SELECT COUNT(*) FROM '.$v[0].' WHERE country_id = '.$v[1].' )';
$r=implode(' + ',$r);
?>

But I'd highly advise against this approach to get what it seems you're trying to do.

---

You can reduce the number of queries, but I don't think trying an UNION would be so much better. My advice is to improve the queries like this:

SELECT COUNT(*) AS number FROM states WHERE country_id IN (121, 122) GROUP BY country_id

Now you have one query per table. On the php side you could do this:

$id = array('121','122');
$table = array('states','cities','areas');
foreach($table as $tableName) {
    $queryString[] = "SELECT COUNT(*) AS number FROM {$tableName} WHERE country_id IN (". implode(',', $id) .") GROUP BY country_id"
}