php pass value to multiple select queries

Question

I'm having trouble passing a variable between MySQL queries on the same page. Maybe someone can advise what I'm doing wrong. I'm new to PHP/MySQL, but the answer seems very easy, I just don't see it. Here is what I have:

1. MySQL: Table A:

    id | gene_id | protein_id | disease_id | etc
    ----------------------------------------------
    1  | 672     | P12803     | 091312
    2  | 817     | P99613     | 020346
    3  | 411     | P52021     | 055823

2. Search result page. Displays a list of results. The reaults are identified by [$id] and an <a href> link passes [$id] to another page for result details. This works perfectly.

3. Details Page. I get the query result from the Search results page and display related information from the table, identified by [$id]. This works fine.

    <?php

    $sql = "SELECT * FROM Table_A
    WHERE id=" . $_GET["id"];

    $rs_result = mysql_query ($sql,$connect);  
    while ($row = mysql_fetch_assoc($rs_result)) {

    ?>

    <table class="table">
    <tr><td>Gene: </td><td><? echo $row[gene_id]; ?></td></tr>
    <tr><td>Protein: </td><td><? echo $row[protein_id]; ?></td></tr>
    <tr><td>Disease: </td><td><? echo $row[disease_id]; ?></td></tr>
    </table>

    <?  } ?>

4. Show related data. On the details page, I want to show related data from Table B in the same database, using the "protein_id" from the query above. But this I can't get to work, to pass "protein_id" to the next query, as follows:

Table B:

    id | protein_asc | synonym | name | etc
    ----------------------------------------------
    11  | P12803    | ABC      | this |
    12  | P99613    | DEF      | that |

    <?php

    $new_id = $row[protein_id];

    $sqla = "SELECT * FROM Table_B
    WHERE protein_asc='".$new_id."'";

    $rsa_result = mysql_query ($sqla,$connect);  
    while ($row = mysql_fetch_assoc($rsa_result)) {

    ?>

    <table class="table">
    <tr><td>Synonym: </td><td><? echo $row[synonym]; ?></td></tr>
    <tr><td>Name: </td><td><? echo $row[name]; ?></td></tr>
    </table>

    <?  } ?>

I have tried many different ways to achieve this, using joins on the second Select query, but nothing seems to work. I know the second query is correct, because if I hard code "$new_id = P12803 ;" then the second query grabs the data.

Any help would be appreciated.

Thanks

are you using variable row for two different queries? – gpicchiarelli May 18 '13 at 14:51 — gpicchiarelli, May 18 '13 at 14:51

score 0 · Answer 1 · edited May 23 '17 at 12:11

You need to move your second loop inside first loop otherwise $row[protein_id] will return as null. Furthermore you are assign $row to both fetching, i changed second to $rowa. So change to

while ($row = mysql_fetch_assoc($rs_result)) {

?>

<table class="table">
<tr><td>Gene: </td><td><? echo $row[gene_id]; ?></td></tr>
<tr><td>Protein: </td><td><? echo $row[protein_id]; ?></td></tr>
<tr><td>Disease: </td><td><? echo $row[disease_id]; ?></td></tr>
</table>

<?php

    $new_id = $row[protein_id];

    $sqla = "SELECT * FROM Table_B
    WHERE protein_asc='".$new_id."'";

    $rsa_result = mysql_query ($sqla,$connect);  
    while ($rowa = mysql_fetch_assoc($rsa_result)) {

    ?>

    <table class="table">
    <tr><td>Synonym: </td><td><? echo $rowa[synonym]; ?></td></tr>
    <tr><td>Name: </td><td><? echo $rowa[name]; ?></td></tr>
    </table>

    <?  } ?>

<?  } ?>

Then I would like to remember you that mysql_ functions are deprecated so i would advise you to switch to mysqli or PDO and indeed you are at risk of sql injection, have a look here How can I prevent SQL injection in PHP?. You should use prepared statment to avoid any risk

Of course, I had a bracket that was closing the first loop. Many thanks for your help. Also thanks for the suggestion on sql injection. — Ken, May 19 '13 at 00:39
@Ken you welcome dude, don't forget to accept answer to help people in the future with similar problems — Fabio, May 19 '13 at 01:17

php pass value to multiple select queries

1 Answers1