PHP Errors with mysqli_fetch_array(), mysqli_fetch_assoc(), and Undefined variables

I did already ask this question on Stack Exchange or somewhere, but didn’t get anywhere, so I’m hoping someone on here can assist, or at least help me understand some of it a bit more.

So, I’m attempting to update some PHP, which used to work a long while ago on older versions of PHP, but I’m running into many issues. The current machine I’m using runs CentOS-7 and is using PHP 7.3.9.

The script is basically trying to do a few simple things by connecting to a database and reading off a record, and then getting the results from an array.

I’m really not too familiar with PHP, but have attempted to update the bits which I’ve learned are no longer supported (notably mysql functions). However, despite lengthy searches of similar issues I don’t seem to be able to make any progress.

I have added debugging code so that I can see where the errors are, but I really don’t quite understand either (a) why they are errors to begin with, or (b) the error message, or (c) how to fix them.

So, the error messages are as follows:

Notice: Undefined variable: conn in /var/www/site1/test.php on line 12
PHP Warning:  mysqli_fetch_assoc() expects exactly 1 parameter, 2 given in /var/www/site1/test.php on line 12

Warning: mysqli_fetch_assoc() expects exactly 1 parameter, 2 given in /var/www/site1/test.php on line 12
PHP Warning:  mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /var/www/site1/test.php on line 13

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /var/www/site1/test.php on line 13
PHP Notice:  Array to string conversion in /var/www/site1/test.php on line 11

Notice: Array to string conversion in /var/www/site1/test.php on line 11
PHP Notice:  Undefined variable: conn in /var/www/site1/test.php on line 12

while the code I’m testing looks like this:

<?php

$conn   = mysqli_connect('localhost', 'solomon', 'mypwd', 'testdb') or die ("Can't connect to MySQL server.");
$arg    = $_SERVER['QUERY_STRING'] ?? '1';

function readdb($arg) {
  $sql     = "SELECT * FROM countries WHERE id=$arg";
  $result  = mysqli_fetch_assoc($conn, $sql);
  $myrow   = mysqli_fetch_array($result);
  $id      = $myrow["id"];
  $country = $myrow["country"];
  $iscity  = $myrow["iscity"];
  return $country;
}

function maxrecord() {
  $conn   = mysqli_connect('localhost', 'solomon', 'mypwd', 'testdb') or die ("Can't connect to MySQL server.");
  $sql    = "SELECT COUNT(1) FROM countries";
  $result = mysqli_query($conn, $sql);
  $result = mysqli_fetch_assoc($result);
  return $result;
}

$max = maxrecord();

if (($arg<=$max)&&($arg>=1))
{
$this_place=readdb($arg);
}
else {exit;}

# ... other not very relevant bits below
?>

Any help and pointers in the right direction would be very much appreciated. Thanks!

It’s a bit difficult to figure out which line is which, but I see a few issues. For example:

