user3118279 user3118279 - 6 months ago 8
SQL Question

MySql only returns 1 result in php

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.

Answer

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.