Two Sort Method?

[jason97673]

So I ran into a problem with a site I am making that should be a bit tough but

probably not too massive.

 

Basically take a look at

http://sykotic-designz.org/roethlisberger/info/comparisons1.php. I originally

wanted to sort the database results by there column name by clicking on

them. On that page you see Quarterback, Games Played, Completions,

Attempts, yards, touchdowns, and interceptions. Those all have a

respective field in the database table. However, rating, comp%, YPA, INT%

and TD% are simply numbers computed using the data in the database.

 

But I found a script that looks like it coculd work my way but having problems, I moodified it to work mostly my way, but

the way it is set up is based on having a field in the database for all

those columns. I guess I prob need two sort methods in the page.

 

The code for this page I put in a function to organize it.

 

function comparisons(){

$page_name="comparisons1.php"; //  If you use this code with a different
page ( or file ) name then change this

@$column_name=$_GET['column_name']; // Read the column name from query
string.

$start=$_GET['start'];                                                    
           // To take care global variable if OFF
if(!($start > 0)) {   // This variable is set to zero for the first page
$start = 0;
}

$eu = ($start - 0);
$limit = 10;            // No of records to be shown per page.
$this1 = $eu + $limit;
$back = $eu - $limit;
$next = $eu + $limit;

/////////////// WE have to find out the number of records in our table. We
will use this to break the pages///////
$query2=" SELECT * FROM overall_stats  ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);
/////// The variable nume above will store the total number of records in
the table////

/////////// Now let us print the table headers ////////////////
//Area of High Concern is mainly below here as these are the links that will be clicked on too be sorted//
echo '<div id="main" class="text">';

echo '<table id=\"comparisons\">';
echo         "<tr>";
echo         "<td><a
href='$page_name?column_name=lname'>Quarterback</a></td>";
echo          "<td><a href='$page_name?column_name=games'>Games
Played</a></td>";
echo         "<td><a
href='$page_name?column_name=completions'>Completions</a></td>";
echo         "<td><a
href='$page_name?column_name=attempts'>Attempts</a></td>";
echo         "<td><a
href='$page_name?column_name=completion_percent'>Comp%</a></td>";
echo         "<td><a href='$page_name?column_name=yards'>Yards</a></td>";
echo         "<td><a href='$page_name?column_name=ypa'>YPA</a></td>";
echo         "<td><a
href='$page_name?column_name=touchdowns'>Touchdowns</a></td>";
echo         "<td><a href='$page_name?column_name=td_percent'>TD%</a></td>";
echo         "<td><a
href='$page_name?column_name=interceptions'>Interceptions</a></td>";
echo         "<td><a
href='$page_name?column_name=int_percent'>INT%</a></td>";
echo         "<td><a href='$page_name?column_name=$rating'>Rating</a></td>";
echo        "</tr>";

////////////// Now let us start executing the query with variables $eu and
$limit  set at the top of the page///////////
$query=" SELECT * FROM overall_stats  ";

if(isset($column_name) and strlen($column_name)>0){
$query = $query . " order by $column_name";
}
$query = $query. " limit $eu, $limit ";
$data=mysql_query($query);
echo mysql_error();

//////////////// Now we will display the returned records in side the rows
of the table/////////
while($info = mysql_fetch_array($data))
{
$completions = $info['completions'];
$attempts = $info['attempts'];
$completion_percent = round($completions / $attempts, 2) * 100;
$yards = $info['yards'];
$ypa = $yards / $attempts;
$touchdowns = $info['touchdowns'];
$interceptions = $info['interceptions'];
$td_percent = ($touchdowns / $attempts) * 100;
$int_percent = ($interceptions / $attempts) * 100;
//=((E18-30) * 0.05) + ((G18 - 3) * 0.25) + (J18 * 0.2) + (L18 *.25) /6) *
100
//QB Rating Formula
$a = ($completion_percent - 30) * 0.05;
$b = ($ypa - 3) * 0.25;
$c = $td_percent * 0.2;
$d = 2.375 - ($int_percent * 0.25);
$rating = (($a + $b + $c + $d) / 6) * 100;


echo '<tr>
                 <td>' .$info['fname'] .' ' .$info['lname'] .'</td>
                 <td>' .$info['games'] .'</td>
                 <td>' .$info['completions'] .'</td>
                <td>' .$info['attempts'] .'</td>
                <td>' .$completion_percent .'%</td>
                <td>' .$info['yards'] .'</td>
                <td>' .round($ypa, 2) .'</td>
                <td>' .$info['touchdowns'] .'</td>
                <td>' .round($td_percent, 2) .'</td>
                <td>' .$info['interceptions'] .'</td>
                <td>' .round($int_percent, 2) .'</td>
                <td>' .round($rating, 2) .'</td>
         </tr>';
}
echo "</table>";
////////////////////////////// End of displaying the table with records
////////////////////////

/////////////// Start the buttom links with Prev and next link with page
numbers /////////////////
//echo "<table align = 'center' width='50%'><tr><td  align='left'
width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display
the link to move back ////////
if($back >=0) {
print "<a href='$page_name?start=$back&column_name=$column_name'><font
face='Verdana' size='2'>PREV</font></a>";
}
//////////////// Let us display the page links at  center. We will not
display the current page as a link ///////////
echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i&column_name=$column_name'><font
face='Verdana' size='2'>$l</font></a> ";
}
else { echo "<font face='Verdana' size='4' color=red>$l</font>";}
/// Current page is not displayed as link and given font color red
$l=$l+1;
}


