Multiple Database, 1 Shared Table, Is It Possible?
I Used MySQL for a long time dev under PHP 3, 4 and now 5
now i'm facing a problems that i solved in the past with php, but i wonder if MySQL could do it on it's own, that'll help me a lot.
Multiple database, 1 shared table, is it possible?
i mean, i already done with 5 install of Xoops CMS and modified it that every install is on the same database, with deifferents prefixes (like db1_ db2_ ...)
then i mod Xoops for each time it called for it's users or saved open sessions id in the DB it use the same table on all install... so the users seamlessly wonder about in 5 differents xoops install without disconnect and change username.
now i face the same problem, i need a multi-install or a single install of a core system that will act as a multi-install sofware, but with the same set of users, sessions and some other things.
View Complete Forum Thread with Replies
Related Forum Messages:
Can A MySQL Database Be Shared Between Windows And Linux?
I've got Windows and Linux running on my computer, on seperate partitions. I also have a shared partition for stroing files, and can be read and written by either system. I also have MySQL installed on both systems. When I make changes to the database in Windows, I have to do a dump of the database (or single tables) to a text file on my shared partition. I then have to switch into Linux and load that file into MySQL there. This is a bit of a hassle. Is it possible to somehow store the database file on the shared partition, and use the same database from both OSes? This would be very convenient, since I wouldn't have to maintain two copies of the same database!
View Replies !
How Often Is Best To Create New Database, Migrate Data With Shared Hosting?
I have got a web site that I have ben working on since March, 2007. It is a news reporting web site. It is replacing an older web site. I created a new database (it uses the MySQL database) last March when I started working on the new web site. I have migrated all of the news content from the old web site database to the new web site database. I have a shared web hosting. It's my understanding that the hosting service provider fills up one server with databases then starts a new server with databases. For example, the IP for the server that is hosting the current database for the new web site is 10.6.166.5 I just created a new database (the hosting allows you to have ten databases per shared hosting). The IP for the server for the recently created database for the new web site hosting is 10.6.166.92 If the servers are ordered sequentially there have been 87 new servers filled up with databases since last March (i.e. 10.6.166.92 recently created IP minus 10.6.166.5 March, 2007 IP equals 87 servers). The problem with migrating the nine-month old database to the new database is that I have got over a thousand news stories and two thousand pictures stored in the database with the 10.6.166.5 IP address. I would have to copy and paste all of the data into the phpMyAdmin program or write a PHP script that would automate the process. Is there any benefit to migrating the 10.6.166.5 March, 2007 IP database over to the more recently created 10.6.166.92 IP database? Would it be reasonable to expect the more recently created database to perform better over the next 12 to 18 months than the one created nine months ago?
View Replies !
UDF Shared Library
I am trying to create a User Defined Function (UDF) using mySql 4.1.11 (mysql-max-4.1.11 for Solaris 8). I am trying to get the examples to work (udf_example.so) I have successfully compiled it and copied the resulting .so file to /usr/lib. The command 'crle' (like ldconfig on Linux) shows that /usr/lib is in the library path, but when I type in mysql: CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so" I get the error 1126, Can't open shared library 'udfexample.so' I've tries renaming the library 'libudf_example.so', but that didn't work either. I don't know how to check what libraries mysql is looking through. I am just guessing that it uses the library path from 'crle'. Any ideas?
View Replies !
Shared Password
I've installed mySQL out of the box and set a new password for the root account, which works in the MySQL monitor but not with the mysqladmin tool. Please can anyone advise whats going on or if I have to do anything else to get access to the admin tool?
View Replies !
MySQL And Shared Storage
--=_D38DA341.B3D2A80A Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I would like to set up a round robin cluster with mysql. The round robin = would be done through the DNS to two different servers. Each of those = servers would have mysql installed and each would reference the same data = directory on our SAN. Are there any problems with doing this? =20
View Replies !
MySQL-shared RPM Install
I'm having problems installing the shaired libraries. This RPM came from the MySQL.com download site. [root@localhost src]# rpm -i MySQL-shared-4.0.17-0.i386.rpm warning: MySQL-shared-4.0.17-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
View Replies !
Replication Using A Shared Fileserver
I thought of a radical way to do replication of MySQL servers. Let's assume two MySQl servers access a shared mapped storage (/mnt/db) which is a local mount to a fileserver on the network. This fileserver is shared between the MySQL servers; which means any writes to /mnt/db appears immediately on /mnt/db on the other MySQL machine. Two MySQL servers may attempt to modify to the same file if we don't use synchronization. My question is, first, can we do this without making patches to MySQL code, and somehow using some synchronization mechanism accross the two machines running MySQL server each. Or we need to patch MySQL code to support such architecture. Second, is it trivial or impossible to do, how much man-time it requires roughly.
View Replies !
Connections On Shared Server
I frequently get the error message: Warning: mysql_connect(): User user1 has already more than 'max_user_connections' active connections in /home/.sites/60/site23/web/Connections/mysite.php on line 9 It's commercial web hosting on a shared server so I have no control over the server side of things. The php scripting has been written using the GUI in Dreamweaver. Is there anything I can do to resolve this? Should I be using mysql_connect or mysql_pconnect, or is this not the problem? Do I need to include somewhere in my scripts a snippet which disconnects from the server? If so where would I put this? I am not all that familiar with php and mySQL, though I understand parts of it when looking at the code. The database connection is made via a connection.php file produced by Dreamweaver which includes the database details etc. My max connections are only 10. Is this the issue? It only seems to appear when I use php to edit or add to the mySQL database.
View Replies !
Find Shared Values
Say I have a one long Table of people and the foods they like. How do I find all the foods that are like by both of two particular people? ie David peas Kate sausages Kate eggs Bill eggs Kate peas Fred sausages Fred peas and i want to find out what both Fred and Fate like:
View Replies !
How To Find Out Values Shared By Some Members
I have a table contains words extracted from documents which are belonging to a number of categories. Each category contains a number of documents. For each category, I want to find out those words which occur at least in N documents belonging to this category. Code:....
View Replies !
Shared Column Data Transfer
I have two tables with one shared column. One other column in table 2 is empty but has to get filled with a value from one column in table 1. select * from tbl1, tbl2 where tbl1.column = tbl2.column; Above query gives me the results that need to be updated. What will the update statement look like?
View Replies !
Security For Shared Server.xml Passwords
I have an ISP that has the server.xml in a shared environment. The server.xml file is where my password is available for database access under connection pooling. I really would like to use connection pooling. Does anyone know how I can protect my password from being viewed by others using the same server.xml file? I cannot get the administrator to change the protection of the file because others in the shared environment need to be able to view it.
View Replies !
Cannot Open Shared Object File!
i just upgraded from MySQL 4.1 to MySQL 5.1. But when i try to run a program, i'm getting this error: error while loading shared libraries: libmysqlclient.so.14: cannot open shared object file: No such file or directory
View Replies !
ODBC Connection To MySQL On Shared Hosted Website
I'm trying to connect to a mySQL database that exists in connection w/ my website. I created the database using c-panel. Everything that I use to access the website from my site uses "localhost" as the host name. I installed the ODBC drivers for Windows on my XP machine and am trying to connect from my local machine. I am not sure what I should use for the hostname in that case -- I tried www.mysitename.com as the host but that didn't work. I also tried the IP address, but that didn't work either. I asked my host's support team, but haven't heard back from them yet. It's a shared hosting service. Any ideas on what the hostname should be?
View Replies !
Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database. The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles. I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field. I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.
View Replies !
Why Dont Shared Hosting Accounts Provide Stored Procedures And Triggers?
i am searching for 3 days now for a reliable, fast hosting company, which supports stored procedures and triggers on mysql5. most of them dont support this, because mysql5 has a design-flaw in which it stores procedures and triggers on a root level-area (tables mysql information_schema). due this logic, most of the hosting companies wont allow you to create/run procedures and triggers. funny, that hosting companies forbid you to use new technologies and methods to build faster.
View Replies !
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?
View Replies !
Is It Better To Have One Big Table Or Break The Table Into Multiple Tables
would like to know what would be the more efficient method if were to create a product table that had multiple values that i would seldom reference? When sql calls for a table does it reference the whole table in the query or only specific cells? for instance Product(primary key) | Price(2008) | Price(1998)| Price (1989)| Each of the Prices will have sub prices of each month in the that year. Should i make individual tables for the prices for each year instead of dumping them altogether into one table? is this more efficient when web users get the data? what are the advantages and disadvantages in each method.
View Replies !
Multiple Database Select
How do I at the best way perform select statements over multiple databases? I have a couple of databases containing the same table definitions with diffrent data. Now I want them to act as a single database giving me one answer on a select statement but the answer fetched from all my defined databases. Right now the databases are on the same node and I guess it is possible to do something like: select * from db1.table1, db2.table1 etc etc. but I really do not want to modify all my sql statements if another database would comes into picture or is revoved from the set.
View Replies !
Multiple Database Connections
I have a simple music script that gets artists from a db and spits them out. On another content box I have the "content", of the artist. It will take songs and spit them out. Sadly, these are on different databases. How can I open 2 databases and get info from both?
View Replies !
Multiple Clients, One Table Or Each A Table
I have been using SQL for a few years for small projects, but recently I have a client who wants a contact system for about 1000 users to use and each user will have about 1000 contacts. My question is should I make a table for each user for their contacts or store all 1,000,000 contacts in one table and then have the system sort through them to display each user's specific contacts. I am trying to find the quickest way for my system to display the contacts. I have read many things on multiple databases or one but I was wondering if I should have a unique table for each user to store their contacts in or have one massive table with everyones contacts?
View Replies !
Multiple Databases Or Single Database
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a single database, so that I can access it with a single Database Wrapper Object (PDO). Should I split it into multiple databases? Will it affect the speed?
View Replies !
Multiple Instances: Moving Database?
I have two mysql instances on the same box. I would like to move one database to the 2nd instance. Does anyone know of an easy way to accomplish this, preferably without going through the dump/import process?
View Replies !
Php/Mysql Multiple Database Select
I'm trying to SELECT from two Mysql Databases in one SELECT statement. Both databases are on the same server. I think I have the SELECT statement syntax correct. What I don't get, is how to connect to query multiple databases at once. I've been messing with the following: $db1 = @mysql_connect("localhost","db1","password"); $db2 = @mysql_connect("localhost","db2","password"); mysql_select_db("db1",$db1); mysql_select_db("db2",$db2); $query = "SELECT db1.field1, db2.field1 FROM db1.table1, db2.table1; $post_table = mysql_query($query); I don't have a strong understand of database connections,
View Replies !
Database Design - Multiple Primary Keys
I am currently designing a simple service orders database. I have played around with MySQL a bit but this is the first time I'm using it in anger, I have a few design queries to make sure I am implementing the database correctly. Currently I have a number of tables, the first being the package table (lists the different service names - such as small, medium etc). Note in the tables I have trimmed a lot of the unnecessary stuff to save space: Package Table: packageID name PRIMARY KEY (packageID) The package durations table lists the different duartions of packages in months, that the customer can select. I don't want duplicate durations for a particular package, so I make unique primary key based on the packageID and duration: Code:
View Replies !
Multiple Instances :: Pointing To The Same Data Files/the Same Database?
I think I know the answer to this question, but is it possible and safe to have multiple instances of MySQL pointing to the same data files/the same database? I'm guessing that because only one instance can lock the files at any time that this won't work, but some confirmation would be good. I checked the MySQL documentation, but didn't see anything.
View Replies !
Database Design: How Best To Manage Data Across Multiple Schemas / Databases
Our organization is looking for the best design for managing shared data. Specifically we wish to have a master table for data related to a Person. That person table would be shared by any other databases within the company that needed it. Of course, we do not want to share the whole master table, just the rows relevant to specific application that will be using it. We also want the sharing to be as transparent as possible to both the users and the support / development staff. Using a view seems like the way to go, BUT...each application has tables with FKs to the Person table. We can't do FKs to a view. We can have the application reference the view for selects / updates to the Person table, and have the other tables define their FKs to the master Person table, but we're a little worried about security since we can't narrow their permissions to only certain columns & rows like in the view. Oh, I am not doing a good job of explaining this.
View Replies !
Multiple Charsets In Same Table
I have developed an internet application in PHP/mySQL which is now getting internationalised and will start to contain information from contries like Poland, Hungary, Czech Republic etc. I'm having troublewith the charset as the table which until now has been holding all the information is in latin1 but I now need to switch to an eastern european one as well. Is there a possibility of making a table use two charsets at the same time? Has anyone else stumbled across this problem.
View Replies !
Multiple Table Query
I'm building an entertainment/food/festival website for my community using PHP and MySQL and have hit my first "speedbump" in the process. I have a simple text form that I need to query 3 seperate tables in my database (venue_profiles, artist_profiles and events) so if someone searches for a term, it will return a numbered list of everything regardless if it is a venue/artist profile or event. Everything was working fine when I only had to query 1 table but now I've been thrown a curveball and need it to search 3. Here are the tables:fields in question. 1) venue_profiles: id, name, city, category, description 2) artist_profiles: id, name, description 3) events: id, title, city, description What will the query look like for what I need? and how do I go about creating one simple list that includes all the results from all 3 tables?
View Replies !
Multiple Tables Or One Table
I'm designing my database right now and I've come to a point where I have to make a decision that will be very important to the future use of this database: Using multiple tables or one main table on semi-related products... I have found a few topics on this forum that helped me, but still didn't get me to decide one way or another... The question is: where do you draw the line on similarities in the products? Let me explain... The products I want in the table (or tables) have mostly the same columns (id, suggested price, name, ...) but all of them also have different columns (DVD's have bonus material, CD's have tracks, ...). Understand? If it's not clear I can explain a little better if you ask (but I think I made myself clear).
View Replies !
Multiple Instances In Another Table
I have 2 tables, and want to compare a field in the first table to multiple in the other I'll try to write it not in pseudo code, to try and explain what I would like to do table1-structure field_need_this | field1 | field2 | ... table2-structure field3 | field4 | field5| ... SELECT field_need_this FROM table1 WHERE field1 NOT EQUAL TO (SELECT field4 FROM table2 WHERE value 'something')*this could for example be 4 values. If field1 isn't the same as any of them I would like the field_need_this
View Replies !
Multiple-table UPDATE.
I'm trying to update multiple-tables at the same time but I read somewhere that you could do it if you had mysql version 4.0.4. But currently, I have version 4.0.0 and I want to know how to do it with this version
View Replies !
Multiple Joins To The Same Table?
In summary, to put it as simply as posiible users table: id, username ref table: id, user1_id, user2_id I would like to return: ref.id, users.username(ref.user1_id), users_username(ref.user2_id)
View Replies !
Multiple Table Delete
I have the following script that will delete records from multiple tables: DELETE course_session_date_time_location, course_enroll FROM course_session_date_time_location AS s, course_enroll AS e WHERE s.course_ID = '$course_ID' AND e.course_ID = '$course_ID' AND s.course_session = '$course_session' AND e.course_session = '$course_session' AND s.course_date = '$course_date' AND e.course_date = '$course_date' My problem is that sometimes there are no records in table "course_enroll". Is there a way to alter my WHERE syntax so that my script will delete from both tables when records exist in both, and will delete from just table "course_session_date_time_location" when records only exist there?
View Replies !
Multiple Table Deletion
PHP Code: delete b.*,c.*,d.*,e.*,h.*,i.*,j.* from dailyIntake b, dailyTasks c, diary d, exercise e, loginDetails h, userInfo i, userStatistics j where b.email = 'sss@hotmail.co.uk' and c.email = 'sss@hotmail.co.uk' and d.email = 'sss1983@hotmail.co.uk' and e.email = 'sss1983@hotmail.co.uk' and h.email = 'sss1983@hotmail.co.uk' and j.email = 'sss1983@hotmail.co.uk' and i.email = 'sss1983@hotmail.co.uk' Basically i want to delete every row in these tables where the email is *that*. but it doesnt seem to work? i run it in phpmyAdmin and it says that the query has executed but the records are still there? andother q: what happens if say in one of these tables there is no row with that email? could that make the query NOT work?
View Replies !
One Table Or Multiple Tables
I'm working on a project that would use huge volume of MySQL-service. So, I ask You give me an advice on how to implement it out better. The question is, What is the best solution in matter of reliability and speed: to use one table where the users can share the data among themselves or to use multiple tables for each user in which case one user that is permitted to share a data with another (on MySQL-server control level) can simply copy his data to the table of that user. But in this letter case I get thousands of those table! May there is a more fair solution. In any way, please share how would professional would make this app.
View Replies !
Multiple Table Search
Firstly I am using ASP pages and a MySQL database with 8 tables in it. These 8 tables contain userdata each table holds different data about the user. What I need to produce is a search engine that will perform a search on all tables, displaying the results, with a description of what it has found (stored within the table where the search item was found). There are only certian fields that can be searched on within each table. I am having great troubles with this as if the user has multiple entries in any table I get that amount of results plus the result of the item itself.
View Replies !
Multiple Table Issue
I am trying to write a SELECT statement that will allow me to combined two tables in a single database. Then I would like it to sort the content of the tables by a specific column called 'urgent'. This is to be used in PHP based webpage so I need to query all fields using '*'. I have tried this: "SELECT * FROM rfs UNION ALL SELECT * FROM dla ORDER BY urgent ASC;" The SELECT statment above comes back with an ERROR reading, "The used SELECT statements have a different number of columns." I removed the "*" from the statement and used the field name 'urgent' and it works ,however, it leaves me with a query that containing none of the other fields. Is their a MYSQL command that will allow me to do this?
View Replies !
Multiple Loads Into One Table
I have data from the past 6 years stored in one file per day that I want to combine into one table. Essentially I want to set up some sort of loop around the 'load data infile' command, with the filename changing each time. Is this possible within the SQL framework? Or do I have to resort to using perl or something similar?
View Replies !
Multiple Joins To One Table
I have a table called "tasks" which has, amongst others, 3 columns called raised_by, assigned_to, parked_with these columns contain the user ids of individuals who have specific responsibilities for the task. I have another table called "users" that contains all the user information, such as login names and passwords as well as two columns called user_id, display_name I am trying to write a query that will return the display_name for the raised_by, assigned_to and parked_with fields.
View Replies !
Multiple Inner Join Within The Same Table
I want to create a new table from an image table where the columns would be the image_path where image_type =1 and image_type=2 for images with the same slide_id. I used the following query with an error: select slide_id, image_path as thumb_path from image as thumb where thumb.image_type=1 inner join image.image_path on image.slide_id=thumb.slide_id where image.image_type=2; Below is the image table: image image_idslide_idimage_typeimage_path 111/Image/full.jpg 212/Image/thumb.jpg 321/Image/full.jpg 422/Image/thumb.jpg 531/Image/full.jpg 632/Image/thumb.jpg 741/Image/full.jpg 842/Image/thumb.jpg
View Replies !
Select Multiple Ids As Name From Table
select * from picks; | id | name1 | name2 | expire | +----+-------+-------+------------+ | 1 | 2 | 2 | 2009-11-01 | | 1 | 1 | 1 | 2009-11-01 | select * from accounts; | uid | gid | account | realname | | 1 | 1 | Fname1 | fname1 lname1 | | 2 | 1 | Fname2 | fname2 lname2 | select realname,name2 as Picked from picks,accounts where name1=uid order by name1; | realname | Picked | | fname1 lname1 | 2 | | fname2 lname2 | 1 | I would like something like the following instead of the above results: | realname | Picked | | fname1 lname1 | fname2 lname2 | | fname2 lname2 | fname1 lname1 |
View Replies !
LIKE On Multiple Fields In Same Table.
Is it possible to make a LIKE search on multiple fields in the same table? (as the following sql example without that great repetition of word 'like'). My problem is: I have a single input on a browser, and inserting a single or multiple words (like a search engine) I would want to find that words. If I insert 'phrase example' I would want to find 'phrase' or 'example' or 'phrase example' (so the following example for this last possibility is wrong) I don't care about match against because I don't want to find exact words because it is not an advanced search. In the following example I would want to search words like 'orange', 'rank', 'frank' etc... I would want something like this following example on: same table, multiple fields and multiple words too.....
View Replies !
Multiple Update Same Table
I have a table that i need to have only 1 field in the same column be yes and the rest turned to no... I have tried SELECT * FROM tab1; UPDATE tab1 SET 'field' = YES WHERE id = 5; UPDATE tab1 set 'field' = NO WHERE id != 5; Obviously running two at the same time does not work but if i can somehow combine these two to do it all at once...
View Replies !
|
TRACKER
