Select count with options

Beginners - Learning PHP
#1

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

#2

Assuming your table looks like:

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

Then the following query will get you what you want.

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. 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.

#3

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?

#4

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

#5

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.

#6

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.

#7

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.

#8 
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
$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);
?>
#9

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"; ?>
#10

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));?

#11

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

#12

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.

#13

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.
#14

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.

#15

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.

#16

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.

#17

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.

#18

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.

#19

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.

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:

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:

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:

    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…

#20

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.

Sponsor our Newsletter | Privacy Policy | Terms of Service