# Select count with options

**URL:** <https://www.phphelp.com/t/select-count-with-options/33945>\
**Category:** Beginners - Learning PHP\
**Created:** [September 14, 2021, 10:48am UTC](https://www.phphelp.com/t/select-count-with-options/33945 "2021-09-14T10:48:33Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 10:48am UTC](https://www.phphelp.com/t/select-count-with-options/33945/1 "2021-09-14T10:48:34Z")

</div>

can someone help with this, can’t seem to find the right code

I have the following :

SELECT Father, COUNT(\*) WHERE Father IN (’\<=5’)  
FROM people;  
GROUP BY Father;  
ORDER BY 2 DESC;

I want the output to show as follows  
FATHERS NAME NUMBER OF CHILDREN  
Bobby 16  
Max 10  
Martin 8

and so on.

I have Name and Father columns in my database, basically i need to count and show the Name and number of children of all Names if the father has more than 5 children.

Thanks for replies

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [September 14, 2021, 1:09pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/2 "2021-09-14T13:09:17Z")

</div>

Assuming your table looks like:

```auto
+--------+----------+
| Child | Father |
+--------+----------+
| Aaron | Bobby |
| Kevin | Max |
| Toby | Martin |
...

```

Then the following query will get you what you want.

```auto
SELECT Father, count(*) AS NumChildren
FROM people
GROUP BY Father
HAVING NumChildren > 5
ORDER BY NumChildren DESC;

```

We select the father’s name and a row count, which we alias as `NumChildren` since that’s what it will represent. We group by `Father` since that’s how we want to group our [_aggregate function_](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html). We can’t use `WHERE` since a `WHERE` clause works the original data being processed, and we need to filter our end result instead. This is what `HAVING` is for. Finally we use the `ORDER BY` to order by our child count, largest first.

---

<div class="post-metadata">

**Author:** ![whispers](https://www.phphelp.com/letter_avatar_proxy/v4/letter/w/858c86/32.png) [@whispers](https://www.phphelp.com/u/whispers)\
**Post date:** [September 14, 2021, 1:14pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/3 "2021-09-14T13:14:24Z")

</div>

Your table layout will dictate what the actual query needs to be.

Is this a ‘one to many’ table?

or a single entry with each ‘column’ being a (potential) child?

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 1:34pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/4 "2021-09-14T13:34:26Z")

</div>

> [@skawid](#):
>
> ```auto
> SELECT Father, count(*) AS NumChildren
> FROM people
> GROUP BY Father
> HAVING NumChildren > 5
> ORDER BY NumChildren DESC;
> 
> ```

Thanks for the reply, i tried your solution but get the following

**Parse error** : syntax error, unexpected ‘Father’ (T\_STRING)

not sure what is happening

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 1:43pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/5 "2021-09-14T13:43:52Z")

</div>

Hello thanks for the reply, only one table with 15 colomns  
over 50 thousand entries.  
First column is Name, Second column is Father.  
Basically any value in the Name column could have the same Father as another Name in that column.  
what i need is a select count code that will list the Names of Fathers that have more than 5 children in a descending order.

Example

Bobby 15  
Max 13

etc etc,  
Thanks.

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [September 14, 2021, 2:27pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/6 "2021-09-14T14:27:42Z")

</div>

> [@Bobby30](#):
>
> Thanks for the reply, i tried your solution but get the following
> 
> **Parse error** : syntax error, unexpected ‘Father’ (T\_STRING)
> 
> not sure what is happening

You now have an error in your PHP code. Probably a missing quote mark. If you can’t find it, ask another question and include the broken code in there. Use the “preformatted text” formatting function to make it easy to read.

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 2:29pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/7 "2021-09-14T14:29:48Z")

</div>

> [@skawid](#):
>
> ```auto
> SELECT Father, count(*) AS NumChildren
> FROM people
> GROUP BY Father
> HAVING NumChildren > 5
> ORDER BY NumChildren DESC;
> 
> ```

I have more or less got it to work with the awnser from “skawid” changing it a little bit.  
here is the code i tried

\<?php $sql = "SELECT Father, count(\*) FROM people AS Children GROUP BY Father HAVING Children \> 5 ORDER BY Children DESC"; $nRows = $Connection-\>RunScalar($sql); echo "$nRows"; ?\>

Two things wrong, if i put NumChildren instead of Children it wont work  
and i’m getting only one Name listed as result with no number of offspring.  
I’m surely doing something wrong !!  
Thanks.

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [September 14, 2021, 2:50pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/8 "2021-09-14T14:50:38Z")

</div>

```sql
SELECT Father, count(*)
FROM pedigree AS Children
GROUP BY Father
HAVING Children > 5
ORDER BY Children DESC;

```

You’re absolutely doing something wrong; you’ve randomly changed the query you were given without understanding its structure.

What result do you get with this code?

```php
<?php
$sql = 'SELECT Father, count(*) AS NumChildren
  FROM people
  GROUP BY Father
  HAVING NumChildren > 5
  ORDER BY NumChildren DESC';

$nRows = $Connection->RunScalar($sql);
var_dump($nRows);
?>

```

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 2:59pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/9 "2021-09-14T14:59:46Z")

</div>

> [@skawid](#):
>
> ```auto
> <?php
> $sql = 'SELECT Father, count(*) AS NumChildren
> FROM people
> GROUP BY Father
> HAVING NumChildren > 5
> ORDER BY NumChildren DESC';
> 
> $nRows = $Connection->RunScalar($sql);
> var_dump($nRows);
> ?>
> 
> ```

Thanks i get the following : string(0) “”

So you can understand the following is another bit of code i use on another page and it works perfectly

\<?php require\_once("common.php"); $sql = "select count(\*) FROM people WHERE Line IN ('A/') AND Title = 'CH'"; $nRows = $Connection-\>RunScalar($sql); echo "$nRows"; ?\>

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [September 14, 2021, 3:24pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/10 "2021-09-14T15:24:57Z")

</div>

Ok. We need to know more about the `$connection` class, since `runScalar` apparently isn’t the method for this. What do you get if you `var_dump(get_class_methods($connection));`?

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 3:28pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/11 "2021-09-14T15:28:37Z")

</div>

> [@skawid](#):
>
> `var_dump(get_class_methods($connection));`?

Thanks i get the result NULL  
Sorry i’m very new to PHP, trying to work out as i’m going along

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [September 14, 2021, 6:17pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/12 "2021-09-14T18:17:46Z")

</div>

My mistake. `$Connection` with a capital `C`. The idea is to see what other methods your database class has, to see what might be useful.

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 6:24pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/13 "2021-09-14T18:24:04Z")

</div>

> [@skawid](#):
>
> `var_dump(get_class_methods($connection));`?

Thanks i am still getting NULL unless i’m doing it wrong

Just can’t understand why it’s not working  
I have loads of the following on other pages and they all work fine

\<?php require\_once("common.php"); $sql = "select count(\*) FROM people WHERE Line IN ('A/') AND Title = 'CH'"; $nRows = $Connection-\>RunScalar($sql); echo "$nRows"; ?\>

Here is another example

\<?php require\_once("pp\_common.php"); $sql = "select count(\*) FROM people WHERE Line IN ('A/','A/1','A/2','A/3','A/4','A/5','A/6','A/7','A/8','A/9','A/10','A/11','A/12','A/13','A/14','A/15','A/16','A/17','A/18','A/19','A/20','A/21','A/22','A/23','A/24','A/25','A/26','A/27','A/28','A/29','A/30','A/31','A/32','A/33','A/34','A/35','A/36','A/37','A/38','A/39','A/40','A/41','A/42','A/43','A/44','A/45','A/46','A/47','A/48','A/49','A/50','A/51','A/52','A/53','A/54','A/55','A/56','A/57','A/58','A/59','A/60','A/61','A/62','A/63','A/64','A/65','A/66','A/67','A/68','A/69','A/70','A/71','A/72','A/73','A/74','A/75','A/76','A/77','A/78','A/79','A/80','A/81','A/82','A/83','A/84','A/85','A/86','A/87','A/88','A/89','A/90','A/91','A/92','A/93','A/94','A/95','A/96','A/97','A/98','A/99','A/100','A/101','A/102','A/103','A/104','A/105','A/106','A/107','A/108','A/109','A/110','A/111','A/112','A/113','A/114','A/115','A/116','A/117','A/118','A/119','A/120') AND Sex = 'female'"; $nRows = $Connection-\>RunScalar($sql); echo "$nRows";... I'm just trying to get different information back.

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [September 14, 2021, 8:48pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/14 "2021-09-14T20:48:00Z")

</div>

This is a guess, but it’s probably not working because the method `runScalar` expects the passed query to return a single value. The query you have here is returning a number of rows, so will need handling differently. You need to find out more about how your database class works.

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 14, 2021, 8:57pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/15 "2021-09-14T20:57:58Z")

</div>

Thanks again, all i can see about RunScalar is the following  
I did not write the original scripts but i’ve added a few options etc “with permission to do so”

function RunScalar($sql)  
{  
$result = $this-\>RawQuery($sql);  
$record = $result-\>records[0];  
return $record[0];  
}  
sorry will be hard to find out more  
Maybe a new function needs to be added in the config file,  
I could do that if someone can tell me how it needs doing to get the results i want.  
Then change the code you gave me to run the new function.

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 16, 2021, 3:44pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/16 "2021-09-16T15:44:45Z")

