Updating data problems.. WHERE clause

[ade2901]

Hi,

 

I have a list of fixtures displayed from the database with input fields for the scores so that the user can insert them. I am having a problem updating it and linking it to the IDs, this is obviously done in the WHERE clause but I don't know quite what to pass it. I have a query to join the fixture and team table so that the teamID can be linked to the teamName. I now need to update the data in the fixtures table based on the teamID not name as the teams are stored as their IDs in the fixture table and not their names.

 

You can see my code below;

 

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "admin") or die(mysql_error());
mysql_select_db("lsl") or die(mysql_error());

//Query to join tables and obtain team names linked to IDs
$sql2= mysql_query ("SELECT *, fixtureVenue, t.TeamName as teamA, tt.TeamName as teamB
from team t inner join fixture f
on f.teamA = t.teamid
inner join team tt
on f.teamB = tt.teamid ORDER BY fixtureday,fixturemonth,fixtureyear")or die(mysql_error());  


echo "<table border='1' width='500'>";
echo "<form name='form1' method='post'>";
echo "<tr> <th>Team 1</th> <th>Score</th> <th> - </th> <th>Score</th> <th>Team 2</th> <th>Date</th></tr>";

while($row = mysql_fetch_array($sql2) ) {

// Print out the contents of each row i

echo "<tr><td>"; 
echo $row['teamA'];
echo "</td><td>"; 
echo "<input type='text' name='teamAScore[]' size='1' />";
echo "</td><td>"; 
echo " - ";
echo "</td><td>"; 
echo "<input type='text' name='teamBScore[]' size='1' />";
echo "</td><td>"; 
echo $row['teamB'];
echo "</td><td>";
echo $row['fixtureDay'], " ", $row['fixtureMonth']," ",$row['fixtureYear'] ;
echo "</td>";
echo "</tr>"; 

} 
echo"<input type='submit' value='Submit Results' name='submit'/>";
echo"</form>";
echo "</table>";

//THIS IS WHERE I AM HAVING THE TROUBLE. It just doesn't update whatsoever. I know my WHERE clause is wrong but not sure how to combat it or what to search for to get help..
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE fixture SET teamAScore='$teamAScore[$i]', teamBScore='$teamBScore[$i]' WHERE teamA = teamID and teamB = teamID";
$result1=mysql_query($sql1);
}
}

if($result1){
echo "Updated!";
}

mysql_close();

?>

 

All help would be extremely appreciated!

 

Any questions please don't hesitate to ask.

 

Many thanks in advance,

 

Aidan

[fugix]

first thing you should do is troubleshoot your query to pinpoint the issue,

$result1=mysql_query($sql1) or die(mysql_error());

[ade2901]

I have tried seeing what error is occuring and there are none. It's submitting and doing nothing. It mustn't be entering the if statement for the submit button..

[ade2901]

I've noticed that my select query isn't working so until I get that sorted my update query won't. Despite the correct output from the select query the join doesn't seem to have worked as I would have thought.

[fugix]

I believe it is because yoare telling it to select all, then specifying specific fields to grab. Choose one or the other

[ade2901]

It turns out it was I made no attempt to relate it to the fixtureID. Further to this I separeted the submit processing into a new file.

 

I'll post the solution for anyone else who may have this problem.

 

Page to display all fixtures for the teams linking up the team name to the team id through a join query.

<?php
// Make a MySQL Connection
mysql_connect("localhost", "yourUsername", "yourPassword") or die(mysql_error());
mysql_select_db("yourDatabase") or die(mysql_error());

//Query to join tables and obtain team names linked to IDs
$sql2= mysql_query ("SELECT 
*, fixtureVenue, 
t.TeamName as teamA, 
tt.TeamName as teamB
from 
team t inner join fixture f
on f.teamA = t.teamid
inner join team tt
on f.teamB = tt.teamid")or die(mysql_error());
  

//DO THE ABOVE BUT FOR UPDATE
echo "<table border='1' width='500' >";
echo "<form name='form1' method='post' action='php_insert_result.php'>";
echo "<tr> <th>ID</th><th>Team 1</th> <th>Score</th> <th> - </th> <th>Score</th> <th>Team 2</th> <th>Date</th></tr>";

while($row = mysql_fetch_array($sql2) ) {

// Print out the contents of each row i
echo "<tr><td>";
echo "<input type='text' name='fixtureID[]' id='fixtureID[]' size='2' value='",$row['fixtureID'],"'/>";
echo "</td>";
echo "<td>"; 
echo $row['teamA'] ;
echo "</td><td>"; 
echo "<input type='text' name='teamAScore[]' size='1' value='",$row['teamAScore'],"' />";
echo "</td><td>"; 
echo " - ";
echo "</td><td>"; 
echo "<input type='text' name='teamBScore[]' size='1' value='",$row['teamBScore'],"' />";
echo "</td><td>"; 
echo $row['teamB'];
echo "</td><td>";
echo $row['fixtureDay'], " ", $row['fixtureMonth']," ",$row['fixtureYear'] ;
echo "</td>";
echo "</tr>"; 

} 
echo "<tr><input type='submit' value='Submit' name='Submit'/></tr>"; 


echo"</form>";
echo "</table>";

mysql_close();

?>

 

The updatefile linked to the form with the update statment refering to the fixtureID.

<?php

$host="localhost"; // Host name
$username="yourUsernameHere"; // Mysql username
$password="yourPasswordHere"; // Mysql password
$db_name="lsl"; // Database name


// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


$submit = $_POST['submit'];
$fixtureID = $_POST['fixtureID'];
$teamAScore = $_POST['teamAScore'];
$teamBScore = $_POST['teamBScore'];


$limit = count($fixtureID);

for($i=0;$i<$limit;$i++) {
    $fixtureID[$i] = mysql_real_escape_string($fixtureID[$i]);
    $teamAScore[$i] = mysql_real_escape_string($teamAScore[$i]);
    $teamBScore[$i] = mysql_real_escape_string($teamBScore[$i]);

    $query = "UPDATE fixture SET teamAScore=$teamAScore[$i], teamBScore=$teamBScore[$i] WHERE fixtureID = $fixtureID[$i]";

    if(mysql_query($query))
        echo "$i successfully updated.<br/><a href='div1Results.php'>Back to main page</a>";
    else
        echo "$i encountered an error.<br/>";
}  

// close connection
mysql_close();
?>

 

Hope this helps others!

 

I now need to get my head around how to update teams details (points, goals scored, goals conceded, games won, games lost, games drawn etc) in the table division1 which is a league table and link the teams through their IDs.. If anyone has any suggestions for this then I'd appreciate that. Otherwise I'll start a new thread with full details of the table structure etc to make it easier. Yet another part I am stuck on!

 

Thanks for the help,

 

Aidan