MYSQL :: Updating Multiple Tables

Updating Multiple Rows
The following UPDATE query works fine if run directly into phpMyAdmin (I take the $sql output of the script with the data in it and paste it into phpMyAdmin).

But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is: Code:

Updating Multiple Rows
I need to update multipule rows a once, using PHP. Here's what I mean.

I have a mysql query out put the data as a form like this:

Item 1
<input type="hidden" name="id" value="1">
<input type="text" name="order" value="">

Item 2
<input type="hidden" name="id" value="3">
<input type="text" name="order" value="">

Item 3
<input type="hidden" name="id" value="4">
<input type="text" name="order" value="">
<input type="submit">

Order need to be updated. I could setup to do a loop with multiple queries.

Updating Multiple Rows
I am running the following UPDATE statement

UPDATE history
set t_own = t_user * 0.75
where user_cur = 7
and lg = 'Local'

t_own and t_user are defined as DECIMAL 12,2

650 rows should be updated, but i get

error 1136 Column count doesn't match value count at row 1.

Updating Multiple Rows.
I went to MySQL home page and found one comment that kind of tried to explain it but it was written in such a manner that I could make no sense of it at all.
I am trying to find a way to update a row with 12 entries in a table that has 12 teams (teamid) and 12 ranks (r1,r2,r3...r12) the data to update the teamid comes from a form and they would all be updated (unless everything remains the same) with the use of the said form.
I am not certain how to make this work (call the table 'team')

PHP Code:

UPDATE team SET teamid='team1', teamid='team2'...teamid='team12' WHERE?

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

Updating Table After Doing Multiple INSERT
I'm currently trying to do the following :

INSERT INTO sometable (userID,otherstuff) select ....blah blah ORDER BY RAND() LIMIT 10;

So I do that to get 10 random entries which works fine, however is there an easy way to then say...

For each of the 10 random userID's just inserted update another-field in another table with matching userID to the ones just inserted?

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Updating Multiple Username In One User Field
how to update one field with 5 name of users and looping it
till the end of records.if i do this

UPDATE CALL_BARR_LIST_READY
SET [USER]=''
WHERE [USER] ='KUREIL'

it will filled all the records with KUREIL as a USER.
but now i have 5 users and > 1000 records to update
where each user get the same total no of records.

Updating Tables
I have a database called "returnsMySQL". Inside there are two tables called "originaldate" and tempdate".

The "originaldate" has dates but it is not a date type field. 3 Years ago the field started saving dates under the short date format.

Example:

9/2/01

In order to get correct searches, I need this field to be updated to the 4 digit year:
Example
9/2/2001

I have tried using Active Server Pages, querying the database and trying to format the results using "FormatDateTime" the get the correct date but I have not had any luck taking the formatted date and saving it to the field "tempdate".

There are about 15,000 records in the database.

What would be my best option to accomplish this?

Updating Tables
I want to update /insert data in one table of one database from another table in another databes. Both databases are Mysql but reside in different computer on LAN. Is this possible in MYSQL. If yes, can someone pl give the syntax. I am using VB6 , ADO ( with connection string for connecting to MYSQL)

Enquiry About Updating/upgrading Tables
I have posted this query on a couple of phpBB support forums but have come here because the problem is not so much specifically related to the forum software and mods but to understand what exactly is happening when I try to upgrade an existing set of tables complete with data to a newer set of tables with upgraded properties...

The results of the php sql script is:

This list is a result of the SQL queries needed for mod

Banner mod
[Already added] line: 1 , CREATE TABLE phpbb_banner ( banner_id MEDIUMINT(8) UNSIGNED NOT NULL, banner_name TEXT NOT NULL, banner_spot SMALLINT(1) UNSIGNED NOT NULL, banner_forum MEDIUMINT(8) UNSIGNED NOT NULL, banner_description VARCHAR(30) NOT NULL, banner_url VARCHAR(128) NOT NULL, banner_owner ......................................

Front End For Updating Several Related Tables
Is there a simple front-end that would let me select records from
multiple related tables and edit their field data?

Right now, I am opening each table individually in mysql control center,
entering the new record, or editing the existing one, then moving on to
the next table.

I know there’s got to be something better, and I am to
busy (spelled overloaded) to create my own from scratch. I use python
now for most of my sql scripts, and PHP on occasion, but could figure
out and modify something simple if necessary.

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 .

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).

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?

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?