piznac Posted June 1, 2007 Share Posted June 1, 2007 Ok,.. seems Im having problems with another loop. I want to take a number of lines from one table in the DB and insert them into another. Please tell me if there is an easier way to do this. I didn't think it would be any big deal but something is not right. mysql_select_db($database_pro, $pro); $query_user = sprintf("SELECT * FROM `users_fish` WHERE `c_num` = '$c'"); $user = mysql_query($query_user, $pro) or die(mysql_error()); $row_user = mysql_fetch_assoc($user); $totalRows_user = mysql_num_rows($user); mysql_select_db($database_pro, $pro); $query_order = sprintf("SELECT * FROM `fishy_cart` WHERE `cookieId` = '$c'"); $order = mysql_query($query_order, $pro) or die(mysql_error()); $row_order = mysql_fetch_assoc($order); $totalRows_order = mysql_num_rows($order); $u_num = $row_user['u_num']; $u_name = $row_user['name']; $order_num0 = date("mdy"); $order_num = "$order_num0$row_user[u_num]"; for($i=0;$i<count($row_order['i_num']);$i++) { $i_num = $row_order['i_num']; $s_name = $row_order['s_name']; $price = $row_order['price'][$i]; $quanity = $row_order['quanity'][$i]; $lot = $row_order['lot'][$i]; mysql_select_db($database_pro, $pro); $insert_sql = "INSERT INTO `itsfishy_products`.`fishy_ordata` (`user_id`, `user_name`, `order_id`, `item_num`, `item_name`, `item_price`, `item_qty`, `lot_price`) VALUES ('$u_num', '$u_name', '$order_num', '$i_num', '$s_name', '$price', '$quanity ', '$lot') "; $doit = mysql_query($insert_sql, $pro) or die(mysql_error()); } It seems Im only getting the first record. There I suppose the loop isnt working the way I planned. I always seem to have issues with these d$#@ loops. Should I use a foreach loop inside the for loop? Or am I way off track? BTW all the SQL statements seem to be working fine as I have them echo'ing on other parts of the page. Quote Link to comment Share on other sites More sharing options...
per1os Posted June 1, 2007 Share Posted June 1, 2007 If the structures are the same I think you can do something like this: INSERT INTO table_name (SELECT * from other_table_name); Unsure on the syntax but maybe google select within insert mysql http://dev.mysql.com/doc/refman/5.0/en/insert-select.html INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; Quote Link to comment Share on other sites More sharing options...
piznac Posted June 1, 2007 Author Share Posted June 1, 2007 thanks,.. but nah the table structures are not exactly the same. Quote Link to comment Share on other sites More sharing options...
per1os Posted June 1, 2007 Share Posted June 1, 2007 If that solution does not workout for you try this: <?php mysql_select_db($database_pro, $pro); $query_user = sprintf("SELECT * FROM `users_fish` WHERE `c_num` = '$c'"); $user = mysql_query($query_user, $pro) or die(mysql_error()); $row_user = mysql_fetch_assoc($user); $totalRows_user = mysql_num_rows($user); $query_order = sprintf("SELECT * FROM `fishy_cart` WHERE `cookieId` = '$c'"); $order = mysql_query($query_order, $pro) or die(mysql_error()); $totalRows_order = mysql_num_rows($order); $u_num = $row_user['u_num']; $u_name = $row_user['name']; $order_num0 = date("mdy"); $order_num = "$order_num0$row_user[u_num]"; while ($row_order = mysql_fetch_assoc($order)) { $i_num = $row_order['i_num']; $s_name = $row_order['s_name']; $price = $row_order['price']; $quanity = $row_order['quanity']; $lot = $row_order['lot']; $insert_sql = "INSERT INTO `itsfishy_products`.`fishy_ordata` (`user_id`, `user_name`, `order_id`, `item_num`, `item_name`, `item_price`, `item_qty`, `lot_price`) VALUES ('$u_num', '$u_name', '$order_num', '$i_num', '$s_name', '$price', '$quanity ', '$lot') "; $doit = mysql_query($insert_sql, $pro) or die(mysql_error()); } ?> That should work, and btw you only need to do the mysql_select_db once throughout the lifetime of a script. Quote Link to comment Share on other sites More sharing options...
piznac Posted June 1, 2007 Author Share Posted June 1, 2007 Ok thanks,.. I'll give that a go. Quote Link to comment Share on other sites More sharing options...
piznac Posted June 1, 2007 Author Share Posted June 1, 2007 Now Im only getting the last record.... :-\ mysql_select_db($database_pro, $pro); $query_user = sprintf("SELECT * FROM `users_fish` WHERE `c_num` = '$c'"); $user = mysql_query($query_user, $pro) or die(mysql_error()); $row_user = mysql_fetch_assoc($user); $totalRows_user = mysql_num_rows($user); mysql_select_db($database_pro, $pro); $query_order = sprintf("SELECT * FROM `fishy_cart` WHERE `cookieId` = '$c'"); $order = mysql_query($query_order, $pro) or die(mysql_error()); $row_order = mysql_fetch_assoc($order); $totalRows_order = mysql_num_rows($order); $u_num = $row_user['u_num']; $u_name = $row_user['name']; $order_num0 = date("mdy"); $order_num = "$order_num0$row_user[u_num]"; while ($row_order = mysql_fetch_assoc($order)) { $i_num = $row_order['i_num']; $s_name = $row_order['s_name']; $price = $row_order['price']; $quanity = $row_order['quanity']; $lot = $row_order['lot']; mysql_select_db($database_pro, $pro); $insert_sql = "INSERT INTO `itsfishy_products`.`fishy_ordata` (`user_id`, `user_name`, `order_id`, `item_num`, `item_name`, `item_price`, `item_qty`, `lot_price`) VALUES ('$u_num', '$u_name', '$order_num', '$i_num', '$s_name', '$price', '$quanity ', '$lot') "; $doit = mysql_query($insert_sql, $pro) or die(mysql_error()); } Quote Link to comment Share on other sites More sharing options...
per1os Posted June 1, 2007 Share Posted June 1, 2007 Did you try my code exactly how I put it? Because it seems you reverted back to your old code with the exception of the while loop. <?php mysql_select_db($database_pro, $pro); // this is the only time this needs to be done. $query_user = sprintf("SELECT * FROM `users_fish` WHERE `c_num` = '$c'"); $user = mysql_query($query_user, $pro) or die(mysql_error()); $row_user = mysql_fetch_assoc($user); $totalRows_user = mysql_num_rows($user); //mysql_select_db($database_pro, $pro); you only need to do this ONCE $query_order = sprintf("SELECT * FROM `fishy_cart` WHERE `cookieId` = '$c'"); $order = mysql_query($query_order, $pro) or die(mysql_error()); // $row_order = mysql_fetch_assoc($order); this does not need to be done here it will be taken care of in the while loop. $totalRows_order = mysql_num_rows($order); $u_num = $row_user['u_num']; $u_name = $row_user['name']; $order_num0 = date("mdy"); $order_num = "$order_num0$row_user[u_num]"; while ($row_order = mysql_fetch_assoc($order)) { $i_num = $row_order['i_num']; $s_name = $row_order['s_name']; $price = $row_order['price']; $quanity = $row_order['quanity']; $lot = $row_order['lot']; // mysql_select_db($database_pro, $pro); again this was already done in this script, it does not need to be done again. $insert_sql = "INSERT INTO `itsfishy_products`.`fishy_ordata` (`user_id`, `user_name`, `order_id`, `item_num`, `item_name`, `item_price`, `item_qty`, `lot_price`) VALUES ('$u_num', '$u_name', '$order_num', '$i_num', '$s_name', '$price', '$quanity ', '$lot') "; $doit = mysql_query($insert_sql, $pro) or die(mysql_error()); } ?> Given that it should work just fine as long as you keep it the same. Quote Link to comment Share on other sites More sharing options...
piznac Posted June 1, 2007 Author Share Posted June 1, 2007 Because it seems you reverted back to your old code with the exception of the while loop. Ahh yes,.. indeed I did. Sorry dude,.. trying to go to fast. Thanks! But Im not seeing what else was wrong. Do the mysql_select_db or fetch_assoc really matter that much if they are duplicated? Would it make the script fail,.. or is there something else i misssed? Thanks Quote Link to comment Share on other sites More sharing options...
per1os Posted June 1, 2007 Share Posted June 1, 2007 Not sure about the db, thats just very inefficient but the first fetch_assoc would make it fail because it throws the loop off balance for the while. You end up missing at least 1 record. Quote Link to comment Share on other sites More sharing options...
piznac Posted June 1, 2007 Author Share Posted June 1, 2007 hmmm,.. ok sounds logical to me thanks for the help! 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.