fetch_assoc mysqli returning empty

Question

I an trying to query a mySQL database using a PHP function. Intermittantly, the function that I am using does not seem to return a result. As far as I can detect, it's not a null response, and based on the function, it doesn't seem to be returning an invalid result ( the return string Nuthin in this case ).

function GeneratePiece2 ($sqlstr){
  $conn = new mysqli(gHOST, gUSER, gPASSWORD, gDATABASE);
  // Check connection
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  $result = $conn->query($sqlstr);
  $returnable = "";
  if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row;
        $returnable = $row;
    }
  } else {
    return "nuthin";
  }
  $conn->close();
  return $returnable;
}

I've been using the same SQL Query of

SELECT  Name FROM Char_Name_full WHERE Male=1 AND DivisionID=12  ORDER BY RAND() LIMIT 1

Around 80% of the time I will receive a result of something like {"Name":"Christoph"} but 1 out of 10 returns nothing.

Please check that there are no rows where the value of Name is NULL. — Chris Schaller, Nov 10 '17 at 04:55
As far as I can tell there are not NULL results. Is there another way to check other than an echo result? — Joe Cool, Nov 10 '17 at 04:56
Actually inspecting the value or echo a character before/after the $row value. Alternatively, exclude nulls with SQL like this: WHERE Name IS NOT NULL AND ... (add your previous criteria). Or do a specific search for nulls, select name from Char_Name_full WHERE Name IS null if the number of rows in that query is greater than 0 then you have null names. Also you could bring back other columns like the id column... — Chris Schaller, Nov 10 '17 at 05:00
So you are checking not the **actual** direct return but a j result of some **other code**, like JSON encode or the like. But you blame fetch assoc. Does it sound logical to you? — Your Common Sense, Nov 10 '17 at 05:23
You are right. I switched echo $row to print_r($row) and was able to see the information. The problem is not with fetch assoc.Thank you! — Joe Cool, Nov 10 '17 at 15:23

score 1 · Answer 1 · answered Nov 01 '20 at 05:14

1

If someone is having problems with fetch_assoc() returning "Undefined index": Check the indexes names (database Column name) - they are case sensitive.

Example: For "SELECT Name FROM..." Your code must be $row["Name"]. Not NAME or name.

answered Nov 01 '20 at 05:14

Rafael Maia Alves

11
1

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

In your query, you are checking if the number of rows is greater than zero, but you are not doing anything to check if the value that is returned is Null. The behaviour that you are describing matches the scenario of at least 1 row in 'Name' that has a NULL value.

Note that I am not going to comment on your code structure or syntax here, but I have changed your response values to help identify / prove that a null value is the issue here.

Also, if your query is limited to a single row, then the while loop will only iterate once.

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Echo out the values for Name in the result set
        if(is_null($row["Name"], )) 
            echo '[NULL]';
        else
            echo $row["Name"];
        
        // your application logic ;)
        $returnable = $row;
    }
} else {
    return "no rows";
}

Please consider using a SQL IDE like Toad for MySQL along side your development, then you can visually inspect your data for these issues without writing code hacks :)

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 10 '17 at 05:14

Chris Schaller

13,704
3
43
81

OP, don't use this code, it is bloated and of no help – Your Common Sense Nov 10 '17 at 05:16
That's not very fair ;) I added 3 lines to OP's code to help them figure it out... The problem is I can't do that very well as a comment. – Chris Schaller Nov 10 '17 at 05:29
Better check the man on the while statement and see if your condition makes any sense – Your Common Sense Nov 10 '17 at 05:57
Alright, I've edited the response to actually compile now, though it only validates that the function does actually return a result, but that the result is null. Hopefully the advice to open a data management tool will help OP to understand what is going on behind the scenes here – Chris Schaller Nov 10 '17 at 07:05
the advice to open a data management tool should be a comment, not answer. And NO, it won't help even a bit – Your Common Sense Nov 10 '17 at 07:16

fetch_assoc mysqli returning empty

2 Answers2