Cant get form to update mySQL database

[chrisidas]

Hey,

 

I am having trouble updating a table in my database. The collum in the table i want to update is called 'transferstatus'

 

Here is my form

<form action="transfers-script.php" method="post">
    <table width="500" border="0" cellspacing="10" cellpadding="5">
  <tr>
    <td>Player
    <?php
$sql = mysql_query("SELECT * FROM players WHERE teamname='Arsenal'");

echo '<select name="playername" id="playername">';
while($row = mysql_fetch_array($sql)){
$playername = $row['playername'];
?>
<option value="<?php echo $playername; ?>"><?php echo $playername; ?></option>
<?php
}
?>
    </td>
    
    <td>Asking Price 
    </td>
    <td>Type
    <select name="transfertype" id="transfertype">
    <option value="sale" selected="selected">Sale</option>
    <option value="loan">Loan</option>
    </select>
    </td>
    <td><input type="submit" value="Submit" /></td>
  </tr>
</table>
</form>

and here is the script

 

<?php
include("../../members_system/include/constants.php");



$con = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
      mysql_select_db(DB_NAME, $con) or die(mysql_error());
?>


<?php
$playername = mysql_real_escape_string($_POST['playername']);
$transfertype = mysql_real_escape_string($_POST['transfertype']);

if("$transfertype='sale'")
{
$sql = "UPDATE players WHERE 'playername'=".$playername." SET 'transferstatus'=sale";
$res = mysql_query($sql);
}

mysql_close($con);


?>

Any ideas where i'm going wrong?

[trq]

This....

 

if("$transfertype='sale'")

 

will *always* evaluate to true. Why? Because are are simply using a string as your expression. You don't need (and they indeed make no sense) the double quotes.

 

As for your issue. What have you done to debug your code? Have you looked to see what mysql_error might produce? Your update query is all over the place. column identifiers do not belong in quotes, but strings do.

[fugix]

there are several things wrong in your script, I will post the correct syntax and then explain what i have done

if($transfertype == 'sale')
{
$sql = "UPDATE players SET transferstatus = 'sale' WHERE playername = '$playername'";
$res = mysql_query($sql) or trigger_error(mysql_error());
}

mysql_close($con);

first thing that i changed is the double quotes that you used to encase your if statement, they are not needed. Second was in your if statement i changed the = to == because you need to use the == whenever comparing a variable value to a string value, === is acceptable as well as long as the two types are the same. Third thing was in your update query, you placed commas around your field names which is not correct syntax, when declaring field names, either encase them in nothing or in backticks (`) if the name is a mysql reserved word. You also placed your WHERE clause before your SET clause. The SET clause is to be placed before the WHERE clause in an UPDATE statement. I also cleaned up a couple other things but those were the main issues that i saw, let me know if it works for you.

[chrisidas]

there are several things wrong in your script, I will post the correct syntax and then explain what i have done

if($transfertype == 'sale')
{
$sql = "UPDATE players SET transferstatus = 'sale' WHERE playername = '$playername'";
$res = mysql_query($sql) or trigger_error(mysql_error());
}

mysql_close($con);

first thing that i changed is the double quotes that you used to encase your if statement, they are not needed. Second was in your if statement i changed the = to == because you need to use the == whenever comparing a variable value to a string value, === is acceptable as well as long as the two types are the same. Third thing was in your update query, you placed commas around your field names which is not correct syntax, when declaring field names, either encase them in nothing or in backticks (`) if the name is a mysql reserved word. You also placed your WHERE clause before your SET clause. The SET clause is to be placed before the WHERE clause in an UPDATE statement. I also cleaned up a couple other things but those were the main issues that i saw, let me know if it works for you.

 

It worked 

 

Thanks, really appreciate it!

 

Pretty new with web design and still trying to get my head around it, getting there though haha

[fugix]

no problem, if you are trying to learn, take a look at the links in my signature, they provide documentation and examples of correct syntax to use.

[chrisidas]

will have a look, cheers