MySql only returns 1 result in php

Question

I've got this PHP code:

$queryGO = "SELECT * from (SELECT g.go as GO, a.gen as Gen, g.descr as Description 
                            FROM geneOntology g, $tabla a where g.go=a.go) resultado 
                            WHERE resultado.go = '$go'";

$result = mysql_query($queryGO, $db);
$items = mysql_affected_rows($db);
$row = mysql_fetch_array($result);

It constructs me this query:

SELECT * from ( SELECT g.go as GO, a.gen as Gen, g.descr as Description
FROM geneOntology g, goHuman a where g.go=a.go) resultado 
WHERE resultado.go = 'GO:0000012'

In PHP, it returns only 1 item (and row) but if I execute the query directly in terminal, it throws 15 registers:

+------------+--------+----------------------------+
| GO         | Gen    | Description                |
+------------+--------+----------------------------+
| GO:0000012 | APLF   | single strand break repair |
| GO:0000012 | APTX   | single strand break repair |
| GO:0000012 | E9PIP4 | single strand break repair |
| GO:0000012 | E9PJ82 | single strand break repair |
| GO:0000012 | E9PLZ0 | single strand break repair |
| GO:0000012 | E9PP57 | single strand break repair |
| GO:0000012 | E9PQ18 | single strand break repair |
| GO:0000012 | H0YEW9 | single strand break repair |
| GO:0000012 | LIG4   | single strand break repair |
| GO:0000012 | M0R2N6 | single strand break repair |
| GO:0000012 | Q6ZNB5 | single strand break repair |
| GO:0000012 | SIRT1  | single strand break repair |
| GO:0000012 | TDP1   | single strand break repair |
| GO:0000012 | TNP1   | single strand break repair |
| GO:0000012 | XRCC1  | single strand break repair |
+------------+--------+----------------------------+
15 rows in set (0,04 sec)

I cannot see what is happening. Indeed, I have similar code in other php action and it executes de query ok... Could you help me?

Thanks in advance! Regards.

Jay Blanchard · Accepted Answer · 2016-05-22 16:48:42Z

2

Loop through your return with a while loop, you're only returning one row:

while($row = mysql_fetch_array($result)){
   print_r($row);
}

Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy.

answered May 22, 2016 at 16:48

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

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

3 Comments

Hanky Panky Over a year ago

@user3118279 And if you switch to PDO you will also be able to use fetchAll() to achieve what you are trying to achieve currently with an API that doesn't support it.

user3118279 Over a year ago

Ok, I'm gonna try it. Anyway, $items = mysql_affected_rows($db), should not return 15 instead 1?

Jay Blanchard Over a year ago

mysql_affected_rows doesn't work for SELECT queries, check the docs.

Collectives™ on Stack Overflow

MySql only returns 1 result in php

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related