Simple SQL question

[waynew]

Simple question. Tried Googling but always ended up with the wrong question being answered.

 

Say I just want to add to a column's value.

 

Would this work?

 

$give_home = mysql_query("INSERT INTO c_table (POINTS) VALUES ((POINTS+3)) WHERE TEAM = '$home[$i]'");

[The Little Guy]

NO. Use UPDATE

 

Try this:

$give_home = mysql_query("UPDATE c_table SET POINTS = (POINTS+3) WHERE TEAM = '{$home[$i]}'");

[DarkWater]

UPDATE c_points SET points = points + 3 WHERE team = '$home[$i]'

[waynew]

That would make sense. Rolleyes.

 

Thanks guys. 

[waynew]

For some reason, it's not working:

 

Here's my update code:

 

<?php

else if($end_result[$i] == 2){
           echo "GIVEN TO AWAY";
           $give_away = mysql_query("UPDATE c_table SET POINTS = (POINTS+3) WHERE TEAM = $away[$i]") or die(mysql_error());
}

?>

 

I know for a fact that the if statement is being proven true as the echo tells me.

 

However, when I try to take the teams out:

 

<?php
$select_all = mysql_query("SELECT * FROM c_table ORDER BY POINTS") or die(mysql_error());

while($row = mysql_fetch_assoc($select_all)){
    
	echo "<TR>";
	echo "<TD>".get_team_name($row['TEAM'])."</TD><TD>".$row['POINTS']."</TD>";
	echo "</TR>";

}
?>

 

All I'm getting is the name of the teams and no points.  ???

[waynew]

Bearing in mind that my table is a temporary one.

[PFMaBiSmAd]

Is TEAM a numeric data type? If not, then $away[$i] must be enclosed in single-quotes. Without the single quotes $away[$i] is being evaluated as a number and if it is a string without any leading digits, ends up being a zero or a NULL and the comparison won't match any of your TEAM values.

 

Also, echo out your query string to make sure it contains what you expect, $away[$i] might not be what you think it is.

[waynew]

TEAM is numeric. It's the ID of a team.

I echoed out the query and it looks like expected.

The failure is happening on the update. I ran mysql_affected_rows( ) and it gave me a 0.

The arrays coming in are correct.

The array value that determines what update to do is correct.

 

So I'm stumped. The teams are in the DB but I can't update the column POINTS.

 

mysql_query("CREATE TEMPORARY TABLE c_table(TEAM INT(24) UNIQUE, POINTS INT(24), PLAYED INT(24))") or die(mysql_error());

[fenway]

You sure you're updating the correct row?

[PFMaBiSmAd]

Without seeing all your code, I doubt we will be able to guess why it is not working.

 

My best guess - you and your code are expecting a temporary table to persist between pages or between connections to the database.