deleting from 1 table whilst adding to another

Simonhurst

Hi Guys

is this possible i have one table that stores serial numbers and when these are added to the main db table is there a way to automatically delete it from this temp table so it wont show in the list of available serials ?

but will show in the active table for stats and the likes
sorry if ive written this down confusingly

TIA

zhabala

You can do it with triggers or within a transaction as two SQl statements (INSERT and DELETE)

Zdenek

Simonhurst

hi thanks for the quick reply but could you give me an example of how this would look like please

zhabala

Triggers are available in MySQL from verson 5.0. It is created on a table where you can tell, what should be done after the row is inserted,updated or deleted.

Second way is use transactions:

BEGIN WORK;
INSER INTO table ( column ) value ( $serial_id );
DELETE FROM table2 WHERE column = $serial_id;
COMMIT WORK; or ROLLBACK WORK

Zdenek

Simonhurst

hiya thanks for this

ok heres what ive done

$sql = INSERT INTO 873wing ( plane_serial ) value ( $sel_aps );
DELETE FROM serial WHERE column = $planeid;
			WHERE callsign = $callsign;

include("conf.php");


$sql_result = mysql_query($sql) or die(mysql_error()); 

	if (!$sql_result) {  

		echo "<P>Couldn't modify record!";

	} else {

		echo "



<h3><p align=Center>The following modifications have been made:</p>
</h3>

<table cellspacing=5 cellpadding=5 align=center>
<tr>
<td valign=center><strong>Callsign:</strong></td>
<td valign=center>$callsign</td>
</tr>
<tr>
<td valign=center><strong>Has been assingned:</strong></td>
<td valign=center>$plane_serial</td>
</tr>
</table>
	</p>
"; 
		}

but i end up with a blank page any ideas where i messed up ?? this is all within the <?php ?> tags

zhabala

This:

$sql = INSERT INTO 873wing ( plane_serial ) value ( $sel_aps ); 
DELETE FROM serial WHERE column = $planeid; 
            WHERE callsign = $callsign;

is totally mad. It has to look like:

$sql = "INSERT INTO 873wing ( plane_serial ) value ( $sel_aps )";
$sql_result = mysql_query($sql) or die(mysql_error());

$sql = "DELETE FROM serial WHERE column = $planeid";
$sql_result = mysql_query($sql) or die(mysql_error());

but your wrong code should output some error or warning.

Simonhurst

hiya

sorry mate you must think me the thickest guy alive 😃

just replaced that code of mine with yours and i get

You have an error in your SQL syntax near 'value ()' at line 1

just shoot me please

zhabala

it means that variable $sel_aps does not contain any information.

Can you post whole code here ?

rgermain

You also forgot the single quotes in that line:


<?

$sql = "INSERT INTO 873wing ( plane_serial ) value ( $sel_aps )"; 
$sql_result = mysql_query($sql) or die(mysql_error()); 

?>

<?
// SHOULD LOOK LIKE

$sql = "INSERT INTO 873wing (plane_serial) VALUE('$sel_aps')";
$sql_result = mysql_query($sql) or die(mysql_error()); 

?>

zhabala

No no, single quotes are necessary only when you are inserting string variables, when it is number, they don't have to be there. Because then the DB server must do the conversion.

Main problem is that there is the value missing.

Zdenek

Simonhurst

sure here it is 🙂

echo "	<FORM method=\"POST\" action=\"aps.php\">\n";
echo "	<tr>\n";
echo"  <td width=\"65%\"><b>Pilot For Plane ID:</b></td>\n";
echo "		<td VALIGN=\"MIDDLE\">\n";
echo "			<select name=\"sel_user\" STYLE=\"WIDTH:100%;\">\n";
echo "				<option value=\"\"> -- Select a Pilot -- </option>\n";

				$sql = "SELECT callsign 
		from 873wing ";

	$sql_result = mysql_query($sql)
		or die("Couldn't execute query.");


				while ($row = mysql_fetch_array($sql_result)){
  $callsign = $row ["callsign"];

					echo "				<option value=\"$callsign\">$callsign</option>";
					}

echo "			</select>\n";
echo "		</td>\n";
echo"  <tr> \n";
echo"  <td width=\"65%\">Assign Plane Id :</td>\n";
echo "		<td VALIGN=\"MIDDLE\">\n";
echo "			<select name=\"sel_aps\" STYLE=\"WIDTH:100%;\">\n";
echo "				<option value=\"\"> -- Select -- </option>\n";

				$sql = "SELECT planeid 
		from serial";

	$sql_result = mysql_query($sql)
		or die("Couldn't execute query.");


				while ($row = mysql_fetch_array($sql_result)){
  $planeid = $row ["planeid"];

					echo "				<option value=\"$planeid\">$planeid</option>";
					}

echo "			</select>\n";
echo "		</td>\n";
echo "	</tr>\n";
echo"  <tr> \n";
echo "<td width=\"50%\"><INPUT type=submit value=\"Assign\"></td>";
echo "  </tr>\n";
echo "	<tr>\n";
echo "	</form>\n";

this goes to here

<?PHP // Try to use the full <?PHP tag, can cause some problems on some systems

include("conf.php");

if (!$sel_user || !$sel_aps) {
			header("Location: modify.php");
			exit;
		}

