How To Use Php Mysqli To Get Number Of Rows And Use Mysql_fetch_array() For Prepared Statments Using The Procedural Method?

I am attempting to relearn PHP, SQL, HTML, CSS and JS because it has been 2 years since I last learn it at Uni. I have the following mysqli statements which either doesnt properly

Solution 1:

I believe that fetch assoc will be better for you. You are using the column names, and using a while() loop, so you have no need to navigate the result data numerically (outside of knowing how many there are)...

$result = mysqli_stmt_get_result($stmt);
$stored_result = mysqli_stmt_store_result($stmt);
$num_of_rows = mysqli_stmt_num_rows($stmt);
if($num_of_rows > 0){
 while($row = mysql_fetch_assoc($result)){
  // declare variables (ex: $user = $row['user'])
 }
} else {
 echo"<tr><td colspan='8'>No results found...</td></tr>"; 
}

Let me know if that works for you... There's no visible reason that your attempts are failing, but without being able to see your db connection variable var dump, or similar methods of bug testing, it's going to be hard to help you.

Can you try echoing out some sql errors?

If nothing works, do this if you don't need the # of rows until the end...

$numResults = 0; 
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
 // Declare variables $numResults++; 
}
echo$numResults; // number of rows found, after loop 

Solution 2:

Wow, spent almost 5+ hours today looking for a solution, and finally I get it, feel so ashamed because the solution was so simple.

Because I am new to creating prepared statements using mysqli, I thought I only could use procedures related to it (i.e. procedures that contained stmt within their function name), therefore 1 such function I was too focused on trying to get working was:

$num_of_rows = mysqli_stmt_num_rows($stmt);

Using the mysqli_stmt_store_result() approach:

So far I was forced to use mysqli_stmt_store_result($stmt);, in order for mysqli_stmt_num_rows above to return me the correct number of rows returned from a query. However, using this mysqli_stmt_store_result() procedure led to me experience errors when i attempted to perform while($row = mysqli_fetch_assoc($result))

Using the mysqli_stmt_get_result() approach:

Because I was unable to fetch the data, I search online and found an alternative procedure, which was mysqli_stmt_get_result($stmt);, now this function enable me to fetch mysqli_fetch_assoc($result)) without issues, which allowed me to access the returned values from the query and manipulate them as I pleased. However, when using this approach mysqli_stmt_num_rows($stmt); always returned 0 regardless of the number of matching items that my query found from the database.

Solution Found After Hours of Searching

In order to find the number of rows that a query has found using:

$result = mysqli_stmt_get_result($stmt);

Was, instead of using:

$num_of_rows = mysqli_stmt_num_rows($stmt);

All i had to use was:

$num_of_rows = mysqli_num_rows($result);

And finally now this works. It returns the total number of rows returned from a query and also allows me to use while($row = mysqli_fetch_assoc($result)) to fetch and manipulate the row items returned from my query.

Also by using this approach I no longer require the need for using:

mysqli_stmt_store_result($stmt);

To fetch data from my database using mysqli.

Sorry for the lengthy answer, I have searched for a solution for hours online, and nowhere did I find a proper solution, and therefore I hope this may help anyone else facing a similar issue.

Thank you every one that helped me here and in chat to try and find a solution.