CODEWITHSUNDEEP
phpmysqlsql
Sercan
Sercan

Reputation: 325

Getting the number of rows on MySQL with SQL and PHP

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?

Upvotes: 2

Views: 89

Answers (3)

GreenBetweenTheBraces
GreenBetweenTheBraces

Reputation: 41

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.

Upvotes: 0

Spell
Spell

Reputation: 8668

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

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21677

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

Upvotes: 2

Related Questions