Couldn'T Fetch Mysqli_Result

[Josee]

Hello,

 

Could someone please help me understand an error message?

 

My script is supposed to fetch from the database all the different forms for a French verb and print them, and so it does, just as expected (here is the beginning of the page):

 

 

 

except that just after the results I get this error message:

 

'/Users/xxx/Dropbox/Travail/htdocs/xxx/flexions_afficher.php5'

line 32 : mysqli_fetch_array() [function.mysqli-fetch-array]: Couldn't fetch mysqli_result

 

 

Line 32 is the first "while" loop in my script:

 

if (isset($_POST['submitted']))
{
  require_once(MYSQL);
  include('includes/fonctions_outils.php5');

  if (empty($_POST['referent']))
  {
     echo '<p class="erreur">Indiquer le référent à chercher.</p>';
  }
  else
  {
     // nettoyer() is a user-defined function just applying 
     // mysqli_real_escape_string(), trim() and so on to data.
     $referent = nettoyer($_POST['referent']);

     $q0 = "SELECT 
        id_referent, 
        SUBSTRING_INDEX(referent, '_', 1) 
     FROM referents 
     WHERE SUBSTRING_INDEX(referent, '_', 1) = '$referent' 
     AND id_gramm_cat = 2";

     $r0 = @mysqli_query($bdd, $q0);
     $n0 = mysqli_num_rows($r0);

     if ($n0 > 0)
     {
        // The following line is line 32.
        while ($row0 = mysqli_fetch_array($r0, MYSQLI_NUM))
        …
     }
  }
}

 

I'm not sure what to copy from the details of the error message, so here is the end. Please let me know if it's not enough information.

 

[_REQUEST] => Array (
  [referent] => cingler,
  [valider] => Valider,
  [submitted] => TRUE,
  [phpSESSID] => b64ad9fd3334351660539297c072d784),
  [page_title] => Flexions,
  [HTTP_SESSION_VARS] => Array (),
  [_SESSION] => Array (),
  [bdd] => mysqli Object (),
  [referent] => cingler,
  [q0] => SELECT id_referent, SUBSTRING_INDEX(referent, '_', 1) 
     FROM referents
     WHERE SUBSTRING_INDEX(referent, '_', 1) = 'cingler'
     AND id_gramm_cat = 2,
  [r0] => mysqli_result Object (),
  [n0] => 1,
  [row0] => Array (
     [0] => 2,
     [1] => cingler
  ),
  [id_referent] => 2,
  [q1] => SELECT
        rf.id_flexion, f.flexion,
        rf.id_gramm_mood, m.gramm_mood,
        rf.id_gramm_tense, t.gramm_tense,
        rf.id_gramm_number, n.gramm_number,
        rf.id_gramm_person, p.gramm_person,
        rf.id_gramm_gender, g.gramm_gender
     FROM referents_flexions AS rf
     INNER JOIN flexions AS f USING (id_flexion)
     LEFT JOIN gramm_moods AS m USING (id_gramm_mood)
     LEFT JOIN gramm_tenses AS t USING (id_gramm_tense)
     LEFT JOIN gramm_numbers AS n USING (id_gramm_number)
     LEFT JOIN gramm_persons AS p USING (id_gramm_person)
     LEFT JOIN gramm_genders AS g USING (id_gramm_gender)
     WHERE rf.id_referent = 2
     ORDER BY rf.id_gramm_mood, rf.id_gramm_tense, rf.id_gramm_number,
        rf.id_gramm_person, rf.id_gramm_gender,
  [r1] => mysqli_result Object (),
  [num1] => 51,
  [row1] => 
)) called at [(null):0]
#1  mysqli_fetch_array(mysqli_result Object (), 2) called at 
[/users/xxx/Dropbox/Travail/htdocs/xxx/flexions_afficher.php5:32]

 

Could someone explain why I get this error message, and what it means ?

 

With thanks in advance for your help,

[Antonio Conte]

When getting the information inside the while-loop, do you use something like:

 

$row0[0] or something like that? $row0 is the name of the variable and as your fetch_array uses numbers, that is the only option.

 

With mysqli_fetch_array($r0, MYSQL_ASSOC) you could use the names of the coulmns queried from the DB. I don't know if this is your problem, but it's an easy mistake.

 

If you use MYSQL_BOTH with mysql_fetch_array, you are allowed to use both representations

Edit: Might also be a good idea to drop the @-symbol to see any errors generated from mysqli_query.

Edited July 5, 2011 by Antonio Conte

[Josee]

Thank you for your answer.

 

Yes, I use $row0[0], like this:

 

if ($n0 > 0)
{
  while ($row0 = mysqli_fetch_array($r0, MYSQLI_NUM))
  {
     $id_referent = (int) $row0[0];
     echo '<h1>'.$id_referent.' '.$row0[1].'</h1>';
     …
  }
}

 

That's what generates the <h1> title (2 cingler) you can see just above the table. I also tried suppressing the @ symbol as you suggested, but that didn't change anything. In fact, the script generates exactly what I want at this stage… except for this error message, of course! That's what's frustrating. When I don't get the results I expected, I more or less know where to start looking for mistakes in my scripts, but here, I don't understand what the problem is.

[Josee]

Problem solved!

 

My mistake was using a "while" loop, because query $q0 returns only one result. That's why the error message says:

 

called at [(null):0]

 

The "while" loop was "looking" for other rows when there couldn't be any since the query was limited to one row. So, the code that works is simply:

 

if (isset($_POST['submitted']))
{
  require_once(MYSQL);
  include('includes/fonctions_outils.php5');

  if (empty($_POST['referent']))
  {
     echo '<p class="erreur">Indiquer le référent à chercher.</p>';
  }
  else
  {
     // nettoyer() is a user-defined function just applying 
     // mysqli_real_escape_string(), trim() and so on to data.
     $referent = nettoyer($_POST['referent']);

     $q0 = "SELECT 
        id_referent, 
        SUBSTRING_INDEX(referent, '_', 1) 
     FROM referents 
     WHERE SUBSTRING_INDEX(referent, '_', 1) = '$referent' 
     AND id_gramm_cat = 2";

     $r0 = @mysqli_query($bdd, $q0);
     $n0 = mysqli_num_rows($r0);

     if ($n0 > 0)
     {
        // No loop, since I'm only querying 1 row.
        $row0 = mysqli_fetch_array($r0, MYSQLI_NUM);
        $id_referent = (int) $row0[0];
        echo '<h1>'.$id_referent.' '.$row0[1].'</h1>';
     }
  }
}

[Antonio Conte]

Glad you worked it out. Weird error and not one that is easy to spot!

 

Good work.