[PHP-DB] Re: New Table Creation with PHP Variables

[Neil Smith [MVP, Digital media]]

At 12:43 29/12/2008, you wrote:

Message-ID: <008b1179ce594ebea03cb06480182...@dragon>
From: "Keith Spiller" 
To: "php_db" 
Date: Sun, 28 Dec 2008 17:39:08 -0700
MIME-Version: 1.0
Content-Type: text/plain;
format=flowed;
charset="iso-8859-1";
reply-type=original
Content-Transfer-Encoding: 7bit
Subject: New Table Creation with PHP Variables

Hi,

I'm trying to join multiple tables to then create a new table from 
the query.  I've figured out that part, but some of the fields need 
to be evaluated and then compared to a php array to derive their 
data.  In this example I am trying to populate the field4 column 
(from the $product_name array) after evaluating the product_type 
value on each row.


CREATE TABLE $table[name]
SELECT field1, field2, field3,
IF(o.product_type='course', $product_name[$product_id], NULL) AS 
field4, field5, field6, field7

FROM table1 as a, table2 as o;

Is this possible?  Is there another way to accomplish this 
task?  Thanks for your help.



http://dev.mysql.com/doc/refman/5.1/en/create-table.html


You can create one table from another by adding a 
SELECT statement 
at the end of the 
CREATE 
TABLE statement:



CREATE TABLE new_tbl SELECT * FROM orig_tbl;


MySQL creates new columns for all elements in the 
SELECT. For example:



mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
->PRIMARY KEY (a), KEY(b))
->ENGINE=MyISAM SELECT b,c FROM test2;




HTHCheers - Neil



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

Re: [PHP-DB] Re: New Table Creation with PHP Variables

[chris smith]

On Mon, Dec 29, 2008 at 10:17 AM, Keith Spiller  wrote:
> Another option that would work if I can figure out the correct syntax is to
> just NULL certain values if a given condition exists.  If
> product_type='course' then just use the o.product_id value for field4.  If
> product_type != 'course' then use NULL for field4.
>
> CREATE TABLE $table[name]
> SELECT field1, field2, field3,
> IF(o.product_type='course', o.product_id, NULL) AS field4,
> field5, field6, field7
> FROM table1 as a, table2 as o;
>
> Is this right?  Thank you for your help.

The idea is right, the format of the query isn't.

I use the case statement for this but it's up to you - work out the
right format for IF from the mysql manual.

(case when o.product_type='course' then o.product_id else null end
case) as field4

-- 
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [PHP-DB] Re: New Table Creation with PHP Variables

[Jack van Zanen]

That looks like it should work, just execute the select query and see what
is the output


Jack

2008/12/29 Keith Spiller 

> Another option that would work if I can figure out the correct syntax is to
> just NULL certain values if a given condition exists.  If
> product_type='course' then just use the o.product_id value for field4.  If
> product_type != 'course' then use NULL for field4.
>
> CREATE TABLE $table[name]
> SELECT field1, field2, field3,
> IF(o.product_type='course', o.product_id, NULL) AS field4,
> field5, field6, field7
> FROM table1 as a, table2 as o;
>
> Is this right?  Thank you for your help.
>
> Keith
>
>
> - Original Message - From: "Keith Spiller"  >
> To: "php_db" 
> Sent: Sunday, December 28, 2008 5:39 PM
> Subject: New Table Creation with PHP Variables
>
>
>
> Hi,
>>
>> I'm trying to join multiple tables to then create a new table from the
>> query.  I've figured out that part, but some of the fields need to be
>> evaluated and then compared to a php array to derive their data.  In this
>> example I am trying to populate the field4 column (from the $product_name
>> array) after evaluating the product_type value on each row.
>>
>> CREATE TABLE $table[name]
>> SELECT field1, field2, field3,
>> IF(o.product_type='course', $product_name[$product_id], NULL) AS field4,
>> field5, field6, field7
>> FROM table1 as a, table2 as o;
>>
>> Is this possible?  Is there another way to accomplish this task?  Thanks
>> for your help.
>>
>> Keith
>>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


-- 
J.A. van Zanen

[PHP-DB] Re: New Table Creation with PHP Variables

[Keith Spiller]

Another option that would work if I can figure out the correct syntax is to 
just NULL certain values if a given condition exists.  If 
product_type='course' then just use the o.product_id value for field4.  If 
product_type != 'course' then use NULL for field4.


CREATE TABLE $table[name]
SELECT field1, field2, field3,
IF(o.product_type='course', o.product_id, NULL) AS field4,
field5, field6, field7
FROM table1 as a, table2 as o;

Is this right?  Thank you for your help.

Keith


- Original Message - 
From: "Keith Spiller" 

To: "php_db" 
Sent: Sunday, December 28, 2008 5:39 PM
Subject: New Table Creation with PHP Variables



Hi,

I'm trying to join multiple tables to then create a new table from the 
query.  I've figured out that part, but some of the fields need to be 
evaluated and then compared to a php array to derive their data.  In this 
example I am trying to populate the field4 column (from the $product_name 
array) after evaluating the product_type value on each row.


CREATE TABLE $table[name]
SELECT field1, field2, field3,
IF(o.product_type='course', $product_name[$product_id], NULL) AS field4, 
field5, field6, field7

FROM table1 as a, table2 as o;

Is this possible?  Is there another way to accomplish this task?  Thanks 
for your help.


Keith 




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