dotaz z MySQL do PHP kodu

bikerxnx

Zdravim

potrebujem vytvorit tabulky (standings) z tabulky (scores), do ktorej su ukladane jednotlive zapasy. Dotaz v MySQL je nasledovny:

select
Tim,
count(*) Z,
count(case when `Goals home` > `Goals away` AND `Overtime` = "" then 1 end) V,
count(case when `Goals home` > `Goals away` AND `Overtime` = "PP" then 1 end) VPP,
count(case when `Goals home` > `Goals away` AND `Overtime` = "SN" then 1 end) VSN,
count(case when `Goals away` > `Goals home` AND `Overtime` = "" then 1 end) P,
count(case when `Goals away` > `Goals home` AND `Overtime` = "PP" then 1 end) PPP,
count(case when `Goals away` > `Goals home` AND `Overtime` = "SN" then 1 end) PSN,
sum(`Goals home`) SG,
sum(`Goals away`) IG,
sum(`Goals home`) - sum(`Goals away`) R,
sum(
case when `Goals home` > `Goals away` AND `Overtime` = "" then 2 else 0 end
+ case when `Goals home` > `Goals away` AND `Overtime` = "PP" then 2 else 0 end
+ case when `Goals home` > `Goals away` AND `Overtime` = "SN" then 2 else 0 end
+ case when `Goals away` > `Goals home` AND `Overtime` = "PP" then 1 else 0 end
+ case when `Goals away` > `Goals home` AND `Overtime` = "SN" then 1 else 0 end
) B
from (
select `Home` Tim, `Goals home`, `Goals away`,`Overtime` from scores
union all
select `Away`, `Goals away`, `Goals home`,`Overtime` from scores
) a
group by Tim
order by B desc, R desc

..tento kod mi vygeneruje tabulku ako vystup dotazu, no potreboval by som tento kod dostat do PHP, a prostrednictvom cyklu ho vypisat do jednotlivych buniek tabulky.

Dufam ze ma chapete, trapim sa stym uz par dni, tak by som poprosil o vase rady. Vopred dakujem.

Kód: Vybrať všetko

$result = mysql_query("select
Tim,
count(*) Z,
count(case when `Goals home` > `Goals away` AND `Overtime` = "" then 1 end) V,
count(case when `Goals home` > `Goals away` AND `Overtime` = "PP" then 1 end) VPP,
count(case when `Goals home` > `Goals away` AND `Overtime` = "SN" then 1 end) VSN,
count(case when `Goals away` > `Goals home` AND `Overtime` = "" then 1 end) P,
count(case when `Goals away` > `Goals home` AND `Overtime` = "PP" then 1 end) PPP,
count(case when `Goals away` > `Goals home` AND `Overtime` = "SN" then 1 end) PSN,
sum(`Goals home`) SG,
sum(`Goals away`) IG,
sum(`Goals home`) - sum(`Goals away`) R,
sum(
case when `Goals home` > `Goals away` AND `Overtime` = "" then 2 else 0 end
+ case when `Goals home` > `Goals away` AND `Overtime` = "PP" then 2 else 0 end
+ case when `Goals home` > `Goals away` AND `Overtime` = "SN" then 2 else 0 end
+ case when `Goals away` > `Goals home` AND `Overtime` = "PP" then 1 else 0 end
+ case when `Goals away` > `Goals home` AND `Overtime` = "SN" then 1 else 0 end
) B
from (
select `Home` Tim, `Goals home`, `Goals away`,`Overtime` from scores
union all
select `Away`, `Goals away`, `Goals home`,`Overtime` from scores
) a
group by Tim
order by B desc, R desc");
if($result && mysql_num_rows($result)>0){
     echo '<table>';
     while($riadok = mysql_fetch_object($result)){
            echo '<tr>';
            echo '<td>' . $riadok->Z . '</td>';
            echo '</tr>';
     }
     echo '</table>';
}

pričom riadok echo '<td>' . $riadok->Z . '</td>'; si dáš pre každú tú bunku, a zmeníš Z na to V, VPP, VSN a ostatné. Mne sa to nechce vypisovať

bikerxnx

super, este som musel upravit "PP" na 'PP' a funguje, velka vdaka

dotaz z MySQL do PHP kodu

dotaz z MySQL do PHP kodu

Re: dotaz z MySQL do PHP kodu

Re: dotaz z MySQL do PHP kodu