[RESOLVED] problem with MySQL save/update script

[retro]

I posted this question on another forum, but as it seems to have got lost in the depths over there, I thought I'd try here!

I have a page, new.php, which either adds a new user to a database, or edits an existing user (depending on how you accessed it.. it uses $_GET['id]). It has various input boxes, as you'd expect for name, address etc. The action for the form is save.php.

Here is the content of save.php:

<?php
header("Location: ./");
?>

<?
//Connect to DB & check for errors
include("dbinfo.inc.php");
$connection = mysql_connect($server,$username,$password);
if (!$connection)
{
  echo mysql_errno().": ".mysql_error()."<br/>";
  exit;
}
if(!mysql_select_db($database))
{
  echo("Database not found<br>");
}

//retrieve post data
$id = $_POST["id"];
$FirstName = $_POST["firstname"];
$MiddleName = $_POST["middlename"];
$LastName = $_POST["lastname"];
$Town = $_POST["town"];
$Street = $_POST["street"];
$PostCode = $_POST["postcode"];
$houseno = $_POST["houseno"];
$MobilePhone = $_POST["mobilephone"];
$HomePhone = $_POST["homephone"];
$OfficePhone = $_POST["officephone"];
$Email = $_POST["email"];
$Homepage = $_POST["homepage"];

if ($id != '') 
{
  //Edit current record
  $query = "UPDATE customers SET firstname='" . $FirstName . "',middlename='" . $MiddleName . "',lastname='" . $LastName . "',
  town='" . $Town . "',street='" . $Street . "',postcode='" . $PostCode . "',houseno='" . $houseno . "',
  mobilehone='" . $MobilePhone . "',homephone='" . $HomePhone . "',officephone='" . $OfficePhone . "',
  email='" . $Email . "',homepage='" . $Homepage . "' WHERE id=" . $id;
}
else 
{
  //Add new record
  $query = "INSERT INTO customers(firstname,middlename,lastname,town,street,postcode,houseno,mobilephone,homephone,officephone,email,homepage)
  VALUES
  (
  '" . $FirstName . "',
  '" . $MiddleName . "',
  '" . $LastName . "',
  '" . $Town . "',
  '" . $Street . "',
  '" . $PostCode . "',
  '" . $houseno . "',
  '" . $MobilePhone . "',
  '" . $HomePhone . "',
  '" . $OfficePhone . "',
  '" . $Email . "',
  '" . $Homepage . "'
  )";
}
//Update DB
$result = mysql_query ($query) or die("SQL Error: " . mysql_error());
mysql_close();
?>

I have two problems...

1. Creating a new user works fine. Editing a user puts their details into the input boxes, which can be altered. However, clicking save doesn't update the record. It seems to perform fine (going back to the home page) with no errors, but the records haven't updated.

2. I want to have it so that once the page has been submitted, it goes to the order page (order.php), filling in the customer details automatically.

I can do this on an existing contact by using a button with

onclick="window.location.href='order.php?id=<?php echo $id ?>'"

However, as the ID field (auto incrementing) has not been created in the database, I cannot do this within save.php (or I would edit the header location).

Could anyone make any suggestions on either of these problems?

Thanks in advance for any advice!

[leatherback]

what does an echo $query give you?

[retro]

Ahh, thanks! That solved the first part - it was a simple spelling error!

12SQL Error: Unknown column 'mobilehone' in 'field list'

Of course, that should be mobilephone !!!

Do you have any ideas about the second part? Is it even possible?

I guess it would be the same as showing the record just entered automatically after saving it. That would need to go to:

contact.php?id=x&mode=read

Obviously, x = the id no, which is an auto incrementing number in the db.

[laserlight]

You can find out the new id with mysql_insert_id(), but I would encourage you to use the PDO extension instead of the MySQL extension.

[retro]

Thanks for that! It looks like it'll do just what I wanted.

Of course, I will take all suggestions on board - I am still learning and any advice from the pros is duly noted. However, I am unfamiliar with the PDO extension you talk of. Could you explain, or link me to somewhere that explains in layman's terms? I find the PHP manual rather confusing at times for new concepts!

Thanks!

[laserlight]

However, I am unfamiliar with the PDO extension you talk of. Could you explain, or link me to somewhere that explains in layman's terms? I find the PHP manual rather confusing at times for new concepts!

The PDO extension "defines a lightweight, consistent interface for accessing databases in PHP". In other words, once you know the PDO extension, you can use it with MySQL, PostgreSQL, SQLite, and other database engines with minimal differences with respect to the PHP code.

Where MySQL is concerned, the PDO extension allows you to take advantage of prepared statements, which is a superior way to escape your user provided data (and indeed you failed to take this into account), besides allowing for SQL statements to be reused more efficiently.

[retro]

I'll get reading - thanks for the info!