Creating matrix summary report

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!


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:


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:


// 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>";


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.



Site Timeline