Create Table

richierich

USING PHP and MySQL I want to create a temporary table based on another table. Acoordingto the MySQL help the syntax is:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

What does the select statement have to look like??

CREATE TABLE temp_accomm SELECT record_id, name, description, photo_id, caption, island, accomm_type, address, phone, email, website, special_, price_text, price_max, price_min, open from ac_records where record_id='1';

richierich

USING PHP and MySQL I want to create a temporary table based on another table. Acoordingto the MySQL help the syntax is:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

What does the select statement have to look like??

I tried :

Anon

You need to create your table defination before you can try to select data into it.

create temporary table my_table (
name varchar(255),
addr1 varchar(255),
addr2 varchar(255))
select p.name, a.addr1, a.addr2 from people p, addresses a where p.people_id = a.people_id

Or something like that. I haven't tested this but it's basically the correct format.

What you have above isn't creating a temporary table, and it's only selecting 1 record from a single table. Temporary tables are best used for aggregating data from multiple tables into one table for quicker queries. Temporary tables should only be used for queries, and should be created only if you have to uses the same set of data over multiple connections. If you only want to get 1 record from the database and use that data in 1 script, then just select all of your data and put it in an array that you will pass around. Only worry about a temporary table if you are going to need that data over multiple scripts and you don't want to have to re-query a large table to get it every time.

richierich

According to the MySQL book I've got

"The create_definition list names the columns and indexes that you want to create. The list is optional if you create the table by means of a trailing SELECT statement."

I took this to mean that you can create a table which is a subset of another table by using a select query. What I need is an example of this so that I can see how it looks.

sepodati

This worked for me. Are you sure you have a version where this is supported?

mysql> select * from del;
+------------+--------+
| var | name |
+------------+--------+
| 2000-11-02 | Al |
| 2000-11-02 | Dick |
| 2000-11-02 | Zorro |
| 2000-11-01 | Abe |
| 2000-11-01 | Wesley |
| 2000-10-09 | Dick |
+------------+--------+
6 rows in set (0.00 sec)

mysql> create table temp_table select var,name from del where name='Dick';
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from temp_table;
+------------+------+
| var | name |
+------------+------+
| 2000-11-02 | Dick |
| 2000-10-09 | Dick |
+------------+------+
2 rows in set (0.00 sec)

---John Holmes...

Anon

could you please tell me when does this temp table delete? does it deleting automatically when that user quit the connection ?

Anon

Quote:

In MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).

---John Holmes...

Anon

thanks for your help...

Anon

HI all,

I am looking to do something somewhat similar, however I would like to create the table (temp or not) with PHP.

I can connect and select and perform INSERTS and updates, etc. I just can not seem to create the table(s) programmatically and it appears only manually.

for example - one would think this would work, but does not:

mysql_query("CREATE TABLE test_nuke (test_id int(10) NOT NULL auto_increment, test_text varchar(50) default NULL)");

thanks,
downsize

Anon

sorry - I found my answer ... I did perfom a search before I posted this, honest!

thanks to all,
downsize