Very odd mysql_query result

PHP
1

Hi there,

I am working on a database driven site, which displays a list of results on a webpage. Very simple stuff. I am only getting half my results when I run the following query:


$result = mysqli_query($link, 'select * from table');
if (!$result)
{
  echo 'Error fetching data:' . mysqli_error($link);
  exit();
}

while ($row = mysqli_fetch_array($result))
{
  $dataset[] = mysqli_fetch_assoc($result);
}

print_r($dataset);

However, if I run the query directly into my mysql database at the command prompt, I get a full set of results.

I have never seen anything like this before. Looking at the results I get, I see that only rows with an even id number are returned.

Before I start the project again from scratch, I wonder if anyone has come across anything like this before or could offer any suggestion as to what might be happening.

Many thanks,
Mike

2

Can you post the actual code you’re using? Also, have you tried using [fphp]mysqli_num_rows[/fphp] to see if the expected row number is present?

Definitely weird though. :slight_smile:

3 
$result = mysqli_query($link, 'select * from table');
if (!$result)
{
  echo 'Error fetching data:' . mysqli_error($link);
  exit();
}

while ($row = mysqli_fetch_array($result))
{
  $dataset[] = $row;
}

print_r($dataset);
4

Hi Anthony, many thanks for your swift reply.

Here’s the full code for the page:


<?php

include_once $_SERVER['DOCUMENT ROOT'] . 'includes/magicquotes.inc.php';
include $_SERVER['DOCUMENT ROOT'] . 'includes/db.inc.php';

if (isset($_GET['addquestion']))
{
  include 'form.html.php';
  exit();
}

if (isset($_POST['questiontext']) && isset($_POST['answertext']))
{
  $questiontext = mysqli_real_escape_string($link, $_POST['questiontext']);
  $answertext = mysqli_real_escape_string($link, $_POST['answertext']);
  $sql = 'insert into question set
    questiontext="' . $questiontext .'",
    answertext="' . $answertext . '",
    questiondate=CURDATE()';
  if (!mysqli_query($link, $sql))
  {
    $error = 'Error adding submitted question: ' . mysqli_error($link);
    include 'error.html.php';
    exit();
  }
  
  header('Location: .');
  exit();
  
}

if (isset($_GET['deletequestion']))
{
  $id = mysqli_real_escape_string($link, $_POST['id']);
  $sql = "delete from question where id = '$id'";
  if (!mysqli_query($link, $sql))
  {
    $error = 'Error deleting question: ' . mysql_error($link);
    include 'error.html.php';
    exit();
  }
  header('Location: .');
  exit();
}

//build dataset from SQL query
$result = mysqli_query($link, 'select * from question');
if (!$result)
{
  echo 'Error fetching questions:' . mysqli_error($link);
  exit();
}

while ($row = mysqli_fetch_array($result))
{
  $questions[] = mysqli_fetch_assoc($result);
}

$count = mysqli_num_rows($result);

include 'questions.html.php';
?>

And interestingly if I use mysqli_num_rows on the result, I get 82 rows, which is a full dataset… However if use count on my $questions array, I get 41. So I’m guessing its the mysqli_fetch_assoc() that’s causing problems.

Is there any way of having php output the raw sql query result? var_dump perhaps?

Thanks again.
Mike

5

D’OH!

So I see the problem now!


while ($row = mysqli_fetch_assoc($result))
{
  $questions[] = $row;
}

is what I wanted, rather than:


while ($row = mysqli_fetch_array($result))
{
  $questions[] = mysqli_fetch_assoc($result);
}

Sometimes you just can’t see the wood for the trees…

Thanks for you help,
Mike

6

Good spot. :slight_smile: