Adding 1 to a table column

@roondogSep 06.2007

I want to add 1 to a column in mysql using PHP am i on the right track with this code:

[code=php] <?php$con = mysql_connect (‘localhost’, ‘******’, ‘*****’); if (!$con) { die(‘Could not connect: ‘ . mysql_error()); } mysql_select_db(“roondog_news”, $con);

/*add to pldplus one to pld*/

if (isset($_POST[‘hometeam1 score’])) { mysql_query(“UPDATE league SET Pld = Pld +1 WHERE teamname = $_POST[‘hometeam1’] AND teamname = $_POST[‘hometeam2’]”) } mysql_close($con); ?>

[/code]

to post a comment

PHP

21 Comments(s) _↴

@JDM71488Sep 06.2007 — #if the original Pld value is in the database, you will have to get that out first, increment it, and then update.

something like:

[code=php]// get Pld and increment
 
 $sql = mysql_query("SELECT Pld FROM league WHERE teamname = {$_POST['hometeam1']} AND teamname = {$_POST['hometeam2']}");
 $row = mysql_fetch_assoc($sql);
 $Pld = $row['Pld'];
 $Pld++;
 
 // update Pld
 
 if (mysql_query("UPDATE league SET Pld = $Pld WHERE teamname = {$_POST['hometeam1']} AND teamname = {$_POST['hometeam2']}"))
 {
 echo("updated");
 }[/code]

@YelgnidrocSep 06.2007 — #Hi roondog,

You are on the right lines.

You don't need to extract the value from the database, modify with php, then put it back in, you can do it all with one UPDATE query.

@roondogauthorSep 06.2007 — #thanks,

my update query doesn't seem to do anything can you see why?

@YelgnidrocSep 06.2007 — #