function readdb($arg) {
  $sql     = "SELECT * FROM countries WHERE id=$arg";
  $result  = mysqli_fetch_assoc($conn, $sql);

you don’t actually execute that query, you just assign it to a string. On the next line, you try to use $conn but that doesn’t exist inside the function, and as you haven’t executed the query, you can’t start fetching results from it. In your other function, you connect inside the function, so that’s OK. You could get around it by connecting inside your function (a bit wasteful), defining $conn as a global variable (a bit horrible) or just passing $conn into the functions.

I don’t use mysqli myself, so there may be more issues, but fix those first and see how many errors go away.

Once you have it working, you need to use prepared statements instead of just appending strings into your query.

Thanks for the reply. I now have it near enough working, but still one error message I don’t quite understand, and I haven’t yet figured out how to change it over to using prepared statements.

For ease, I have continued to use the global conn, (but changed it to “con”), and now pass it across to each function I use. The readdb function now looks like this, and at least produces a result:

function readdb($arg,$con) {
  $sql     = 'SELECT * FROM countries WHERE id=' . $arg;   # line 14
  $result  = mysqli_query($con,$sql); 
  $myrow   = mysqli_fetch_assoc($result);                  # line 16
  printf ("%s %s %s\n", $myrow["id"], $myrow["country"], $myrow["iscity"]);

  $id      = $myrow["id"];
  $country = $myrow["country"];
  $iscity  = $myrow["iscity"];

  return $country;
}

The warning messages I get still relate to lines 14 and 16, as marked above:

PHP Notice:  Array to string conversion in /var/www/site1/test.php on line 14
PHP Warning:  mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in /var/www/site1/test.php on line 16

On line 14 all I’m doing is concatenating two strings; I really don’t understand why it even mentions array to string conversion when there is no array.
On line 16, I’m mystified as well, since $result is not a bool.

If someone is able explain the error messages/warnings, or to show me a quick way to convert to a prepared query then that would be really useful, but meantime I’ll endeavor to try and find solutions myself. Thank you for the post; appreciated.

$result will be a bool, specifically one with a value of false, if your query did not work for some reason. As you don’t check to see whether it worked, you’re now trying to use a false value instead of a result object. Because your $arg is an array, or at least PHP thinks it is, it won’t have been added to the end of the query, so the query won’t run because it’ll have a syntax error.

What do you get if you var_dump($arg) ?

For prepared statements, scroll down a bit and browse some threads - there are plenty of examples on here, some in the last few days. I use PDO, and it’s a bit different, otherwise it would have been quicker for me to type an example than type this para. But I always have to look up mysqli syntax.

Okay, I’ve made a little headway. Some of my issue had to do with the fact that because I default the ID of the record to 1, when I later try to read the prior record there is none. Also, I have now set the default value of ID as an integer, rather than a string.

I’ve posted pretty much the full script below, and you’ll note that it actually goes into the readdb function three times, hence three var_dump’s:

<?php
$con    = mysqli_connect('localhost', 'solomon', 'mypwd', 'testdb') or die ("Can't connect.");
$arg    = $_SERVER['QUERY_STRING'] ?? 1;
if (mysqli_connect_errno()) {echo "Failed to connect to MySQL: " . mysqli_connect_error();}

function readdb($arg,$con) {
  var_dump($arg);
  $sql     = 'SELECT * FROM countries WHERE id=' . $arg;
  $result  = mysqli_query($con,$sql);
  $myrow   = mysqli_fetch_assoc($result);
  printf ("%s %s %s\n", $myrow["id"], $myrow["country"], $myrow["iscity"]);

  $id      = $myrow["id"];
  $country = $myrow["country"];
  $iscity  = $myrow["iscity"];
  return $country;
}

function maxrecord($con) {
  $sql    = "SELECT COUNT(1) FROM countries";
  $result = mysqli_query($con, $sql);
  $mymax  = mysqli_fetch_assoc($result);
  return $mymax;
}

$max = maxrecord($con);   echo "\nDebug: Max = $max\n";    # *This is line 35*

if (($arg<=$max)&&($arg>=1))
{
$this_place=readdb($arg,$con);
}
else{exit;}

if ($arg==$max){$n_arg=1;}else{$n_arg=$arg+1;}
if ($arg==1)   {$p_arg=$max;}else{$p_arg=$arg-1;}

$next=readdb($n_arg,$con);      # this tries to get the next record after the current one
$prev=readdb($p_arg,$con);      # this tries to get the previous record before the current one

# a few other print bits below here ... but no other queries
?>

So, running this to get the var_dump gives this:

PHP Notice:  Array to string conversion in /var/www/site1/test.php on line 35

Debug: Max = Array
int(12)
12 Unites States of America
int(13)
13 USA
int(11)
11 United States

So, among other things, $max is obviously not meant to be an Array, but I’m not quite sure how it seems to be getting in such a muddle. (or, rather, how I am).

Update:
Ah, figured it out. I’ve now changed the maxrecord function to the below:

function maxrecord($con) {
  $sql    = "SELECT COUNT(1) AS recs FROM countries";
  $result = mysqli_query($con, $sql);
  $mymax  = mysqli_fetch_assoc($result);
  return $mymax['recs'];
}

which (when I set the default ID to record number 12) gives:

Debug: Max = 42415
int(12)
12 Unites States of America
int(13)
13 USA
int(11)
11 United States

Thank you very much for your help so far; I’ll now look at the prepared statements stuff.

1 Like
  1. Undefined variables are not a big problem just declare them or use error_reporting(E_ALL ^E_NOTICE) ;
    2.use one method both are giving arrays mysqli_fetch_assoc(); is the best.
  2. Most importantly before getting data first execute the query and in “fetch assoc” function provide only 1 parameter which is mysqli_query
  3. Also at yhe end of your sql command you must have to add semi colon “;”

I’ve just looked at a sample of the queries that I use in a site I wrote for myself, and none of them have a semi-colon on the end of the query.

Thanks for both replies. Yes, can confirm that my code works okay without the semi-colon in the query.
Still need to look at the prepared statements stuff, but for now I’m just running the input (i.e. $_SERVER[‘QUERY_STRING’]) through a regex to ensure I only have numerics, and that they’re only between a certain range.
Thank you again for your previous assistance droopsnoot; I really do appreciate it.

I wouldn’t say “must”, I’d say “can”.

I’m used to running queries in the CLI before I put them into PHP code. eg.

 ..> SELECT COUNT(1) AS recs FROM countries; 

in the CLI, without the semicolon it would wait for more. (the line would be seen as the beginning of a multi-line)

If I copy/paste into PHP

 $sql    = "SELECT COUNT(1) AS recs FROM countries;";

it doesn’t give me any errors.

After looking at documentation, it might be that if there is anything between the semicolon and the enclosing quotation marks PHP thinks it is a multi-statement and would error? TBH I don’t know as it’s never happened to me, but if so, it could be argued that the semicolon should not be included.

The obsolete mysql_query has

The query string should not end with a semicolon.

; is a way to terminate a query. In MySQL there is also \G to end a query if you want to see the output in rows instead of columns, like:

  id: 1
name: Mittineague
---
  id: 2
name: rpkamp

etc.

I would thus say that ; is indeed just a CLI artifact and is completely optional in PHP query. I personally always opt to leave it out, but to each their own I suppose.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.