MYSQL :: Verify Table Name

Verify Password
A 'change password' screen in an application typically asks for the current password and the new password twice. I wanted to verify the current password before setting the new one, using:

SELECT IF(password=PASSWORD('oldpass' ),1,0) as PWvalid FROM user WHERE user='myusername';

But the server hosting my database doesn't give you access to the mysql database, for obvious reasons.

Is there a way to verify the current password without having to disconnect and reconnect?

Verify If Date Is Between An Interval
I have a table with 2 date fields. The first one is called cm_data1 and the other cm_data2.

How can i verify if a date submitted by a user is between those two dates?

Ps.: cm_data1 is always lower than cm_data2.

How To Verify If MySQL Is Running On My Windows XP?
I uninstalled that and reinstalled that. Then it doesn't work anymore... I check the process of the "Windows Task Manager", but I couldn't find the fimilar mysql-nt.exe...

Do you have any idea how should I fix this problem? Should I reinstall the whole XP system?

Sould Someone Verify LEFT OUTER JOIN?
could someone please verify :

SELECT *
FROM Members, Submits, MNotify LEFT OUTER JOIN MemPay as mp on
(mp.MPMem_ID=Members.MemID and Members.MemID=colname and mp.MPSub_ID=MNotify.FK_SubmID)
WHERE Submits.SubmID=MNotify.FK_SubmID and Members.MemID=colname
MemPay - payment table, where
MPID | MPMem_ID (actually foreign) | MPSub_ID

Members
MemID

Submits - submitted ads
SubID

MNotify - Members become notified of individual submitted ads
MNID | FK_MemID | FK_SubmID

Members can view each MNotify.FK_SubmID (submitted ads -- the ads may be bought, and if so are found in MemPay table. MPMem_ID is the buyer primary id and MPSub_ID is the id of the submitted ad bought.)

In any case, the Member can view all submitted ads (including purchases) while the check on the LEFT OUTER JOIN values gives what he or she has bought.

While it seems to output as it should, when I check on the LEFT OUTER JOIN values with php conditional statements I've getting values oppositie of what they should (checking on NULL, <>NULL, empty, !empty)

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

Create Virtual Table During Query Or Load Table To Memory From Text File
I have access to my application database via a secure link, so I cannot link a local database to it, only run queries on the remote server and download the result.

The database is missing some useful information, and I cannot add a table at this stage. I would like to achieve the result by creating a virtual table or the like. ...

Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured.

A & B

Table A, has column: Product (product code) as primary key

Table B doesn't.

Apart from that they have the exact same fields.

There's also a column: supplier

I want to

Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples

So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes.

mysql version 4.0.27

Inserting Multiple Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table.

To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table.

My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered.

The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"?

Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?

Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so:

PHP Code:

$selectt = mysql_query("SELECT trailers.trailer_id,
trailers.trailer_title,
trailers.description, trailers.file_url,
trailers.runtime, trailers.views,
trailers.rating, trailers.tt, trailers.keyw, trailers.date,
tags.snub,
films.film_title, films.studio, films.keyw, films.image
FROM films,tags,trailers
WHERE trailers.trailer_id=tags.trailer_id
AND trailers.film_id=films.film_id
ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh);

I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it.

Possible?

ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211).

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5359211
Avg_row_length: 801
Data_length: 4294967288
Max_data_length: 4294967295
Index_length: 45783040
Data_free: 0
Auto_increment: 5406252
Create_time: 2007-04-20 18:26:38
Update_time: 2007-08-22 09:55:22
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Here is the table structure.

