Getting the number of rows on MySQL with SQL and PHP

Question

I have a db with some tables like this:

- fruitsTable

     id       name       numberOfPeople
     1        apple      3
     2        orange     4
     3        cherry     1 

- peopleTable

     person   fruit
     john     apple
     john     orange
     john     cherry
     jack     apple
     jack     orange
     ryan     apple
     ryan     orange
     meg      orange

Think that peopleTable get changed everyday and consists of tens of thousands of rows. Also, the fruitsTable has more than thousand rows.

How can I calculate the numberOfPeople column, periodically?

I am planning to set up a daily cron job which will trigger a SQL command in order to calculate the number.

Assuming that I have no experience with those JOIN commands in SQL, What kind of SQL command do I need to use without having any performance issue?

What do you mean calculate the numberOfPeople column, periodically? — AdRock
– AdRock, Commented Dec 5, 2013 at 13:30
@AdRock I want to print the number of people who likes cherry, orange and other stuff in one page. I thought that, in order to this, I will need a numberOfPeople column and I also need to update it frequently as the peopleTable changes. — Sercan
– Sercan, Commented Dec 5, 2013 at 13:35

Filipe Silva · Accepted Answer · 2013-12-05 13:32:07Z

2

You probably shouldn't store that field, otherwise it will be outdated very frequently if peopleTable is changed often. And simply calculate it whenever you want to show it.

But if you insist on having it, you can do:

UPDATE fruitsTable f
SET f.numberOfPeople = (
    SELECT COUNT(*)
    FROM peopleTable p
    WHERE p.fruit = f.name
    )

If you want to get table fruits with the numberOfPeople updated everytime you do need it you would do:

SELECT f.id, f.name, COUNT(*) AS numberOfPeople
FROM fruitsTable f
INNER JOIN peopleTable p ON p.fruit = f.name
GROUP BY f.id, f.name

You can change INNER JOIN to LEFT JOIN if you have fruits that don't have any people associated with it and you still want to count them (0).

answered Dec 5, 2013 at 13:32

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

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

2 Comments

Sercan Over a year ago

I will give a shot as soon as I reach my files on home computer.

Filipe Silva Over a year ago

I advise you to see the suggestion from Spell to change name to id in peopleTable. This way, if you ever change the name of a fruit, you don't have to change every occurrence of it in peopleTable.

Spell · Accepted Answer · 2013-12-05 13:33:55Z

1

I advice you to change peopleTable table to follow structure

peopleTable
person fruit_id
john   1
...

And by the question you need follow sql

SELECT a.id, COUNT(*) as count FROM fruitsTable a
LEFT JOIN peopleTable b ON a.id = b.fruit_id
GROUP BY a.id

This will output follows (Example data)

id  count
1   2
2   4
...

And update query

UPDATE fruitTable a SET numberOfPeople = (
    SELECT COUNT(*) FROM peopleTable b WHERE a.id = b.fruit_id GROUP BY b.fruit_id
);

answered Dec 5, 2013 at 13:33

Spell

8,7582 gold badges19 silver badges19 bronze badges

2 Comments

Sercan Over a year ago

Do I need to put this code in a loop, or it will work for all the fruits automatically?

Spell Over a year ago

No, you dont need to do a loop. Untill you will set WHERE section in UPDATE. Otherwise it will update everything that in a table

GreenBetweenTheBraces · Accepted Answer · 2013-12-05 14:27:14Z

0

This will get what your asking without needing the fruitTable, doesn't depend the fruit from the peopleTable actually be in the fruitTable

select fruit, count(*) as 'Count' from peopleTable
group by fruit
order by fruit asc

The other solution I would have if both tables need to exist is the same as Filipe.

answered Dec 5, 2013 at 14:27

GreenBetweenTheBraces

411 silver badge8 bronze badges

Collectives™ on Stack Overflow

Getting the number of rows on MySQL with SQL and PHP

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related