CODEWITHSUNDEEP
phpmysqlsorting
William
William

Reputation: 1

Sorting table data from different queries in PHP/MySql

I am really confused as I am a (poorly) self-taught amateur. I am building a statistics site for a league and I managed to create a table that takes data from several tables in a database and collates it together. The current "columns" in the table are Driver Name, total number of races, total wins and podium placements. Driver Name is the only "straightforward" set of data I have - all the rest is obtained through calculations and, as such, requires the initial data regarding the names.

At the moment, I have the table showing the correct data in drivers' alphabetical order. How can I do it so that it can be sorted (even in a completely different page) by the other three fields?

The current table can be seen here: http://www.gpvwc.com/en/services/database/driver/

Here is the code I wrote to have that appear:

$data90=mysql_query("SELECT DISTINCT competition FROM DCO_results ORDER BY competition ASC", $CONNECTW);
        while($row=mysql_fetch_row($data90)) {

        $competitionnum="$row[0]";

        $data80=mysql_query("SELECT competition FROM DCO_competitions WHERE id='$competitionnum'", $CONNECTW);
        while($row=mysql_fetch_row($data80)) {

        $compstring="$row[0]";  

            echo "<span class='footer'><a href='#$compstring'>$compstring</a> | </span>";
        }}


echo "<p>&nbsp;</p>";

