[SOLVED] Moving A Tow To A New Table

[jj20051]

How Would You Move A Row To Another Table Based On The Content Of The Row?

 

Ex: The Person Enters A Barcode and PHP Trys To Match It To The One In The Database. If The Barcodes Are == Then Move The Row To Another Table.

 

You Can Explain or What Ever You Think Will Help Me The Best. As I Said I Know Very Little Even Though I've Read Alot...

[abdfahim]

INSERT INTO dest_table (col1,col2) SELECT col3,col4 FROM source_table where barcode='something'

 

if I understand what you want !

[jj20051]

Ok I Think That's What I Need, but I Have A Few Questions....

 

1. Where (col1,col2) is I Have 5 Columns So Should I Do (col1,col2,col3,col4,col5)?

 

2. Where It Says col3,col4 Why Is That There or Is that Supposed to actually be the name of col3,col4?

[abdfahim]

1) Yes, you are right

2) nop ... as long as you have same type of data in col1 and col3, also in col22 col4

 

for more vivid picture

INSERT INTO dest_table (`Item_name`,`Item_price`,`Item_code`) SELECT `name`,`price`,`code` FROM source_table where barcode='something'

 

here Item_name,Item_price,Item_code are the column names of destination table and name,price,code are the corresponding column names of source table.

 

you can add as many column as you want but number of column must be equal in both side of the query. And you have to have same type of data in corresponding column, like "item_name" of dest_table has to be same type of column as in "name" column in source_table. Column name does not matter.

[jj20051]

Ok I Have It Working, but unfortunately it doesn't delete the old information in the old table so should i run this code after this? This Code deletes a row based on the content... Would this work:

 

$query ="DELETE FROM products WHERE barcode='$barcode'";

mysql_query($query);

echo "Product Deleted!";

[abdfahim]

You are spot on. INSERT syntax can never delete any record. So once you insert it, then you have to delete the unnecessary one by the code you mentioned.

[jj20051]

Well I Apparently Have To Have It Redirect and Then Run This Code On Another Page because It Isn't Working On The Same Page...

[abdfahim]

no way!! they should work on same page. You first insert, then delete like

 

<?php
$res1=mysql_query(INSERT INTO dest_table (`Item_name`,`Item_price`,`Item_code`) SELECT `name`,`price`,`code` FROM source_table where barcode='something') or die(mysql_error());
$res2=mysql_query(DELETE FROM source_table where barcode='something') or die(mysql_error());
?>

[jj20051]

Yay! I Got It Working... Thanks For the Help... 

[jj20051]

I Need A Little More Help ( Again )... I Want To Have It See If The Quantity Is > 1 If It Is It Will Only Subtract One From The Quantity Before Moving A Copy Of It To The Next Table. If the Quantity Is = 1 Then It Will Delete It... I Have No Idea If This Is Right:

 

if (mysql_query ("IF quantity > 1 IN products WHERE barcode='$barcode'")) {

mysql_query (UPDATE INTO products SELECT quantity WHERE barcode='$barcode'")

}

else {

mysql_query("INSERT INTO checkout (id,name,barcode,price,quantity) SELECT id,name,barcode,price,quantity FROM products where barcode='$barcode'")

or die(mysql_error());

echo "Data Inserted!";

 

$stupid ="DELETE FROM products WHERE barcode='$barcode'";

mysql_query($stupid);

echo "Product Deleted!";

}

[abdfahim]

you are in total mess!!!! I feel you are inventing some new functions in mysql !! please, grow some habit to do a search in google !!!!

jokes apart, I guess you want to decrease quantity by one to all the products (not only the one which match the barcode)

mysql_query ("UPDATE products SET Quantity =Quantity -1 WHERE Quantity >1");
mysql_query ("DELETE FROM products WHERE Quantity =1");
mysql_query("INSERT INTO checkout (id,name,barcode,price,quantity) SELECT id,name,barcode,price,quantity FROM products where barcode='$barcode'"
mysql_query("DELETE FROM products WHERE barcode='$barcode'")

 

if you want to update quantity only for the selected barcode, then re-write first two line as

 

mysql_query ("UPDATE products SET Quantity =(Quantity -1) WHERE Quantity >1 AND barcode='$barcode'")
mysql_query ("DELETE FROM products WHERE Quantity =1 AND barcode='$barcode');

 

 

[jj20051]

What I'm Trying To Do Is Build A Checkout System For My Dad's Game Store. I Still Have One More Problem and I'm Sorry To Bug You...

 

I Need To -1 Off Of The Quantity Where The barcode entered is = to one in the database... I Wrote This Based On What You Gave Me Before:

 

mysql_query("UPDATE products SET quantity=(quantity -1) WHERE barcode='$barcode'")

 

Something Is Wrong With This Because It Displays: ( This Is Line 20 )

 

Parse error: syntax error, unexpected T_STRING in /home/bluestar/public_html/users/gameland/checkout/stage1.php on line 20

[abdfahim]

No probs. We are here to help you and there are some more masters to help me . The earth is about give and take

 

Anyway, I hope you didn't forget to put a semicolon at the end of the line

 

mysql_query("UPDATE products SET quantity=(quantity -1) WHERE barcode='$barcode'");

[jj20051]

Why Don't You Just Strap A Dunce Cap On Me! I Hope This Is All. BTW I Tried To Make A Small Donation, but The Link Doesn't Work...

[abdfahim]

.. just a small tip .. whenever you see any problem stating T_STRING, first of all, check for some missing/extra semicolon.

[jj20051]

Ok... Thanks! ( You've Probably Gussed By now That I Just Learned PHP About 48 Hours Ago )