MYSQL :: Views Support In 5.0.0-alpha

Where <field> Is Not Alpha
I have a companies table and I need to sort the names using PHP. From A-Z is fine cause I have each link pointing to the right letter. But there are some companies that start with numeric numbers or even # @ other charecters.

My question is how do I make a Select query to get all that dosen't start with a letter?

select * from companies where name not like 'alpha%';

its the only thing i though about. and I actualy cant search the mysql manual cause I don't know how to call this problem.

To Use MySQL 5.0.1. Alpha?
I am creating my first web app using MySQL database and would like to use stored procedures. As far as I know this is a future only in MYSQL 5.

My question is if the server is enough stable for serious use? Are there any problem?

PHP 4.3.9 And MySQL 5.0 Alpha
Are they compatible at all? I got PHP 4.3.9 up and running on my XP box using Apache, but when I try to setup phpmyadmin, I keep getting authentication

Errors Message.
1250 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Mysql 5.0.1 Alpha
i just installed 5.01. alpha for my class coz we have to use stored procedures.
i cant startthe server. there is not mysqld.exe
i triend mysqld-max --install but it tells me that service is running.

MySQL Connector / J 3.1.0 Alpha
I thought this is the most popular jdbc driver for mySql.

I have been using this for my project in Win2000 environment. But today I tested my application in winXP, and I was shocked. Somehow it just failed when retrieving a resultset from mySQL and the field names are all corrupted.

This does not happen in win2k environment. Is this a bug?

Anyway, what is the alternative driver or is there any recommendation?

Can't Connect From Localhost With 4.1.1 Alpha
I can connect from SQLYog,MySqlCC,jdbc from remote with no problem.
However, when I try to connect(mysql -u root) from localhost, then I
get access denied. Checked the user table and root can connect from
anywhere(%).

I am running 4.1.1 alpha on Red Hat with the --old-password option.
TIA.

Dump Problem With 5.0.2 Alpha
I've just installed mysql 5.0.2 alpha on Windows 2000
I've tried to make a dump of one my DB

shell> mysqldump db_name > myDump.sql

I've an Exception with access violation.

MyODBC 3.51 To MySQL 5.0 Alpha
I am recently testing out MySQL 5.0.0_2 Alpha on a FreeBSD 5.2 server.
Also, I am experimenting with MS Windows connectivity, and although the MySQL Administrator application works beautifully, I seem unable to establish an ODBC DSN to the database.
I installed MyODBC 3.51.06 but it fails upon authentication with the following message:
[MySQL][ODBC 3.51 Driver] Client does not support authentication protocol requested by server; consider upgrading MySQL client
And am I considering, but it doesn't help. Any good suggestions or is it not yet possible to connect through ODBC

MySQL 5.0.2 (Alpha) Released
A new version of the MySQL database, version 5.0.2, has been released. This is an alpha release for the current MySQL development branch, and it is now available in source and binary form from the MySQL 5.0 Snapshot download.

Select Items Starting With Non Alpha
Want to select all items that don't start with a-f or A-F

Query Error With Alpha Input
I am trying to look for multiple values in a mySQL table. For example, I am trying to pull 3 records based on CPT (5 alpha numneric characters).

For example, I am trying to pull:
99201
A0040
99205

If I use this select criteria, I get the first record but then nothing else:

2nd Request, Alpha & Numeric Queries
I am trying to pass through a string that I am exploding into individual values to use in a query. For example, I want to pull the following three values through a query:

99201
A0040
99211

