Steppio Posted May 20, 2008 Share Posted May 20, 2008 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. Quote Link to comment Share on other sites More sharing options...
bilis_money Posted May 20, 2008 Share Posted May 20, 2008 First advice is you need to make your code CLEAN. make it short. like SELECT * ... then use JOIN. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted May 20, 2008 Share Posted May 20, 2008 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. Quote Link to comment Share on other sites More sharing options...
Steppio Posted May 20, 2008 Author Share Posted May 20, 2008 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? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted May 20, 2008 Share Posted May 20, 2008 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"); Quote Link to comment Share on other sites More sharing options...
Steppio Posted May 20, 2008 Author Share Posted May 20, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.