//echo "</td><td  align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be
displayed. Here we check that /////
if($this1 < $nume) {
print "<a href='$page_name?start=$next&column_name=$column_name'><font
face='Verdana' size='2'>NEXT</font></a>";}
//echo "</td></tr></table>";
}
{/code]

That should be it. I pretty much just need two sort methods. The database
sorting works but I need to sort the columns with the values that were
computed like rating, TD% etc.

Thanks for any help!

[trq]

Sorry, you've lost me. Whats the question?

[jason97673]

Sorry, you've lost me. Whats the question?

 

A script I found sorts columns by data in the database. However some of these fields such as rating are simple math computations. I am unsure how I can create a clickable link to sort by the fields in the database(attempts, completions etc) AND create a link to sort by values that do not have fields in the database. they are mainly variables that holds numbers.

 

Make any sense? its kind of hard to explain better.

[teng84]

you have to use get var .eg...     

index.php?x=asc&field=names

 

now you will query it like

$field =$_GET[field];

$val = $_GET[x];

select * from table order by $field $val

that is the basic

[jason97673]

you have to use get var .eg...

index.php?x=asc&field=names

 

now you will query it like

$field =$_GET[field];

$val = $_GET[x];

select * from table order by $field $val

that is the basic

 

Well see the variables can only be created in the loop that I have. If I put the variavle where the x is, that will take place above the loop as in the SQL statement. The problem imaginie lies within

echo '<table id=\"comparisons\">';
echo         "<tr>";
echo         "<td><a
href='$page_name?column_name=lname'>Quarterback</a></td>";
echo          "<td><a href='$page_name?column_name=games'>Games
Played</a></td>";
echo         "<td><a
href='$page_name?column_name=completions'>Completions</a></td>";
echo         "<td><a
href='$page_name?column_name=attempts'>Attempts</a></td>";
echo         "<td><a
href='$page_name?column_name=completion_percent'>Comp%</a></td>";
echo         "<td><a href='$page_name?column_name=yards'>Yards</a></td>";
echo         "<td><a href='$page_name?column_name=ypa'>YPA</a></td>";
echo         "<td><a
href='$page_name?column_name=touchdowns'>Touchdowns</a></td>";
echo         "<td><a href='$page_name?column_name=td_percent'>TD%</a></td>";
echo         "<td><a
href='$page_name?column_name=interceptions'>Interceptions</a></td>";
echo         "<td><a
href='$page_name?column_name=int_percent'>INT%</a></td>";
echo         "<td><a href='$page_name?column_name=$rating'>Rating</a></td>";
echo        "</tr>";

 

