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

Re: New Table Creation with PHP Variables

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

Re: Re: New Table Creation with PHP Variables

------=_Part_118470_6880380.1230523916560
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_118470_6880380.1230523916560--

Re: Re: New Table Creation with PHP Variables

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: New Table Creation with PHP Variables

At 12:43 29/12/2008, you wrote:
>Message-ID: <008B1179CE594EBEA03CB064801823D1@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