PHP or SQL to do this?

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.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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?

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP or SQL to do this?

On 24/02/2010 05:46, 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.
>
Hi,

If your using MySQL (Other DBs may support this as well) you can insert
multiple rows like this.

INSERT INTO destination_table ( barcode2, wineID)
SELECT barcode2, wineID
FROM wine
WHERE barcode2 > 0;

(adjust to your spec)

Check out the manual page here (MySQL 5.1):

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

You can jump to the manual for other versions from this page too.


Build up your SELECT statement first and then add the INSERT line when
you're happy its getting the right values. If you want to be really
careful, insert into a temporary table first.


Regards

Ian
--




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php