php,MySQL: how to display SELECT COUNT WHERE results?

Question

I want to show results of a 'SELECT COUNT' with clause 'WHERE' on display, and I don't know if it is a syntax issue or not; here is my code:

$sql="SELECT COUNT(*) FROM utenti WHERE surname= 'Smith'";
$rs = $conn->query($sql);
$result = mysql_fetch_array($rs);
echo $result[0];

Answer 1

You are trying to use deprecated API MySQLi http://php.net/manual/en/book.mysqli.php

Try to use PDO instead https://www.w3schools.com/php/php_mysql_connect.asp.

$sql = "SELECT count(*) FROM utenti WHERE cognome = 'Tosone'"; 
$result = $con->prepare($sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn(); 

Answer 2

Try this:

$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE);

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT count(*) AS nb FROM utenti WHERE surname= 'Smith'")) {
    $res = $result->fetch_assoc();

    print_r($res["nb"]);

    /* free result set */
    $result->close();
}