Selecting certain Details from a table

[dwighty]

Hi guys,

Something i have been working with last night and not seeming to get how i want it.

I have a db table formatted as below
ID,Week_id,Home_id,Home_result,Away_result,Away_id

In the Week_id i would put the actual week value == 1,2 etc.

What i want is a drop down box which details all the week's e.g. 9,8,7,6,5,4,3,2,1 then the user can select a week and the relevant results appear in a table below/on another page.

What i am getting at the moment is that it details ALL the Week_id e.g. 9,9,9,9,9,9,9,9,8,8,8,8,8,8,8,7,7,7,7,7, and so on.

Have i set-up my table wrong? If so would a second table be needed?

I would image there is a query that would be able to get all the Week_id's from the table but only show the number once and when the user selects say week_id==8 then it displays all of the results for week 8.

Sorry i can't post my code at the moment do to not having it no my current machine at work.

Thanks in advance

Dwighty

[jatar_k]

select distinct Week_id from tablename

[dwighty]

jatar_k,

Thanks for the reply. Will try this.

Dwighty

[dwighty]

Hey guys,

I have another question, not really to do with obtaining certain details from the db but in ordering the information.

Here is my code (some of it):

<?php

// Set Server access variables
$host ="localhost";
$user ="test";
$pass ="test";
$db ="testdb";

// Open Connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to Connect!");

// Select the Database Needed
mysql_select_db($db) or die ("Unable to Connect to the Database");

// Create a Query
$query ="SELECT * FROM pfl_league_table ORDER BY Points , GD DESC LIMIT 0,20";

// Execute the Query
$result = mysql_query($query) or die ("Error in Query: $query. ".mysql_error());

// See if any rows were returned
if (mysql_num_rows($result) > 0) {
// If the statement is YES then Print them 1 after another
echo "<table cellpadding=10 border=1>";
while(list($Team_id, $Pld, $W, $D, $L, $F, $A $GD, $Pts) = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>$Team_id</td>";
echo "<td>$Pld</td>";
echo "<td>$W</td>";
echo "<td>$D</td>";
echo "<td>$L</td>";
echo "<td>$F</td>";
echo "<td>$A</td>";
echo "<td>$GD</td>";
echo "<td>$Pts</td>";
echo "</tr>
}
echo "</table>
}
else {
// If the statement is NO the Print Error Message
echo "No results found!";
}

// Free result set memory
mysql_free_result($result);

// Close the connection to the database
mysql_close($connection);

?>

What i want to happen is for it to first order by the Points then if a team has the same points it goes on GD (goal difference) etc. Is this even possible? I thought that by adding , GD after points it would do it in the order of points then GD but doesnt work.

Your help would be appreciated.

Dwighty

[mykel79]

You correctly added

ORDER BY points, GD

It should work and order things just the way you want them - first by points, then by GD.

What results are you getting? Are the fields in random order? Is the GD a numeric field?

[dwighty]

mykel79,

Thats what i thought, however...

If if place ORDER BY Points .... (no GD Order)
->
It will order the results by Points Desc like i want, but if 2 or more teams have the same points instead of reading the ORDER BY GD (which is numeric) it orders by Team_id!

If if place ORDER BY Points, GD ....
->
It will only order by GD NOT Points first then GD is Points are level.

Not sure why.

Any help would be appreciated.

[dwighty]

Just a thought,

Do I need do the following? (Not on home comp at the moment so can't test it!)

...ORDER BY Points DESC, GD DESC ......

Or will 2 different queries have to be made.

Thanks