PHP coding question

Question

How can I grab the count value from the query MySQL query below using PHP.

Here is My MySQL code.

$dbc = mysqli_query($mysqli,"SELECT COUNT(*) FROM((SELECT users_friends.id
                            FROM users_friends
                            INNER JOIN users ON users_friends.user_id = users.user_id
                            WHERE users_friends.user_id = 1 
                            AND users_friends.friendship_status = '1')
                            UNION
                            (SELECT users_friends.id
                            FROM users_friends
                            INNER JOIN users ON users_friends.friend_id = users.user_id
                            WHERE users_friends.friend_id = 1
                            AND users_friends.friendship_status = '1')) as friends");

Answer 1

if ($result = mysqli_query($link, "SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", mysqli_num_rows($result));

    /* free result set */
    mysqli_free_result($result);

http://us.php.net/manual/en/mysqli.query.php

Answer 2

using SQL_CALC_FOUND_ROWS should simplify things:

$dbc = mysqli_query($mysqli,"SELECT SQL_CALC_FOUND_ROWS users_friends.id
                        FROM users_friends
                        INNER JOIN users ON users_friends.user_id = users.user_id
                        WHERE users_friends.user_id = 1 
                        AND users_friends.friendship_status = '1'
                       ");

then afterwards do

$rs = mysqli_query($mysqli, "SELECT FOUND_ROWS()"));
$rec = $rs->fetch_array();
$count = $rec[0];

This method will return the number of records that match the query, ignoring any LIMIT statement, whereas using $rs->num_rows will only give you the number of records actually returned. Depends which one you want.

Answer 3

Assuming that you are correctly connected to the MySQL server and your query are executed correctly, you can use the following code:

$values = mysql_fetch_row($dbc);
$count = $values[0];

Answer 4

Your query should look like SELECT COUNT(*) as numThings FROM xxx

The numThings is what you will reference in PHP:

$result = mysql_query("SELECT COUNT(*) as `numThings` FROM xxx");
$row = mysql_fetch_assoc($result);
$count = $row['numThings'];