</div>

This problem has been solved partly with the following

$sql = “select count(\*) FROM people WHERE Father OR Mother IN (’$\_name’)”;  
$nRows = $Connection-\>RunScalar($sql);  
echo “Number of Children : $nRows”;

This will show the number of Children on any of the Fathers or Mothers pages not the list that i wanted but will leave it like this for now.

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [September 16, 2021, 3:53pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/17 "2021-09-16T15:53:13Z")

</div>

This is similar to the other post. Please do not post duplicate questions.

Well, we can not help you with query’s without knowing your table structure. Shawid guessed at it, but, we can help you a lot better if we know your data structure.

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 16, 2021, 4:18pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/18 "2021-09-16T16:18:57Z")

</div>

ErnieAlex, Sorry if you thought this is a Duplicate question, as i said earlier i’m new to PHP so for me it was a completely different subject.  
As i also said earlier i have no access to my Database only thing i know is what is in the actual table.  
Think i need to stop asking questions, as long as i can’t find out more about data structure etc i’m just wasting peoples time.

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [September 16, 2021, 4:50pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/19 "2021-09-16T16:50:30Z")

</div>

Well, you can display what’s in a database. Easy enough to do. It is not a problem with two posts, but, it’s basically the similar questions. In this post you say you do not know the data and in the other and this one you mention people is the table and child, father and mother are fields. So, to could them, it depends what you want counted. Normally, you run a query for each total needed or use nested queries to get them all in one query. I think you are not explaining what you really need here.

