Join Bill Weinman for an in-depth discussion in this video Installing the test environment on a Mac, part of SQL Essential Training.
In this movie I'll show you how to install a test environment to follow along with the exercises on a Mac. In order to follow along with the exercises, there are a few things that you need. You need a web browser, any modern web browser will do. I'm using Google Chrome, but that's just my preference. If you like Firefox or Safari, that'll work just fine. You just need a modern up-to-date browser. You need a web server with PHP and a database engine. In this movie I'll show you how to install XAMPP, that XAMPP.
XAMPP is a package of things that includes the Apache web server, PHP, and SQL wide among a lot of other things. This is the environment I'll be demonstrating with so unless you know how to install and configure all these things yourself, it may be best that you install XAMPP and follow along with me. If you have your own installation of PHP and a database engine and you know how to install scripts and configure them, that'll work too. Otherwise, follow along and install XAMPP with me. Also, keep in mind that every database system has its own dialective SQL, so some of the examples may not work on another database system.
If you want to follow the exercises with me, you'll need an up to date version of SQL Lite installed. Many installations of PHP already have SQL Lite and the corresponding PDO driver available. You'll also need a plain text editor. This is different than a word processor. A word processor is designed for writing documents and includes a lot of formatting information along with the text. The formatting information will actually interfere with the SQL code in the example files, so word processor will not work for this purpose.
Unfortunately the text edit program that ships with OS 10 also doesn't work well for this purpose. So for the Mac I suggest BB Edit or Text Wrangler. Text Wrangler is free from the App Store or from barebones.com. So I suggest that if you don't already have a good text editor that you use TextWrangler, and just for reference, this is the barebones website if you don't want to use the app store. You can download TextWrangler from here. Finally, you'll need the Sit and Crud applications that I wrote for demonstrating SQL.
These are included with the exercise files and I'll show you how to install them with XAMPP. So first I suggest that you install TextWrangler which is as simple as clicking on the install button here in the App Store. And then we're going to install XAMPP. I suggest you install XAMPP from my website, and that would be the version here next to XAMPP for SQL 2014 in the Mac section. You can install it from the Apache Friends website but I don't recommend it. When they update the package, which they seem to do frequently, they often change so many things around that installation instructions won't do you much good any more.
This is the version that I'm using for the demonstrations in this course, so I recommend that you install this version. I've already downloaded it to my desktop. So I'm going to double-click on the DMG file. And double-click on the installer. Type in my password. If you've installed software on your Mac before, this process should be pretty familiar. Their installer's a little different, but it shouldn't be too difficult. So I'll click on Next. You want to make sure that both of these are checked. And in this case, they're grayed out, it's not even letting me uncheck them.
And Next. It's going to install it to your application's folder, and that's the correct place to put it. Now this is really just an advertisement. If you leave this check, you'll get a website with all the stuff that Bitnami has available for XAMPP. You don't need it for this course, if you want to leave that checked and take a look at it, that's fine. I'm unchecking it. And click next. Setup is now ready so I click next one more time. And this process can take a few minutes. It's a pretty big package. So this setup is complete.
I'm going to leave this checked and click finish. This'll launch Safari and this XAMPP page, and I'll click on English, and if you get this screen then you have done it correctly. I just want to show you this security page real quick, we're not going to go through this in any detail, but this is just to make a point. Running a web server on your desktop computer is always a dangerous thing. You're a developer. You need to have powerful software running on your computer that could cause problems in the wrong hands.
And so it's just really, really important that you have an effective firewall set up. Most modern routers like the kind that you would use for the wi-fi in your house or even many of the cable modems and DSL modems have a firewall built into them. What you need to do is you need to learn how that works and configure it and make sure that you're on top of that. Because once you have this web server running on your desktop, it does create security issues. If you've gotta good effective firewall, that's a great defense.
Just make sure that you understand how to do that. So for now I'm going to close Safari. I'm going to eject the installer volume, that's by control clicking anywhere on the volume there and press eject in the pop up menu. And there's a couple of things that I want to do right here. First I'm going to go to the system preferences and click on sharing. And under sharing you want to make sure that internet sharing is unchecked. That runs a web server. If you already have a web server attached to the web server port, which is port 80, then Apache will not run.
So you want to make sure that this is unchecked whenever you're running XAMPP. So, I'll quit that. And now we're going to run the XAMPP control panel. And I do this like this. Here in the Finder, I'm just pressing command n to bring up a new Finder window. And, I'm going to Applications and scroll all the way down here to XAMPP. And, you see this MANAGER-OSX.app. I'm just going to drag that into my dock and I can close the finder window now and then I'm going to run this manager.
And, you need to type your password because it needs administrator privileges to do this. Have to type your password correctly, and this is simply the way that the servers are managed. This first page on the welcome page, these are all web pages and they're nothing that you really need. What you need is this manage servers tab. You notice that the Apache Web Server's running, it's got a green light there. These other things, you don't need them for this course. You might want to play around with them at another time. If you ever need to stop the Apache web server, for instance so you can turn on web sharing on your Mac, you just press Stop.
It says stopping. It takes it a minute. It's gotta do stuff to stop. And then it says stop. And you want to start it again, you just press the Start button. And it starts up, takes it a minute. It's got things that it needs to do. And there it's running. And then you can quit this at any time. It doesn't actually stop the Apache server from running. So I'll just go ahead and say yes, and that's quit. And now it's working just fine. I'm going to just bring up Google Chrome again. And I'm going to open up a new tab and I'm going to type in local host.
Just like that and there's our XAMPP panel there in Chrome. So XAMPP runs the Apache web server, you connect to it with your browser, any browser will do, it just needs to be a modern browser for our purposes and, that's how that works. So now we're going to install SID and CRUD. And you'll find these in the exercise files. And you see these directories down here, SID and CRUD and also SQL. And I'll show you what we're going to do with that. For now, I'm just going to start another Finder.
This is easier to do this way than doing it in a tab. And go to Applications, and XAMPP, and htdocs. And I'm going to come over here, and I'm just going to grab these three folders I'm holding down the Shift key while I click on each of them. And while holding down the option key, I'm going to grab them and drag them over into the htdocs folder. I'm holding down the option key so that it'll make copies rather than moving the files.
It's important that you make copies because you're going to need the originals for other purposes, and just confirm that they're still there, yes they are, so I made copies and I didn't move them. And I'm going to come in here to the SQL folder and I'm actually going to delete these three files. I'm selecting them while pressing the command key, which allows you to select different files in a folder without selecting the ones in-between them. And I'm just going to delete those. I'm pressing command delete. All I need is these .db files in this SQL folder.
I don't need the .sql files. These are just the databases. What these are, these are the actual SQL Lite databases. SQL Lite stores an entire database with all its meta information, everything in one file and it's a portable file. These same files work on a Mac, on a PC, on Linux. It's really a very, very convenient system. So this is all under XAMPP and htdocs, which you'll notice is a shortcut and that's okay, and SQL. Now we're going to go into the SID folder and we're going to edit this sid.php file.
But before we do that, I'm going to command click on it and select Get Info. And you notice mine says, Open with Text Wrangler because I already had Text Wrangler installed. You want to make sure that yours says Open with TextWrangler. And so you click on that, and you will click on Change All, so that all of your .php files are automatically edited with Text Wrangler. And if you want to, you can go into the CSS folder and you can do the same thing for .css files and .HTML files. I find that convenient.
It's up to you if you want to do that. You don't need that for this course. So now we're going to edit the sid.php file. And inside this file you'll scroll down around line 39. You want to make sure that it says define DBDIR. And you're going to take this whole string inside of the quotes and you're going to change it. And, it's going to say /Applications, be very careful, spelling counts here, /XAMPP, all caps, /htdocs, /SQL also in all caps.
Just like that and I'm pressing command S to save. And now when I bring up my web browser I should be able to come in here and instead of XAMPP there, type local host SID in all caps and then sid.php just like that. And when I press the enter key it should bring up our SID application, and you'll notice here that all our databases are visible and if I want to select one of them I can come in here and I can type an SQL command, select asterisk from album, semicolon and press go, and I get results.
So SID is working, that's great. Now we want to do the same thing and edit the crud PHP file. If you're like me and want to make sure that something that works gets copied over to someplace else so it'll also work, then you select this string and press command c to copy it. And then when I open the CRUD version, it's here in this folder under Applications, XAMPP, hddocs, CRUD and crud.php. And, you'll find the same line here.
Well, it's a little bit different. But, somewhere around line 29, it'll say DBFILE and everything inside the quote marks all the way up to that last slash because you want that slash album.db at the end. And then press Command V and Command S to Save. And, it should look just like this /Applications/XAMPP/htdocs/SQL/album.db. Just like that. And, we're going to come back out to the browser. I'm going to bookmark SID here. So I'm just going to grab that and drop it in my bookmarks bar.
And then, I'm going to come in here and I'm going to type CRUD, all caps, /crud.php, lower case. And there's our CRUD application. And you can see it's reading from the data base just fine. Grab that, drop it onto the bookmarks bar and we have now installed everything that we need for this course and now let's get started.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.