user3255412 user3255412 - 4 months ago 7
MySQL Question

error inserting data into table mysql

i fixed everything long time ago that but i have problem inserting data with the second code

First code

<?php
include("conf.php");
$file_name = basename(__FILE__,'.php');
$table = $file_name;
$query = "SELECT Home FROM " . $table;
$result = mysqli_query($conn, $query);
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"
CREATE TABLE IF NOT EXISTS $file_name (
Home varchar(255) NOT NULL,
PlayedHome varchar(255) NOT NULL,
TotalGoalsHome varchar(255) NOT NULL
)CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else
?>


Second code

<?php
include("conf.php");
$query = "INSERT INTO test(Home, PlayedHome, TotalGoalsHome)
SELECT HomeTeam As Home, COUNT(HomeTeam) AS PlayedHome, SUM(HomeGoals) AS TotalGoalsHome FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC";
$data= mysqli_query($conn,$query) or die(mysqli_error());
while($row = mysqli_fetch_assoc($data)){
foreach($row as $cname => $cvalue){
print "$cname: $cvalue\t";
}
print "\r\n";
}
?>


When i run the second code i get this error


mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean
given in C:\test.php on line 6


I have tried everything and the problem i thin is on the

$query = "INSERT INTO test(Home, PlayedHome, TotalGoalsHome) SELECT HomeTeam As Home, COUNT(HomeTeam) AS PlayedHome, SUM(HomeGoals) AS TotalGoalsHome FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC";


When i change values to numbers or text the data is inserted into table, with
SELECT ..
doesn't work.
And when i remove
INSERT INTO
and print the query it works

Answer

From the code snippet included I see the insertion is happening here:

if ($t = 1) {
    $sql = "INSERT INTO $file_name (HomeTeam, HomeGoals) VALUES (SELECT COUNT(HomeTeam) AS HomeTeam, SUM(HomeGoals) As HomeGoals FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC)";
}

First of all change if ($t = 1) to if ($t == 1). Also if you want to insert it everytime I done see the point of an if condition being there.

Secondly you need to execute the query:

 $res = mysqli_query($conn, $sql);

Also try executing this query: $sql = "INSERT INTO $file_name (HomeTeam, HomeGoals) VALUES (SELECT COUNT(HomeTeam) AS HomeTeam, SUM(HomeGoals) As HomeGoals FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC)" in SQL first to see if this works and is not causing in Database side errors. Because that would be tough to catch from client-side.