Problem fetching query results

wadori · August 7, 2025, 11:39pm

I am having problems with the fetch statement in my script. First of all, I have the following script that works perfectly:

<?php
include 'login.php';
$cnct = mysqli_connect($hostname, $usernam, $passwrd) or die('Unable to connect to MySQL server.');
$database = 'wardrick_all_words_bigger';
mysqli_select_db($cnct, $database) or die('Unable to select database.');  

$query = "SELECT word FROM allwords";
$doit = mysqli_query($cnct, $query) or die("Unable to select from database");
$rowcount=mysqli_num_rows($doit); //number of records
echo "<p>rowcount is $rowcount</p>";

$count = 5;
for($i=0; $i<$count; $i++)
{
$fetchit = mysqli_fetch_assoc($doit) or die('Couldnt fetch'); 
		$w = $fetchit["word"];
		echo "<p>$w</p>";
}
echo "done";
mysqli_close($cnct);
?>

The page that I am trying to fix is the following (the entire script):

<?php
include 'login.php';
$cnct = mysqli_connect($hostname, $usernam, $passwrd) or die('Unable to connect to MySQL server.');
$database = 'wardrick_all_words_bigger';
mysqli_select_db($cnct, $database) or die('Unable to select database.');  

$query = "SELECT word FROM allwords";
$doit = mysqli_query($cnct, $query) or die("Unable to select from database");
$rowcount=mysqli_num_rows($doit); //number of records
echo "<p>rowcount is $rowcount</p>";
	
for($i=0; $i<=$rowcount; $i++)
	{	$fetchit = mysqli_fetch_assoc($doit) or die('Couldnt fetch'); 
		$w = $fetchit["word"];
		$stringlength = strlen($w);
		if($stringlength == 5)
			{
				//insert into table five_letter_words
				$query2 = "INSERT INTO five_letter_words (word) VALUES ('$w')";      ########## hangs up on this query #################
				$doit = mysqli_query($cnct, $query2) or die("Unable to insert into db");
					
			}
	}
echo "done";
mysqli_close($cnct);

As you can see, the fetch statement is exactly the same in both scripts, but I get the following error when running the second script:

[07-Aug-2025 17:26:25 America/Boise] PHP Fatal error: Uncaught TypeError: mysqli_fetch_assoc(): Argument #1 ($result) must be of type mysqli_result, bool given in /home1/wardrick/public_html/create_table.php:13
Stack trace:
#0 /home1/wardrick/public_html/create_table.php(13): mysqli_fetch_assoc(true)
#1 {main}
thrown in /home1/wardrick/public_html/create_table.php on line 13

It seems that it doesn’t like the $doit argument, but it is the same as it is in the script that does work. (the $doit = msqli… statement is also the same.) Does somebody know what the problem is?

phdr · August 8, 2025, 1:33am

The error is because you are assigning the result from the INSERT query to the same $doit variable and when the code tries to fetch the next row of data, $doit no longer contains the result from the SELECT query.

Since you don’t actually need the boolean result value from the INSERT query, simply delete the $doit = from that line of code.

Next, the internet is filled with bad, unnecessarily complicated, and out of date code examples. Your code should -

Use require for things it must have.
You can select the database in the connection statement.
Don’t use or die(…) for error handling. Modern php (8+) uses exceptions for database error handling and any discrete conditional error logic, like the or die(…) code, will never get executed upon an error and should be removed.
If you are only using a value one place in your code, such as the strlen($w), don’t create a separate variable for it, just use the statement that is producing the variable where it is used.
For what you are doing, use a simple while(…) loop to fetch and loop over the result from a query. Your current code, looping from 0 to $i<=$rowcount, will attempt to fetch an extra row of data and produce an error.
The INSERT query should be a prepared query, prepared once, before the start of any looping, then executed with each data value inside the looping, so that no sql special characters in a value, such as a ', won’t break the sql query syntax. If one of the 5 letter words happens to be something like won’t or can’t, the current code will produce a query error. The current code is dealing with known data. If you have a query that deals with arbitrary user submitted data, you MUST protect against sql special characters in a value, as this is how sql injection is accomplished. If it seems like using the mysqli extension with prepared queries is overly complicated and inconstant, it is. This would be a good time to switch to the much simpler and better designed PDO extension.
There’s generally no need to free up result sets, close prepared query handles, or close database connections in your code, since php destroys all resources when your script ends.

Making all these changes, except the conversion to a prepared query, would look like -

require 'login.php';
$database = 'wardrick_all_words_bigger';

$cnct = mysqli_connect($hostname, $usernam, $passwrd, $database);