/* Start The Content on the Page ----------------------------------------------------- */

$sql = "SELECT planeid FROM serial  WHERE planeid= $sel_aps";

include("conf.php");


$sql_result = mysql_query($sql) or die(mysql_error()); 

	if (!$sql_result) {  

		echo "<P>Couldn't get item!";

	} else {

	$row 		= mysql_fetch_array($sql_result);
	$planeid 	= $row ["planeid"];
	$callsign = $row ["callsign"];




echo "<table BORDER=\"1\" ALIGN=\"CENTER\" cellspacing=\"4\" cellpadding=\"4\">\n";
echo "	<FORM method=\"POST\" action=\"aps2.php\">\n";
echo "	<tr>\n";
echo "		<td align=center COLSPAN=\"3\">\n";
echo "			<H3>You have selected to Assign <br><font size=5 color=#FF0000> $sel_user</H3></font></td>\n";
echo "			<INPUT TYPE=\"HIDDEN\" NAME=\"callsign\" VALUE=\"$sel_user\">\n";
echo "	</tr>\n";
echo "	<tr>\n";
echo "		<td valign=MIDDLE>\n";
echo "			<strong>Assigned serial #:</strong></td>\n";
echo "		<td VALIGN=\"MIDDLE\">\n";
echo "			<INPUT type=\"text\" name=\"serial\" value=\"$sel_aps\" size=8 maxlength=8></TD>\n";
echo "	<tr>\n";
echo "		<td valign=MIDDLE ALIGN=\"CENTER\" COLSPAN=\"2\">\n";
echo "			<INPUT type=\"submit\" value=\"Assign Plane ID\"></td>\n";
echo "	</tr>\n";
echo "	</FORM>\n";
echo "</table>\n";



	}
?>

then the last page which is the one i'am having the problem with

<?

$sql = "INSERT INTO 873wing ( plane_serial ) value ($sel_aps)"; 
$sql_result = mysql_query($sql) or die(mysql_error()); 

$sql = "DELETE FROM serial WHERE column = $planeid"; 
$sql_result = mysql_query($sql) or die(mysql_error()); 


include("conf.php");


$sql_result = mysql_query($sql) or die(mysql_error()); 

	if (!$sql_result) {  

		echo "<P>Couldn't modify record!";

	} else {

		echo "



<h3><p align=Center>The following modifications have been made:</p></h3>

<table cellspacing=5 cellpadding=5 align=center>
<tr>
<td valign=center><strong>Callsign:</strong></td>
<td valign=center>$callsign</td>
</tr>
<tr>
<td valign=center><strong>Has been assingned:</strong></td>
<td valign=center>$plane_serial</td>
</tr>
</table>
	</p>

<p align=center><a href=modify.php> logistics page </p></a>
<p align=center><a href=log.php>873rd Logbook</p></a>

"; 
		}

?>

thanks for helping me out here

Simonhurst

Originally posted by rgermain
You also forgot the single quotes in that line:


<?

$sql = "INSERT INTO 873wing ( plane_serial ) value ( $sel_aps )"; 
$sql_result = mysql_query($sql) or die(mysql_error()); 

?>

<?
// SHOULD LOOK LIKE

$sql = "INSERT INTO 873wing (plane_serial) VALUE('$sel_aps')";
$sql_result = mysql_query($sql) or die(mysql_error()); 

?>

[/B]

tried it like that as i thought that was the cause aswell but didnt work

zhabala

It's quite clear. From the third php file named aps.php the $sel_aps is passed as a $serial variable. Watch out the input hidden variables.

Zdenek

zhabala

sorry no input hidden, but input text named serial.

Simonhurst

so it dosnt take the $sel_aps as the value but the name is that right ?

zhabala

No, this:
[php
echo " <td VALIGN=\"MIDDLE\">\n";
echo " <INPUT type=\"text\" name=\"serial\" value=\"$sel_aps\" size=8 maxlength=8></TD>\n";
echo " <tr>\n";
echo " <td valign=MIDDLE ALIGN=\"CENTER\" COLSPAN=\"2\">\n";
echo " <INPUT type=\"submit\" value=\"Assign Plane ID\"></td>\n";
[/code]

means that the user can edit the value stored in $sel_aps, but when the form is submitted, the value is stored in variable named serial. So in the last php script where is the insert statement you have to use $serial instead of $sel_aps. Or you have to name the variable sel_aps instead of serial. Like this :

echo "            <INPUT type=\"text\" name=\"sel_aps
" value=\"$sel_aps\" size=8 maxlength=8></TD>\n";

Simonhurst

okidoki getting closer now 😃

now i get this error

You have an error in your SQL syntax near 'value (93-111)' at line 1

so its picking up the vairiable now

zhabala

What data type is the plane_serial column in 873wing table ?

And another mistake:

$sql = "INSERT INTO 873wing ( plane_serial ) value ( $sel_aps )";

should be

$sql = "INSERT INTO 873wing ( plane_serial ) values ( $sel_aps )";

watch out the value/values

Simonhurst

data type for plane_serial is Varchar(10)

changed the value to values now it just adds -18 to the plane_serial field but not to the assigned callsign ie it adds a new field

zhabala

You mean it adds new row.

Sure it does, because you are using INSERT statement,
probably you have to use UPDATE statement.

It is quite complex to help you ;-)