In need of help correcting a code

[TomFromKWD]

Hi guys,

 

First time post here so go easy on me 

 

OK, heres the deal:

 

I have a page with a form and when submitted it needs to update MySQL tables

 

The code for the form is as follows:

 

<form method="post" action="update.php">
<select name="Batsman">
<option value="Player 1">Player 1</option>
<option value="Player 2">Player 2</option>
</select>
<input type="text" name="Runs" size="5" />
<input type="text" name="Overs" size="5" />
<input type="submit" value="Submit Batting Scores" />
</form>

 

The code for the 'update.php' is as follows:

 

<?php

$Batsman = $_POST['Batsman'];
$Runs = $_POST['Runs'];
$Overs = $_POST['Overs'];

mysql_connect ("HOST", "*USERNAME*", "*PASSWORD*") or die ('error: ' .mysql_error());
mysql_select_db ("DB_NAME");

$query="UPDATE batting SET runs = ("'.$Runs.'"+runs)  WHERE batsman = '$Batsman' "
$query2="UPDATE bowling SET overs = ("'.$Overs.'"+overs) WHERE batsman = '$Batsman' "

mysql_query($query) or die ('Error updating result: ' .mysql_error());
mysql_query($query2) or die ('Error updating result: ' .mysql_error());

echo "Results updated with: ".$Batsman." - ".$Runs." Runs" ;
?>

 

The database layout is:

 

2 tables, 1 named "batting" another named "bowling"

 

 

What the form SHOULD be doing is:

 

Take what ever number is inputed to "Runs" and ADD TO THE CURRENT TOTAL of 'Runs' in 'Batting'

Take what ever number is inputed to "Overs" and ADD TO CURRENT TOTAL of 'Overs' in 'bowling'

 

The IDs on each table are 'Player 1' and 'Player 2' - labeled on each table as 'batsman'

The corresponding 'batsman' is selected on the form via the drop down before submiting.

 

For example if 'Player 2' currently has 50 'runs' in 'batting' and 10 'Overs' in 'bowling', and I then select him on the drop down and input 10 runs and 1 over, when I hit submit his data in the MySQL tables SHOULD go to 60 'runs' in 'batting' and 11 'overs' in 'bowling'

 

Im hoping labeling the table data in this way provides a clear enough explanation of the set up with out the need to know about the subject matter in hand, (cricket, if you havent guessed already  )

 

 

Now the table worked fine when I was updating to REPLACE the numbers in 'runs' or 'overs' but I need it to ADD to the existing number

I was advised else where to add the '+runs' '+overs' in order to get this to work but all Im getting is an error.

 

The current error coming back is:

 

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/a2552500/public_html/update.php on line 10

 

If someone can correct me whereever I am going wrong that would be great, however an explination to acompany any correction would be appreciated as Id like to learn what is wrong, why its wrong and how to fix it.

 

Thanks in advance for your time

 

Tom

[PFMaBiSmAd]

You are missing the ; on the end of both of the following two lines of code. You also have the single and double quotes reversed around the $Runs and $Overs variables. Since you are dealing with numbers, there's no need for the single quotes anyway AND it is much simpler and less error prone to NOT use string concatenation (the dot . ) with php variables -

 

$query="UPDATE batting SET runs = $Runs + runs  WHERE batsman = '$Batsman'";
$query2="UPDATE bowling SET overs =  $Overs + overs WHERE batsman = '$Batsman'";

 

You should also validate or cast to an integer your $Runs and $Overs variables to prevent sql injection. You also need to escape the $Batsman variable to prevent sql injection (see the mysql_real_escape_string() function.)

[TomFromKWD]

Thats brilliant, its now submiting the data and ADDING it to the existing total

 

However it only seems to be working for the 'batting' table and not the 'bowling' table

 

is $query2 valid and going to work in the way I am coding it to update the 'bowling' table?

[Muddy_Funster]

PFM' has covered why you are getting your errors. I personaly much preffer using . concat's, (although I think I am alone in this ) Try changing

$query="UPDATE batting SET runs = ("'.$Runs.'"+runs)  WHERE batsman = '$Batsman' "
$query2="UPDATE bowling SET overs = ("'.$Overs.'"+overs) WHERE batsman = '$Batsman' "

