How to get count of rows in MySQL table using PHP?

You have a couple of options how to get the value of COUNT(*) from the SQL. The easiest three are probably this:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_assoc($result)['COUNT(*)'];
echo $count;

or using column alias:

$sql = "SELECT COUNT(*) as cnt FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_assoc($result)['cnt'];
echo $count;

or using numerical array:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_row($result)[0];
echo $count;

If you are using PHP 8.1, then you can do it even simpler:

$sql = "SELECT COUNT(*) FROM news";
$result = mysqli_query($con, $sql);
$count = mysqli_fetch_column($result);
echo $count;
// or using OO style
echo $con->query("SELECT COUNT(*) FROM news")->fetch_column();

Do not use mysqli_num_rows to count the records in the database as suggested in some places on the web. This function has very little use, and counting records is definitely not one of them. Using mysqli_num_rows you would be asking MySQL to retrieve all matching records from database, which could be very resource consuming. It is much better to delegate the job of counting records to MySQL and then just get the returned value in PHP as shown in my answer.

I would also recommend to learn OOP, which makes your code cleaner and easier to read. The same with OOP could be done as follows:

$sql = "SELECT COUNT(*) FROM news";
$count = $con->query($sql)->fetch_row()[0];
echo $count;

If your query uses variables, then you could do a similar thing, but using prepared statements.

$sql = "SELECT COUNT(*) FROM news WHERE category=?";
$stmt = $con->prepare($sql);
$stmt->bind_param('s', $category);
$stmt->execute();
$count = $stmt->get_result()->fetch_row()[0];
echo $count;