MYSQL :: Views And Altering Base Tables

Altering A Table
Im currently trying to alter a table within my phpbb installation. The specific edit:

ALTER TABLE phpbb_users ADD user_from_school(25) NULL AFTER user_from;

MySQL seems to have a problem with this, giving me the error

"#1064 - You have an error in your SQL syntax near '(25) NULL AFTER user_from' at line 1"

Just a little background info, phpbb_users already exists. Im trying to create a new field in a users profile in which they can select which school they attend.

Any ideas what the problem is?

Altering A Table
I've already created my two tables I want to be related. Now I need to relate them. Would anyone please show me how to create a foreign key on a table that has already been created?

I tried the following syntax and got an error in MySQL Monitor:

mysql> alter table Main foreign key (Department) references Department(DepartmentID);

Altering My Table
basically i have two tables, cars and people.

cars:
make
model
engine

people:
name
address
sex

what i would like to do is alter my table to create a foreign key 'MAKE' in the people table. i dont know the syntax to do this.

Altering All Rows
I want to prepend something to a textfield in a given column for all rows. What's my best option

Example:

BEFORE:

col1
---------
234
456
789

AFTER:

col1
---------
abc234
abc456
abc789

Altering A Table
i have some kind of a problem:
I am using MySQL 5.0.20. When Creating a table i want to make a field unique which is not a primary key (varchar 8, collation latin_general_cs). I need one million recordsets, but when when making this field UNIQUE, it is not possible to insert more than 12.228 recordsets.

Altering Datatype
I'm having a little trouble and can't seem to find the correct command to change an existing field's datatype to a different one. Currently the field has datatype "int(10)" and I'd like to change it to "decimal(9, 1)".
If anyone can point me in the right direction to solving this problem.

Altering Date Fields
I'm brand new to MySQL and have just migrated my Access database to MySQL.
In Access I have two date fields, one for Date (DateLogged) and another for Time (TimeLogged). Bad idea, but I didn't know better at the time.
Nw, in MySQL, the DateLogged field has the date information from Access and also the time as 00:00:00. The TimeLogged field has the time information with the date set as 1899-12-30.
Is there a way in which I can join these fields, and just have one datetime field? Or is it impossible?

Altering My Table To Add Foreign Key
i have two tables, cars and people.

cars:
make
model
engine

people:
name
address
sex

what i would like to do is alter my table to create a foreign key 'MAKE' in the people table. i dont know the syntax to do this.

Arrays And Altering Directory
I am new to this forum. I intend to share my knowledge of MySQL wid everyone here and also gain more than wat i can share. My queries are:
1.Does MySQL provide arrays.
2.Can i change the directory which stores the databases that are created by the user.
Besides Pls check www.mysql.com homepage abt a security alert regardin MySQL on Windows. there is sum worm call UDF........anyways i hope to obtain a reply soon.

Altering Table And Field Names
I would like to copy the values from each record in the Program table to =
another table (tblPrograms). This is the syntax I came up with:=20

select * from Program insert tblPrograms set ProgramCode=3DLocation, =
ProgramName=3DDescription;

However, it fails without telling me why. Perhaps someone could correct me =
or re-direct me on this issue?

Altering Huge Innodb Table
I need to add an index to an Innodb table 110gb big. When I last tried it took forever and made the db unusable to visitors.

My solution was to create another table with the same structure with the extra index and copy everything over with a php script. This is working so far but is taking a long time and still hitting the performance for visitors. Is my solution the best way or is there another?

Altering Table Structure In Live Database?
I want to add a field to a table in a database that is live and being
accessed from the web. I'm using phpMyAdmin and when I try to add the field
I get error #1142 (ER_TABLEACCESS_DENIED_ERROR) "ALTER command denied to
user 'xxx' for table 'yyy'"

I'm a newbie to MySQL but I guess that's a reasonable error as there are
probably open connections. If that is the reason, is there a way I can force
all connections to close and not reopen while I make the alteration to the
table structure? It won't matter in this case that users will experience
access problems while this is taking place (all access is read-only too.)