These links are where the sorting is taking place. A link such as

echo         "<td><a
href='$page_name?column_name=lname'>Quarterback</a></td>";

works fine because my database has a lname field. However a link such as

echo         "<td><a href='$page_name?column_name=rating'>Rating</a></td>";

wont work because there is no rating field in the database(it is determined later on in the loop by a math formula).

 

[Ken2k7]

However, rating, comp%, YPA, INT%

and TD% are simply numbers computed using the data in the database.

An easy way would be to add those columns into the database tables and just calculate them before you insert it into the columns. Then you can just pull them out.

 

But as of now, you can store the values into a var and then calculate them and insert them into however you want to display the text.

[jason97673]

However, rating, comp%, YPA, INT%

and TD% are simply numbers computed using the data in the database.

An easy way would be to add those columns into the database tables and just calculate them before you insert it into the columns. Then you can just pull them out.

 

But as of now, you can store the values into a var and then calculate them and insert them into however you want to display the text.

 

Well lets see. I just thought of, I wonder once my loop starts if I can create an insert SQL statement so the values get computed then inserted automatically into the database. Im just trying to avoid having to compute every time I make an update to the database

[trq]

wont work because there is no rating field in the database(it is determined later on in the loop by a math formula).

 

There is part of your problem. Why calculate this stuff in php when mysql is quite capable of doing so within your queries?

[jason97673]

wont work because there is no rating field in the database(it is determined later on in the loop by a math formula).

 

There is part of your problem. Why calculate this stuff in php when mysql is quite capable of doing so within your queries?

 

Yeah my last post I mentioned that.

 

I believe this statement would work

"INSERT INTO overall_stats ($completion_percent, $td_percent)
VALUES (' .$completion_percent .', ' .$td_percent .')";

But how do I have this SQL statement executed automatically. I assume I put it in a variable then how do I execute that variable upon page load? But it needs to be after my loop.

[Ken2k7]

wont work because there is no rating field in the database(it is determined later on in the loop by a math formula).

 

There is part of your problem. Why calculate this stuff in php when mysql is quite capable of doing so within your queries?

 

Yeah my last post I mentioned that.

 

I believe this statement would work

"INSERT INTO overall_stats ($completion_percent, $td_percent)
VALUES (' .$completion_percent .', ' .$td_percent .')";

But how do I have this SQL statement executed automatically. I assume I put it in a variable then how do I execute that variable upon page load? But it needs to be after my loop.

 

thorpe made a good point on his previous post.

 

Note that you probably should add the columns completion and td into the table before you insert values into them. You can use:

mysql_query("ALTER TABLE table_name ADD COLUMN column_name") or die(mysql_error());

 

Then you can run:

mysql_query("INSERT INTO overall_stats ($completion_percent, $td_percent)
VALUES (' .$completion_percent .', ' .$td_percent .')") or die(mysql_error());

[trq]

That is not at all what I meant. You can do mathimatical equations within mysql queries. eg;

 

SELECT touchdowns, attempts, (touchdowns/attempts) * 100) as td_percent FROM overall_stats;

 

This would then simply enable you to sort your data. eg;

 

SELECT touchdowns, attempts, (touchdowns/attempts) * 100) as td_percent FROM overall_stats ORDER BY td_percent DESC;

 

As it stands, you have far too much logic happening within php that could easily be done within mysql.

[Ken2k7]

That is not at all what I meant. You can do mathimatical equations within mysql queries. eg;

 

SELECT touchdowns, attempts, ((touchdowns/attempts) * 100) as td_percent FROM overall_stats;

 

This would then simply enable you to sort your data. eg;

 

SELECT touchdowns, attempts, ((touchdowns/attempts) * 100) as td_percent FROM overall_stats ORDER BY td_percent DESC;

 

As it stands, you have far too much logic happening within php that could easily be done within mysql.

Parenthetical corrections.

[jason97673]

Thanks, ill give that a try tomorrow and reply or mark it solved