logic to convert this array to a string using loop

Question

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

(SELECT COUNT(*) FROM states WHERE country_id = 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) )
      );

Please benchmark your query with EXPLAIN. I'd be surprised if this'd be efficient. — fabrik
– fabrik, Commented Apr 5, 2011 at 7:04

Community · Accepted Answer · 2017-05-23 10:33:11Z

5

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) . ")";

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Apr 5, 2011 at 6:17

fabrik

14.4k8 gold badges58 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Ibrahim Azhar Armar Over a year ago

the problem is i want to do it dynamically, because $id can have more then one value and as many as it can be, and $value can have 1 to 3 values.

Frank Schmitt Over a year ago

To avoid unnecessary casting, you might want to remove the quotation marks around 121 and 122 (since this is a number field).

fabrik Over a year ago

just updated my snippet so you can use many variables dynamically.

Ibrahim Azhar Armar Over a year ago

@fabrik i am unable to understand your code, i will be glad if you could try explaining on what is happening with that query. sorry but i am still learning things :)

fabrik Over a year ago

@Ibrahim Azhar Armar Please read the linked MySQL documentation page too, but i'll try to summarize what happens. See my update on my answer.

|

jon_darkstar · Accepted Answer · 2011-04-05 19:22:11Z

1

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)

edited Apr 5, 2011 at 19:22

answered Apr 5, 2011 at 7:52

jon_darkstar

16.9k7 gold badges35 silver badges38 bronze badges

3 Comments

Ibrahim Azhar Armar Over a year ago

that's what even i got confused the primary keys are just id's not state_id, or city_id, or area_id.. anyway thanks ill test and let you know

Ibrahim Azhar Armar Over a year ago

my primary key is just id and foreign key is country_id,state_id,city_id,area_id etc. when i use your code it gives me this error. Unknown column 'states.country_id' in 'where clause' i have used the following code 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 states.country_id IN(118)

Ibrahim Azhar Armar Over a year ago

wooow, thank you so much after a very long lasting testing and much patience i not only understood the code, but got it to work brilliantly. all thanks to you... :)

Khez · Accepted Answer · 2011-04-05 06:18:07Z

1

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.

answered Apr 5, 2011 at 6:18

Khez

10.3k2 gold badges35 silver badges53 bronze badges

Comments

Bogdan Constantinescu · Accepted Answer · 2011-04-05 06:19:12Z

0

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"
}

answered Apr 5, 2011 at 6:19

Bogdan Constantinescu

5,3864 gold badges42 silver badges51 bronze badges

1 Comment

Ibrahim Azhar Armar Over a year ago

this looks interesting, let me try and get back to you

Collectives™ on Stack Overflow

logic to convert this array to a string using loop

4 Answers 4

22 Comments

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

22 Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related