Identifying the Root Cause of MySQLi Query Retrieving Only One Row

When facing the issue where a MySQLi query returns only one row despite expecting multiple, it's essential to examine the code involved. In the provided case, the query aims to retrieve data from the sb_buddies and sb_users tables.

The code selects columns from both tables and joins them based on the buddy_requester_id field. However, the subsequent line attempts to fetch only a single row using $request_list_result->fetch_array().

Solution: Using fetch_all() to Retrieve Multiple Rows

To retrieve multiple rows, it's necessary to employ the fetch_all() method:

$request_list_result = $mysqli->query("
SELECT buddy_requester_id, buddy_reciepient_id, user_id, user_fullname FROM sb_buddies
JOIN sb_users ON buddy_requester_id=user_id
WHERE buddy_status='0' AND buddy_reciepient_id='". get_uid() ."'");

$request_list_rows = $request_list_result->fetch_all();

echo $request_list_rows[0]['user_fullname'];

Explaining the Difference

• fetch_array() returns a single row as an associative array, where column names serve as keys.
• fetch_all() returns all rows as an array of associative arrays, each representing a single row.

By utilizing fetch_all(), the code can now access the data for all matching rows, resolving the issue of obtaining only one row.