I have two table : buy_temp & buy_detail, i need transfer data from buy_temp to buy_detail . For the first step i will get data from buy_temp like this :

$sid = $db->data("email", $user_session);
$sql = mysql_query("SELECT * FROM buy_temp WHERE id_session='$sid'");

while ($r=mysql_fetch_array($sql)) {
$cart[] = $r;
}
$j   = count($cart);

All data where id_session='$sid' will collect and then with action submit data will save to buy_detail

for($i=0;$i<$j;$i++){
mysql_query("INSERT INTO buy_detail(id_orders, id_product, qty, option) 
VALUES('$id_orders',{$cart[$i]['id_product']}, {$cart[$i]['qty']}, {$cart[$i]['option']})");}

But why i can't save for {$cart[$i]['option']}, when OPTION is varchar. ?

Im not sure what you are asking..

If the column is varchar, what is being passed in as the value? Is it NULL? A number? Are you getting a SQL error? Have you tried doing a var_dump on $r and seeing what is actually in there? Are you sure buy_temp has a valid "option" column?

So many points of potential failure, it's hard to debug from what you have given...

Also, as a matter of practice and good coding, please don't use

"select * from..."

with "production" code. It's slow, returns way too much data most of the time, and if you ever add a column you will be returning it without the need of it. Be explicit, and save yourself problems in the future. It will also make debugging easier for you, since you will know exactly what you are asking for, and helps the SQL engine return more meaningful errors to you (such as, invalid column name, etc..) where as PHP will just ignore the request because a mistyped column name simply doesn't have a map in the hash table.

no getting error, just not insert data for column option. If column varchar is being passed every value should be insert because is varchar.

Member Avatar for diafol

Confused. You are using a DB object, but then you start using depreacted (now dead in PHP 7) mysql_*() functions. It also appears that you are inserting data directly into your SQL. This may be secure-ish if coming directly from your cart, but don't take the chance - use a prepared statement (PDO/mysqli) or if you insist on using dead code, sanitize.

However, it is probably due to OPTION being a reserved word. If you must use this as a column name, then backtick it.

See DW Tutorial: Common Issues with MySQL and PHP

Your problem seems to be that you're not enclosing your text value in single quotes - it should be '{$cart[$i]['option']}'

A la:

for($i=0;$i<$j;$i++){
    mysql_query("INSERT INTO buy_detail(id_orders, id_product, qty, option) 
    VALUES('$id_orders',{$cart[$i]['id_product']}, {$cart[$i]['qty']}, '{$cart[$i]['option']}')");}

However, do you actually need to modify the data at all in between the select and the insert? If not, then there's no need to bring the data into PHP at all. Instead, you can use an INSERT..SELECT statement like this

INSERT INTO buy_detail(id_orders, id_product, qty, option)
SELECT id_orders, id_product, qty, option FROM buy_temp 
WHERE id_session='$sid'

After all that, I would strongly recommend moving to mysqli or PDO given that the mysql extension has been deprecated now.

commented: Good catch on the quoting option variable +15

enclosing a value in single quotes still not working

Member Avatar for diafol

Show your current sql

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.