Setting up a MySQL connection on a Mac

Report · Jul 16, 2006

I have in the past set up a database app on a PC network, using IIS etc - it was a while ago, but I remember the testing folder having to be a specific folder on the PC (which I can't remember now).

I'm basically now having a look at MySQL - can anyone let me know the equivalent I need to do on a Mac for this? So stuff like the testing folder / where the localhost is, and where I should find the 'MySQL Server', 'username' and 'password' in MySQL (I've downloaded MAMP for this).

Cheers,
Iain

Report · Jul 16, 2006

Iain71 wrote:
> I have in the past set up a database app on a PC network, using IIS etc - it
> was a while ago, but I remember the testing folder having to be a specific
> folder on the PC (which I can't remember now).
>
> I'm basically now having a look at MySQL - can anyone let me know the
> equivalent I need to do on a mac for this?

Use the Sites folder within your home folder.

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Jul 17, 2006

Isn't that where I would store the site's files - but wouldn't they go somewhere else for the testing server? An equivalent to the inetpub/wwwroot folder in windows?

When i click on connect in dreamweaver, and it brings up the dialogue box asking me for :

connection name
mySQL server
username
password
database

What do i need to put in here :

Is it

connection name : my_connection_name?
mySQL server : MySQL 5.0.19 (ie the name of the version of SQL that's running)?
username : localhost, or 127.0.0.1?
password : root?
database : my_database_name?

These were my best guesses, but just got an error about the testing server no being set up correctly?

This was using local / network, but i haven't tried it with my hosting account - would it be fine with this if i set that all up as the testing / remote server with FTP, and my host's account details?

When i upload the site, would it simply upload the database too?

I'm sure all of this stuff is fairly simple - once you know exactly what goes where!

Iain

Report · Jul 17, 2006

Iain71 wrote:
> Isn't that where I would store the site's files - but wouldn't they go
> somewhere else for the testing server? An equivalent to the inetpub/wwwroot
> folder in windows?

The equivalent of inetput/wwwroot is Macintosh
HD:Library:WebServer:Documents. Any files located here are accessible
through http://localhost/

The Sites folder in your home folder is also suitable as a testing
server. Any files here are accessed through http://localhost/~username/

~username is the name of your home folder preceded by a tilde.

> connection name : my_connection_name?
> mySQL server : MySQL 5.0.19 (ie the name of the version of SQL that's running)?
> username : localhost, or 127.0.0.1?
> password : root?
> database : my_database_name?
>
> These were my best guesses, but just got an error about the testing server no
> being set up correctly?

They were reasonable guesses, but wrong.

connection name: can be anything, but it mustn't begin with a number or
contain any spaces
MySQL server: localhost
username: root or the name of a MySQL account
password: the password of the account you are using
database: the name of the database

> This was using local / network, but i haven't tried it with my hosting account
> - would it be fine with this if i set that all up as the testing / remote
> server with FTP, and my host's account details?

Yes, you can use a remote server, but it's not as efficient. You need to
be online whenever you do any testing.

> When i upload the site, would it simply upload the database too?

No. You have no direct access to the database files on a Mac. Even on
Windows, there isn't a single file that you can upload. You need to
create a SQL dump of the database and then use that to re-create the
database on your remote server. The easiest way to do this is with
phpMyAdmin.

> I'm sure all of this stuff is fairly simple - once you know exactly what goes
> where!

It isn't difficult, but there's a lot to learn. You also need to get
your testing server definition correct in the Dreamweaver site
definition. The URL prefix depends on whether you store your files in
Macintosh HD:Library:WebServer:Documents or your Sites folder. Nothing
works unless you get that right.

If you're completely new to PHP/MySQL, you may find my book saves you a
lot of wasted time and frustration. It contains full instructions for
both Mac and PC. It also shows you how to transfer a database to a
remote server. More details on my site
http://foundationphp.com/dreamweaver8/

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Jul 17, 2006

Thanks David - that's a great help.

I think I tried the localhost, but without my folder after it - so will check that again and make sure I have the prefix correct.

When I talked about uploading the site, I meant in the first instance - so effectively I create a database structure, save it, upload it to a host server somewhere - and design pages that get uploaded also, and from then on the database has records amended / added / deleted etc by logging in to that website and doing it all within the browser? So once it's live I guess.

I'm only really taking a look at the moment as a client may want a simple database driven app. As I say, I'm familiar with Access/ASP a bit more so that should help a bit. If it looks like I can reasonably do it, I'll certainly invest in a good reference book so I can hopefully continue to pick it up and be able to offer it in future.

Iain

Report · Jul 17, 2006

Iain71 wrote:
> When I talked about uploading the site, I meant in the first instance - so
> effectively I create a database structure, save it, upload it to a host server
> somewhere - and design pages that get uploaded also, and from then on the
> database has records amended / added / deleted etc by logging in to that
> website and doing it all within the browser? So once it's live I guess.
>
> I'm familiar with Access/ASP

Your familiarity with ASP will help, but not Access. PHP uses loops,
arrays, and functions in the same way as ASP. However, MySQL doesn't
store data in a single file in the same way as Access. MySQL is a
database server, which keeps all files in a central data store. It also
allows you to choose different storage engines (MyISAM is the default,
and is normally the only storage engine permitted by hosting companies).
The way different databases coexist in the same data store is controlled
by user accounts, which set privileges on individual databases, and even
individual tables.

Building the web pages that interact with the database is the same for
ASP and PHP, but transferring the database itself is completely different.

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Jul 17, 2006

So how does that work in layman's terms?

My layman like understanding probably doesn't extend much beyond the idea that for a live site, the HTML/ASP/PHP files/pages sit on a host server alongside the database. You can create a link between the web pages and the database in Dreamweaver, then when it's all sitting on the host server, the database can then essentially be updated by a designated user in any browser?

Whilst the structure of an Access database and one in MySQL may differ, how much different does it get from a 'setting it all up on a website' point of view?

Iain

Report · Jul 17, 2006

i think i've got it working at last.

CMSQL connected fine using localroot:8888 - looking at it, it reminds me a lot of Foxpro that i've used in the past (although never really liked it compared to Access, but i think that's a familiarity thing as much as anything.)

also have a working database set up with a working connection.

in dreamweaver :

local info :

site name : DatabaseTest
local root folder : Macintosh HD:Users:Iain:Sites: DatabaseTest:

remote info :

access : local/network
remote folder : Macintosh HD:Applications:MAMP:htdocs: DatabaseTest:

testing server :

server model : PHP/MySQL
access : local/network
testing server folder : Macintosh HD:Applications:MAMP:htdocs: DatabaseTest:
url prefix : http://localhost:8888/DatabaseTest/

does that sound about right?

i guess the next thing to do is play around in CMSQL and figure out how to create queries etc - but hopefully it's not too tricky.

and how to upload this lot to a host, so that it works on a live website.

Iain

Report · Jul 17, 2006

Iain71 wrote:
> does that sound about right?

I have no experience of MAMP. Mac OS X has Apache installed by default,
and there are excellent Mac Packages for both PHP and MySQL. However, if
it works for you, that's the main thing.

> i guess the next thing to do is play around in CMSQL and figure out how to
> create queries etc - but hopefully it's not too tricky.

I have never heard of CMSQL. Most hosts offer phpMyAdmin. You would
probably be better off finding out what the host uses, and sticking with
that.

> and how to upload this lot to a host, so that it works on a live website.

As I have said before, it's very simply with phpMyAdmin Export and Import.

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Jul 17, 2006

MAMP is basically a Mac app that incorporates mySQL, phpMyAdmin, apache etc under one roof.

CMSQL was my shorthand for cocoaMySQL, which is a slightly more approachable, Mac styled front end than phpMyAdmin, and reminds me a bit more of FoxPro (Microsoft's older database app), in that it displays fields with drop down menus for changing their settings etc).

As of now, I've gotten as setting up a mySQL connection in DW, and creating a .php page that is working correctly on the testing server all as it should be.

From there, I've done a .sql dump from cocoaMySQL and uploaded that to my host's server. Then I created a database on my host server using phpMyAdmin in the control panel, and imported the dumped .sql file.

So now I've got the data sitting correctly on my host server's mySQL server.

Lastly, I took the connection file, edited to reflect the details for the hosted copy of the database, uploaded that, and it all seems to be working fine.

Thanks for your help - it was much appreciated.

Iain