MYSQL :: Import Into Multiple Tables

Import Multiple Files
I have 5 clients uploading structured .txt files into a directory. Every day, there are about 2000 text files uploaded.
I would like to import this textfiles every second with a cronjob.
How can i automate this import without having problems with the different "file_xxxx_0123.txt" filenames ?
How can i make the script select the oldest file in the directory

Extract Multiple Columns From Multiple Tables
For hours and hours I've been trying to work out how to write the sql query to extract some data from some tables, but with no luck.....

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

Database Import To Over Ride Tables
I am generating auto-backups from simple database dumps. I'm then trying to import these on a regular basis (1 per week) into my test database through a auto-import script using PHP.

The problem I am having is that I can't seem to import into a database with the existing database table structures as it says 'Table already exitsts etc'. Is there a way when dumping the database I can specify 'if table exisits, drop and insert' similar to when you specify the requirement when exporting the datbase from phpmyadmin? I would prefer not dropping the database and regenerating it each time.

Tables Import PhpBB/phpNuke
My existing site is ok but limited and I'm now at the point where I would like to use a CMS, but I want to import all the old sites membership and comments into the new install.

I HAVE A SCRIPT THAT WILL DO THIS called. phpbb_nuke_conv.tar should anyone else ask.

but for this to happen the phpBB data must be in the same database where the nuke data resides, ie moving the phpBB tables over to the nuke SQL database. The prefixes are different so there no problems there, could someone please point set-by-step for this proceedure.

I'm sure it only a few buttons and it would save me an ENOURMOUS amount of time reading about a subject I would rather not.

Import Some Tables Into Another Pre-existing Database
I want to dump all but 6 tables of a database into a sql file.
THe below is all but those six tables (do I have to wrote out all the table names??)

mysqldump -u root -p --opt dbname --tables view_history top_level_feed tag_set pending_tag_set pending_tag pending_fact namespaced_predicate main_search_ns_pred main_search_keywd_pred main_search_keywd_ns_pred main_search_facets main_search_descr_pred main_search_descr_ns_pred job_watcher job_log_entry item_address_watcher feed_watcher feed_log_entry feed_group feed entity_type_sample_uri entity_type dw_user content_search_ns_pred content_search_facets colleague_search_ns_pred cnt_search_keywd_pred cnt_search_keywd_ns_pred cnt_search_descr_pred cnt_search_descr_ns_pred browse_trigger_pred browse_trigger_ns_pred browse_template browse_ns_pred auto_feed > dbNameTables917.sql

Then import them into another database that has all these tables already.
I want to overwrite these tables with new data from the dump.

Will the mysqldump utility work or should I use the mysqlimport dbname --lock-tables --delete /path/to/sqlfile.sql work?

Multiple Smaller Tables V.s. Fewer Bigger Tables
What is the best way to store data in a database? Multiple smaller tables (which means many Inner/Left Joins when fetching the information) or fewer bigger tables (which means few or no Inner/Left Joins when fetching information).

Import Tables From Another Non-Joomla Mysql File
Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla? Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries.

What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are:

id - title- body

IN other words, just the bare essentials.

The target files has a table called jos_content with columns:
id - title - title_alias - introtext - fulltext in that order and others but are not important.

I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time!

It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials.

There is another column called tags which I would be happy to have as well and I'm not bothered about start dates or anything else.

Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH
LINUX)and I'm wondering which is the best option to do :

I currently have one file for each of my modules, (I'm using filepro plus) ,
so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with
their own fields.

1) Should I create multiple databases on MySQL

2) Should I create a single database on MySql and with multiple tables ?

Which one of these options is the best and more safer ?

Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time.

I have to decide if I should use :

1)one database with one table for all users.
2)one database for each user.
3)one database with one table for each user.

I would really appreciate if you can tell me what choice is best and why.

Multiple Tables
Why yes! I am new and terrible at both mysql & php! Now that I got that off my chest...

Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables?

Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc....

I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once.

Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info.

Which of these would you do, or how would you organize this data.

