MySQL forespørgelse med PHP

$query = "SELECT * FROM league_teamdata WHERE league='RET_DETTE' ORDER BY team_name ASC";

Har rent faktisk været inde over noget i den retning, men kan ikke finde ud af hvad jeg skal skrive.

Sæsonnavnet bliver defineret et andet sted i systemet, og det bliver puttet ind i "league" når der oprettes et nyt hold, her vælger man også liga/sæson.

Som jeg ser det, skal jeg have trukket holdene ud der hvor "league" er den nyeste, men det kan jeg måske slet ikke kun med denne forespørgelse?

Sæson/liganavn bliver som sagt oprettet i en anden tabel kaldet "league_no_of" hvori der kun er ID og NAVN i.

Kan man måske kombinere forspørgelsen således at den først kigger på league_no_of finder det højeste ID (altså det seneste) og herefter trækker holdene fra league_teamdata?

Ved bare ikke hvordan man laver en sådan forespørgelse.

Ja, det kan du godt :) det er der ingen problem i.

Først kør:
SELECT id FROM league_no_of ORDER BY id DESC LIMIT 0,1

og så bruge den id til:
SELECT * FROM league_teamdata WHERE league='id_fra_anden_query' ORDER BY team_name ASC

Har forsøgt mig her, men synes ikke jeg kan få det til at virke, Der er ingen data i dropdown menuen overhovede :(


KODE:

//Hold 1 START
echo "<tr>\n";
echo "<td width='15%'>Hold 1</td>\n";
echo "<td width='85%'>Runder vundet</td>\n";
echo "</tr>\n";

echo "<tr>\n";
echo "<td width='15%'>";

include "config.php";
$link = mysql_connect($dbhost, $dbuser, $dbpasswd) or die("Couldn't establish connection");
mysql_select_db($dbname);
$query = "SELECT id FROM league_no_of ORDER BY id DESC LIMIT 0,1";
$result = mysql_query($query);

$query_list = "SELECT * FROM league_teamdata WHERE league = $result ORDER BY team_name ASC";
$result_list = mysql_query($query_list);   
    echo "<select name='team_1' size='1'>";
      echo "<option></option>";
while($row = mysql_fetch_array($result_list))
      {
      echo "<option>$row[team_name]</option>";
      }
      echo "</select>";

echo "</td>\n";

echo "<td width='85%'><input name='result_1' type='post' value=''</td>\n";
echo "</tr>\n";
//Hold 1 SLUT

$result = mysql_result(mysql_query($query));

burde kunne fixe dit problem :)

Der er ingen grund til at lave to mysql forespoergsler.  Du vil have alt fra league_teamdata hvor league er den league der har det hoejeste id i league_no_of, ikke sandt?

Det stoerste id finder du ved at spoerge "SELECT MAX(id) FROM league_no_of"

Den league der svarer til det hoejeste id finder du saaledes:
"SELECT league FROM league_no_of WHERE id = (SELECT MAX(id) FROM league_no_of)"

Saa de data i league_teamdata hvor league er den der har det hoejeste id finder du saaledes:

SELECT * FROM league_teamdata WHERE league = (SELECT league FROM league_no_of WHERE id = (SELECT MAX(id) FROM league_no_of));

Hvis du vil have det ordnet og sorteret som du siger overfor bliver det til

$query = "SELECT * FROM league_teamdata WHERE league = (SELECT league FROM league_no_of WHERE id = (SELECT MAX(id) FROM league_no_of)) ORDER BY team_name ASC"

Jeg proevede det af ved at lave en league_teamdata og en league_no_of tabel som foelger og jeg fik dette resultat:

id  team_name  league  matches  won  lost  p_score  n_score  points 
      5 team5 league3 5 10 15 20 25 30
      6 team6 league3 6 12 18 24 30 36
      7 team7 league3 7 14 21 28 35 42
      8 team8 league3 8 16 24 32 40 48

Her er mine tabeller:

INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team1', 'league1', 1, 2, 3, 4, 5, 6);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team2', 'league1', 2, 4, 6, 8, 10, 12);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team3', 'league2', 3, 6, 9, 12, 15, 18);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team4', 'league2', 4, 8, 12, 16, 20, 24);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team5', 'league3', 5, 10, 15, 20, 25, 30);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team6', 'league3', 6, 12, 18, 24, 30, 36);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team7', 'league3', 7, 14, 21, 28, 35, 42);
INSERT INTO league_teamdata(team_name, league, matches, won, lost, p_score, n_score, points) VALUES ('team8', 'league3', 8, 16, 24, 32, 40, 48);

CREATE TABLE league_no_of(id INT, league text);
INSERT INTO league_no_of VALUES(1,'league1');
INSERT INTO league_no_of VALUES(2,'league2');
INSERT INTO league_no_of VALUES(3,'league3');

Mrvaux, saa du mit indlaeg?  Hvad er dine kommentarer?  (Jeg opretter dette som et svar idet jeg haaber at jeg har bidraget til at besvare dit spoergsmaal.  Hvis jeg har misforstaaet problemet/behoved saa forklar.)

Havde skrevet en hel roman og trykkede accepter svar hvorefter det hele forsvandt.

Så nøjes med at sige tusinde tak til Christian for at vise mig en måde jeg ikke var klar over.

Ligeledes tak til drlotto for hjælpen.

Problem løst :o)

KODE:
//Hold 1 START
echo "<tr>\n";
echo "<td width='40%'>Hold 1</td>\n";
echo "<td width='60%'>Runder vundet</td>\n";
echo "</tr>\n";

echo "<tr>\n";
echo "<td width='40%'>";

include "config.php";
$link = mysql_connect($dbhost, $dbuser, $dbpasswd) or die("Couldn't establish connection");
mysql_select_db($dbname);
$query = "SELECT * FROM league_teamdata WHERE league = (SELECT leagues FROM league_no_of WHERE id = (SELECT MAX(id) FROM league_no_of)) ORDER BY team_name ASC";
$result = mysql_query($query);
   
    echo "<select name='team_1' size='1'>";
      echo "<option></option>";
while($row = mysql_fetch_array($result))
      {
      echo "<option>$row[team_name]</option>";
      }
      echo "</select>";

echo "</td>\n";

echo "<td width='60%'><input name='result_1' type='post' value=''</td>\n";
echo "</tr>\n";
//Hold 1 SLUT