Problem with PHP of MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gijs
Forum Commoner
Posts: 53
Joined: Wed Aug 28, 2002 4:05 am
Location: Belgium

Problem with PHP of MySQL

Post by gijs »

Hi,

I've a problem with PHP or MySQL, so don't kill me if I'm cross posting ! :wink:

Description of the problem:

a) In file 1 i've got a form. The variables are nicely posted to file 2, no problem.

b) In file 2 I want to perform the following things:

1) inserting the posted variables into an existing table
2) after this "insert query" performing a second insert query passing values from another table into the same table where the posted variables were inserted.

I used the following code to perfom the first "insert query":

Code: Select all

if (isset($_POSTї'action'])) {
switch ($_POSTї'action']) {
					
			case 'insert':

			// Check if all the variables are set
			if (isset($_POSTї'acco_id']) && isset($_POSTї'acco_name']) && isset($_POSTї'city_zip']) && isset($_POSTї'city_name'])) { 
			
			// Inserting the data posted from the form
			$address_insert_SQL = "INSERT INTO acco_address_finance (acco_id, company, zip, city) 
			VALUES('".$_POSTї'acco_id']."', '".$_POSTї'acco_name']."', '".$_POSTї'city_zip']."', '".$_POSTї'city_name']."')";
					
				
			// Displaying a message depending on the outcome of the query
			$bool = mysql_query($address_insert_SQL);
			if($bool == 1) echo "<SCRIPT LANGUAGE=Javascript>window.alert('The Hotel Address has been copied to the Financial Address.')</script>";
			if($bool <> 1) echo "<SCRIPT LANGUAGE=Javascript>window.alert('There has been an error.')</script>";
			
			
			// displaying error message if variables are not set
			&#125; else &#123;
			echo "ERROR the variables form the form Copy Hotel Address are not set !";
			&#125;
			
			
			
			// Kicking out the variable
			unset($_POST&#1111;'action']);
			
			
			// Stopping the switch statement
			break;
			
			default :
			echo "The default value for the switch statement";
			
&#125;
&#125; else &#123; 
   		  	// actions when $_POST&#1111;'action'] not set
			// not defined yet
&#125;
?>
This works fine. But when I insert the second "insert query" (after the fist)
it no longer performs any of them.
I used following code:

Code: Select all

// Inserting the data from address to address_finance
			$address_copy_SQL = "INSERT INTO acco_address_finance (street) 
			SELECT acco_address.street FROM acco_address 
			WHERE acco_address.acco_id='".$_POST&#1111;'acco_id']."'";
Strangely, when I de-activated the first "insert query" the second inserts a new record with the value "street" into to the table.
But it can never be correct data entry, because the address_finance-tabel doesn't contain an acco_id yet.

Would appreciate some help to solve this.

Thanks in advance,

Gijs
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

You could use the abs() function returning the absolute value of the input, although I would probably use JavaScript to validate the input.

Direwolf
User avatar
gijs
Forum Commoner
Posts: 53
Joined: Wed Aug 28, 2002 4:05 am
Location: Belgium

Post by gijs »

Thanks Johnm,

Your answer is without any doubt the solution to my problem, but I came up with one of my own. As I'm a complete newbie it's probably not the best way but is works.

What did I do in the switch-statement

case "insert'
Step 1: I run the first "insert query"

Step 2: run a check if the "insert query" was succesfull

Step 3: in stead of using a second "insert query", I query all the data corresponding to the $_POST['acco_id'] from the tabel acco_address.

Step 4: in stead of the second "insert query", I use an "update query" which copies all the selected data from acco_address into acco_address_financial.

Step 5: run a check if the "update query" was succesfull.

Here is the code I used:

Code: Select all

// Check if variable $action from the form is set
if (isset($_POST&#1111;'action'])) &#123;
switch ($_POST&#1111;'action']) &#123;
					
			case 'insert':

			// Check if all the variables are set
			if (isset($_POST&#1111;'acco_id']) && isset($_POST&#1111;'acco_name']) && isset($_POST&#1111;'city_zip']) && isset($_POST&#1111;'city_name'])) &#123; 
			
			// Inserting the data posted from the form
			$address_insert_SQL = "INSERT INTO acco_address_finance (acco_id, company, zip, city) 
			VALUES('".$_POST&#1111;'acco_id']."', '".$_POST&#1111;'acco_name']."', '".$_POST&#1111;'city_zip']."', '".$_POST&#1111;'city_name']."')";
			
			// Displaying a message depending on the outcome of the query
			$bool = mysql_query($address_insert_SQL);
			if($bool == 1) echo "<SCRIPT LANGUAGE=Javascript>window.alert('The Hotel Address Part 1 has been copied to the Financial Address.')</script>";
			if($bool <> 1) echo "<SCRIPT LANGUAGE=Javascript>window.alert('There has been an error.')</script>";
			
			
			// Inserting the data from Address to Address_finance
			// $address_copy_SQL = "INSERT INTO acco_address_finance (street) 
			// SELECT acco_address.street FROM acco_address 
			// WHERE acco_address.acco_id='".$_POST&#1111;'acco_id']."'";
			
			// Selecting the corresponding row from Address
			$address_select_SQL = "SELECT * FROM acco_address WHERE acco_id='".$_POST&#1111;'acco_id']."'";
			$address_result = mysql_query($address_select_SQL);
			$address = mysql_fetch_array($address_result);
			
			// Updating the record in the database
			$address_update_SQL = "UPDATE acco_address_finance SET street='".$address&#1111;'street']."', 
			number='".$address&#1111;'number']."', website='".$address&#1111;'website']."', tel_1='".$address&#1111;'tel_1']."',
			fax_1='".$address&#1111;'fax_1']."', email_1='".$address&#1111;'email_1']."', tel_2='".$address&#1111;'tel_2']."', fax_2='".$address&#1111;'fax_2']."',
			email_2='".$address&#1111;'email_2']."', resp_1_titel='".$address&#1111;'resp_1_titel']."', resp_1_first_name='".$address&#1111;'resp_1_first_name']."',
			resp_1_name='".$address&#1111;'resp_1_name']."', resp_1_function='".$address&#1111;'resp_1_function']."', 
			resp_1_tel='".$address&#1111;'resp_1_tel']."', resp_1_fax='".$address&#1111;'resp_1_fax']."',
			resp_1_mobile='".$address&#1111;'resp_1_mobile']."', resp_1_email='".$address&#1111;'resp_1_email']."',
			resp_2_titel='".$address&#1111;'resp_2_titel']."', resp_2_first_name='".$address&#1111;'resp_2_first_name']."',
			resp_2_name='".$address&#1111;'resp_2_name']."', resp_2_function='".$address&#1111;'resp_2_function']."',
			resp_2_tel='".$address&#1111;'resp_2_tel']."', resp_2_fax='".$address&#1111;'resp_2_fax']."',
			resp_2_mobile='".$address&#1111;'resp_2_mobile']."', resp_2_email='".$address&#1111;'resp_1_email']."' 
			WHERE acco_id='".$_POST&#1111;'acco_id']."'";
			
			// Displaying a message depending on the outcome of the query
			$bool = mysql_query($address_update_SQL);
			if($bool == 1) echo "<SCRIPT LANGUAGE=Javascript>window.alert('The Hotel Address Part 2 has been copied to the Financial Address.')</script>";
			if($bool <> 1) echo "<SCRIPT LANGUAGE=Javascript>window.alert('There has been an error.')</script>";
			
			
			// displaying error message if variables are not set
			&#125; else &#123;
			echo "ERROR the variables form the form Copy Hotel Address are not set !";
			&#125;
			
			
			
			// Kicking out the variable
			unset($_POST&#1111;'action']);
			
			
			// Stopping the switch statement
			break;
			
			default :
			echo "The default value for the switch statement";
			
&#125;
&#125; else &#123; 
   		  	// actions when $_POST&#1111;'action'] not set
			// not defined yet
&#125;
If someone has any suggestions for improvement, your welcome.

Thanks,

Gijs
Post Reply