INSERT into a Table from within a WHILE/FOREACH loop?

PHP
1

I have a Table value stored as array (121-1,231-4,323-2, where 121 is the product_id and 1 is the quantity etc.) I can retieve and split these and echo them out O.K., but I am having trouble inserting the values into another Table.

It goes through the first iteration well and it inserts the first pair into the user_transactions table (product_id=121 and product_quantity=1) but after that I get:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\index2.php on line 22 (relating to the start of the WHILE loop).

Is what I am trying even possible?

Thanks for any assistance.

The code is as follows:-

<?php  
$db_host = "localhost"; 
// Place the username for the MySQL database here 
$db_username = "root";  
// Place the password for the MySQL database here 
$db_pass = "root";  
// Place the name for the MySQL database here 
$db_name = "array"; 

// Run the actual connection here  
mysql_connect("$db_host","$db_username","$db_pass") or die ("could not connect to mysql");
mysql_select_db("$db_name") or die ("no database"); 

$email = "pedro";// A test email

$sql = mysql_query("select * from array_field where payer_email='$email' order by id") or die(mysql_error);//Get everything from the array_field table which corresponds to that EMAIL.

$orderCount = mysql_num_rows($sql); // count the number of orders

    
while($row = mysql_fetch_array($sql))
 { 

$product_id_string = $row["product_id_array"];
$product_id_string = rtrim($product_id_string, ","); // remove last comma

$id_str_array = explode(",", $product_id_string); // Uses Comma(,) as delimiter(break point)
            
foreach ($id_str_array as $key => $value)
       {
$id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate product ID from its quantity
$product_id = $id_quantity_pair[0]; // Get the product ID
$product_quantity = $id_quantity_pair[1]; // Get the quantity
                            
// Get the following from the array_field table
 $txn_id = $row["txn_id"];
$payer_email = $row["payer_email"];
                                    
$sql = mysql_query("INSERT INTO user_transactions  (product_id, product_quantity, txn_id, payer_email) VALUES ('$product_id',' $product_quantity','$txn_id','$payer_email')") or die ("unable to execute the query");                                   
     }
}
    
?>
2

Hi,

The problem is that you’re overwriting the result set from the first query, with the result of your insert query. Just change the variable name and you should be OK.

I should mention that the mysql extension is depreciated and will be removed from PHP at some point. You should really be using [fphp]mysqli[/fphp] or [fphp]pdo[/fphp] instead.

3

Hello Fretburner,

Thank you very much for that suggestion. I changed the $sql to $sql2 for the INSERT statement and it worked perfectly in localhost but it would not execute the INSERT when I uploaded it to a live server giving an “unable to execute the query” error.

I’m looking into mysqli but I’m stuck. Any suggestions.

Thanks again,
Pedro

4

Hi Pedro,

Here’s an example of how you could rewrite your script to use mysqli:


$db_host = "localhost"; 
$db_username = "root"; 
$db_pass = "root"; 
$db_name = "array"; 

// Run the actual connection here 
$con = mysqli_connect($db_host, $db_username, $db_pass, $db_name);
if ($con->connect_errno) {
    die("Failed to connect to MySQL: (" . $con->connect_errno . ") " . $con->connect_error);
}

$email = "pedro";// A test email

if ($result1 = mysqli_query($con, "select * from array_field where payer_email='$email' order by id"))
{
    $orderCount = mysqli_num_rows($result1); // count the number of orders

    while($row = mysqli_fetch_array($sql))
    { 
        $product_id_string = $row["product_id_array"];
        $product_id_string = rtrim($product_id_string, ","); // remove last comma

        $id_str_array = explode(",", $product_id_string); // Uses Comma(,) as delimiter(break point)

        foreach ($id_str_array as $key => $value)
        {
            $id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate product ID from its quantity
            $product_id = $id_quantity_pair[0]; // Get the product ID
            $product_quantity = $id_quantity_pair[1]; // Get the quantity

            // Get the following from the array_field table
            $txn_id = $row["txn_id"];
            $payer_email = $row["payer_email"];

            $result2 = mysqli_query($con, "INSERT INTO user_transactions (product_id, product_quantity, txn_id, payer_email) VALUES ('$product_id',' $product_quantity','$txn_id','$payer_email')");
            
            if ($result2 == FALSE) {
                echo "Insert failed: (" . $con->errno . ") " . $con->error;
            }
        }
    }
}
else
{
    echo "Query failed: (" . $con->errno . ") " . $con->error;
}
// close connection
mysqli_close($con);

If either of the two DB queries fail, it’ll output a more detailed error message to help diagnose the problem.

5

Hello Fretburner,

I’m not having much luck here. I tried the code as you suggested but I get a similar result to the original error warning.

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /homepages/12/d381232898/htdocs/dsc065295767/Westland/view_orders.php on line 31

which equates to this line : while($row = mysqli_fetch_array($sql))
Regards,
Pedro

6

Oops, sorry Pedro, I missed that line! It needs to be:

while($row = mysqli_fetch_array($result1))
7

I think it’s getting close.
It will not read the subsequent product_id and Product_quantity if the txn_id is the same (which it will be if there are multiple orders in a transaction).

This is the output:

Insert failed: (1062) Duplicate entry ‘3RH33064XJ1616301’ for key 'txn_id’Insert failed: (1062) Duplicate entry ‘5S780105V0477431G’ for key 'txn_id’Insert failed: (1062) Duplicate entry ‘3FY58978HM3686103’ for key 'txn_id’Insert failed: (1062) Duplicate entry ‘3FY58978HM3686103’ for key 'txn_id’Insert failed: (1062) Duplicate entry ‘62V05573WX4052612’ for key 'txn_id’Insert failed: (1062) Duplicate entry ‘62V05573WX4052612’ for key ‘txn_id’

Sorry for this, I do appreciate your time and effort.
Pedro

8

Am I right in thinking then that you might have some new entries to be inserted into the user_transactions table, and some existing entries that need to be updated?

9

Fretburner,

If I delete the INSERT of the txn_id and delete the txn_id field from the table, everything goes through O.K.!

10

Not realyy. What I have is an array with a number of product_id, product_quantity in the form product_id(1)-product_quantity(1), product_id(2)-product_quantity(2), etc. stored as 112-2,134,1,334,4, all of which would be on one transaction XCXCDF343434(txn_id)

I need it to store it IN USER_TRANSACTIONS as:
id … txn_id … product_id…product_quantity
1…XCXCDF343434…112…2
2…XCXCDF343434…134…1
3…XCXCDF343434…334…4
so far it only stores the first product_id and product_quantity and skips the second and third.

Does that make sense?

Pedro

11

You need to modify the user_transactions table so that the txn_id column is a non-unique index (mysql just calls this type ‘index’) and then you won’t have trouble inserting multiple records with the same value.

12

O.K. I’m going to give that a go tomorrow.

Thanks for your help, I’ll let you know how it goes.

Pedro

13

Hello Fretburner,

I tried your suggestion and everything now works O.K.

Thanks for your help. I will go away and start working on MySQLi.

Thanks again,
Pedro