If possible I want to do this without stopping the MySQL server - it is a
shared resource AFAIK and the host is in the US (I'm in the UK) so easiest
to coordinate if I can do it all from here if possible.

Altering Values In Rows Into Sequential Numbers
I have a database that I need to import into another application that has a different database structure/format. I changed as much as I can to make it compatible for importing except for one thing.

The old database uses the category and parent category for referencing whereas the new one uses category_id and parent_id

Quick example: (the tables have more fields but this is all I'm trying to figure out right now) Code:

First Base
I'm trying to follow some simple instructions but I just can't seem to get anywhere. I'm running it through XP, I've installed it and can get into winmysqladmin no problems at all. It didn't ask me for a password or anything when I started which some of the books suggested.

I set up a user s_roper with a password and went into the command prompt to create a database. I typed in the username and password and it was all correct but it just keeps saying access denied. I set up the root user and set a password for that but it keeps saying the same :access denied for user: 'root@localhost' <using password: Yes>

So I decided perhaps I'd done something wrong so I removed it and started again but it seems even when I remove it it still holds the passwords and users.I'm completely stuck.

Recreate Data-base?
if i build a temprorary DB on my server for a clients site while i create it. is there someway i can make the DB give me a creation code that i can give to the client to re-create my entire temporary DB?

/var/lib/mysql/base-bin Files?
I run a hsoting company and we have a dedicated server. Our server details are at below:

OS: CentOs 4.5

Recently I have discovered server disk usage is getting bigger with no reason. I have googled the issue and find out exim temp files were getting very big. I have deleted them.

Also I have figured out /var/lib/mysql/ directory is another large directory. In the directory there are base-bin files. Can you tell what these base-bin files for. I do not think they are neccessary for server but I did not want to delete them without knowing what they really are.

Restore To New Data Base
I am using a heavily modded PHPBB forum and want to convert all my posts, users, etc, into a new PHPBB forum using some other mods. I am using MySQL 3.23.58 .

I ran the conversion steps as the creator fo the modded forum stated but at the point where I run the install.php page I get an error that says "installation aborted as table xxxxxx already exists."

Is there a way around this like perhaps makign the new tables over write the old tables? I need to keep the tables as they are or I will lose the posts and such.

One Big Data Base Or Many Smaller Ones
I am creating an on-line course and would like to know if it will make a
difference in the way I set up my database(s).

Option 1 : Create one large Database for the course

Here I would prefix the tables with their functions, ie. reg_ quizzes_
forum_ etc.

Option 2 : Create a smaller Database for each function

Here I would create a registration database, a quizzes database a forum
database etc.

I do not have my own server, so I would have to get my ISP to create
each database, which might be a hassle for them.

Data Base Transfer
Im trying to learn if it is possible to transfer data from one mysql to another with a diferent structure ie different table names etc am I beating my head against a brick wall? .... its thick enuf.

LINKs For A Data Base
I am trying to create a database that have email addresses. I would lie it so that when some one clicks on the email address, it would open up the email ( mailto:xxxx.xxxxx.com) , yet I want it to display as xxxx.xxxxx.com (as a hyperlink)
The above answer might answer the next quest, but if not please advise on it as well.
I would like to create a link to a different page in a table (pictures of people), again, this should be a hyperlink.

Anyone Using OpenOffice BASE As MySQL Frontend?
I recently saw OpenOffice's Base:

"BASE enables you to manipulate database data seamlessly within OpenOffice.org. Create and modify tables, forms, queries, and reports, either using your own database or BASEâ€™s own built-in HSQL database engine. BASE offers a choice of using Wizards, Design Views, or SQL Views for beginners, intermediate, and advanced users ... For more advanced requirements, BASE supports many popular databases database natively (Adabas D, ADO, Microsoft Access, MySQL)"

I' am interested to hear anyone's experience with using this as a tool, development tool, or front-end builing tool for MySQL apps.

Transfer A Data Base To My Site
i've create a data base with phpmyadmin in my computer and now I want to put it in my site...it's possible to do it? And how?

Access Intranet Data Base
I have a db in my intranet, and I need to access the information from my web site which is hosted somewhere else (internet).
Whats the best way to do this?
Should I use a public IP for my intranet server? (then theres no point on hosting my web site some where else right?Could I syncronize both MySQL data bases?

Transforming Sql Server Data Base To Mysql One ..
I have an sql server Database Backup , I got it from My client who wants to rebuild his site with php-mysql .

What i want to do is to transform the sql server backup to MYsql one without losing any data ..

I want to do this without Loading the sql server on my PC ...

i tried many tools but it requires a pre-installed version of sql server to connect to ..

I have the sql server bckup file with the name website.bak

Transaction Timeout For Data-base Mutex
I'de like to use transaction locking mechanism as a mutex. I've already succeeded making this work on pgsql.

The algo is simple: I open a connection, set lock timeout to whatever i want, start the transaction and try to update a dummy row or create a dummy table (P/semTake of mutex), check if I got the mutex token (affected rows > 0 on update, no error on create table) (if not exit), execute my critical job and finally close the data-base connection causing a roll-back on update or create table (V/semGive of mutex).

with MySQL, I haven't found anything to modify the lock timeout. Does someone know?

How To Store Images In Mysql Data Base
how to store images in mysql.

Moving Data Base From / Var To Home Directory
I'm successfully running MySql under Debian with the data files
in /var/lib/mysql. I would like to move the data base files to my home
directory and have mysql use them from there. How do I do it? Can I just
copy them to my home directory and start mysql with a parameter which point
to the new locaion? Can I do that with mysql starting at boot time?

I just looked at /etc/init.d/mysql and see that it appears to starts mysql
by calling /usr/bin/mysql_safe but there is no such file.

As an alternative to putting it in my home directory, I would be happy
putting the files in /usr/local. Can I do that without rebuilding mysql?

The reason I want to do this is that I want to install a new version of
Knoppix and that will format and clean out the / partition and I don't want
to loose the data I have in /var/lib/mysql. Could I just
save /var/lib/mysql and restore it after installing the new version of the
system?

Converting Mysql Data Base Into Text File
how can i convert mysql database into text file ,is there any way to do such conversion (if possible) using php script.

How To Change Read/write Permission On Mysql Data Base.
I had a total crash and the server guy gave me all my data in a hand basket.. just kidding..

Actually I was given the data all zipped up and the mysql data bases were separate and they were the direct downloads as follows:
wp_comments.MYD
wp_comments.MYI
wp_comments.frm
all data was in this format.
I used SSH and uploaded it to the proper directory on the server and all is well at least as far as visitors seeing all the data.. but when I log into the site I get the following...

WordPress database error: [Table 'wp_options' is read only]
UPDATE wp_options SET option_value = 'O:9:"magpierss":18:{s:6:"parser";i:0;s:12:"current_item";a:0:{}s:5:"it ems";a:10:{i:0;a:12:{s:5:"title";s:13:"WordPress 2.2";s:4:"link";s:54:"http://wordpress.org/development/2007/05/wordpress-22/

I used SSH and tried to change the concerned files to writable and it works fine but the same error is generated.

I can go direct to the data base using myAdmin and cannot edit (write) to any table. It says the data base is read only.

On the same mysql area, I have another data base that was done via the normal way and I can write to it just fine.

I can find nothing to change to make the site writeable.

Any help here would be greatly appreciated..

SSH to the server and changing the files to writable has no effiect..

Very Easy Question - Assigning A Base A Username And Password In PHPmyadmin
Maybe I am just getting very tired all of a sudden, but I have forgotten how to assign and username and password to a MySQL base - can someone please put me out my misery on how to do this

Views?
do views exist fysically a separate "table",
or are they generated on the fly whenever they are queried?

if they exist fysically they could improve performance (..php, web),
for example a view being a join between two or more tables..

Views?
do views exist fysically a separate "table",or are they generated on the fly whenever they are queried?

if they exist fysically they could improve performance (..php, web),for example a view being a join between two or more tables..

Views In 5.01
We have just loaded 5.01 so that we can take advantage of views. One question I have before I start experimenting is this: Can you "view" across databases? And if the answer is yes, can you "view" across servers?

Given two tables "company" and "people", each living in a different database (and potentially on different servers) can you give an example (the SQL) of how to link them in a view?

assume the following:

company
-------
c_id
c_name

people
------
p_id
p_name
c_id

Views In MySQL 5.x
Was wondering if anyone here has had any experience with views in mysql 5? I know what they are and how to use them, was just wondering if anyone here has before and if they feel it is better, in mysql, to just create the new table or use the view instead? Or perhaps a complex join?

Question About VIEWS
I have never used MySQL views before, however, in my current situation I will sooner or later need to use them.

I have one table which consists of sent messages and another which consists of recieved messages. When someone sends a message to another person, the message will be stored in their own sent messages. However, when they recieve a reply from the end user, the message will appear as a conversation as apart of their own inbox. So basically, the query which requests the inbox records will fetch both the sent and recieved message records.

So I need to use a view to fetch both messages, how do I do this?

If you guys are still wondering what I am talking about, then check out FaceBook's private messaging system.

Views Overhead
Not far long ago, I was posting a question about performance of database with a lot of tables. Now I have a good idea towards moving to a shared tables approach, so there will be only one set of tables for all users, but each of the users will have an own set of views like:

create view username_posts as
select id, title
from posts
where user_id = user_id
It would make database more efficient, and would need only a small set of changes to the application.

What do you think about this approach? Is there a huge overhead of using views like the one mentioned above, in comparison with adding the user_id = ... clause to the application? How well does MySQL (InnoDB) behave in case database has a lot of views?

As Views Really Supported
With MySQL 5 beta and InnoDB tables, are views really supported? I can see the syntax in the manual but I get syntax errors on even a trivial attempt to define a view as in

create view foo as select * from project

produces You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'view foo as select * from project' at line 1

Phpmyadmin And Views
I created a veiw in phpmyadmin How can I modify it? when i click on structure instead of seeing the view I see it as if it was a table with fields.

Views In MySQL
Can I create views in MYSQL. Or is there an alternate technique.

Views In MySQL
I want to migrate my existing SQL Server database to MySQL. It had many views in it. When i tried to make views in MySQL 5.0, it gives error. I was suggested by this forum that views can be created in MySQL version 5.0.1, but i couldn't download it from anywhere. From where can i get it?

I tried to download from mysql.com file named "mysql-5.0.1-alpha-snapshot-win.zip".
This downloaded file gives me 5.0.0-alpha-nt server and 5.0.1-alpha client. This version is also not allowing me to create a view.

Views, Procedures
I have version 4.0.12 of MySql installed. I can not create views and stored procedures. Is this normal or is there something wrong in the installation?

Performance Of Views
I have a large table in a database that takes a long time to do any queries on. All user queries on this table are done as part of a background process at the moment.

The table holds transaction information from organisation and account id's; each organisation may have more than one account id. This table contains the raw transaction data, so there are many 1000's of records for each id. The table is keyed on customer number, which is different from organisation id and one customer number may have more than one organisation id. Code:

Views In MySql/PHP
This thread is answering a question from [url=http://www.codingforums.com/showthread.php?s=&threadid=12528[/url]this thread] -- I didn't want to distract from the original question.

To recap:A view is a virtual table. It is a stored query, usually based on two or more tables. It can be used as a table by other queries (by default, all queries created in Access are, in fact, views).

The main advantages are efficiency and security. You can merge complex tables that store complete information to produce more simple tables that contain live information without any data duplication. You can also give the view query different access, meaning that you can let people see only some columns of a table (eg a view restrict who can see creditcard numbers, without restricting who can see other customer details).

They are not supported in mySql.
Followed by:
what is the alternative to 'view' in mySQL/PHP then ...
The simple answer is there is no alternative. You can easily enough create a database layer that stores queries in php. But, if the user has access to run these queries on the underlying tables, then they also have access to bypass them.

You can also, with a little effort, create a method for building queries from SQL (this is a fairly standard way of operating a large system). However, you do not get the dabase level security benefits that you get from using views. Nor do you get the performance benefits that you get from views (I don't know all the details, but a view does run faster than a regular sql query).

You can use a degree of database level security -- by limiting access to tables using GRANT statements. With views, you can use database security to completely limit access to rows, columns, and calculated fields without any data duplication. Of course, it is possible to implement application level securty in PHP, but the dual (or more often treble) layer of security is quite useful when you're dealing with complex two and three tier systems.