CODEWITHSUNDEEP
phpmysql
user1523311
user1523311

Reputation: 145

Insert Values into mysql database

I am working on inserting values into database. i need to insert products. user can add more than one product on front-end. for this i have 2 tables into database.first is tbl_test and second tbl_test_months. In tbl_test i am inserting product name and growth rate of products. Products price and sale are going for 12 months. So in 2nd table i need to enter product id , sales, price according to month. but my product id is not entering in right way. i want after 12 month it should take 2nd product id.This is my code. Please help me.

for($p=0; $p < $countProduct; $p++)
{
    $revInsert= "insert into tbl_test(id, user_id, product_name, price_growth_rate_year1, price_growth_rate_year2, sale_growth_rate_year1, sale_growth_rate_year2, scenario, currency_type, price_raise)
                values('','".$user_id."', '".$productName[$p]."', '".$growth_price_first[$p]."', '".$growth_price_second[$p]."', '".$growth_sale_first[$p]."', '".$growth_sale_second[$p]."', '".$scenario."', '".$pCurrency[$p]."', '".$priceRaiseStatus[$p]."')";
    $QueryRev= mysql_query($revInsert);
    $id= mysql_insert_id();
}
for($q=0; $q<$cnt; $q++)
{  
    $rev_month_insert= "insert into tbl_test_months(id, user_id, product_id, month, year, sale_volume, sale_price, scenario) 
    values('','".$user_id."', '".$id."','".$months1[$q % 12]."', '".$year."', '".$sale_volume1[$q]."', '".$price_currency_contract[$q]."', '".$scenario."')";
    $query= mysql_query($rev_month_insert);
}

Upvotes: 0

Views: 548

Answers (2)

vee
vee

Reputation: 38645

From the docs (http://php.net/manual/en/function.mysql-insert-id.php).

Return Values: The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

I am assuming your id column is an AUTO_INCREMENT column by the use of mysql_insert_id, in which case you don't want to specify id in your insert statements.

Even if the column is auto increment you can still supply id in your insert query. But in your case you are supplying ''. If this is a primary key column then your insert queries in your first loop should be failing right at index $p=1 because of duplicate primary key.

Based on what you've provided I think the solution would be the following: 

for($p=0; $p < $countProduct; $p++)
{
    $revInsert= "insert into tbl_test(user_id, product_name, price_growth_rate_year1, price_growth_rate_year2, sale_growth_rate_year1, sale_growth_rate_year2, scenario, currency_type, price_raise)
                values('".$user_id."', '".$productName[$p]."', '".$growth_price_first[$p]."', '".$growth_price_second[$p]."', '".$growth_sale_first[$p]."', '".$growth_sale_second[$p]."', '".$scenario."', '".$pCurrency[$p]."', '".$priceRaiseStatus[$p]."')";
    $QueryRev= mysql_query($revInsert);
    $product_id= mysql_insert_id();

    for($q=0; $q<$cnt; $q++)
    {  
      $rev_month_insert= "insert into tbl_test_months(user_id, product_id, month, year, sale_volume, sale_price, scenario) 
      values('".$user_id."', '".$product_id."','".$months1[$q % 12]."', '".$year."', '".$sale_volume1[$q]."', '".$price_currency_contract[$q]."', '".$scenario."')";
      $query= mysql_query($rev_month_insert);
    }

    // Update
    unset($product_id);
}

Upvotes: 0

GautamD31
GautamD31

Reputation: 28773

You need to take the ids in an array and insert them accordingly.Try like this

$id_arr = array();
for($p=0; $p < $countProduct; $p++)
{
    $revInsert= "insert into tbl_test(id, user_id, product_name, price_growth_rate_year1, price_growth_rate_year2, sale_growth_rate_year1, sale_growth_rate_year2, scenario, currency_type, price_raise)
                values('','".$user_id."', '".$productName[$p]."', '".$growth_price_first[$p]."', '".$growth_price_second[$p]."', '".$growth_sale_first[$p]."', '".$growth_sale_second[$p]."', '".$scenario."', '".$pCurrency[$p]."', '".$priceRaiseStatus[$p]."')";
    $QueryRev= mysql_query($revInsert);
    $id_arr[] = mysql_insert_id();
}
for($q=0; $q<$cnt; $q++)
{  
    $rev_month_insert= "insert into tbl_test_months(id, user_id, product_id, month, year, sale_volume, sale_price, scenario) 
    values('','".$user_id."', '".$id_arr[$q]."','".$months1[$q % 12]."', '".$year."', '".$sale_volume1[$q]."', '".$price_currency_contract[$q]."', '".$scenario."')";
    $query= mysql_query($rev_month_insert);
}

And makesure that you have an auto increment field in the tbl_test table and Dont use mysql_* functions due to they are deprecated.Instead use mysqli_* functions or PDO statements

Upvotes: 1

Related Questions