Error saving data in mysql table

[vivis933]

Hi i made this code but when i run there is no data in the table

<?php
include("conf.php");
$file_name = basename(__FILE__,'.php');
$table = $file_name;
$query = "SELECT HomeTeam FROM " . $table;
$result = mysqli_query($conn, $query);

if(empty($resultat)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $file_name (
HomeTeam int NOT NULL PRIMARY KEY,
HomeGoals varchar(255) NOT NULL
)CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else

$sql = "INSERT INTO $file_name (HomeTeam, HomeGoals) VALUES (
SELECT HomeTeam, COUNT(HomeGoals) As HomeGoals FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC
)";
?>

 

[Barand]

Can you describe what is is you are trying to do so I can make some sense of your code.

[vivis933]

+------------+------------+
| HomeTeam   | HomeGoals  |
+------------+------------+
| Team1      |          2 |
+------------+------------+
| Team3      |          5 |
+------------+------------+
| Team1      |          2 |
+------------+------------+
| Team2      |          3 |
+------------+------------+
| Team2      |          2 |
+------------+------------+
| Team3      |          2 |
+------------+------------+
| Team1      |          2 |
+------------+------------+

I need the following results

Number of times the team is on column

Team1 = 3 times

Team2 = 2 times

Team3 = 2 times

And the total number of goals the time has

Team1 = 6 goals

Team2 = 5 goals

Team3 = 7 goals

And inserting it in new table

[Barand]

Your data shows team as character field, yet you create a table with team name as INT and goals as varchar(). As this table contains only one field for goals, and you say you want both a count and a total, which goes in the new table (count or total).

 

And why are you doing this at all? You can get these by query any time, you should not store data derived from the data in databases.

[vivis933]

I need both count and total but in the code i used only total for goals, i need this for further development. The table is created but the problem is in the INSERT INTO or SELECT can you help me do debug this.

[Barand]

First step to debugging is to have error_reporting switched on and also check for mysqli_error values after attempting queries.

 

 

if(empty($resultat)) {

Where is $resultat defined? If it isn't it should be throwing an "undefined variable" error.

[vivis933]

no there is no error report when i run it i fixed the

if(empty($resultat)) {

with

if(empty($result)) {

 data still not inserted, and no columns in table.

 

 

[Barand]

Do you have error reporting turned on?

 

Have you checked the values of mysqli_error?

 

I cannot do it for you.

[vivis933]

<?php
include("conf.php");
$file_name = basename(__FILE__,'.php');
$table = $file_name;
$query = "SELECT HomeTeam FROM " . $table;
$result = mysqli_query($conn, $query);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

if (!mysqli_query($conn, "SET a=1")) {
printf("Errormessage: %s\n", mysqli_error($conn));
}
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $file_name (
HomeTeam int NOT NULL PRIMARY KEY,
HomeGoals varchar(255) NOT NULL
)CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else

$sql = "INSERT INTO $file_name (HomeTeam, HomeGoals) VALUES (
SELECT HomeTeam, COUNT(HomeGoals) As HomeGoals FROM GetHistoricMatchesByLeagueAndDateInterval GROUP BY HomeTeam ORDER BY HomeTeam ASC
)";
?>

Errormessage: Unknown system variable 'a'

<p>fundtable exists</p>

Process finished with exit code 0