W3Schools example script changes.

[MissileMoose]

Hello, I'm trying to update information in a database through a form but I have very limited knowledge in PHP/SQL. I'm using an example given on the W3School website, but it needs editing and I don't have a clue how to do it.

 

 

Example script:

 

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("UPDATE Persons SET Age = '36'
WHERE FirstName = 'Peter' AND LastName = 'Griffin'");

mysql_close($con);
?>

 

My attempt to change it: (Does not work, no errors given)

 

<?php
$con = mysql_connect("localhost","name","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

mysql_query("UPDATE roster_basic SET callsign, rank, location = '[new_username]','[new_rank]','[new_location]'
WHERE callsign = '[old_username]'");

mysql_close($con);
?>

 

I need to be able to enter the old user name (that I want to edit) into a text field, then the new information into three more fields. I would appreciate it very much if anybody could shed some light onto this situation or even rewrite the "mysql_query" part.

 

Thank you

MissileMoose

[Pikachu2000]

Post the form also, please.

[sunfighter]

change these lines and see if that helps

 

$link = mysql_select_db("my_database", $con);

 

and

 

$results = mysql_query("UPDATE roster_basic SET callsign, rank, location = '[new_username]','[new_rank]','[new_location]' WHERE callsign = '[old_username]'");

 

then to check the quest by echoing the $results.

[MissileMoose]

Here's the form as requested. Please also note that I changed the ID's of the fields in my original post to make it easier to understand, they do match on my website.

 

<form id="roster_update" name="roster_update" method="POST">
            <p>Section:<br />
              <select name="member_section" id="member_section" size="1" onChange="this.form.action=this.options[this.selectedIndex].value;">
                <option value="" selected="selected">- Select -</option>
                <option value="../staff/roster/update_titan.php">Titan</option>
                <option value="../staff/roster/update_phoenix.php">Phoenix</option>
                <option value="../staff/roster/update_basic.php">Basic Training</option>
              </select>
            </p>
            <p>Callsign to update:<br />
              <input name="member_selectcallsign" type="text" id="member_selectcallsign" size="30" maxlength="30" />
            </p>
            <p>CallSign:<br />
              <input name="member_callsign" type="text" id="member_callsign" size="30" maxlength="30" />
            </p>
            <p>Rank:<br />
              <select name="member_rank" id="member_rank">
                <option value="Captain" selected="selected">Captain</option>
                <option value="First Lieutenant">First Lieutenant</option>
                <option value="Second Lieutenant">Second Lieutenant</option>
                <option value="Chief Warrant Officer 5">Chief Warrant Officer 5</option>
                <option value="Chief Warrant Officer 4">Chief Warrant Officer 4</option>
                <option value="Chief Warrant Officer 3">Chief Warrant Officer 3</option>
                <option value="Chief Warrant Officer 2">Chief Warrant Officer 2</option>
                <option value="Warrant Officer">Warrant Officer</option>
                <option value="Sergeant Major">Sergeant Major</option>
                <option value="Master Gunnery Sergeant">Master Gunnery Sergeant</option>
                <option value="First Sergeant">First Sergeant</option>
                <option value="Master Sergeant">Master Sergeant</option>
                <option value="Gunnery Sergeant">Gunnery Sergeant</option>
                <option value="Staff Sergeant">Staff Sergeant</option>
                <option value="Sergeant">Sergeant</option>
                <option value="Corporal">Corporal</option>
                <option value="Lance Corporal">Lance Corporal</option>
                <option value="Private First Class">Private First Class</option>
                <option value="Private">Private</option>
                <option value="Recruit">Recruit</option>
              </select>
            </p>
            <p>Location:<br />
              <select name="member_location" id="member_location">
                <option value="United States" selected="selected">United States</option>
                <option value="United Kingdom">United Kingdom</option>
                <option value="Canada">Canada</option>

              </select>
            </p>
            <p> </p>
            <p>
              <input type="submit" name="roster_update" id="roster_update" value="Update" />
              <input type="reset" name="roster_reset" id="roster_reset" value="Reset Fields" />
            </p>
          </form>

 

Thank you

MissileMoose

[MissileMoose]

I used this (Not sure if I did it right haha):

 

echo "Not sure if this is correct $results" . mysql_error();

 

Then got this when I tested it:

 

Not sure if this is correct 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 ' rank, location = '[member_callsign]','[member_rank]','[member_location]' WHERE ' at line 1

 

PHP Version: 5.2.15

SQL Version: 5.1.52

 

I'm not sure if this helps.

[Pikachu2000]

UPDATE query syntax is:

UPDATE table SET field = 'value', field2 = 'value2', field3 = 'value3' WHERE some_field = 'some_value'

 

But you aren't going to get anything other than the literal strings '[member_callsign]', '[member_rank]' and '[member_location]' inserted into the table. You need to extract the values from the $_POST array sent by the form, then validate and sanitize them for use in the query string.

 

For a string type data value it would resemble

 

if( !empty($_POST['value']) ) {
     $value = trim(mysql_real_escape_string($_POST['value']));
}

 

Then $value would be sanitized for use in the query string. Providing an example of validation isn't as easy, as validation isn't as generic and should be tailored to the acceptable values you'd expect to receive.

[MissileMoose]

Snip..

 

Thank you very much for your help sir, though I am struggling to understand most of what you posted as I'm a noob when it comes to PHP (Two days experience). Would you be kind enough to set it out exactly as it needs to be please? Sorry for my incompetence.

 

Thank you very much.

MissileMoose

[Pikachu2000]

Do you understand how data is passed via the $_POST and/or $_GET arrays from a form? Before anything else makes sense, you'll need to have a grasp of that.

[MissileMoose]

if( !empty($_POST['value']) ) {
     $value = trim(mysql_real_escape_string($_POST['value']));
}

 

After a lot of thinking, I understand it a bit more.

 

I have a few questions if you don't mind:

 

1. How would I add more 'values' in the above PHP?

2. What would those values be? I assume the first would be the name of the form, then the rest would be the field ID's, correct?

3. Where would I place the above PHP in the script I already have, does that matter?

 

Thank you very much for your patience, I do apologise for my incompetence though.

 

MissileMoose