$data70=mysql_query("SELECT DISTINCT competition FROM DCO_results ORDER BY competition ASC", $CONNECTW);
        while($row=mysql_fetch_row($data70)) {

        $compnum3="$row[0]";


$data60=mysql_query("SELECT competition FROM DCO_competitions WHERE id='$compnum3'", $CONNECTW);
        while($row=mysql_fetch_row($data60)) {

        $compstring3="$row[0]";         

echo "<a name='$compstring3' id='$compstring3'></a><h2>$compstring3</h2>";          

if ($compnum3=="1") {

$sql = '(SELECT DISTINCT driver1 FROM DCO_results WHERE competition="1"  AND driver1 IS NOT NULL) UNION (SELECT DISTINCT driver2 FROM DCO_results WHERE competition="1"  AND driver2 IS NOT NULL) UNION (SELECT DISTINCT driver3 FROM DCO_results WHERE competition="1"  AND driver3 IS NOT NULL) UNION (SELECT DISTINCT driver4 FROM DCO_results WHERE competition="1"  AND driver4 IS NOT NULL) UNION (SELECT DISTINCT driver5 FROM DCO_results WHERE competition="1"  AND driver5 IS NOT NULL) UNION (SELECT DISTINCT driver6 FROM DCO_results WHERE competition="1"  AND driver6 IS NOT NULL) UNION (SELECT DISTINCT driver7 FROM DCO_results WHERE competition="1"  AND driver7 IS NOT NULL) UNION (SELECT DISTINCT driver8 FROM DCO_results WHERE competition="1"  AND driver8 IS NOT NULL) UNION (SELECT DISTINCT driver9 FROM DCO_results WHERE competition="1"  AND driver9 IS NOT NULL) UNION (SELECT DISTINCT driver10 FROM DCO_results WHERE competition="1"  AND driver10 IS NOT NULL) UNION (SELECT DISTINCT driver11 FROM DCO_results WHERE competition="1"  AND driver11 IS NOT NULL) UNION (SELECT DISTINCT driver12 FROM DCO_results WHERE competition="1"  AND driver12 IS NOT NULL) UNION (SELECT DISTINCT driver13 FROM DCO_results WHERE competition="1"  AND driver13 IS NOT NULL) UNION (SELECT DISTINCT driver14 FROM DCO_results WHERE competition="1"  AND driver14 IS NOT NULL) UNION (SELECT DISTINCT driver15 FROM DCO_results WHERE competition="1"  AND driver15 IS NOT NULL) UNION (SELECT DISTINCT driver16 FROM DCO_results WHERE competition="1"  AND driver16 IS NOT NULL) UNION (SELECT DISTINCT driver17 FROM DCO_results WHERE competition="1"  AND driver17 IS NOT NULL) UNION (SELECT DISTINCT driver18 FROM DCO_results WHERE competition="1"  AND driver18 IS NOT NULL) UNION (SELECT DISTINCT driver19 FROM DCO_results WHERE competition="1"  AND driver19 IS NOT NULL) UNION (SELECT DISTINCT driver20 FROM DCO_results WHERE competition="1"  AND driver20 IS NOT NULL) UNION (SELECT DISTINCT driver21 FROM DCO_results WHERE competition="1"  AND driver21 IS NOT NULL) UNION (SELECT DISTINCT driver22 FROM DCO_results WHERE competition="1"  AND driver22 IS NOT NULL) UNION (SELECT DISTINCT driver23 FROM DCO_results WHERE competition="1"  AND driver23 IS NOT NULL) UNION (SELECT DISTINCT driver24 FROM DCO_results WHERE competition="1"  AND driver24 IS NOT NULL) UNION (SELECT DISTINCT driver25 FROM DCO_results WHERE competition="1"  AND driver25 IS NOT NULL) UNION (SELECT DISTINCT driver26 FROM DCO_results WHERE competition="1"  AND driver26 IS NOT NULL) UNION (SELECT DISTINCT driver27 FROM DCO_results WHERE competition="1"  AND driver27 IS NOT NULL) UNION (SELECT DISTINCT driver28 FROM DCO_results WHERE competition="1"  AND driver28 IS NOT NULL) UNION (SELECT DISTINCT driver29 FROM DCO_results WHERE competition="1"  AND driver29 IS NOT NULL) UNION (SELECT DISTINCT driver30 FROM DCO_results WHERE competition="1"  AND driver30 IS NOT NULL) UNION (SELECT DISTINCT driver31 FROM DCO_results WHERE competition="1"  AND driver31 IS NOT NULL) UNION (SELECT DISTINCT driver32 FROM DCO_results WHERE competition="1"  AND driver32 IS NOT NULL) UNION (SELECT DISTINCT driver33 FROM DCO_results WHERE competition="1"  AND driver33 IS NOT NULL) UNION (SELECT DISTINCT driver34 FROM DCO_results WHERE competition="1"  AND driver34 IS NOT NULL) UNION (SELECT DISTINCT driver35 FROM DCO_results WHERE competition="1"  AND driver35 IS NOT NULL) UNION (SELECT DISTINCT driver36 FROM DCO_results WHERE competition="1"  AND driver36 IS NOT NULL) UNION (SELECT DISTINCT driver37 FROM DCO_results WHERE competition="1"  AND driver37 IS NOT NULL) UNION (SELECT DISTINCT driver38 FROM DCO_results WHERE competition="1"  AND driver38 IS NOT NULL) UNION (SELECT DISTINCT driver39 FROM DCO_results WHERE competition="1"  AND driver39 IS NOT NULL) UNION (SELECT DISTINCT driver40 FROM DCO_results WHERE competition="1"  AND driver40 IS NOT NULL) UNION (SELECT DISTINCT driver41 FROM DCO_results WHERE competition="1"  AND driver41 IS NOT NULL) UNION (SELECT DISTINCT driver42 FROM DCO_results WHERE competition="1"  AND driver42 IS NOT NULL) UNION (SELECT DISTINCT driver43 FROM DCO_results WHERE competition="1"  AND driver43 IS NOT NULL) UNION (SELECT DISTINCT driver44 FROM DCO_results WHERE competition="1"  AND driver44 IS NOT NULL) UNION (SELECT DISTINCT driver45 FROM DCO_results WHERE competition="1"  AND driver45 IS NOT NULL) UNION (SELECT DISTINCT driver46 FROM DCO_results WHERE competition="1"  AND driver46 IS NOT NULL) UNION (SELECT DISTINCT driver47 FROM DCO_results WHERE competition="1"  AND driver47 IS NOT NULL) UNION (SELECT DISTINCT driver48 FROM DCO_results WHERE competition="1"  AND driver48 IS NOT NULL) ORDER BY driver1 ASC';  

$result = mysql_query($sql); 

$totaldrivers=mysql_num_rows($result);

echo "<p>&nbsp;</p>";
echo "<p>A total of $totaldrivers drivers took part in the GPVWC $compstring3.</p>";

echo "<table width='100% border='0' cellspacing='0' cellpadding='0' align='left'>";

    echo "<tr>

                <th width='250' class='bg_th'><span class='footer'>Driver</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'><a href='$RKP/en/services/database/driver/sort/?target=Races'>Races</a></span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'><a href='$RKP/en/services/database/driver/sort/?target=Wins'>Wins</a></span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'><a href='$RKP/en/services/database/driver/sort/?target=Podiums'>Podiums</a></span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Points</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Pts Finish</span></th>
            </tr>";

while($get_info = mysql_fetch_row($result)){

$driversnames="$get_info[0]";

$data55=mysql_query("SELECT id FROM DCO_results WHERE competition='$compnum3' AND session='R' AND (driver1='$driversnames' OR driver2='$driversnames' OR driver3='$driversnames' OR driver4='$driversnames' OR driver5='$driversnames' OR driver6='$driversnames' OR driver7='$driversnames' OR driver8='$driversnames' OR driver9='$driversnames' OR driver10='$driversnames' OR driver11='$driversnames' OR driver12='$driversnames' OR driver13='$driversnames' OR driver14='$driversnames' OR driver15='$driversnames' OR driver16='$driversnames' OR driver17='$driversnames' OR driver18='$driversnames' OR driver19='$driversnames'OR driver20='$driversnames' OR driver21='$driversnames' OR driver22='$driversnames' OR driver23='$driversnames' OR driver24='$driversnames' OR driver25='$driversnames' OR driver26='$driversnames' OR driver27='$driversnames' OR driver28='$driversnames' OR driver29='$driversnames' OR driver30='$driversnames' OR driver31='$driversnames' OR driver32='$driversnames' OR driver33='$driversnames' OR driver34='$driversnames' OR driver35='$driversnames' OR driver36='$driversnames' OR driver37='$driversnames' OR driver38='$driversnames' OR driver39='$driversnames' OR driver40='$driversnames' OR driver41='$driversnames' OR driver42='$driversnames' OR driver43='$driversnames' OR driver44='$driversnames' OR driver45='$driversnames' OR driver46='$driversnames' OR driver47='$driversnames' OR driver48='$driversnames') ORDER BY id DESC", $CONNECTW);
$totalraceslist=mysql_num_rows($data55);

$data66=mysql_query("SELECT id FROM DCO_results WHERE competition='$compnum3' AND session='R' AND (driver1='$driversnames') ORDER BY id DESC", $CONNECTW);
$totalwinslist=mysql_num_rows($data66);

$data77=mysql_query("SELECT id FROM DCO_results WHERE competition='$compnum3' AND session='R' AND (driver1='$driversnames' OR driver2='$driversnames' OR driver3='$driversnames') ORDER BY id DESC", $CONNECTW);
$totalpodiumslist=mysql_num_rows($data77);


    echo "<tr>

                <td width='250' class='bg_th'><span class='footer'><a href='$RKP/en/services/database/driver/?target=$driversnames'>$driversnames</a></span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>$totalraceslist</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>$totalwinslist</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>$totalpodiumslist</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>N/A</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>N/A</span></td>                    
            </tr>"; }}

if ($compnum3=="2") {

$sql = '(SELECT DISTINCT driver1 FROM DCO_results WHERE competition="2"  AND driver1 IS NOT NULL) UNION (SELECT DISTINCT driver2 FROM DCO_results WHERE competition="2"  AND driver2 IS NOT NULL) UNION (SELECT DISTINCT driver3 FROM DCO_results WHERE competition="2"  AND driver3 IS NOT NULL) UNION (SELECT DISTINCT driver4 FROM DCO_results WHERE competition="2"  AND driver4 IS NOT NULL) UNION (SELECT DISTINCT driver5 FROM DCO_results WHERE competition="2"  AND driver5 IS NOT NULL) UNION (SELECT DISTINCT driver6 FROM DCO_results WHERE competition="2"  AND driver6 IS NOT NULL) UNION (SELECT DISTINCT driver7 FROM DCO_results WHERE competition="2"  AND driver7 IS NOT NULL) UNION (SELECT DISTINCT driver8 FROM DCO_results WHERE competition="2"  AND driver8 IS NOT NULL) UNION (SELECT DISTINCT driver9 FROM DCO_results WHERE competition="2"  AND driver9 IS NOT NULL) UNION (SELECT DISTINCT driver10 FROM DCO_results WHERE competition="2"  AND driver10 IS NOT NULL) UNION (SELECT DISTINCT driver11 FROM DCO_results WHERE competition="2"  AND driver11 IS NOT NULL) UNION (SELECT DISTINCT driver12 FROM DCO_results WHERE competition="2"  AND driver12 IS NOT NULL) UNION (SELECT DISTINCT driver13 FROM DCO_results WHERE competition="2"  AND driver13 IS NOT NULL) UNION (SELECT DISTINCT driver14 FROM DCO_results WHERE competition="2"  AND driver14 IS NOT NULL) UNION (SELECT DISTINCT driver15 FROM DCO_results WHERE competition="2"  AND driver15 IS NOT NULL) UNION (SELECT DISTINCT driver16 FROM DCO_results WHERE competition="2"  AND driver16 IS NOT NULL) UNION (SELECT DISTINCT driver17 FROM DCO_results WHERE competition="2"  AND driver17 IS NOT NULL) UNION (SELECT DISTINCT driver18 FROM DCO_results WHERE competition="2"  AND driver18 IS NOT NULL) UNION (SELECT DISTINCT driver19 FROM DCO_results WHERE competition="2"  AND driver19 IS NOT NULL) UNION (SELECT DISTINCT driver20 FROM DCO_results WHERE competition="2"  AND driver20 IS NOT NULL) UNION (SELECT DISTINCT driver21 FROM DCO_results WHERE competition="2"  AND driver21 IS NOT NULL) UNION (SELECT DISTINCT driver22 FROM DCO_results WHERE competition="2"  AND driver22 IS NOT NULL) UNION (SELECT DISTINCT driver23 FROM DCO_results WHERE competition="2"  AND driver23 IS NOT NULL) UNION (SELECT DISTINCT driver24 FROM DCO_results WHERE competition="2"  AND driver24 IS NOT NULL) UNION (SELECT DISTINCT driver25 FROM DCO_results WHERE competition="2"  AND driver25 IS NOT NULL) UNION (SELECT DISTINCT driver26 FROM DCO_results WHERE competition="2"  AND driver26 IS NOT NULL) UNION (SELECT DISTINCT driver27 FROM DCO_results WHERE competition="2"  AND driver27 IS NOT NULL) UNION (SELECT DISTINCT driver28 FROM DCO_results WHERE competition="2"  AND driver28 IS NOT NULL) UNION (SELECT DISTINCT driver29 FROM DCO_results WHERE competition="2"  AND driver29 IS NOT NULL) UNION (SELECT DISTINCT driver30 FROM DCO_results WHERE competition="2"  AND driver30 IS NOT NULL) UNION (SELECT DISTINCT driver31 FROM DCO_results WHERE competition="2"  AND driver31 IS NOT NULL) UNION (SELECT DISTINCT driver32 FROM DCO_results WHERE competition="2"  AND driver32 IS NOT NULL) UNION (SELECT DISTINCT driver33 FROM DCO_results WHERE competition="2"  AND driver33 IS NOT NULL) UNION (SELECT DISTINCT driver34 FROM DCO_results WHERE competition="2"  AND driver34 IS NOT NULL) UNION (SELECT DISTINCT driver35 FROM DCO_results WHERE competition="2"  AND driver35 IS NOT NULL) UNION (SELECT DISTINCT driver36 FROM DCO_results WHERE competition="2"  AND driver36 IS NOT NULL) UNION (SELECT DISTINCT driver37 FROM DCO_results WHERE competition="2"  AND driver37 IS NOT NULL) UNION (SELECT DISTINCT driver38 FROM DCO_results WHERE competition="2"  AND driver38 IS NOT NULL) UNION (SELECT DISTINCT driver39 FROM DCO_results WHERE competition="2"  AND driver39 IS NOT NULL) UNION (SELECT DISTINCT driver40 FROM DCO_results WHERE competition="2"  AND driver40 IS NOT NULL) UNION (SELECT DISTINCT driver41 FROM DCO_results WHERE competition="2"  AND driver41 IS NOT NULL) UNION (SELECT DISTINCT driver42 FROM DCO_results WHERE competition="2"  AND driver42 IS NOT NULL) UNION (SELECT DISTINCT driver43 FROM DCO_results WHERE competition="2"  AND driver43 IS NOT NULL) UNION (SELECT DISTINCT driver44 FROM DCO_results WHERE competition="2"  AND driver44 IS NOT NULL) UNION (SELECT DISTINCT driver45 FROM DCO_results WHERE competition="2"  AND driver45 IS NOT NULL) UNION (SELECT DISTINCT driver46 FROM DCO_results WHERE competition="2"  AND driver46 IS NOT NULL) UNION (SELECT DISTINCT driver47 FROM DCO_results WHERE competition="2"  AND driver47 IS NOT NULL) UNION (SELECT DISTINCT driver48 FROM DCO_results WHERE competition="2"  AND driver48 IS NOT NULL) ORDER BY driver1 ASC';  

$result = mysql_query($sql); 

$totaldrivers=mysql_num_rows($result);

echo "<p>&nbsp;</p>";
echo "<p>A total of $totaldrivers drivers took part in the GPVWC $compstring3.</p>";

echo "<table width='100% border='0' cellspacing='0' cellpadding='0' align='left'>";

    echo "<tr>

                <th width='250' class='bg_th'><span class='footer'>Driver</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Races</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Wins</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Podiums</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Points</span></th>
                <th width='50' class='bg_th' align='center'><span class='footer'>Pts Finish</span></th>
            </tr>";

while($get_info = mysql_fetch_row($result)){

$driversnames="$get_info[0]";

$data55=mysql_query("SELECT id FROM DCO_results WHERE competition='$compnum3' AND session='R' AND (driver1='$driversnames' OR driver2='$driversnames' OR driver3='$driversnames' OR driver4='$driversnames' OR driver5='$driversnames' OR driver6='$driversnames' OR driver7='$driversnames' OR driver8='$driversnames' OR driver9='$driversnames' OR driver10='$driversnames' OR driver11='$driversnames' OR driver12='$driversnames' OR driver13='$driversnames' OR driver14='$driversnames' OR driver15='$driversnames' OR driver16='$driversnames' OR driver17='$driversnames' OR driver18='$driversnames' OR driver19='$driversnames'OR driver20='$driversnames' OR driver21='$driversnames' OR driver22='$driversnames' OR driver23='$driversnames' OR driver24='$driversnames' OR driver25='$driversnames' OR driver26='$driversnames' OR driver27='$driversnames' OR driver28='$driversnames' OR driver29='$driversnames' OR driver30='$driversnames' OR driver31='$driversnames' OR driver32='$driversnames' OR driver33='$driversnames' OR driver34='$driversnames' OR driver35='$driversnames' OR driver36='$driversnames' OR driver37='$driversnames' OR driver38='$driversnames' OR driver39='$driversnames' OR driver40='$driversnames' OR driver41='$driversnames' OR driver42='$driversnames' OR driver43='$driversnames' OR driver44='$driversnames' OR driver45='$driversnames' OR driver46='$driversnames' OR driver47='$driversnames' OR driver48='$driversnames') ORDER BY id DESC", $CONNECTW);
$totalraceslist=mysql_num_rows($data55);

$data66=mysql_query("SELECT id FROM DCO_results WHERE competition='$compnum3' AND session='R' AND (driver1='$driversnames') ORDER BY id DESC", $CONNECTW);
$totalwinslist=mysql_num_rows($data66);

$data77=mysql_query("SELECT id FROM DCO_results WHERE competition='$compnum3' AND session='R' AND (driver1='$driversnames' OR driver2='$driversnames' OR driver3='$driversnames') ORDER BY id DESC", $CONNECTW);
$totalpodiumslist=mysql_num_rows($data77);


    echo "<tr>

                <td width='250' class='bg_th'><span class='footer'><a href='$RKP/en/services/database/driver/?target=$driversnames'>$driversnames</a></span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>$totalraceslist</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>$totalwinslist</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>$totalpodiumslist</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>N/A</span></td>
                <td width='50' class='bg_th' align='center'><span class='footer'>N/A</span></td>                    
            </tr>";     }}

    echo "</table>"; 
    echo "<p>&nbsp;</p>";}




    echo "</table>"; 
    echo "<p>&nbsp;</p>";   }

I don't even know if that is comprehensible (I can say I am not exactly canonical as an editor) but I've been losing days (and my sleep) on this and I finally decided to ask for help.

Upvotes: 0

Views: 852

Answers (2)

Sabeen Malik
Sabeen Malik

Reputation: 10880

Welcome to complex world of web technologies :)

I cant give you the full solution really but i will try to point you in the right direction.Also I think it would be better if you showed us your DB schema. I am pretty sure there is some room for improvement there.

1 : You should never really be using the names of the drivers, you should have a table which contains the drivers name and you have an integer identifier field in it, which you will use as a foreign key in other tables. This massively speeds up the query process and makes life alot easier. Specially in cases where someone realizes that there is a typo in a drivers name and needs to update it?

2 : Look into JOINs, they are really helpful in doing the kind of stuff you are doing. By joining multiple tables, you would be able to easily sort by any column.

3 : Instead of using so many ORs use fieldname IN('a' , 'b' , 'c')

4 : You should really get a good IDE, it would make life easier for you and everyone related to your code, and that now includes us :)

Upvotes: 3

Chris McClellan
Chris McClellan

Reputation: 1105

It looks to me like you are doing a bunch of different queries that are fed from your first query (where you get the player name?)

You should read into joining tables. You can sort the data you want easier in a unified query. Apart - it is not sortable because it is basing everything off your first query.

here is a good primer http://www.tizag.com/mysqlTutorial/mysqljoins.php

Upvotes: 0

Related Questions