MySQL syntax or crap PHP programming?

[Steppio]

I have a giant MySQL query thats processed by PHP and then put into two tables in the database, the only problem being it adds to 1 table and not the other. The query is as follows (NB: The Majority of this can be ignored, the problem is likely at the bottom):

 

 $id = $conn->insert_id;
$linkid = $conn->insert_id;
$result1 = $conn->query("INSERT into t_orders (orderid, orderdate, ordercat, userid, main_head_type, idler_head_type, 
bridge_centres, length_crs_to_crs, blade_fitting_dia, inward_bend, build_type, overall_length, main_bend_length, 
i_bend, bend_bolt_length, bend_degree, bend_end_hook, i_clip, crank, spindle_a, a_dim, spindle_b, b_dim, 
crs_of_pin, pin_dia, pin_length, motor_shaft_size, crs_of_hole, hole_dia, thread_hand, arm_straight, 
main_spindle_dia, main_spindle_thread_dia, idler_spindle_thread_dia, idler_spindle_dia, main_liner_length, 
idler_liner_length, brop_arm_crs, conn_link, idler_drop_arm, spindle_dia, thread_dia, liner_length, drop_arm, 
cranked_straight, up_down, screen_height_a, screen_height_b, mounting_distance, spindle_centers_d, 
spindle_position, top_bottom, 12_24, minimum_liner, left_right, open_length, tube_length, stroke_length, 
tube_dia, end_fittings, newtons, rod_dia) VALUES 
								('" . $id . "','" . $orderdate . "','" . $ordercat . "','" . $userid . "','" . $main_head_type . "',
								'" . $idler_head_type . "','" . $bridge_centres . "','" . $length_crs_to_crs . "',
								'" . $blade_fitting_dia . "','" . $inward_bend . "','" . $build_type . "',
								'" . $overall_length . "','" . $main_bend_length . "','" . $i_bend . "',
								'" . $bend_bolt_length . "','" . $bend_degree . "','" . $bend_end_hook . "',
								'" . $i_clip . "','" . $crank . "','" . $spindle_a . "','" . $a_dim . "',
								'" . $spindle_b . "','" . $b_dim . "',
								'" . $crs_of_pin . "','" . $pin_dia . "','" . $pin_length . "','" . $motor_shaft_size . "',
								'" . $crs_of_hole . "','" . $hole_dia . "','" . $thread_hand . "',
								'" . $arm_straight . "','" . $main_spindle_dia . "','" . $main_spindle_thread_dia . "',
								'" . $idler_spindle_thread_dia . "','" . $idler_spindle_dia . "',
								'" . $main_liner_length . "','" . $idler_liner_length . "',
								'" . $drop_arm_crs . "','" . $conn_link . "','" . $idler_drop_arm . "',
								'" . $spindle_dia . "','" . $thread_dia . "','" . $liner_length . "',
								'" . $drop_arm . "','" . $cranked_straight . "','" . $up_down . "',
								'" . $screen_height_a . "','" . $screen_height_b . "','" . $mounting_distance . "',
								'" . $spindle_centers_d . "','" . $spindle_position . "','" . $top_bottom . "',
								'" . $volt . "','" . $minimum_liner . "','" . $left_right . "','" . $open_length . "',
								'" . $tube_length . "','" . $stroke_length . "','" . $tube_dia . "',
								'" . $end_fittings . "','" . $newtons . "','" . $rod_dia . "'); INSERT into t_o_link (linkid, orderid, userid) VALUES ('" . $linkid . "','" . $id . "','" . $userid . "')");
if (!$result1)
	echo '<h4>Could not register order in database '
								.'- please try again later.</h4>';

 

Now everythings swell and candylicious up until i attempt to insert data into the t_o_link table, probably due to my crappy programming. The result returns negative and as such throws an error. Is there anyway to do a query like this were another query is added after another?

The reason i dont seperate the two querys is that once i've used $conn->insert_id for the variable $id, its left in the first query and i cant figure out a way to get that same id into the second table.

 

Any ideas or solutions would be helpful and very gratefully accepted.

Thank you in advance. 

[bilis_money]

First advice is you need to make your code CLEAN.

 

make it short. like SELECT * ...

 

then use JOIN.

[PFMaBiSmAd]

The php mysql client library does not execute multiple queries that are separated by ;

 

You need to form each query and execute it separately.

 

From the php manual:

 

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

[Steppio]

So how would i get the same ID for each new order to be saved into the t_o_link table if php does'nt handle multiple queries?

[PFMaBiSmAd]

Execute two queries in a row. 

 

What you have now -

 

$id = $conn->insert_id;
$linkid = $conn->insert_id;
$result1 = $conn->query("INSERT into ... rest of query 1; INSERT into ... rest of query 2");

 

Change it to -

 

$id = $conn->insert_id;
$linkid = $conn->insert_id;

$result1 = $conn->query("INSERT into ... rest of query 1");
....
$result1 = $conn->query("INSERT into ... rest of query 2");

[Steppio]

The problem is that when i define $id = $conn->insert_id that id is specific / primary to the orders table, so running another query would not bring that variable with it, i need to stick that ID into another table to be able to cross-link the tables t_users and t_orders through t_o_link.

Maybe i'm going about this the wrong way