Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

MySQL - get data from one table to another

I have an table called 'items' with fields 'id' and 'itemPrice'
What SQL statment should I use to alter my cart table below with itemPrice from my items table if no price exists in cart table?

So basically I need to get itemsPrice from items table where cart.items_id = items.id

Thanks in advance

CREATE TABLE IF NOT EXISTS `cart` (
  `cartId` int(11) NOT NULL AUTO_INCREMENT,
  `cookieId` varchar(50) DEFAULT NULL,
  `items_id` int(11) DEFAULT NULL,
  `spec1` varchar(40) DEFAULT NULL,
  `spec2` varchar(40) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `price` decimal(8,2) DEFAULT NULL,
  `comments` tinytext,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`cartId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=763 ;

--
-- Dumping data for table `cart`
--

INSERT INTO `cart` (`cartId`, `cookieId`, `items_id`, `spec1`, `spec2`, `qty`, `price`, `comments`, `timestamp`) VALUES
(645, 'f22ce279fd806cc0560d6c8cc9d3b348', 1019, '', '', 1, '12.60', ', '2014-05-14 23:56:19'),
(643, '080e258ccfcb753e843585e0134bf360', 1098, '', '', 1, '14.66', '', '2014-05-14 19:45:48'),
(644, 'f22ce279fd806cc0560d6c8cc9d3b348', 762, '5kg', '', 1, '', '', '2014-05-14 23:56:17'),
(642, '080e258ccfcb753e843585e0134bf360', 762, '5kg', '', 2, '', '', '2014-05-14 19:41:40'),
(641, '080e258ccfcb753e843585e0134bf360', 1019, '', '', 2, '', '', '2014-05-14 19:41:40'),
(640, '6aac7d47725e201993968e4598fccb79', 762, '5kg', '', 1, '', '', '2014-05-12 04:11:28'),
(639, '6aac7d47725e201993968e4598fccb79', 1019, '', '', 1, '', '', '2014-05-12 04:11:28'),
(637, '55de36e79597ff74b1d54d84a8fde49b', 762, '5kg', '', 1, '', '', '2014-05-06 11:07:59');

Open in new window

0
sabecs
Asked:
sabecs
  • 3
  • 2
1 Solution
 
arnoldCommented:
are you trying to update one table's element with data from another?

Or are you looking at a join query to report .......
0
 
sabecsAuthor Commented:
Hi, I am trying to update one table's element with data from another.
0
 
arnoldCommented:
you can do

update cart set price=items.price where cart.items_id=items.id

copy the two tables and test first to make sure the results is what you want.

I am uncertain why you would like the price be part of the cart rather given you have a separate table.
When the price is separate you can account for price changes over time as well as make "sale prices" and other such options available.
i.e. the price displayed will vary by promotions, deals you might offer.

When you have data in different tables, you run into the issue you have where you have to maintain the data in multiple table, while using the cart with a reference to the items_id will mean you only need to maintain the product's price in one location.

Is cart the price the buyer will be paying? in this case, the manner in which you populate the cart information might need to be changed/updated.
0
 
sabecsAuthor Commented:
Thanks Arnold, very much appreciated.
I am using the cart table to contain the actual price paid and this becomes part of the order.  The cookieId in the cart table becomes my order number which I then join the orders table. The reason I have done this is that the items table can contain multiple prices depending on option chosen for that product (Size, weight etc.).  I hope this makes sense.
0
 
arnoldCommented:
It does, but the way to populate the cart table is at the time of the order using the join to build the set of data with the insert into directive or if you use an intermediary php etc processing is to assemble the data.

It seems that you are building the table cart which often depending on approach would have had a Tim limited reference that can be used to get to the order which in turn Will be traced to the item that make up that order, ......

Glad I could help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now