CREATE TABLE `messages` (
`id` int(11) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`message` text NOT NULL,
`attachment_path` varchar(255) default NULL,
`new` tinyint(4) NOT NULL default Ɔ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On searching I found the this link.
http://dev.mysql.com/doc/refman/4.1/en/full-table.html

According to manual i executed the following query

MySQL
ALTER TABLE messages MAX_ROWS=20000000000;
I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this.

After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left.

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 170109
Avg_row_length: 649
Data_length: 110563036
Max_data_length: 281474976710655
Index_length: 1961984
Data_free: 0
Auto_increment: 5409214
Create_time: 2007-08-23 10:07:08
Update_time: 2007-08-23 13:41:57
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=4294967295
Comment:
1 row in set (0.00 sec)
What could be the reason of this rows deletion?

I am on 32 bit system.
MySQL version: 4.1.18-standard-log
Operating System : CentOS 3.x
Memory: 4 GB DDR

3,000,000 Unique INT(11) Low-Hi Ranges In My TABLE, Am I Stuck With FULL TABLE SCAN?
I have a table with 3,000,000 different IP netranges.

each row has a unique range: ie: 1xxxxxxx-1xxxxxxx2
..

I try to find the primary_index when querying WHERE var BETWEEN lowIP and highIPrange.

Since there all of the netranges are unique, it seems like my INDEXES are useless, so the only way to optimize this would be to horizontally partition the data? The query is extremely slow ~1 second in MyISAM, and ~.50 second in MEMORY table format.

I'm assuming the only option is to horizontally position the data to improve performance, am I correct?

ERROR 1005: Can't Create Table './db/table.frm' (errno: 150)
I found the key to solve this problem in:

http://darkstar.ist.utl.pt/mysql/do...onstraints.html

You'll probably need an INDEX for that new foreign key you are declaring in
older versions this isn't neccesary but in latest ones it is a restriction.

You can have more info about the error description if you have root access
by typing
mysql> show innodb status;

LATEST FOREIGN KEY ERROR
------------------------
030807 1:27:10 Error in foreign key constraint of table database/table:
There is no index in the table database/table where the columns appear
as the first columns. Constraint:
foreign key (key) references database.table(key) on delete cascade)
type=innodb

also if you type:
shell> perror 150

you obtain:
150 = Foreign key constraint is incorrectly formed

I hope this to be enough...

Trying Abort One Select Accessing One Table Locked By Lock Table.
I'd like to configure one time to abort one transaction that are accessing one table locked by lock table write command ex:

Transaction A
mysql> start transaction;
mysql> lock table mytable write;

After I'll start new transaction B (another connection)

mysql> start transaction;
mysql> select * from mytable;

It's will become long time to wait...

My transaction B never "die".

I tried to configure innodb_lock_wait_timeout=20 in my.ini without success,

Copying Column Names And Properties From An Existing Table To A New Table?
I've got a table with 42 columns, set to different types (string, datetime, date, integer). It has 8 million rows. I want to make a new table with the exact same columns, but with no data in it. Is there an easy way to do this without re-defining every column?

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form:

year | season | hits

2004 | Summer | 42
2004 | Autumn | 43
2005 | Spring | 51
2005 | Summer | 52
2005 | Autumn | 53
2006 | Spring | 61

I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form

year | Spring | Summer | Autumn
2004 | ------ | ----42 | ----43
2005 | ----51 | ----52 | ----53
2006 | ----61 | ------ | ------

Could anyone suggest a mysql query to generate the required output?

1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly.

With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B.

I will do this using the Cron.

But what would be the best insert query to use so that it does the process really quick.

The query that I have so far is:

Quote:

mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1");

With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A.

How can I add to the query so that it does this.

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

ALTER TABLE `table` ORDER BY `column_a` DESC
I try to get better performance by implementing this:

http://dev.mysql.com/doc/mysql/en/alter-table.html

"ORDER BY allows you to create the new table with the rows in a
specific order. Note that the table does not remain in this order after
inserts and deletes. This option is mainly useful when you know that
you are mostly going to query the rows in a certain order; by using
this option after big changes to the table, you might be able to get
higher performance. In some cases, it might make sorting easier for
MySQL if the table is in order by the column that you want to order it
by later."

I run this SQL query on one of the table:

Not Unique Table/alias: <tablename> For Temporary Table
Iâ€™m converting an application from Paradox to MySQL (first time Iâ€™ve used this) and am replacing Paradox local tables with MySQL temporary tables â€“ I understand each user will have a unique copy of the table. Basically I issue the following two commands :

'drop temporary table if exists <tablename>'

'create temporary table <tablename> like <tablename>'

where <tablename> is a table structure in the database with 0 records. This strategy was successful while I was working with a local copy of MySQL on my development PC, but when I run the system from a server on the office network I get the following error :

#42000Not unique table/alias: <tablename>

The environment I work in is Windows XP Pro SP2, Delphi 5 and CoreLabs MyDAC data access components. Iâ€™ve searched this site and Googled to no avail. Hoping someone has seen this before.

Renaming Table In Database, Restoring Data To Table
A consultant recently hired at my company overlayed/wiped- out a table in a database I created for him. This database was a copy of a live database so I have the original table data he deleted.

How do I rename the product table he created to os-product, it must live in the same database?

How do I create a the product table and import the original data from the live database?

Fixed Table, Variable Number Of References To Another Table?
I have two tables A and B. I want to have a column in table A that can reference to zero or more rows in table B. Is there a way to do this without making my table dynamic? I want to keep it fixed. Is there a solution or workaround?

