There are 2 tables: subjects and messages.
subjects table:
----id---- ----title----
1 Subject 1
2 Subject 2
3 Subject 3
messages table:
----id---- ----subject---- ----message----
1 1 Message to Subject 1
2 1 Message to Subject 1
3 2 Message to Subject 2
How to get mysql_num_rows of subjects, which has got any messages in messages? The result must be 2, because Subject 1 has message in messages, and Subject 2 has message in messages, but Subject 3 hasn't got any message in messages.
Something like:
mysql_num_rows(mysql_query("SELECT * FROM subjects ...
You would normally use in or exists for this purpose:
select count(*)
from subjects s
where exists (select 1 from messages m where m.subject_id = s.id);
This can easily be modified to get the subjects with no messages.
If you have a proper foreign key relationship defined between the tables, then you can just count the subjects in messages:
select count(distinct m.subject_id)
from messages;
Aggregation in MySQL is quite expensive. There are circumstances where this will perform better. However, the existsis likely to perform better under most circumstances, assuming you have an index onmessages(subject_id)`.
TRY THIS: I think we can simply do it using INNER JOIN
SELECT COUNT(mes.subject) total_msg
FROM subjects sub
INNER JOIN messages mes on mes.subject_id = sub.id
Try this
$result = $mysqli->query("SELECT messages.id, messages.message,
subjects.title,subjects.id as subjectID
FROM messages
INNER JOIN subjects
ON messages.subject=subjects.id");
$row_cnt = $result->num_rows;
echo $row_cnt;
If you want to search for title in the messages, then try this.
select count(s.title) as result
from subjects s
where exists (select 1 from messages m
where instr(m.message,s.title)> 1
)
Explanation: instr(m.message,s.title) will search for title in messages and return the start position (integer). If not found, you will get 0. So instr(m.message,s.title)> 1 will only be true for the expected matches.
Then count(s.title) will give you distinct count.