You can see an example of the data I am inserting into tables here (http://www.webpropertiesinc.com/compor.php).

Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this?

Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?

Multiple Tables DB
Does anyone know of a practical tutorial (with code) that uses multiple related tables.
I want to learn how to update all related data when doing an insert.

e.g.: if I add a user to users table and each user gets a car by defualt, how do I insert a car to cars table and keep a reference to the user (owner of this car).

Multiple Tables Or Just One?
I want to store a large set of variables into a database that are all characteristics of the users of my site. They can register by filling out their name/ adres/ city / day of birth etc.

Besides that they can set a number of preferences (e.g. 'available on monday', 'available on tuesday', 'category 1', 'category 2' etc.) that can be either '1' or '0'.

Furthermore I want to store some variables that tell me if the supplied email adress is validated, what the ip-adress of the registrant is and on what date he registered.

Using MySql, what method is preferred to store the data:

1) one table with +/- 40 columns (note that there a lot of values are booleans).

2) 3 tables, e.g. users, preferences, other with 5 to 20 columns each.

The thing is that I want to be able to perform searches that include all types of characteristics. E.g. I want to select all users that are:

'male, older that 18, are available on monday, preffer category 2 and where the email adress is validated'

Multiple Tables
I have two tables that have item information that I am trying to make a search for. I have it working for searching one table, but I can't figure out how to make it search both.

Current:
"SELECT * FROM `god_items` WHERE `item_name` LIKE '%$search%' OR `guardian` LIKE '%$search%' ORDER BY type ASC"

second table is set_items

Multiple Tables
I have 7 tables with identical fields (OK ideally therefore they should just be in one big table! But cos it's maintained in MS Access originally using forms etc, I'd like that option to be a last resort).

I simply want to sort them into alphabetical order where the 'for_sale' AND 'new' fields equal 1.

Should I just do 7 separate queries? Should I use the UNION function (which seems very complicated at the moment)?

Multiple Tables
I am setting up a staff contact list. Basically fields like, First Name, Spouse, Last Name, Address, Phone, Birthday, Children and their birthdays, job title, email, and a few other fields.
Also need to learn how to put a photo on here as well.

Multiple Tables
i have a database with 10 tables, each table has around 9,000 record, is it faster and better to put all records in ONE HUGE TABLE and define each table with a column ?? i mean i have table for games, softwares, movies, etc ... so i put ALL the records in one table and make a column named "type" and i insert in each column the corresponding value.

Multiple Tables
I would like to SELECT information out of around 100 tables. The thing is I would like to avoid typing all those 100 names manually.
Is there a way of doing something like:

sql Cod
Original
- sql Code

SELECT oneCell FROM tablePrefix_% WHERE id=1;

SELECT oneCell FROM tablePrefix_% WHERE id=1;

The tables are named as tablePrefix_ and than the name of the table like tablePrefix_A, tablePrefix_B and so on.The version I am using is mySQL 4.0.27.

Deleting Multiple Tables
I'm installing vbulletin on a site that previously have phpbb installed. I don't have access to phpmyadmin for the site. Is there a possible query where I can delete ALL tables with the prefix phpbb_?

Altering Multiple Tables
pls tell me can we alter multiple tables with one query ?
e.g i have 2 tables t1 and t2 i want to add auto increment feature on primary keys of these tables.

i was using

ALTER TABLE `t1` ,
't2' CHANGE `column1` ,
'column2' `column1` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`column2` INT( 11 ) NOT NULL AUTO_INCREMENT

Update Multiple Tables?
situation:
a user wants to change their username on my site and their username is littered accross many tables in the DB. If in every one of those tables i've got a static "user_id" column as well as the "username" column, what is the best way to update all of the different tables with the most efficiency?

Selecting Across Multiple Tables
I currently have 3 tables that I want to select data from: categories, calendars, pages. Each table has a parent field (its parent category) and an order field (its order in the menu). What I want is a query that will search all 3 tables and return one ordered resultset of categories, calendars, and pages.

I tried playing around with some queries, but couldn't really figure it out.

What Join To Use With Multiple Tables?
I'm assuming I need some sort of join to accomplish this but I've yet to find something that I have been able to understand.

I have 3 tables - Items, Photos and a table that links the two that I've elegantly named ItemsPhotos

Items
- ItemID
- ItemName
- ItemEtc

