PHP only returns one result from a mysql query - mysql console returns dozens

Question

I would like to read all the data from my database, when I run this query from the console I get many results but for some reason php is only reading one.

$query = "
   SELECT b.raw as 'address', a.raw as 'name', c.TouchTime as 'time'
   FROM touchName a, touchHome b, trackTouch c
   WHERE a.raw like \"%{$name}%\" 
      AND c.AgentID = 1
      AND a.relations = b.relations
      AND b.relations = c.relations
      AND a.relations = c.relations
   ORDER BY time desc
";

//So we can double check in the console
echo $query . "<br><br>";

$result = mysqli_query($mc, $query);

$array = mysqli_fetch_assoc($result);

//says there is only one row
$total = count( mysqli_num_rows($result) );
echo $total."<br>";

I have tried many ways of breaking the data out of the result, I have modified the query in several ways, with group by's, counts, etc trying to break this out.

Fairly new to joins also so if this is ugly, it's because it was the first hack that didn't spit out 12 million results.

You need to fetch the results in a loop.

AyB
– AyB

2014-04-17 11:37:12 +00:00
Commented Apr 17, 2014 at 11:37 — AyB
– AyB, Commented Apr 17, 2014 at 11:37

Tyralcori · Accepted Answer · 2014-04-17 11:37:27Z

3

Try this:

$array = array();
while ($row = mysql_fetch_assoc($result)) {
   $array[] = $row;
}

answered Apr 17, 2014 at 11:37

Tyralcori

1,06714 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Pease Over a year ago

I was using a for loop comparing $i to this flawed code $total = count( mysqli_num_rows($result) ); which of course will return 1 no matter what. This method removes even the possibility of this error.

Priya Bhojani · Accepted Answer · 2014-04-17 13:12:08Z

0

The function mysql_fetch_assoc returns a single record by default . You'll need to iterate loop as @tyralcori said..

answered Apr 17, 2014 at 13:12

Priya Bhojani

1309 bronze badges

Comments

Ipsita Rout · Accepted Answer · 2014-04-17 13:35:44Z

0

$query = "
   SELECT b.raw as 'address', a.raw as 'name', c.TouchTime as 'time'
   FROM touchName a, touchHome b, trackTouch c
   WHERE a.raw like \"%{$name}%\" 
      AND c.AgentID = 1
      AND a.relations = b.relations
      AND b.relations = c.relations
      AND a.relations = c.relations
   ORDER BY time desc
";

echo $query;
$handler= mysqli_query($mc, $query);
$result = array();  
while($rec = mysqli_fetch_assoc($handler)){
    $result[]['address'] = $rec['address'];
    $result[]['name'] = $rec['name'];
    $result[]['time'] = $rec['time'];
}
print_r($result);

answered Apr 17, 2014 at 13:35

Ipsita Rout

5,2894 gold badges39 silver badges41 bronze badges

1 Comment

Patrick Pease Over a year ago

This is a good piece of code, I would have at least +1 if I could. But I was using a for loop comparing $i to this flawed code $total = count( mysqli_num_rows($result) ); which of course will return 1 no matter what. This method removes even the possibility of this error.

Collectives™ on Stack Overflow

PHP only returns one result from a mysql query - mysql console returns dozens

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related