[code=php]if (isset($_POST['hometeam1 score'])) { 
 mysql_query("UPDATE league SET Pld = Pld +1 
 WHERE teamname = $_POST['hometeam1'] 
 AND teamname = $_POST['hometeam2']") 
 [/code]

A couple of things, in your query teamname has got to be equal to BOTH $_POST['hometeam1'] AND $_POST['hometeam2']")

Also, I guess teamname is a string (text/char/varchar), amd so in your query you need to have quotes around the $_POSTS, an easier way might be:

[code=php]
 $ht1 = $_POST['hometeam1'];
 $ht2 = $_POST['hometeam2'];
 
 if (isset($_POST['hometeam1 score'])) { 
 mysql_query("UPDATE league SET Pld = Pld +1 
 WHERE teamname = '$ht1' 
 AND teamname = '$ht2'") 
 [/code]

Don't forget the first point I made.

Finally, I wouldn't use $_POST['hometeam1 score'] as there is a space, instead use $_POST['hometeam1_score'] - and check that it isset by echo 'i am set' after the { and just before mysql_query...

@JDM71488Sep 06.2007 — #i just learned something new... thanks

@YelgnidrocSep 06.2007 — #Just one last thing, you should make sure that the $_POST's are made safe before inserting the contents into a database.

@YelgnidrocSep 06.2007 — #You're welcome JDM. Life's for learning.

@roondogauthorSep 07.2007 — #i've worked it out at last. I needed two update queries. now the rest of the script should follow on easily as it's pretty much the same. Although it will be very long.

@roondogauthorSep 07.2007 — #here is the full script, i have one problem are though that isn't working

[code=php]
 <?php
 
 $con = mysql_connect ('localhost', '******', '******');
 
 if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }
 mysql_select_db("roondog_news", $con);
 
 $ht1 = $_POST['hometeam1'];
 $at1 = $_POST['awayteam1'];
 $ht1s = $_POST['hometeam1_score'];
 $at1s = $_POST['awayteam1_score'];
 
 if (isset($ht1s)) {
 
 mysql_query("UPDATE league SET played = played +1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET played = played +1
 WHERE teamname = '$at1'");
 
 
 if ($ht1s > $at1s) {
 
 mysql_query("UPDATE league SET won = won +1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET lost = lost + 1
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET points = points + 3
 WHERE teamname = '$ht1'");
 
 }else if ($ht1s < $at1s) {
 
 mysql_query("UPDATE league SET lost = lost +1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET won = won +1
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET points = points + 3
 WHERE teamname = '$at1'");
 
 
 }else{
 
 mysql_query("UPDATE league SET drawn = drawn +1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET drawn = drawn +1
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET points = points +1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET points = points +1
 WHERE teamname = '$at1'");
 
 }
 
 
 /*from here to the end the table isn't updating
 */
 
 
 {
 mysql_query("UPDATE league SET for = for +'$ht1s'
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET away = away +'$at1s'
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET for = for +'$at1s'
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET away = away +'$ht1s'
 WHERE teamname = '$at1'");
 }
 
 }
 {
 mysql_query("UPDATE league SET goaldif = for - away");
 }
 mysql_close($con);
 
 ?>
 [/code]

thanks for all the help so far. The time spent on this is going to save me at least half an hour every week.

@YelgnidrocSep 07.2007 — #mysql_query("UPDATE league SET for = for +'$ht1s' WHERE teamname = '$ht1'");

Should be:

mysql_query("UPDATE league SET for = for + $ht1s WHERE teamname = '$ht1'");

You had quotes around $ht1s which you must not have around a number.

Same for your other statements.

@roondogauthorSep 07.2007 — #Its still not working. I'm gong to tinker around and see what i can do to solve it.

@YelgnidrocSep 07.2007 — #Ensure that, for example, $ht1s is not empty, i.e. it contains a number even if the number is zero.

@roondogauthorSep 07.2007 — #there is something wrong with those variables although it uses them correctly in the if statements above.

@YelgnidrocSep 07.2007 — #In what way?

@roondogauthorSep 07.2007 — #for example when deciding how many points to add it checks which is higher $ht1s or $at1s. That part seems to work.

I've mad another script that just echos the variables and nothing appears.

I'm using <input type="text" /> in my form as far as I know there isn't a dedicated input for numbers.

@YelgnidrocSep 07.2007 — #you can use something like die("$ht1s - $at1s"); to stop the script and display the variables.

Could you please post your form (where data is input by the user) and the up to date handler (that updates the database)

@YelgnidrocSep 07.2007 — #Just realised, you are using a column in your database called for

[code=php]
 mysql_query("UPDATE league SET for = for +'$ht1s' 
 WHERE teamname = '$ht1'"); 
 [/code]

for is a reserved keyword so you will need backticks (`

) around it, or better still choose a name that is not a reserved keyword.
 
 i.e.
 
 <CODE lang="php">[code=php]<i>
 </i>mysql_query("UPDATE league SET

for = for` +'$ht1s'
WHERE teamname = '$ht1'");
[/code]

@roondogauthorSep 07.2007 — #I had a feeling that the for thing was a problem. My other problem was my stupidity in that it should have been against rather than away. All is working now. Thanks again for all the help.

@YelgnidrocSep 07.2007 — # All is working now. Thanks again for all the help.[/QUOTE]

Great stuff - you're welcome.

@roondogauthorSep 07.2007 — #i've added this bit to the top

[code=php]
 if ($ht1s == '99') {
 echo 'match cancelled';
 }else{
 
 mysql_query("UPDATE league SET played = played + 1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET played = played + 1
 WHERE teamname = '$at1'");
 
 
 if ($ht1s > $at1s) {
 
 mysql_query("UPDATE league SET won = won + 1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET lost = lost + 1
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET points = points + 3
 WHERE teamname = '$ht1'");
 
 }else if ($ht1s < $at1s) {
 
 mysql_query("UPDATE league SET lost = lost + 1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET won = won + 1
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET points = points + 3
 WHERE teamname = '$at1'");
 
 }else{
 
 mysql_query("UPDATE league SET drawn = drawn + 1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET drawn = drawn + 1
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET points = points + 1
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET points = points + 1
 WHERE teamname = '$at1'");
 
 }
 
 mysql_query("UPDATE league SET for = for + $ht1s
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET against = against + $at1s
 WHERE teamname = '$ht1'");
 mysql_query("UPDATE league SET for = for + $at1s
 WHERE teamname = '$at1'");
 mysql_query("UPDATE league SET against = against + $ht1s
 WHERE teamname = '$at1'");
 
 }
 
 [/code]

I want it to be that when i put 99 in the form that section is not processed but it doesn't seem to work. It echoes match cancelled but still does the rest.

@YelgnidrocSep 08.2007 — #I can't see why it should do more than display match cancelled, but you could always use:

[code=php]
 if ($ht1s == '99') { 
 die('match cancelled'); 
 }
 [/code]

Also in #PHP _↴

trouble updaing database Port Listener - FPS game Form Spam

Success!

Help @roondog spread the word by sharing this article on Twitter...

Tweet This

Adding 1 to a table column

21 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Adding 1 to a table column

21 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

21 Comments(s) _↴

Also in #PHP _↴