Photos
- PhotoID
- PhotoFilename
- PhotoEtc

ItemsPhotos
- ItemPhotoID
- ItemID
- PhotoID

Each Item has a unique ID, as does each Photo. The ItemsPhotos keeps track of their relationship.

I want to be able to select all of the items and only one of the the available photos listed for that item to be returned to an Array for use like the following;

foreach(#){
$databaseOutput[#]['ItemName'];
$databaseOutput[#]['PhotoFilename'];
}

If anoyone knows how I can accomplish this I would be very grateful for a solution.

Sincere appologies if there is an obvious answer or if I have mis-posted - I've been trying to get this (among other things) to work for hours now and I'm desperate for a solution.

How To Search In Multiple Tables
i want to search in multiple tables with selected fields in each table, i make a query like this:

SELECT location_id FROM locations_mast WHERE description like '%$Keyword%'
union
SELECT course_id FROM courses_mast WHERE description like '%$Keyword%'

its giving me results but as the "union" use the first SELECT statement as the column names for the results, its giving me only one record set as "location_id"

i want all the separate column names in the record set as location_id, course_id so on...

Selecting From Multiple Tables
I am a beginner when it comes to mysql and have really gone out of my own depth! I am sure it isnt too hard I just cant seem to figure it out!

OK here is what I am trying to achieve.
lets say I am on a page where the category_id of the current page is 2.
I want to select product_id, product_model, product_image, product_price from the Products table where the product is in the same category as the current page, and the product must be featured.
I have come up with this, but it brings back nothing.

SELECT products.products_id, products.products_model, products.products_image, products.products_price FROM products, featured, products_to_categories WHERE products_to_categories.categories_id=2 and products.products_id=featured.featured_id

Here is the structure of the tables I am using.

featured

featured_id
products_id
featured_date_added
featured_last_modified
expires_date
date_status_change
status
products_to_categories

products_id
categories_id
Products

products_id
products_quantity
products_model
products_image
products_price
products_date_added
products_last_modified
products_date_available
products_weight
products_status
products_tax_class_id
manufacturers_id
products_ordered

How To Count() From Multiple Tables
I have 3 tables as follows:

Lesson:

lesson_id | lesson_name |
-------------------------|
1 | Lesson One |
2 | Lesson Two |
3 | Lesson Three |
4 | Lesson Four |
--------------------------

student_lesson:

lesson_id | student_id |
----------------------|
1 | 44 |
1 | 45 |
1 | 46 |
1 | 47 |
----------------------|

class_lesson:

lesson_id | class_id |
-------------------|
1 | 901 |
2 | 902 |
3 | 903 |
4 | 904 |
-------------------|

The following query:

SELECT crs_lesson.lesson_id, lesson_name,
count(crs_student_lesson.student_id) AS student_count,
count(crs_class_lesson.class_id) AS class_count
FROM crs_lesson
LEFT JOIN crs_student_lesson USING (lesson_id)
LEFT JOIN crs_class_lesson USING (lesson_id)
GROUP BY crs_lesson.lesson_id

Produces the following result:

lesson_id | lesson_name | student_count | class_count |
------------------------------------------------------|
1 | Lesson One | 4 | 4 |
2 | Lesson Two | 0 | 0 |
3 | Lesson Three | 0 | 0 |
4 | Lesson Four | 0 | 0 |
------------------------------------------------------|

This shows that the second count is always set to the same value as the first count even though the values should be different. Does anyone know how to obtain the correct values?

Delete From Multiple Tables
Having a bit of a problem deleting from multiple tables (hey, who doesnt!).

So I have 3 tables, news, comments and reviews.

News has a autoincrement ID as the primary key. This is represented in the comments and reviews table as a field called newsID (comments and reviews have a seperate autoinicrement ID as the primary key).

I pass into the query a $nid which is the ID of the news story I want deleted from the news table.

mySQL v4.0.21

DELETE FROM news, reviews, comments WHERE news.id = '$nid' AND comments.newsID = '$nid' AND reviews.newsID = '$nid'
Unfortunately this dont work and I think its because of the joins. Any advise on how to formulate the sql correctly?

SQL Query Across Multiple Tables
I wish to make a 'Latest Comments' section, but I am unsure of the most efficient query to use.

I have comments spread across 6 different tables, named as below;

cms_artistcomments
cms_featurecomments
cms_freshtalentcomments
cms_newscomments
cms_reviewcomments
cms_wallpapercomments

Each set up as follows;

comment_id
artist_id (changes for each table)
comment_author
comment_post
comment_date

What I would like is to extract 'so many' number of comments and in reverse date order, to give the latest 'so many' comments.

Best Way To Update Multiple Tables At Once
I need to update a few tables at once. I have a form, were I fill in some information about a concert, which updates thez concert-table. Next to this, I need to update the bands-table, the statistics-table, and some others. What would be the safest, and fastest way to achieve this?

Updating Multiple Tables
My webserver is running an older version of MySQL (3.23.56 ). I am trying to update a table based on criteria from two tables, and I can't seem to get the syntax quite right. Here is what I tried:

UPDATE (member, unit)
SET member.status_id = 3 WHERE member.unit_id = unit.unit_id AND unit.etime > 0

Does anyone know an older syntax to do this?

Querying Multiple Tables
I need to query multiple (15) tables to build a data entry screen. Is better to create 15 separate SELECT statements and query each table independently or create 1 large SELECT and query one time?

Query On Multiple Tables
I'm new to mySQL and databases and was hoping someone would be kind enough to help me out.

I have two tables, events and images. I want to get the data from column image_path based upon two variables, $event_date and $event_id.

The date is only in the events table but event_id is a foriegn key in the images table.

I'm really struggling with the syntax and have got really good at generating errors!

Query Multiple Tables
I have a database called 'xxx', i need to query 3 tables inside this database called a, b, and c. Each table has a common field called artist: i need to select artist from all 3 tables (a, b and c) and group the results by artist. It has a catch of i needs them all to comply with a LIKE statement like below. So far i have:

SELECT a.artist, b.artist, c.artist FROM a, b, c WHERE a.artist, b.artist, c.artist LIKE '$_GET[url]%' GROUP BY a.artist, b.artist, c.artist ORDER BY a.artist, b.artist, c.artist ASC

I know this doesnt work but im guessing its along those lines on how it does work?

Query Across Multiple Tables?
I am new to mysql and need help on how to join tables. I have a database
which contains 4 tables, the main table contains information by date order
and the other 3 contain data also in date order. So I need to write a
query that retrieves all the information for one record, lets say I want to
query on the main table any entry that is for the 2004-01-06 and this date
is also in a field called 'Date' in the other tables, how do I go about
pulling back all the info for that entry across all the tables?

I have tried doing this:

WHERE main.Date = table2.Date AND table3.Date AND table4.date

however it returns loads of data when it should only pull back the one
entry.

One Query For Multiple Tables
I am using MySQL 4.0

I have 4 tables w/ the same columns. Table1, Table2, Table3, Table4, under database=survey. I want to count how many surveys have been completed under each Table. Using one query, I want the total of each Count(survey_id) from each table. Basically, Select Count(Survey_id) from Table1 + Select Count(Survey_id) from Table2 + Select Count(Survey_id) from Table3 + Select Count(Survey_id) from Table4. HOw can i do this in one query?

Query Multiple Tables
I have multiple tables I'd like to retrieve data from using the same query statment.
All tables are setup as follows:

ID
Reference
Value
Timestamp

I would like to select all the data from "table1" and "table2" where there timestamp is between Jan 01 2005 9:00:00 am till Jan 02 2005 16:00:00

Is this possible?

Query From Multiple Tables
I want to construct one single query that gets values from three different tables

Table "A" has a column "id"
Table "B" has columns "topicid", "topic" and "userid"
Table "C" has coluns "userid" and "address"

A.id links to B.topic_id.
B.userid links to C.userid

I want to get A.id, B.subject, C.address from all three tables in one query.

Due to some other constraint, I must use left join with the first two tables:

select A.id, B.topic from A left join B on (A.id = B.topicid);

How can modify the above query to get C.address in the same query?

Counting From Multiple Tables
I have a question about my query. Hopefully I place it in the right section.
I want to count some values from several tables.
When I run this query, the count values are wrong. When I only count from table 'bestellingen' (orders) or 'facturen' (bills), the count values match with what they should be.

The query:
"SELECT l.naam AS leverancier, COUNT(b.bestellingen_id) AS 'totaal aantal bestellingen' , COUNT(bo.bestelorders_id) AS 'totaal aantal bestelorders', COUNT(f.facturen_id) AS 'totaal aantal facturen'
FROM leveranciers AS l ...................................

Deleting Multiple Tables
I am trying to create a Delete User section in a website that will delete all the related information stored in different tables and held togethrer by the session variable 'username' The code I have been trying to do this with is as follows: Code:

Using GROUP BY On Multiple Tables
I am trying to use various aggregate functions on multiple tables, with limited success. My first attempt looked something like:

SELECT dt.docId, COUNT(l.lineId), SUM(dt.weight) AS tot FROM DocumentTags dt LEFT JOIN Lines l ON dt.docId = lt.docId WHERE dt.tag = "example" GROUP BY dt.docId ORDER BY tot DESC

As you probably know, the above returns less than ideal results, multiplying the COUNT and SUM values. So, round two looks like:

SELECT dt.docId, (SELECT COUNT(l.lineId) FROM Lines l WHERE lt.docId = dt.docId), SUM(dt.weight) AS tot FROM DocumentTags dt WHERE dt.tag = "example" GROUP BY dt.docId ORDER BY tot DESC

This seems to work well, but I can't imagine it is very quick. Is there any way to modify the first example, to allow for multiple GROUP BYs on multiple tables?

Data From Multiple Tables
I am working on a website about sea shells and basically I have 3 tables:

- shell_tb; (table of shells)
- site_tb; (sites where they can be found)
- instrmt_tb; (instruments used for it)

As you may already have guessed there are two columns in shell_tb (site and inst) which may use more than one item from the site_tb and instrmt_tb.
1st. How do I configure the columns in shell_tb in order to accept more than one value?
(I tried:

CREATE TABLE shell_tb (
shell_id INT(1) NOT NULL AUTO_INCREMENT,
shell_name CHAR(40) NOT NULL,
site INT(20) NOT NULL,
inst INT(20) NOT NULL,
PRIMARY KEY (shell_id)
) TYPE=MyISAM;

but no luck at all when inserting more than on value into inst column.) 2nd. How do I retrieve the info from there?

Multiple Tables Request
I would like to perform a query on two tables. I want only records that match the conditions in each of the base.

First bas would be : where date>mktime()
Second would be : where test is not empty

When I performed with the following SELECT, I have too many records with strange joining.

$now=mktime()
SELECT *
FROM table1, table2
WHERE table1.date>$now
AND table2.test<>""

Selecting From Multiple Tables
I am trying to select from three different tables. I can select from two of the tables, but once I add another table into the WHERE clause, there are no results.

here is my query:

SELECT k.keyword, k.keyword_id, l.label, uk.searchable, uk.displayable, uk.profile, uk.comment

FROM keywords as k, user_keywords as uk, labels as l

WHERE uk.user_id = $userid AND uk.keyword_id = $id AND k.keyword_id = $id AND l.label_id = uk.label_id";

What I am trying to do is get the name of the label (l.label) from the labels table where the label_id in the labels table is the same as the label_id that is in the user_keywords table. I have done this before successfully. This time, all of the other ids are provided (user id and keyword id), but we must distinguish what the label id is in the scope of the MySQL statement.

Inserting Into Multiple Tables
I am having trouble finding any sort of information that can assist me with the following. Taking information from a single form, then inserting it into multiple tables all at once.

Creating Multiple Tables At Once
I have made my own CMS and I want to create an install.php for users so they can create the tables in MySQL. What's the easiest way of creating multiple tables in SQL using php?

At the moment the only way I can do it is to write it out manually but there are about 30 tables. Would using .sql tables be an option? If so, how do I use them?

Updates To Multiple Tables
can i insert multiple records into multiple tables using MySQL:

statement.executeUpdate("INSERT INTO members, members2, members3, members4, members5 SET
La_Na='"+lana+"', Fi_Na='"+fina+"', NRIC='"+nric+"', CCode='"+ccode+"'");

is this possible?

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?