1036:Table Read Only Error, Cant Add A New Column To My Table
I have a table with arround 30 columns, and I am trying to add another column to it with the following query.

alter table tablename add column column_name varchar(10);

and I get the following error.
ERROR 1036: Table 'ProfilesDesc' is read only

what could be wrong?

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

Check Table And Repair Versus Optimize Table
I am confused about the difference between running a CHECK table and REPAIR(if necessary) versus running an OPTIMIZE table. It's not very clear in the documentation, but seems that OPTIMIZE table does a check and repair in addition to the other functions performed by optimize.

I have inherited some legacy code that does a check and repair and optionally follows it with an optimize. That seems redundant to me and I would like to streamline the code to make it an "either-or" selection.

Can't Create Table -- Error 1050 - Table Already Exists
I tried to create a table in the database. Here is the query sentence :

CREATE TABLE word (aff_code varchar( 10 ) NOT NULL default '',position tinyint( 1 ) NOT NULL default '0',flag tinyint( 1 ) NOT NULL default '0',PRIMARY KEY ( aff_code, position )) ENGINE = MYISAM DEFAULT CHARSET = latin1;

I was expecting to create a three column table. However, MySQL kept reporting error : ERROR 1050 (42S01): Table 'word' already exists. The fact is there is no table in this database. I then changed the table name, engine type, MySQL still report the same problem. I've tried changing database name. Still the same problem.

Prior to the query, I run a program that created (and inserted data as well) 65,000 tables in another database (same server although). I'm wonderring would this cause some kind of dead lock that result in the problem of not able to create any more table?

Can anybody give me some tips on how to solve the prolem?

Rights To Create Table, Select, Then Drop Table..
I have a need to get data from the db that requires me to=20

1) do a select and create a new table with the results=20
2) run a query against that new table=20
3) drop the new table=20

I have a script on my server that does this using the root account that has all on *.* for the db. It works fine.=20

I now want to get these results on a web page.=20 I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done.=20

what priviledges do I need to give that user?=20

currently I have the following but the user can't even log into the db from the command line..=20

mysql> show grants for user;=20
+-----------------------------------------------------------------------
---------+=20
| Grants for user@% |=20
+-----------------------------------------------------------------------
---------+=20
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD
'6fe4c0ab2cf30ae3' |=20
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |=20
+-----------------------------------------------------------------------
---------+=20
2 rows in set (0.00 sec)=20

when I do a "show grants for user", what should I see to allow what I want?

PhpMyAdmin: Export Table - Import Table
I find in PhpMyAdmin where I can export a table, however when I click the "GO" button where is the exported file? And how do I name it?

When I go to import a table I can select the file to import and that makes sense.

My goal is to take a table from one database and use it in another database, so I hope I am in the correct place?

Mysql Move Table Content To Another Table
How do I move the contents from one table to another table in the same
database. ie the contents of "cart table" to "orders table" thus removing the
contents of the "cart table" and placing it in the "orders table".
I have looked everywhere for a simple method other than the long winded Select, Insert and the Deleting.

Perform Alter Table Only If A Table Exists
I need to run a batch of "Alter table" statements on a set of database tables.
However, some of the databases may not exist, and even if they exist, the specific table might not exist.

Alter table only works if the table exists. Is there a way to tell MySQL to execute the "Alter table" statement only if the database and table exist? If so, how?

Convert The MyISAM Table To InnoDB Table
Greeting, do some body know how to "Convert The MyISAM table to InnoDB table"?

Inserting Into A Table From Antoher Table With A Different Structure
Say i have table A with column structure 'a','b','c','d'
and I have table B with column structure 'a','b','c'

I've tried doing an insert into A select * from B; but it says I cannot do this due to the size difference in tables.

Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning.

current query looks like this:

SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id;

So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?

Update Column In Table A With The Count Of Another Table
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.

Can't Find Table Error, Even Though Table Exists
I use the following query:

SELECT * from ads where user!='$id' and ready=1 and category='$category' and main.id=user and main.credits>4;

And it returns #1109 - Unknown table 'main' in where clause

But the "main" table exists. What gives?
I've tried it with other tables in the same database and it says that they all don't exist.

Any ideas?

Relating User Table With Content Table
I have a mysql table with the curriculum vitae (CV) information of about 50 researchers. I would like each researcher to be able to edit and update his/her information. In order to do this I need to create a membership system for the researchers to access their respective CV page.

Before I do anything I want to plan this out, hopefully with a little help of more experienced developers and programmers. So I am open to any ideas.