When I run it through the following query (it's missing some html tages, just trying to do a dry run) i twill run the first value through (regardless if it's a alpha based or numeric based), but after that it hits the else phase of the if (mysql_num_rows($mcalookup) >= '1') { code.

Any clues what's going on? BTW< if I strip off the ' ' around the variable, it dies when it hits the alpha based variable. Code:

Compiling Mysql In Alpha With Tru64Unix V5.1
I try to compiling Mysql V4.0 in the Alpha/Tru64 Unix V5.1a. I have
installed in the Alpha C++ and C version 6.5, and gcc 3.3 and GNU MAKE 3.8.

Follow the Steps.

1- Dowload from http://www.mysql.com/downloads/mysql-4.0.html in the
SOURCE DOWNLOADS >> Tarball(tar.gz)
2- I saved in the /usr/local/
3-Gunzip mysql-4.0xx.tar.gz
4-Tar mysqlxxxx
5-CC=cc CFLAGS=-O CXX=gcc CXXFLAGS=-O3
./configure --prefix=/usr/local/mysql
6-Make
# make
No suffix list.
make all-recursive
No suffix list.
Making all in .
No suffix list.
Making all in include
No suffix list.
Make: % rule can only use a single ':'. Stop.
*** Exit 1
Stop.
*** Exit 1
Stop.

Enable Unicode In MySQL 4.1.0-alpha
I have read that Unicode (UTF-8) was not enabled by default in the
mysql-4.1.0-alpha binaries. How can I enable it? What should I change in the
code to do so?

Selecting Entries That Begin With Non-alpha Characters
I'm sure this has been answered before, but I had a tough time composing a search for this on google or here ...

Basically what I'm attempting to do is formulate a MYSQL query that will select all entries begining with non-alpha characters, primarily all those that begin with numbers. Is there a better option than just a bunch of co-joined statements ... like Ɔ%' OR like Ƈ%'

Problems Compiling Mysql In Alpha With Tru64Unix V5.1
------=_NextPart_000_0019_01C2D0F9.BFBD6610
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hello There,

I try to compiling Mysql V4.0 in the Alpha/Tru64 Unix V5.1a. I have
installed in the Alpha C++ and C version 6.5, and gcc 3.3 and GNU MAKE 3.8.

Follow the Steps.

1- Dowload from http://www.mysql.com/downloads/mysql-4.0.html in the
SOURCE DOWNLOADS >> Tarball(tar.gz)
2- I saved in the /usr/local/
3-Gunzip mysql-4.0xx.tar.gz
4-Tar mysqlxxxx
5-CC=cc CFLAGS=-O CXX=gcc CXXFLAGS=-O3
./configure --prefix=/usr/local/mysql
6-Make
# make
No suffix list.
make all-recursive
No suffix list.
Making all in .
No suffix list.
Making all in include
No suffix list.
Make: % rule can only use a single ':'. Stop.
*** Exit 1
Stop.
*** Exit 1
Stop.

------=_NextPart_000_0019_01C2D0F9.BFBD6610--

Break Search Results Into Alpha Segments
i got how to break into pages, but how about into these alpha segments?

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.

Phpmyadmin And Views
When we create *.sql for out database, does it not copy views. I created *.sql and imported it to phpmyadmin, but it is not showing the view.

Do I have to re-write the view or there is some other solution?

Views In MySQL 4.1
Is it possible to create views in MySQL 4.1 or am I just wasting my time trying to figure out the syntax?

I've tried searching this site but can't find anything about it for 4.1.

Delete Views
how to drop a view in Ms Access using ColdFusion.I don't think the problem is ColdFusion, i think the problem is the query but it might be anything.

Views And MyODBC
well i just installed MySQL "5.0.1-alpha-nt-max" to play around with the new Features on my local PC.reating and accessing Views seems to work, but as it seems MyODBC 3.51.09 cant handle Views.
Are there any plans to support Views also via MyODBC ?
If so, is that Feature currently in the Bitkeeper-Repository of "MySQL Connector/ODBC 3.52" ?

Mysql Views
I have only ever developed database application using Mysql and so have limited experience of views and how they work. I'm sorry if this seems basic.
Suppose I create a view from a table, say A and tranform it using a select query to create my view, B.
If I want all the data from B, ie. SELECT * FROM B will the DBMS need to go back to the original data and execute the query that generated B and then return my results - or - does it store the data for view B in a table like format and then just quickly return that.
Essentially is there a performance advantage when using a view where the transformation from table A to view B is complex.

How To Overcome On Views Limits
I have 6 tables with many many columns and I have a SELECT statement that joins all the table and brings me all the columns in each table in single view. The number of columns that I get by executing this SELECT statement are more than 2000. The SELECT statement runs perfect and give me no problem. But the problem comes when I use this select statement in a view. I get the error message

CREATE VIEW failed because column '' in view '' exceeds the maximum of 1024 columns

Is there a way I can get all the columns in one single view. I know SQL Server View has maximum limit of 1024 colums.

Stored Procedure Vs Views
I am a little confused on the benefits of views when compared to stored procedures. When should you use a view and when should you use a stored procedure? Do either technology have a performance edge over the other?

Concept - Replacement For Views?
I'm kind of new to MySQL but am familiar with other DBs. Currently I'm working with a MySQL4 database and I have a very specific problem which could be easily solved via views.

Problem:
I have as follows -
tbl_cat
-id_cat
-txt_name
...

tbl_prop
-id_prop
-id_cat (fk)
-txt_desc
-txt_type
-txt_default
....

tbl_product
id_prod
txt_ref_name
...

tbl_prod_prop
id_prod_prop
id_prop
txt_hash

OK what is all of this? it's a dynamic product for a shop. How does it work conceptually? We create a product category(tbl_cat) and add properties to it (tbl_prop) such as name, picture, html text, etc...
Finally when we create a product, we determine it's category and fill in each of the properties.
My original idea was to use a view to generate the listing of the products and properties, but mysql4 doesn't have views...
What would be the best aproach? I thought of creating a cache table, where when a product is updated or inserted, I run a script that creates updates a record of a temporary dinamic table. Soething like a false view, updated on demand. Is that as stupid as it sounds?

Performance Improvement With Views?
If we have an option of using view to static assignment of options which one should we go for? Like if we have a combo box which is gonna have lets say 5 options then should we assign these options statically or should we store them in a view and then retrieve them dynamically each time someone clicks on the drop box.

Keeping Number Of Views To Max. How?
I've got a "smallint(3)" to track the number of views of an article, it is updated width "SET views=(views+1)". What I'm just wondering is if when the article reaches 999 views, the next view will set it to 100(0) or will just remain with 999. I think it will go on counting so, how can I just leave it with 999 views?

Indices Created For Views
I have a view that combines data from 4 tables. Can I create an index for the view? or should the indices created for the original tables speed up searching enough?

Design Strategy And Views
I am writing a php interface to access a large (100 million + records) database which is broken into 50+ tables by state.The script creates a temporary merge table with a union of the selected states depending on user input. I believe this will provide the largest reduction of unnecessary row scans.
I then query the temporary merge table with any additional criteria given by the user.
Is there a benefit if I create a view on the merge table to futher reduce unwanted rows and then query the view instead of the merge table directly? (I'm not really clear on how a view works. Does it actualy run the select query like a subquery?

Does MySql 5.1 Supports VIEWS With WHERE Clause?
Can I write "SELECT * FROM vView WHERE ..." ?

VIEWS,CHECK SYNTAX AND FOREIGN KEY
i am trying to see what views i have created on my database and am lost with the code part. this is what i have tried in both the USE DATABASE, USE INFORMATION_SCHEMA

SHOW VIEWS;
SHOW CREATE VIEW;
SHOW CREATE VIEWS;

ANYONE GOT ANY HELP?

I am also trying to use check and it doesn't seem to work. I am trying to 'ensure the only values entered into the GENRE table are dance, pop and jazz.
I had been trying with this:
CHECK(GENRE IN('DANCE','POP','JAZZ')

how do i add ORDER_NUM as a foreign key in a table called ORDER_LINE?
i tried this:
ALTER TABLE ORDER_LINE
ASS FOREIGN KEY(ORDER_NUM) REFERENCES CUSTOMER;