to

 $qry = 'UPDATE batting INNER JOIN bowling ON (batting.batsman = bowling.batsman) SET batting.runs = ((SELECT batting.runs) + '.$Runs.') bowling.overs = ((SELECT bowling.overs) + '.$Overs.') WHERE batting.batsman = \''.$Batsman.'\'';

That should at the least get you a different error   At best it should condense what you are doing into a single query.

[TomFromKWD]

Sorry, but it came back with a new error 

 

Is there another way of condensing 2 or more table edits to one query that codes in a similar fashion to that of my original work?

 

I more or less understand what is where writing like that, however the condessing option:

PFM' has covered why you are getting your errors. I personaly much preffer using . concat's, (although I think I am alone in this ) Try changing

$query="UPDATE batting SET runs = ("'.$Runs.'"+runs)  WHERE batsman = '$Batsman' "
$query2="UPDATE bowling SET overs = ("'.$Overs.'"+overs) WHERE batsman = '$Batsman' "

to

 $qry = 'UPDATE batting INNER JOIN bowling ON (batting.batsman = bowling.batsman) SET batting.runs = ((SELECT batting.runs) + '.$Runs.') bowling.overs = ((SELECT bowling.overs) + '.$Overs.') WHERE batting.batsman = \''.$Batsman.'\'';

That should at the least get you a different error   At best it should condense what you are doing into a single query.

 

Well, with that im honestly not 100% whats happening where,

dont get me wrong, with time Id get it, but time is not on my side at the moment

[kenrbnsn]

And the new error is?

 

Ken

[Muddy_Funster]

sorry, I didn't mention, but did you change the execution of the queries to match the new variable?

mysql_query($query) or die ('Error updating result: ' .mysql_error());
mysql_query($query2) or die ('Error updating result: ' .mysql_error());

should become

mysql_query($qry) or die ('Error updating result: ' .mysql_error());
// mysql_query($query2) or die ('Error updating result: ' .mysql_error());

[TomFromKWD]

Well when I changed it to

 

$qry = 'UPDATE batting INNER JOIN bowling ON (batting.batsman = bowling.batsman) SET batting.runs = ((SELECT batting.runs) + '.$Runs.') bowling.overs = ((SELECT bowling.overs) + '.$Overs.') WHERE batting.batsman = \''.$Batsman.'\'';

 

I got:

 

Error updating result: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bowling.overs = ((SELECT bowling.overs) + 100) WHERE batting.batsman = 'Player 1' at line 1

 

So I changed back to the:

 

$query="UPDATE batting SET runs = $Runs + runs  WHERE batsman = '$Batsman'";
$query2="UPDATE bowling SET overs =  $Overs + overs WHERE batsman = '$Batsman'";

 

now with the $query, $query2 version, the $query seems to be working fine, but the $query2 doesnt update its table, BUT also does not kick back an error, I guess I could add an 'or die' to $query2 and see what comes back, unless someone has better ideas

 

oh and btw, thanks a bunch to everyone making an imput on the topic and helping me work through this

[TomFromKWD]

and yes muddy, I changed that over to $qry

[Muddy_Funster]

yet again I am bested by a comma 

stick a comma right after

SET batting.runs = ((SELECT batting.runs) + '.$Runs.')

Let us know what comes back after that's done.

[TomFromKWD]

muddy

 

IVE DONE IT!! 

 

Your code stopped kicking back errors after I added the comma,

 

I also solved the issue of why the "$query2" I had wasnt updating before,

 

I had the value nulled on the table in phpadmin so no matter what figure i inputted through my form it did nothing

 

I changed it from being nulled to a 0 and its started working both ways, with the $query2 and your way

 

so i can do it which ever now

 

Im going to stick with $query $query2 for now as its what I know, but Im keeping your methods to hand to have a proper look through them in future muddy, it seems a nice way to code it all into one query

 

Thanks so much for all your help

[Muddy_Funster]

Your welcome (even though I did very little! you got there yourself in the end up), glad it worked out.  You are 100% right to stick with what you know.  If you need anything else you know where we are.