user3255412 user3255412 - 3 months ago 6
SQL Question

MySql : Query returns only the first row from selection

I created this code but when i run it creates the table but only with the first row, the table i get the data has multiple rows, i need the complete data to be inserted. Some help here

<?php
include("conf.php");
$file_name = "testing2";
$table = "TEMP_".$file_name;
$table1 = "TEMP1_".$file_name;
$query = "CREATE TABLE IF NOT EXISTS $table1(Id INT AUTO_INCREMENT PRIMARY KEY)
CHARACTER SET utf8 COLLATE utf8_general_ci
SELECT Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome, GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway, AvarageGoalsScorredAway, AvarageGoalsAcceptedAway,
AvarageRedCardGotHome, AvarageRedCardGotAway, AvarageShotsOnTargedHome, AvarageShotsOnTargedAway,
AvarageGoalsScorredHome / avg(AvarageGoalsScorredHome) AS AttackingStrengthHome, AvarageGoalsAcceptedHome / avg(AvarageGoalsAcceptedHome) AS DefensiveStrengthHome,
AvarageGoalsScorredAway / avg(AvarageGoalsScorredAway) AS AttackingStrengthAway, AvarageGoalsAcceptedAway / avg(AvarageGoalsAcceptedAway) AS DefensiveStrengthAway
FROM $table
";
$data= mysqli_query($conn,$query) or die(mysqli_error($conn));
while($row = mysqli_fetch_assoc($data)){
foreach($row as $cname => $cvalue){
print "$cname: $cvalue\t";
}
print "\r\n";
}
?>

Answer

Your query has aggregation functions (avg()) with no GROUP BY. However, merely adding a GROUP BY doesn't seem like the right thing. I think you want something like this:

SELECT Team, GamesPlayedHome, GoalsScorredHome, GoalsAcceptedHome, 
       RedCardGotHome, AvarageGoalsScorredHome, AvarageGoalsAcceptedHome,
       GamesPlayedAway, GoalsScorredAway, GoalsAcceptedAway, RedCardGotAway,
       AvarageGoalsScorredAway, AvarageGoalsAcceptedAway, 
       AvarageRedCardGotHome, AvarageRedCardGotAway, AvarageShotsOnTargedHome, AvarageShotsOnTargedAway,
       AvarageGoalsScorredHome / tt.avg_AvarageGoalsScorredHome AS AttackingStrengthHome,
       AvarageGoalsAcceptedHome / tt.avg_AvarageGoalsAcceptedHome AS DefensiveStrengthHome,
       AvarageGoalsScorredAway / tt.avg_AvarageGoalsScorredAway AS AttackingStrengthAway,
       AvarageGoalsAcceptedAway / tt.avg_AvarageGoalsAcceptedAway AS DefensiveStrengthAway 
FROM $table t CROSS JOIN
     (SELECT avg(AvarageGoalsScorredHome) as avg_AvarageGoalsScorredHome,
             avg(AvarageGoalsAcceptedHome) as avg_AvarageGoalsAcceptedHome,
             avg(AvarageGoalsScorredAway) as avg_AvarageGoalsScorredAway,
             avg(AvarageGoalsAcceptedAway) as avg_AvarageGoalsAcceptedAway
      FROM $table tt
     ) tt;

Note: You should spell column names correctly: "average" and "scored" are the correct spellings.

Comments