Loopage,.. god I hate loops

[piznac]

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.

[per1os]

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;

[piznac]

thanks,.. but nah the table structures are not exactly the same.

[per1os]

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.

[piznac]

Ok thanks,.. I'll give that a go.

[piznac]

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());
}

[per1os]

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.

[piznac]

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

[per1os]

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.

[piznac]

hmmm,.. ok sounds logical to me thanks for the help!