Strange Distinct SQL / PHP Behaviour

ironclaw · 23-02-2013 09:53PM #1

Hi Folks,

Bit of an issue with my PHP / SQL here. This is the code:

$query = "SELECT DISTINCT student FROM classes LIMIT 100";
$result = mysqli_query($link, $query);
$row = mysqli_fetch_array($result, MYSQLI_NUM);
print_r($row);

Having run the query manually I get about 40 returned values. All good there. However when I do the print_r, I'm only getting the first returned value i.e. The $row array only has one entry. Link and db are fine, it just appears to be my array handling.

Any Thoughts?

ironclaw · 23-02-2013 10:09PM

Racked my brains, you need to loop:

while($row = mysqli_fetch_array($result, MYSQLI_NUM))
{
    print_r($row);
}

amen · 24-02-2013 06:58PM

You may have a bigger problem.

[PHP]SELECT DISTINCT student FROM classes LIMIT 100[/PHP]

may return a random set of 100 students each time the sql is run.

If thats what you want fine but if not you should consider adding an order by clause.

ironclaw · 24-02-2013 10:07PM

amen wrote: »

You may have a bigger problem.

[PHP]SELECT DISTINCT student FROM classes LIMIT 100[/PHP]

may return a random set of 100 students each time the sql is run.

If thats what you want fine but if not you should consider adding an order by clause.

Sorry, that was an example only. I was limiting it for debug purposes. Its a good point however!

OneIdea · 24-02-2013 10:31PM

ironclaw wrote: »
Racked my brains, you need to loop:
while($row = mysqli_fetch_array($result, MYSQLI_NUM))
{
    print_r($row);
}

Pretty much the same thing with a condition and clear/close your connections after queries.
[php]

// I normally have an include database connection file here

$query = "SELECT DISTINCT student FROM classes LIMIT 100";

$result = $mysqli->query($query);

if ($result->fetch_array(MYSQLI_NUM) == 0 ){
// do something if nothing found
} else {
while ($row = $result->fetch_array(MYSQLI_NUM)){
print_r($row)
}
}

$result->free();
$mysqli->close();
[/php]

EDIT: Just to add, although it is very rare, I went to school with a student of the same name as mine. Perhaps you might consider altering your query to include something extra like $query = "SELECT DISTINCT student, DATE of BIRTH FROM classes LIMIT 100";

[php]

// I normally have an include database connection file here

$query = "SELECT DISTINCT student, dob FROM classes LIMIT 100";

$result = $mysqli->query($query);

if ($result->fetch_array(MYSQLI_NUM) == 0 ){
// do something if nothing found
} else {
while ($row = $result->fetch_array(MYSQLI_ASSOC)){
print_r($row)
// print_r($row)
}
}

$result->free();
$mysqli->close();
[/php]

Strange Distinct SQL / PHP Behaviour

Comments