[SOLVED] Help with multiple query syntax

[poleposters]

Hi,

 

I have a script which needs to make 3 seperate insert queries. Originally the script used only a single query and ended with

if (mysql_affected_rows() == 1)

 

The script works fine with the 3 insert queries but I just want to make sure I have structured it properly to uses the mysql_affected rows.

 

Is this right?

 

 

 

 

 

 

 

 

$query = "INSERT INTO users (business_id, email, pass,business, business_type, first_name, last_name, active,paid_listing, registration_date) VALUES ('','$e', SHA('$p'),'$bn', '$cat','$fn', '$ln', '$a',0, NOW() )";		
		$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

		$bid=mysql_insert_id();

		$query2 = "INSERT INTO address (address_id,business_id,unit_number,street_number, address, postcode) VALUES ('','$bid','$bn','$un','$sn' '$ad','$pc')";		
		$result2 = mysql_query ($query2) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

		$bid2=mysql_insert_id();

		$query3 = "INSERT INTO phone (phone_id,business_id,phone_one) VALUES ('','$bid2','$ph')";		
		$result3 = mysql_query ($query2) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());





		if (mysql_affected_rows() == 1) { // If it ran OK.

[GingerRobot]

Are you displaying your errors to the user? If you are, there's not much point in checking the affected rows, since you throw an error if the INSERT query fails.

 

If your not displaying your errors, you could always make the call to mysql_affected_rows() 3 times - once after each query - and increase a counter with its result. Check that counter at the end. It should equal 3.

 

Of course, the above wouldn't tell you which query failed, but what you do depends how you want it to work.

[poleposters]

Ahhh. Of course.Does this look right?

 

                       

$count=0
                        $query = "INSERT INTO users (business_id, email, pass,business, business_type, first_name, last_name, active,paid_listing, registration_date) VALUES ('','$e', SHA('$p'),'$bn', '$cat','$fn', '$ln', '$a',0, NOW() )";		
		$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

                        if (mysql_affected_rows() == 1) {$count ++} 

		$bid=mysql_insert_id();

		$query2 = "INSERT INTO address (address_id,business_id,unit_number,street_number, address, postcode) VALUES  ('','$bid','$bn','$un','$sn' '$ad','$pc')";		
		$result2 = mysql_query ($query2) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

                        if (mysql_affected_rows() == 1) {$count ++}


		$query3 = "INSERT INTO phone (phone_id,business_id,phone_one) VALUES ('','$bid2','$ph')";		
		$result3 = mysql_query ($query2) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

                        if (mysql_affected_rows() == 1) {$count ++}

                        if($count=3){----do this.}

 

                        Not knowing which query failed could be a problem. If the first query succeeds and the second fails I'm going to have records in the first table and no linked data in the second table. In that case I would need to delete the record in the first table so that the user can try again.

 

I'm thinking I could structure it so that the second query only executes if the first query is successful and then if the second query fails deleting the record where id=mysql_insert_id.

 

Does this sound like a good idea? Is there an easier way.Or even better is it possible to enter the records in all three tables with a single query?

[poleposters]

Also, do i need to put the query name into the mysql_affected rows() brackets.

 

ie mysql_affected_rows($result)

 

??

[GingerRobot]

Ahhh. Of course.Does this look right?

 

Yep.

 

I'm thinking I could structure it so that the second query only executes if the first query is successful and then if the second query fails deleting the record where id=mysql_insert_id.

 

Sounds good to me.

 

Or even better is it possible to enter the records in all three tables with a single query?

 

Not as far as I know.

 

Also, do i need to put the query name into the mysql_affected rows() brackets.

 

No. The function takes one optional parameter - the mysql connection. You could have answered that for yourself by looking on the manual page.