[Resolved] Updating / Inserting into two tables

vaavi8r

I am trying to use one script to insert values from a form into one table (which is working fine). In addition, I want to take one of the variables ($date) and UPDATE another table with that information.

Here is the script so far....

<?php 
$db = mysql_connect("localhost", "maceo100","scarlet"); 
mysql_select_db("margaritaair_com",$db);

$date=$_POST['date'];
$name=$_POST['name'];
$macid=$_POST['macid'];
$hours=$_POST['hours'];
$type=$_POST['type'];
$departure=$_POST['departure'];
$destination=$_POST['destination'];
$charterno=$_POST['charterno'];
$charterdoll=$_POST['charterdoll'];
$comment=$_POST['comment'];
$image=$_POST['image'];

$sql = "INSERT INTO flightlog (date, name, macid, hours, type, departure, destination, charterno, charterdoll, comment, image) VALUES ('$date', '$name', '$macid','$hours','$type','$departure','$destination','$charterno','$charterdoll','$comment','$image')";
$result = mysql_query($sql); 
if ($result) {
echo ("PIREP recorded successfully");
} 

SELECT * FROM pilots WHERE macid='$macid'
$sql = "UPDATE pilots (pirepdate) VALUES ('$date') WHERE macid="$macid";
$result = mysql_query($sql); 
if ($result) {
echo ("Thank You for flying with Margarita Air Club");
} 



MySQL_close()
?>

I want to update the record titled 'pirepdate' in the table 'pilots' with the same date that was used to insert into 'flightlog' as 'date' for the record that has the same 'macid' (the users member number).

I can get the script to do one or the other, but not both.
Any ideas?

devinemke

$db = mysql_connect('localhost', 'maceo100', 'scarlet');
mysql_select_db('margaritaair_com', $db);

$date = $_POST['date'];
$name = $_POST['name'];
$macid = $_POST['macid'];
$hours = $_POST['hours'];
$type = $_POST['type'];
$departure = $_POST['departure'];
$destination = $_POST['destination'];
$charterno = $_POST['charterno'];
$charterdoll = $_POST['charterdoll'];
$comment = $_POST['comment'];
$image = $_POST['image'];

$sql = "INSERT INTO flightlog (date, name, macid, hours, type, departure, destination, charterno, charterdoll, comment, image) VALUES ('$date', '$name',  '$macid','$hours','$type','$departure','$destination','$charterno','$charterdoll','$comment','$image')";
$result = mysql_query($sql);  

if ($result) {echo 'PIREP recorded successfully';}

$sql = "UPDATE pilots (pirepdate) VALUES ('$date') WHERE macid = '$macid'";
$result = mysql_query($sql); 
if ($result) {echo 'Thank You for flying with Margarita Air Club';}

vaavi8r

Thanks for the reply....

I have tried that script before, but get no result for the UPDATE portion of the script.

The entry is recorded in 'flightlog' but the value in 'pirepdate' is not updated in 'pilots'.......😕

Any idea why?

devinemke

Originally posted by vaavi8r
The entry is recorded in 'flightlog' but the value in 'pirepdate' is not updated in 'pilots'. Any idea why?

perhaps because there is no record in the "pilots" table that matches the given "macid". or perhaps there is no "pirepdate" field in the "pilots" table. do this and see what the error says:

$sql = "UPDATE pilots (pirepdate) VALUES ('$date') WHERE macid = '$macid'";
$result = mysql_query($sql) or die('sql: ' . $sql . '<br>error: ' . mysql_error());

vaavi8r

OK, it says:

PIREP recorded successfullysql: UPDATE pilots (pirepdate) VALUES ('2004-7-27') WHERE macid = 'MAC100'
error: You have an error in your SQL syntax near '(pirepdate) VALUES ('2004-7-27') WHERE macid = 'MAC100'' at line 1

devinemke

try it like this:

$sql = "UPDATE pilots SET pirepdate = '$date' WHERE macid = '$macid'";

vaavi8r

Yep, thats it.....

Got a bit cute with the script as you can see:

$sql = "UPDATE pilots SET pirepdate = '$date' WHERE macid = '$macid'";
$result = mysql_query($sql) or die('sql: ' . $sql . '<br>error: ' . mysql_error());
if ($result) {
echo ("<P><b>Thank You for flying with Margarita Air<br>Please return the stewardess to her fully upright and locked position!<img src='phpBB/images/smiles/icon_biggrin.gif'</b>");
}

Thanks for the great help!!:p :p