| Charles Alexander 2004-09-08, 3:57 pm |
| Hello
I need some help redisplaying and formatting data from a MySQl DB. So far
using PHP, I have the (partial for this example) data list:
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
What I would like to do is create a matrix/summary like this:
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
Thus far my code looks like this:
<?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_connection = (mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
//Select DB
mysql_select_db (DB_NAME);
$sql_query1 = "SELECT sample_id,snpidxref,var FROM sampleresults";
$sql_result1 = mysql_query($sql_query1, $db_connection);
$no_of_rows = mysql_num_rows($sql_result1);
echo $no_of_rows;
echo "</br>";
while ($results_array = mysql_fetch_array($sql_result1)) {
echo "<tr><td>";
echo $results_array["sample_id"];
echo " ";
echo "</td><td>";
echo $results_array["snpidxref"];
echo " ";
echo "</td><td>";
echo $results_array["var"];
echo "</td></tr>\n";
echo "</br>";
}
echo "</table>";
mysql_close($db_connection);
?>
My guess is that I need to suck the above list into a different array but
I'm not sure how to progress past the following:
To get unique Ids
$id_query= "SELECT DISTINCT sample_id FROM sampleresults";
$id_result= mysql_query($id_query, $db_connection);
$snp_query= "SELECT DISTINCT snpidxref FROM sampleresults";
$snp_result = mysql_query($snp_query, $db_connection);
I then presume I have to run a WHILE or FOREACH loop but just as how to
construct it, I am at at total loss.
Thanks
Charles
--
|