Hello
I am new to php & MySQL - I am trying to retrieve some records from a MySQL
table and redisplay them. The data in list form looks like this:
Sample_ID Marker_ID Variation
G23_NA17192.fsa rs7374540 A/C
I23_Control.fsa rs7374540 C/C
C03_NA17110.fsa rs7428779 C/C
E21_NA17183.fsa rs6788899 G/G
K15_NA17162.fsa rs6599223 C/C
M15_NA17163.fsa rs6599223 C/C
M15_NA17163.fsa rs312451 A/C
I'd like to redisplay them as such:
Marker_ID
Sample_ID rs7374540 rs7428779 rs6788899 rs6599223 rs31245
G23_NA17192.fsa A/C
I23_Control.fsa C/C
C03_NA17110.fsa C/C
E21_NA17183.fsa G/G
K15_NA17162.fsa C/C
M15_NA17163.fsa C/C A/C
The code passes the parser but repeats thru the entire loop 7 times (once
for each line of the results). So I must be not placing the while loop in
the correct block.
Also, all the Marker_IDs are displayed for each Sample_ID, not just the
Marker_IDs specific to each Sample_ID.
I'll work on the formatting of the display once I know the appropriate
results are returned. Thanks much. Code and Results follow.
Charles
-- Code ---
<?php
// DB specific info
DEFINE (DB_USER, "mysql");
DEFINE (DB_PASSWORD, "vil43nrt");
DEFINE (DB_HOST, "localhost");
DEFINE (DB_NAME, "snplexdb_prototype");
//Connect to MySQL
$db_connect = (mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
//Select DB
mysql_select_db (DB_NAME);
//get the full results from the MySQL table
$db_query = "SELECT sample_id,snpidxref,var FROM sampleresults GROUP BY
sample_id, snpidxref ORDER BY sample_id, snpidxref";
$db_result = mysql_query($db_query, $db_connect);
$no_of_rows = mysql_num_rows($db_result);
echo $no_of_rows;
echo "</br>";
echo "Now on to next block";
echo "<p>";
//work on this part!!
while ($results_array = mysql_fetch_array($db_result)) {
echo "Start Outer Loop";
echo "</br>";
echo "Loop In";
//logic for each sample_id
//get unique sample ids
$sample_query = "SELECT DISTINCT sample_id FROM sampleresults ORDER
BY sample_id";
$sample_ids = mysql_query($sample_query, $db_connect);
while ($sample_array = mysql_fetch_array($sample_ids)) {
echo "</br>";
echo "Start Inner Loop";
echo "</br>";
echo "now we evaluate iter";
echo "</br>";
if ($results_array[sample_id] == $sample_array[sample_id]);
echo "</br>";
echo $sample_array[sample_id];
echo "</br>";
$snp_query = "SELECT DISTINCT snpidxref FROM sampleresults ORDER BY
snpidxref";
$snp_ids = mysql_query($snp_query, $db_connect);
while ($snp_array = mysql_fetch_array($snp_ids)) {
if ($results_array[snpidxref] == $snp_array[snpidxref]);
echo "</br>";
echo $snp_array[snpidxref];
echo "</br>";
}
echo "End Inner Loop";
echo "</br>";
}
//}
echo "Loop Out";
echo "</br>";
echo "</br>";
//}
//}
echo "End Outer Loop";
}
mysql_close($db_connect);
?>
Results are now displayed as such:
7
Now on to next block
Start Outer Loop
Loop In
Start Inner Loop
now we evaluate iter
C03_NA17110.fsa
rs312451
rs6599223
rs6788899
rs7374540
rs7428779
End Inner Loop
Start Inner Loop
now we evaluate iter
E21_NA17183.fsa
rs312451
rs6599223
rs6788899
rs7374540
rs7428779
End Inner Loop
Start Inner Loop
now we evaluate iter
G23_NA17192.fsa
rs312451
rs6599223
rs6788899
rs7374540
rs7428779
End Inner Loop
Start Inner Loop
now we evaluate iter
I23_Control.fsa
rs312451
rs6599223
rs6788899
rs7374540
rs7428779
End Inner Loop
Start Inner Loop
now we evaluate iter
K15_NA17162.fsa
rs312451
rs6599223
rs6788899
rs7374540
rs7428779
End Inner Loop
Start Inner Loop
now we evaluate iter
M15_NA17163.fsa
rs312451
rs6599223
rs6788899
rs7374540
rs7428779
End Inner Loop
Loop Out
(Except the above loop repeats 6 more times and the rs#s are for the entire
set and not just for the specific .fsa Ids).