Null Result Set Being Returned

[HDRebel88]

Need some help finding out why this is returning a null result set:

 

The below code is giving me a result set of an integer set to "0" (the id), a "word"and "def" both set to NULL. It's failing at my "Error #2" point. var_dumps on $id, $word, and $def all give the null output. Each $category is being shown when I var_dump($category).

 

I know this shouldn't be the case because when I run this straight in PHPMyAdmin I get a non-null result set.

 

(Not sure why my indentation is not carrying over to the forum. Sorry about that.)

<?php
session_start();
if(!isset($_SESSION['loopcatch']) || $_SESSION['loopcatch']==null || !is_int($_SESSION['loopcatch'])){
$_SESSION['loopcatch']=0;
}
if($_SESSION['loopCatch'] > 1){
//Email error
die();
}
require 'dbConnect.php';
$categories=array('business', 'music', 'film', 'drum');
//Pull Quotes
//Query to Pull random quote
$mainQuery="SELECT `r1`.`id`, `r1`.`word`, `r1`.`def` FROM `dictionary` AS `r1` JOIN (SELECT (RAND() * (SELECT MAX(`id`) FROM `dictionary`)) AS `id`) AS `r2` WHERE `r1`.`id` >= `r2`.`id` AND `category`=? AND `checked`=0 ORDER BY `r1`.`id` ASC LIMIT 1";
//prepare quotes query
if($prepareQuote=mysqli_prepare($conn, $mainQuery)){
//filter through each category
foreach($categories as $category){
//Bind the variable to the quotes query
mysqli_stmt_bind_param($prepareQuote, "s", $category);
//execute quotes statement
mysqli_stmt_execute($prepareQuote);
//Store quotes result set
mysqli_stmt_store_result($prepareQuote);
//Check how many rows are returned
if(mysqli_stmt_num_rows($prepareQuote) > 0){
//Bind results to variables
mysqli_stmt_bind_result($prepareQuote, $id, $word, $def);
//If $id, $word, or $def is null abort and email error
if(!is_null($id) && is_numeric($id) && !is_null($word) && !is_null($def)){
while($row=mysqli_stmt_fetch($prepareQuote)){
mysqli_autocommit($conn, FALSE);
//Input into second table
$updateQuery="UPDATE `quotes` SET `word`=?, `def`=? WHERE `category`=?";
//prepare insert query
if($updateQuote=mysqli_prepare($conn, $updateQuery)){
//Bind the variables to the insert query
mysqli_stmt_bind_param($updateQuote, "sss", $word, $def, $category);
//execute insert statement
mysqli_stmt_execute($updateQuote);
//Store insert quote result set
mysqli_stmt_store_result($updateQuote);
//Check how many rows are returned on insert quote query
if(mysqli_stmt_affected_rows($updateQuote) > 0){
//If query run sucessfully insert and update; if not rollback.
//mark quote checked
$checkedQuery="UPDATE `dictionary` SET `checked`=1 WHERE `id`=?";
//prepare checked query
if($checkedQuote=mysqli_prepare($conn, $checkedQuery)){
mysqli_stmt_bind_param($checkedQuote, "i", $id);
//execute checked statement
mysqli_stmt_execute($checkedQuote);
//Store checked quote result set
mysqli_stmt_store_result($checkedQuote);
//Check how many rows are returned on checked quote query
if(mysqli_stmt_affected_rows($checkedQuote > 0)){
mysqli_commit($conn);
}
else{
echo 'Error #6 ';
mysqli_rollback($conn);
}
}
else{
echo 'Error #5';
//Email error
die();
}
}
else{
echo 'Error #4';
mysqli_rollback($conn);
}
}
else{
echo 'Error #3';
//Email error
die();
}
}
}
else{
echo 'Error #2';
//Query returned blank result set - Email Error
}
}
else{
//If zero rows returned, uncheck rows in table for that specific category and re-run the query.
$uncheckQuery="UPDATE `dictionary` SET `checked`=0 WHERE `category`=?";
if($uncheckQuotes=mysqli_prepare($conn, $uncheckQuery)){
//Bind the variable to the query
mysqli_stmt_bind_param($uncheckQuotes, "s", $category);
//execute statement
mysqli_stmt_execute($uncheckQuotes);
//Store result set
mysqli_stmt_store_result($uncheckQuotes);
//Check how many rows are returned
if(mysqli_stmt_affected_rows($uncheckQuotes) > 0){
$_SESSION['loopCatch']++;
header("Location: ./pullDailyQuotes.php");
}
else{
//Email error
}
}
}
}
}
else{
//Email error
echo 'Error #1';
die();
}
?>

Edited June 2, 2014 by HDRebel88

[Jacques1]

You're checking the result variables before you've even fetched a single row. How is that supposed to work? What are $id, $word and $def supposed to contain before the first mysqli_stmt_fetch() call?

 

You first need to fetch a row. And then you can check the content of this particular row.

 

Besides that, this looks like an awful lot of code for such a trivial task. The first thing you should do is get rid of all the manual error checks and simply have MySQLi throw an exception whenever something goes wrong:

// do this before you establish a database connection
$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

Then delete all the useless mysqli_stmt_store_result() calls. Not sure what that is supposed to do in case of an UPDATE query.

 

Why you're trying to rollback an update query when it has no affected rows is also beyond me. What is there to be rolled back then? And what's the problem with zero affected rows, anyway?