$query = "SELECT word FROM allwords";
$doit = mysqli_query($cnct, $query);
$rowcount = mysqli_num_rows($doit); //number of records
echo "<p>rowcount is $rowcount</p>";

while($fetchit = mysqli_fetch_assoc($doit))
{
	$w = $fetchit["word"];
	if(strlen($w) == 5)
	{
		// insert into table five_letter_words
		$query2 = "INSERT INTO five_letter_words (word) VALUES ('$w')";
		mysqli_query($cnct, $query2)
	}
}
echo "done";

Converting to the PDO extension and using a prepared query would look like -

require 'login.php';

// fill in with your connection credentials or change the variable names in the connection statement to match your variables
$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = 'wardrick_all_words_bigger'; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions - this is the default setting now in php8+
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

$query = "SELECT word FROM allwords";
$stmt = $pdo->query($query);
$doit = $stmt->fetchAll();

// prepare the INSERT query
$query = "INSERT INTO five_letter_words (word) VALUES (?)";
$i_stmt = $pdo->prepare($query);
		
$rowcount = count($doit); //number of records
echo "<p>rowcount is $rowcount</p>";

foreach($doit as $fetchit)
{
	$w = $fetchit["word"];
	if(strlen($w) == 5)
	{
		// insert into table five_letter_words
		// execute the prepared query with the current data value
		$i_stmt->execute([ $w ]);
	}
}
echo "done";

wadori · August 9, 2025, 6:25am

Thank you for the detailed information, and even providing the corrected code. You went “above and beyond the call of duty”. However, I ran the code that you provided (the first example, not the PDO), and it still doesn’t work. (I had to insert a ; after the mysqli_query line for the insert query; otherwise I didn’t make any changes.) It did insert 140 records before it quit, the last entry being “acers”. When I checked the database the words are coming from, the next five-character word after “acers” was “ace’s”, with an apostrophe, so I thought the apostrophe might be the problem. But I removed all words from the original database that contained apostrophes, and I still get the same error:

[08-Aug-2025 23:49:42 America/Boise] PHP Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘s’)’ at line 1 in /home1/wardrick/public_html/create_table.php:20
Stack trace:
#0 /home1/wardrick/public_html/create_table.php(20): mysqli_query(Object(mysqli), ‘INSERT INTO fiv…’)
#1 {main}
thrown in /home1/wardrick/public_html/create_table.php on line 20

I don’t know what it means by ‘s” at line 1, since line 1 is simply <?php, and the text ‘s’ doesn’t appear anywhere in the script. Once again it inserted about 140 records before failing. In this case, the last word entered was “abase”, and the next five letter word after that in the original table is “abash”.

Do you know why it is still failing?

Some other questions if you wish to address them:
You said that the query should be a prepared query. In both my original code as well as your code, we have “$query = ….”, and then the next line runs the query. Is this not a prepared query?
Also, you said it should be prepared before any looping, but your “$query2 = …” is inside the While loop. So it looks like you didn’t prepare it before looping, did you?
Finally, I am a very occasional (and beginner) programmer. (The last time I wrote a PHP program might have been before Covid! I have had to relearn a lot to get to where I am so far.) I do not at all understand what a PDO extension is. Since I rarely do any coding, I am not really intending to learn something new unless it is necessary. If you wish to suggest otherwise, you may do so.

phdr · August 9, 2025, 7:38am

The error is an sql syntax error -

The sql syntax error is on line 1 of the sql query statement, which is being executed at line 20 in your php code. It’s due to a value containing an sql special character, a single-quote, followed by an s. If you removed all the words with a ', you apparently missed one. You can echo the INSERT sql query statement inside the loop to see what it actually is. This will show you the whole word when it is failing.

A prepared query is a specific programming method. The $query = “…”; line is technically “building” the sql query statement and assigning it to a php variable. I just searched for “prepared query” on google and every result on the first page concerned the programming method this refers to.

The first code example I posted excluded the conversion to a prepared query -

Again, searching for ‘pdo extension’ on google returned a whole page of relevant search results.

The reason I suggested using the much simpler and better designed PDO extension, is because to implement prepared queries, which is what is needed to fix the current sql query errors, and to secure your queries for all types of user supplied data, using the msyqli extension is like using 2 different sets of database statements. The mysqli prepared and non-prepared programming interfaces are completely different. So, if you must go to the trouble of learning in order to fix this problem, you might as well learn something that is simple and consistent.

wadori · August 9, 2025, 5:07pm

Thanks again. You were right. I did a search in the original table and there were a whole bunch of words still in there with apostrophes. I don’t know why it didn’t get them the first time, but they are gone now. I ran the script again, and I now have a full table of results. Thank you so much for your help. I appreciate it.