This is the thing that stumps me the most right now:

Right now I have a CV table (with all the curriuculum vitae information) and need to create a users table that will somehow link with the CV table. Eventually, what I would like is with a login and password (that they choose) they will automatically open their CV page. These are the questions I have:

What fields in the user table should I use to link the user table with the CV table?
Once that is established, how, by providing their login and password can I direct them to their own page to update? Should one of the fields in one of the tables contain the (already existing) address of their CV page?

Finding All Records In One Table That Don't Have A Corresponding Record In Another Table
I have photo information stored in a table called pictures. One column is keyword.

Visitors to my site can search for photos by keyword.

I record search terms in another table called searches. Each search term is recorded, meaning there are duplicate search rows. This allows me to see easily the popularity of a search term.

Step 1. What query can I run to find all search terms that return no matching picture in the picture table.

I tried the following, but no luck:Quote:select term from searches left join pictures on term like keyword where picture_id is NULL

SELECT From A Table Depending On A Result From Another Table
This may sound a bit weird so i'll explain:

I have three tables:

tbl1foobar
------------------
ididid
type enum('foo', 'bar')infoinfo
typeid

Now I want to get the info from either foo or bar depending on tbl1.type
Is it possible to do this in one select statement?

Inserting Data From One Table To Another Table In Same Database
I have got about 80 tables (same structure/fields) from my client. I want data of all 80 tables in one single table. There are around 90 lakhs records in 80 tables. How do I get to do this ?

Alter Table 'table' Auto_increment = # Doesn't Work
i cannot set auto_increment to start from anything other than 1:

mysql> create table test (id int unsigned not null auto_increment primary key);

mysql> alter table test auto_increment=2;

mysql> insert into test() values();

mysql> select * from test;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

Passing Primary Key From One Table Accross To Another Table?
I am struggling in getting the user ID (PK) from my user table into my leaderboard_1 table as a FK. I am confused on how to pass this information accross. Below is my code. At the moment a new leaderboard_1 record is created but it gives each record a value of 0 for id!?

?>
<?php

$self = $_SERVER['PHP_SELF'];
$country = $_POST['country'];
$username = $_POST['username'];

if( (!$country) or (!$username)) .....

Adding Visual Table To MySQL Table
I just successfully created my table that is up and running. I also created an HTML form that allows me to submit new information. I have seen people that created tables and when they view the table they add style (color, fonts, etc..) to it as well as actually put the data in a visual HTML table. I was wondering how I would do this.

How To Load A BLOB Data Into A DB Table And How To Get It From The DB Table?
Explains how to load a BLOB data into a DB table and how to get it from the DB table? image will be uploaded using cfform.

Comparing Table Names With Entries In A Different Table
Let's say that all of my tables names are types of animals, i.e. cat, dog, mouse, horse, etc... I also have another table, call it 'Master', that basically is a master list of various information. Within this table there is a field called 'name' in which there are animal names, i.e, cat, dog, mouse, etc. What I would like to do is to run a query that compares all of the table names in my database to the entries in the field 'name' within the 'Master' table. I would like the query to return any table names that don't match up with an entry in the 'name' field of the 'Master' table. For example, if I have the following tables in my database:

cat, dog, mouse, horse, badger, Master

and within the table 'Master', I have the following entries in the field 'name':

cat, dog, mouse, horse, badger, cow, snake, rabbit

then the query that I would like to run would return: cow, snake, and rabbit, Master. This is a much simpler example, obviously than the actual amount of tables and information in my real database.

Temporary Table Or Append Master Table
I am using PHP to interface to MySQL. After PHP provides the user inputted data, MySQL will perform several queries using both the user data as well as several other non-user specific tables, and return the resultant values which PHP will return to the user. Multiple users will access the system simultaneously. I know of two possible solutions, but donâ€™t know which is better.

I can either create a table called T_USER_DATA (there will actually be 6 tables, some with one row per user and others with multiple rows) with a column for C_USER_ID, and additional columns for the userâ€™s data. The user data rows will be added, and then filtered based on the USER_ID.

Or, I can create a temporary table (maybe using a memory engine for faster speed?) specific for a given user on the fly when PHP passes the data. The queries are performed, data returned to PHP, and then the temporary table is deleted when the connection to MySQL is closed.

Verify Table Name

Is there any way to verify the existence of a table in mysql? sort of like the php function file_exists(); .. "SELECT TABLE xxx FROM db_name" ?

Is there any way to verify the existence of a table in mysql?

sort of like the php function file_exists(); .. "SELECT TABLE xxx FROM db_name" ?