PHP - INSERT using multiple SELECT statements with MySQL

[chris]

Hi there,

This is my first post and I quite new to PHP so this is probably quite a common problem that is easily sorted so bare with me please if I'm being too much of a noob.

I have a small 5 a side football league website and Im trying insert data into the league table. All of the queries below return the correct results when I run them individualy in MySQL and when a echo the results, however when I try the INSERT statement ($UPDATE) every entry shows up in the league_table as 0 apart from $ID and $NAME because they are currently text.

To me this looks like the correct syntax but im obviously missing something and your opinion on the subject would be most appreciated.

$ID = mysql_query("SELECT team_id FROM team WHERE team_name = 'Avengers'");
$NAME = mysql_query ("SELECT team_name FROM team WHERE team_name = 'Avengers'");
$P = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name='Avengers'");
$W = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name = 'Avengers' AND result = 'Win' ");
$D = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name ='Avengers' AND result='Draw'");
$L = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name ='Avengers' AND result='Lost'");
$GF = mysql_query ("SELECT SUM(goals_scored) FROM performance WHERE team_name='Avengers'");
$GA = mysql_query (" SELECT SUM(goals_scored) FROM performance WHERE NOT team_name = 'Avengers' AND game_id =6 ");

$UPDATE = mysql_query ("INSERT INTO league_table (team_id, team_name, played, won, drawn, lost, goals_for, goals_against)
                       VALUES ('$ID', '$NAME', '$P', '$W', '$D', '$L', '$GF', '$GA'");

(PS - I will be changing the team name in the queries to a SESSION ID later once I have this figured)

[PhPHelper]

the insert statements works for inserting new data not for updating existing data for that you want update statements like this:

Code: [Select]

$UPDATE = mysql_query("UPDATE leage_table SET team_name='$NAME', played='$P', won='$W', drawn='$D', lost='$L', goals_for='$GF', goals_against='GA' WHERE team_id='$ID'");


[chris]

Sorry I wasn't making myself clear at this stage I just want to add those values to the table I will be working updating later as at the moment there is only one false record in the table. Sorry for any misconception

[PhPHelper]

yeah got the wrong end of it.

Have you tired just echoing out the values instead of inserting into a db to see the values of each var?

[chris]

Yeah for each var i used the mysql_fecth_array like below and it deffinately shows the correct values as I've tested it many times:

while($row = mysql_fetch_array($W)){
   
    echo "<p>The Team has won: " .$row['COUNT(team_name)'] . " Games</p>";   
   
}

It's just left me a bit puzzeled as the syntax and logic looks fine

[PhPHelper]

I agree it looks fine can you post the full code your using?

[chris]

Hi there sorry for late reply, heres the code im using for this page:


<?php # update_league_table.php

include ('includes/config.inc.php');

$page_title = 'Update League Table';

include ('includes/header.html');

if(isset($_POST['submitted'])) {
   
   require_once (MYSQL);
 

$ID = mysql_query("SELECT team_id FROM team WHERE team_name='Avengers'");
$P = mysql_query ("SELECT COUNT(team_name) FROM performance
WHERE team_name='Avengers'");
$W = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name = 'Avengers' AND result = 'Win' ");
$D = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name ='Avengers' AND result='Draw'");
$L = mysql_query ("SELECT COUNT(team_name) FROM performance WHERE team_name ='Avengers' AND result='Lost'");
$GF = mysql_query ("SELECT SUM(goals_scored) FROM performance WHERE team_name='Avengers'");
$GA = mysql_query (" SELECT SUM(goals_scored) FROM performance WHERE NOT team_name = 'Avengers' AND game_id =6 ");

   
    if (mysqli_affected_rows($dbc) == 0) {

$UPDATE = mysql_query ("INSERT INTO league_table (team_id, team_name, played, won, drawn, lost, goals_for, goals_against)
                    VALUES ('1','Avengers' ,'$P', '$W', '$D', '$L', '$GF', '$GA')");

    }else{

        echo '<p>The information could not be entered into the database<p>';

    }
   
   
mysqli_close($dbc);

}

?>


<h1>Update</h1>

<form action="update_league_table.php" method="post">
       
       
    <div align="center"><input type="submit" name="submit" value="Update" /></div>

<input type="hidden" name="submitted" value="TRUE" />

</form>

<?php


    include ('includes/footer.html');



?>

[PhPHelper]

again can't see anything wrong with your code as long as each var has a value added to them they should go into the database.

[chris]

Yeah that's what I thought, well at least you agree the syntax is correct I'll start looking at lower end things that could be causing 0's to be inserted rather than me just getting it wrong. Any way thanks for your help and if I get it sorted I'll post it up here.

[chris]

I recently got an error message saying that the query was empty, however I fixed it without fixing the root problem so I can't post it up here sadly. Apart from running the queries through MySQL and printing the query results in PHP how else would I check that the vars have the query results assigned to them?

[PhPHelper]

I would do it via php and put the vars in a die command to see what is outputted.

[chris]

Hi, thanks again for your input but even with the die command in the query still runs but inserts 0's instead of the required values.

[PhPHelper]

what about using print_r();

[chris]

Right I used print_r($GF) which returned "Resource id #11". $GF works out the goals for, which should return 3. Any ideas? Thanks again btw and the query for $GF is :

SELECT SUM(goals_scored) FROM performance
WHERE team_name='Avengers';

[PhPHelper]

just a thought but it looks like the result is coming back as true/false on/off 1/0

so how about doing a little different by acually trying to pull data from the query then adding that to a var and then submitting it to the database. Here's an example for the team_id

Code: [Select]

<?php
$GetID = mysql_query("SELECT team_id FROM team WHERE team_name='Avengers'");
$row = mysql_fetch_object($GetID);
$ID = $row->team_id;
?>