> [@Bobby30](#):
>
> I want the output to show as follows  
> FATHERS NAME NUMBER OF CHILDREN  
> Bobby 16  
> Max 10  
> Martin 8

This is what you wanted originally at the top. You have not told us age was involved, but in your example output you show it. What you would need to create this query would be something like this:

```auto
SELECT father, count(*) AS number_children FROM people GROUP BY father

```

This would give you a list of the fathers and the count of their children. Then, you can loop thru the results using a WHILE() function. Inside the WHILE, you would do a second query using the results[“father”] from the first as your selection for the children. Something like this:

```auto
SELECT child, age FROM people WHERE father=$results["father"]

```

This would run for each father. You would of course echo the data in this section. Another WHILE() loop would print all the children for that one father. If no results were found, they would not have any children.

Does this logic make sense to you at all? Hope it helps!

Oh, by the way, to get the fieldnames and their fieldtypes, you can run this query:

```auto
    SELECT COLUMN_NAME.
    FROM INFORMATION_SCHEMA. COLUMNS.
    WHERE.
    AND TABLE_NAME = 'people' ;

```

Just display all the results from it… Or, log into your control panel on the server and select myPHPadmin and view the table…

---

<div class="post-metadata">

**Author:** ![Bobby30](https://www.phphelp.com/letter_avatar_proxy/v4/letter/b/258eb7/32.png) [@Bobby30](https://www.phphelp.com/u/Bobby30)\
**Post date:** [September 16, 2021, 8:10pm UTC](https://www.phphelp.com/t/select-count-with-options/33945/20 "2021-09-16T20:10:22Z")

</div>

ErnieAlex,  
thanks for your replies, i think i didn’t explain my question properly this question is not the same as the second one.  
Start at the beginning, i have a Database table that i don’t have access to but a program on my computer does.  
I cant login to the database in any way.  
After that i have some PHP files that i didn’t write but have permission to change for my needs.  
These files are then used on my Website to display information from the Database table.  
so the schema of it all is basically, I add things in the program, which updates information in the database table, which updates the webpages.  
There are several things i need to improve on the website and need a few more things to display.  
Firstly which is my first question and not the same as the second one,

In my database table i have a Name column and Father column, i am trying to get code that will make a list of Fathers and the Number of each Fathers children. Starting list at the top with the Father that has the most children then going down depending on number of children a Father has.  
Hope this is clearer.

Now to my second question, another php file will get Father of Father of Father etc to fill in like a Family tree. that works fine but if any Fathers are missing it will just put Blanks.  
I’m trying to find a way that if the page doesn’t find a Father (which it gets from database table) instead of showing a blank it shows something like “sorry father not in database”.  
Hope this explains things better. As i dont really know a lot about PHP or what queries etc can be run from the Database because iv’e not got access to it, i understand that my questions are difficult to reply to. I know the Columns of the database and whats in them because i add the information through the program but no idea of the structure etc.  
Thanks for reading.

[Next page](https://www.phphelp.com/t/select-count-with-options/33945.md?page=2)