在mysql中连接2个表

how would i count posts for specific user logged in. for example when user with id 3 is logged in it should show me 4 posts I already did it for total posts count:

<?php
$post_query1 = "SELECT count(*) AS total FROM posts "; 
$post_result1 = mysqli_query($db, $post_query1); 
$post1 = mysqli_fetch_array($post_result1); 
?>

You have only to use a simple join.

SELECT count(*)
FROM USER u,
          post p
WHERE p.id_user = u.id_user
AND u.id_user = 3

Try below example :

select count(*) as total from user as u inner join post as p on  p.id_user = u.id_user AND u.id_user = 3

If you want to get only the posts count for the particular user, say user with id = 3, your query should be this:

$query = "SELECT count(*) AS total FROM posts WHERE id_users = 3";

But if you want to get both the posts count as well as the user information and other post information, you will have to run a join query on both the users and posts table. Your query would now become:

$query = "SELECT u.*, p.*, count(p.id_posts) FROM users AS u JOIN posts AS p ON u.id_users = p.id_users WHERE p.id_users = 3";

Some Useful Notes

p.* - * is a wildcard character that means get all the columns in the posts table
u.* - * is a wildcard that means get all the columns in the users table
posts as p - AS is for aliasing. So, we are giving posts table a temporary name.

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Note: It is necessary that you have to join two/more tables only with the help of foreign key. Without the foreign key is is meaningless to join two or more tables

Reference 1: https://www.w3schools.com/sql/sql_join.asp

Reference 2: https://www.tutorialspoint.com/mysql/mysql-using-joins.htm

As per the Question what you have asked to join the tables

Query:

SELECT * FROM TABLE 1 JOIN TABLE 2 ON TABLE1.id = TABLE2.id WHERE TABLE2.ID=3

Kindly replace TABLE1 & TABLE2 with the Tables that are to be joined and the id with the foreign key what you have specified in the Table.

Hope so this might be helpful for you to write your own code in future. Happy Coding :)