I've a following 3 table in mysql db.
project table :
p_id p_name p_notes is_active p_owner p_owner_id p_date
8 project notes 1 shibbir 18 01-01-2015
Project_assign_clients:
pac_id assign_clients assign_client_id is_main_user p_id
39 Hara Adachi 8725 1 8
40 Aihara 8726 0 8
41 Akanuma Kenji 8023 0 8
42 Bayani Patrick 7801 0 8
Project_assign_users:
pas_id assign_users assign_user_id p_id
5 teustace 12 8
6 alawson 10 8
7 mfischer 14 8
8 smitchell 15 8
Now I want to get how many projects users created. e.g. I have logged in user who's id is 18 so using above table it's should be return 1 project using mysqli_num_rows. here is my query but it's return 30 strange !!
$logged_user_id = $_SESSION['user_id'];
$query = mysqli_query($link, "SELECT projects . *, projects_assign_clients . *, projects_assign_users . * FROM projects LEFT JOIN projects_assign_clients on projects.p_id = projects_assign_clients.p_id LEFT JOIN projects_assign_users ON projects.p_id = projects_assign_users.p_id WHERE projects.p_owner_id = '$logged_user_id' ");
$num = mysqli_num_rows($query);
echo $num . " found"; // retrun 30 but should be 1 according to above table data
Just keep your query, but also add a sub-select to get the number of projects:
SELECT projects.*,
projects_assign_clients.*,
projects_assign_users.*,
(select count(*) from projects where p_owner_id = p.p_owner_id)
FROM projects p LEFT JOIN projects_assign_clients
ON projects.p_id = projects_assign_clients.p_id
LEFT JOIN projects_assign_users ON projects.p_id = projects_assign_users.p_id
WHERE projects.p_owner_id = '$logged_user_id'