From Access to MySQL

What's the learning curve?

[Beagle]

After seeing the level of most of the threads in the Database forum, I thought my questions would fit better in this one!

My day job is with an MS-happy organization, so I have a working (by no means expert) knowledge of Access, and have the program on my computer. But my non-dayjob-related websites are with a webhost who uses Apache servers and offers MySQL databases. BTW, the host is great and I don't want to change hosts.

My #2 website has grown bigger than I expected it to, with 1500 different widgets available, and is still expanding. I have the widgets divided into categories, with some amount of HTML interlinking when it seems appropriate, but it's getting to the point where I'd like visitors to be able to search by various attributes: yellow widgets, flower-related widgets, woven widgets, large-size widgets, etc., instead of my single category definitions: yellow widgets, blue widgets, green widgets, etc., and then have to sort out the other variables themselves within the category.

I could do this by expanding the HTML interlinking to the nnnth degree, but since the site is still growing, I'm thinking that it would save time in the long run if I started using a database for the purpose now. I'd appreciate input on three questions (and any that I haven't thought to ask):

Is it time for a database solution?

Would an Access database "work" on a server that's set up for MySQL?

If I come with a working knowledge of Access, how difficult would it be for me to start working with MySQL? Would I basically be starting over, or do they have some commonalities? I'm not particularly MS-happy myself, and would pick MySQL over Access if I knew neither of them, but I also don't want to re-invent the wheel.

The go-to site in the field has over 10,000 widgets and, of course, uses databases, but I can see some ways their system could be improved on and would like to try.

Thanks for any insights.

[ogletree]

It is not hard at all. I use phpmyadmin to help with a lot of what I need. Most the time your host will have help sections with sample code to help you out. I taught myself all I know about mysql/php. Also for some querries all you have to do is make the querrie in access and change view to sql and copy paste the code. Access will convert it for you.

[siXcrookedhighways]

it's not a tough leap, go for it. when i was using access for a db i did not have a web interface to make simple changes on records so i was uploading the new db(very small though) each time. with the growing db you have, you may be using a web interface to maintain records already. once i went to mysql it was no going back. the more you use it the more advantages you will find.

[Beagle]

Thanks for the replies. I'll definitely be looking into MySQL. (If you're wondering about the first sentence of my original post, I'd put it in the New to Web Development forum, thinking it might be too elementary for this one, but the mod moved it here. So even more thanks for providing the helpful input.)

[txbakers]

I made the leap in one weekend. I only made one mistake in the conversion - leaving a number field as Tiny Integer, thinking that the "3" meant three digits, and the higest number I needed was 555.

But the Tin Integer 3 meant three bytes, so all my numbers higher than 255 were reduced to 255 and a few of my users got mad.

That was about 5 years ago, and I've never had a problem since, and wouldn't go near access again.

[FalseDawn]

Just to correct the last post, a tinyint is in fact 1 byte, and the (3) in the field definition does refer to the number of digits displayed.

It's just that a tinyint can't hold more than 255 (the limit of 1 byte) anyway, so defining a field as tinyint(3) is a bit pointless.

[volatilegx]

I was very familiar with using Access when I started learning about MySQL, and I must say the transition was rockier than I first imagined. It was because Access is so "visual", with many wizards, dialog boxes, etc. Most of the time in Access, you aren't working directly with the queries, you are using some sort of visual tool to build them.

However, with MySQL, and many other databases, you will be working directly with the queries, structuring them yourself from SQL, and dealing with the results. Also, you will take a more active hand in designing the database.

Working with a MySQL database is quite a contrast from the average small business Access database, in my opinion.

However, all that being said, the time I spent learning MySQL was well worth it. I suggest finding a good reference book that deals both with MySQL and the scripting language of your choice to interface with it. I like PHP for that purpose, but some people like Perl, too. There are books that specialize in teaching MySQL/PHP or MySQL/Perl... they will be invaluable to you.

[Beagle]

I'll be using PHP, so will follow that advice when I'm looking at books. Thanks.

I avoid wizards, but do use dialog boxes. In general, I do better with something if I'm close enough to the process to understand why I'm doing something, rather than just following instructions, so hopefully that will help. But I'm definitely expecting to have to put some work into this.

[Siegler]

Hello everyone..

I am a cheapskate.. I switch from Access to MySQL 4.1 ... I run both PHP 5 and ASP 3.0 en ASP.NET on my Windows XP Workstation which also serves as testing server.

One problem... one guy suggested to me to just copy paste the queries in Access (SQL View) and past them into the SQL part of the recordset that I am building with Dreamweaver MX 2004.

Unfortunately.. it fails... can anyone help me out?

How?

The query TopSellers in Access..

SQL View:
SELECT TOP 5 OrderDetails.ProductID, Products.Product
FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY OrderDetails.ProductID, Products.Product
ORDER BY Count(OrderDetails.ProductID) DESC;

The content of the recordset from the file best_sellers.asp

SQL
SELECT ProductID, Product
FROM TopSellers

The query CrossSelling in Access..

SQL View:
SELECT TOP 5 OrderDetails.ProductID, OrderDetails.ProductName, Count(OrderDetails.ProductID) AS CountOfProductID
FROM OrderDetails
WHERE (((OrderDetails.OrderID) In (select OrderID from OrderDetails where ProductID=[pid])))
GROUP BY OrderDetails.ProductID, OrderDetails.ProductName
HAVING (((OrderDetails.ProductID)<>[pid]))
ORDER BY Count(OrderDetails.ProductID) DESC;

The content of the recordset that uses this is the Detail.asp

<%

set cmdCrossSelling = Server.CreateObject("ADODB.Command")
cmdCrossSelling.ActiveConnection = MM_CharonCart_STRING
cmdCrossSelling.CommandText = "CrossSelling " & Request("ProductID")
cmdCrossSelling.CommandType = 4
cmdCrossSelling.CommandTimeout = 0
cmdCrossSelling.Prepared = true
set CrossSellingRS = cmdCrossSelling.Execute
CrossSellingRS_numRows = 0

%>

The query ProductQuery in Access..
SQL View:
SELECT Products.*, Categories.Category, Manufacturers.Manufacturer
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN Manufacturers ON Products.ManufacturerID = Manufacturers.ManufacturerID;

<%
Dim rsProductCat__MMColParam
rsProductCat__MMColParam = "1"
If (Request.QueryString("ProductID") <> "") Then
rsProductCat__MMColParam = Request.QueryString("ProductID")
End If
%>
<%
Dim rsProductCat
Dim rsProductCat_numRows

Set rsProductCat = Server.CreateObject("ADODB.Recordset")
rsProductCat.ActiveConnection = MM_CharonCart_STRING
rsProductCat.Source = "SELECT * FROM ProductQuery WHERE ProductID = " + Replace(rsProductCat__MMColParam, "'", "''") + ""
rsProductCat.CursorType = 0
rsProductCat.CursorLocation = 2
rsProductCat.LockType = 1
rsProductCat.Open()

rsProductCat_numRows = 0
%>

<%
Dim RelatedProductsRS__param1
RelatedProductsRS__param1 = "0"
If (Request.QueryString("ProductID") <> "") Then
RelatedProductsRS__param1 = Request.QueryString("ProductID")
End If
%>
<%
Dim RelatedProductsRS
Dim RelatedProductsRS_numRows

Set RelatedProductsRS = Server.CreateObject("ADODB.Recordset")
RelatedProductsRS.ActiveConnection = MM_CharonCart_STRING
RelatedProductsRS.Source = "SELECT RelatedProducts.ProductID, Products.Product, Products.Image FROM RelatedProducts INNER JOIN Products ON RelatedProducts.ProductID = Products.ProductID where RelatedProductID=" + Replace(RelatedProductsRS__param1, "'", "''") + " UNION SELECT RelatedProducts.RelatedProductID, Products.Product, Products.Image FROM RelatedProducts INNER JOIN Products ON RelatedProducts.RelatedProductID = Products.ProductID WHERE RelatedProducts.ProductID=" + Replace(RelatedProductsRS__param1, "'", "''") + " ORDER BY Product"
RelatedProductsRS.CursorType = 0
RelatedProductsRS.CursorLocation = 2
RelatedProductsRS.LockType = 1
RelatedProductsRS.Open()

RelatedProductsRS_numRows = 0
%>
<%
Dim ReviewsRS__param
ReviewsRS__param = "0"
If (Request.QueryString("ProductID") <> "") Then
ReviewsRS__param = Request.QueryString("ProductID")
End If
%>
<%
Dim ReviewsRS
Dim ReviewsRS_numRows

Set ReviewsRS = Server.CreateObject("ADODB.Recordset")
ReviewsRS.ActiveConnection = MM_CharonCart_STRING
ReviewsRS.Source = "SELECT * FROM ReviewQuery WHERE ProductID=" + Replace(ReviewsRS__param, "'", "''") + ""
ReviewsRS.CursorType = 0
ReviewsRS.CursorLocation = 2
ReviewsRS.LockType = 1
ReviewsRS.Open()

ReviewsRS_numRows = 0
%>