Re: [PHP] PHP or SQL to do this?

Rob Gould wrote:
> I'm not sure if I need to write a PHP for-loop to do this, or if it can all be done in one SQL statement?
> 
> Basically, I want to copy all the barcodes from one table and put them into another table, but only if the barcode in the first table > 0, and only if the wineid's match from table to table.
> 
> 
> Steps individually are something like this:
> 
> 1)  First, I get all the records from the "wine" table that have barcodes, like this:
> 
> 	SELECT *  FROM `wine` WHERE barcode2 > 0
> 
> The fields I need are "barcode2", and "wineid"
> 
> 
> 2)  Next, I need to match all the wineid's from this "wine" table with the wine id's from the "usersdata" table.  Both fields in both tables are called "wineid".
> 
> 3)  Then, if the wineid's match, I need to copy the "barcode2" value from the wine table and put it into the field "custombarcode" in the "usersdata" table.
> 
> 
> I'm tempted to write a PHP script which does a while-loop through all the records returned from the wine table and do the matching with the usersdata table, but I wouldn't be surprised if there's some sort of table-join-type query that can do all this in one step.
> 

Looks like you should be able to do this in the SQL.  Creating a dummy DB and testing, the following 
seems to do the trick.

UPDATE usersdata SET
	custombarcode = (
		SELECT	barcode2
		FROM	wine
		WHERE	usersdata.wineid = wine.wineid
		AND	wine.barcode2 > 0
		)

Here is the DB schema and data that I used for the test

CREATE TABLE IF NOT EXISTS `usersdata` (
   `id` int(11) NOT NULL auto_increment,
   `wineid` int(11) NOT NULL,
   `custombarcode` int(11) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=7 ;

INSERT INTO `usersdata` (`id`, `wineid`, `custombarcode`) VALUES
(1, 1, 0),
(2, 2, 0),
(3, 3, 0),
(4, 4, 0),
(5, 1, 0),
(6, 1, 0);

CREATE TABLE IF NOT EXISTS `wine` (
   `wineid` int(11) NOT NULL auto_increment,
   `barcode2` int(11) NOT NULL,
   PRIMARY KEY  (`wineid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=4 ;

INSERT INTO `wine` (`wineid`, `barcode2`) VALUES
(1, 5),
(2, -5),
(3, 10);


-- 
Jim Lucas

A: Maybe because some people are too annoyed by top-posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?