PHP & MySQL Crosstab Problem

[denovo]

Hello

I am new to php & MySQL - I am trying to retrieve some records from a MySQL
table and redisplay them in a kind of crosstab. 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:

                                           Unique Marker IDs

Unique 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

All the Marker_IDs are displayed for each Sample_ID, not just the
Marker_IDs specific to each Sample_ID. The same for the specific 'var' values. They also loop thru 7 times for each row. Need to know how to avoid this redundancy.

I'll work on the formatting of the display once I know the appropriate
results are returned. Thanks much. Code and Results follow.

Charles

//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!!

  //work on this part!!
echo "Start Outer Loop";
echo "</br>";

echo "Start Results Block"."</br>";

while ($results_array = mysql_fetch_array($db_result)) {
//echo "Loading Results Array"."</br>";
//echo ($results_array['sample_id'])."</br>";
//print_r ($results_array);

        echo "Start Sample_ID Block"."</br>";

     //get unique sample ids

        $sample_query = "SELECT DISTINCT sample_id FROM sampleresults
        ORDER BY sample_id";
        $sample_ids = mysql_query($sample_query, $db_connect);

        echo "Start Sample_ID Loop"."</br>";
        while ($sample_array = mysql_fetch_array($sample_ids)) {
        //echo "Loading Sample_IDs Array"."</br>";
        if ($sample_array['sample_id'] == $results_array['sample_id']);
        echo ($sample_array['sample_id'])."</br>";
        //echo ($results_array['snpidxref'])."</br>";
        echo "</br>";
        //print_r ($sample_array);

                echo "Start SNP_ID Block"."</br>";

                //get unique snp ids

                $snp_query = "SELECT DISTINCT snpidxref FROM
                sampleresults ORDER BY snpidxref";
                $snp_ids = mysql_query($snp_query, $db_connect);

                echo "Start SNP_ID Loop"."</br>";
                while ($snp_array = mysql_fetch_array($snp_ids)) {
                //echo "Loading SNP Array"."</br>";
                if ($sample_array['snpidxref'] ==
                    $results_array['snpidxref']);
                //echo ($results_array['sample_id'])."</br>";
                echo ($snp_array['snpidxref'])."</br>";
                echo ($results_array['var'])."</br>";
                //print_r ($snp_array);
                echo "</br>";
                }

                echo "End SNP_ID Loop"."</br>";

                echo "End SNP_ID Block"."</br>";
                echo "</br>";

        }
        echo "End Sample_ID Loop"."</br>";
        echo "End Sample_ID Block"."</br>";
        echo "</br>";            

[AstroTeg]

I'd recommend reading this:

http://www.devshed.com/c/a/MySQL/MySQL-wizardry

I had to read it at least twice. But it made a ton of sense and allowed me to put together a wicked crosstab report which not only duplicated an Excel cross tab my boss created, but also included some additional data my boss wasn't able to include due to Excel's limitation (and all of it on the fly).

To do a crosstab requires complete and total understanding of the data and SQL involved. It would be hard for me to just give you code and have it work (although you might find some libraries out there that will do this for you).

[denovo]

Hi AstroTeg

Thanks for the link to the article. I actually have this article and have read it. I'll read it again.

The only thing with it was that I was using PHP and the article uses Perl and that's fine. But I have a feeling there are some subtle differences in control structures.

I'm using PHP because all this stuff needs to be web accessible. Plus there are better functions IMHO for handling the kinds of stuff I want to do.

Still I appreciate the help.

Best,

Charles

[AstroTeg]

You'll have to understand the SQL that's going on. Once you understand what is happening, you'll have a really really easy time doing the PHP. In fact, skip over the Perl code. All the magic takes place in the SQL. Making PHP do this really isn't difficult as long as you take it 1 small piece at a time.