Installing MySQL PHP Help

Before you can tackle the new language of databases, you’ve got to get a database installed on your computer. In this book, you’ll be working with the MySQL database, which is one of the most common databases used in web applications. The reason for this is because it’s easy to get, easy to install, and easy to use.

If you installed MAMP on your Mac or WampServer on your Windows-based PC then you already have a local installation of MySQL. Pretty sweet. right? (And, if you didn’t-you masochist. you!-check out Appendix B for detailed, step-by-step instructions on installing MySQL Wi(l1()U! MAMP or WampServer)

Just as Chapter 1deals with a local installation of PHP, this chapter starts out by focusing on a local installation of MySQL. Of course, just as your PHP work in  progressed into working with a remote installation of PHP  the same applies here, so if you don’t want to mess with WampServer or MAMP, you can jump on your hosting provider, fire up the mysql program, and get to typing. Of course, this assumes that your hosting provider lets you have shell access and lets you run the mysql console program-neither of which is a sure thing. If those become an issue, you can always resort to the already-installed MAMP/WampServer version on your local computer.

The mysql Console Program: Your New Best Friend.

Regardless of how you install MySQL, your first step toward database mastery is to begin using the mysql console program. Although MySQL is a database, mysql is a program that lets you interact with that database from a command line. Every installation of rvlySQL comes with the mysql tool; you just have to know how to get at it.

But first you have to starr the MySQL service. Otherwise, you’ll get errors because the mysql program has nothing to which it can connect. For WampServer, on the right side of the taskbar, click the green “W” icon and choose Put Online or Start All Services and read on. For MAMP, start the program, select Start Servers, and then go to page 98.

Run the mysql Tool on WampServer

WampServer installs mysql along with the MySQL database. However, it doesn’t set up your PATH to access that program, so you’ll have to do a little digging around.

FIND THE MYSQL COMMAND-LINE PROGRAM

. Once you’ve started up MySQL, change into the wamo/ directory on the drive on which you installed WampServer. For example, if you put WampServer on the c.\ drive, go to a command prompt (Start-skun and then type commandon Windows 7 and earlier; Windows key+R on Windows 8) and then type the f@’!lowing:

C:\Users\bdmOS09> cd c:\wamp\bin\mysql\

Now, you’ll have to do a little investigation, because things change based upon the version of MySQL your copy of WampServer installed. Do a directory listing

C:\wamp\bin\mysql>dir
Volume in drive C has no label.
Volume Serial Number is 7C78-FE01
Directory of C:\wamp\bin\mysql
.’ 08/01/2012 02:32 PM <DIR>
08/01/2012 02:32 PM <DIR>
08/01/2012 02:32 PM <DIR> mysqls.S.24
o File(s) 0 bytes
3 Dir(s) S2,739,S47,136 bytes free

You should see just one directory, specific to the version of MySQL installed by Wamp Server. Change into that directory. For MySQL 5.5.24, use the following:

C:\wamp\bin\mysql> cd mysqls.S.24

Now, you can go into yet another bm directory and finally run the mysql command

C:\wamp\bin\mysql\mysqls.S.24> cd bin

C:\wamp\bin\mysql\mysqls.S.24\bin>mysql
ERROR 2003 (HYOoo): Can’t connect to MySQL server on ‘localhost’ (10061)

GtVE MYSQl THE RIGHT USER AND PASSWORD

You have the right program, but the error message tells you that things aren’t working yet. That’s because you haven’t told mysql what user to use for logging in. You must specify the user with the -u option and then tell it to log in as “root”. Here’s how it looks

C:\wamp\bin\mysql\mysqlS.5.24\bin>mysql -u root Welcome to the MySQL monitor. Commands end with; or \g. Your MySQL connection id is 2 Server version: S.S.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement

mysql>

And there you have it! Somewhat surprising, you don’t need to provide a password to WampServer. Of course, because this is all running on your own local installation, security isn’t the concern it will be when you start connecting to a MySQL database on your web host out on the Internet. For now, you’re ready to start interacting with your database.

Run the mysql Tool on MAMP

If you’re on Mac as x, MAMP came with a mysql tool, available through the Terminal. It’s stored in so you can run it like this:

GIVE MYSOL THE RIGHT USER AND PASSWORD

For MAMP installations, just as with WampServer installations, using the mysql tool log in to MySQL as the root user. You specify that by using the -u option and then the username root, like this:

Running this command as is doesn’t quite give you what you were hoping for. In fact, you’ll probably see an error similar to this:

$ /Applications/MAMP/library/bin/mysql -uroot
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password:
NO)

That’s because users have passwords, and you haven’t supplied one. On top of that, for reasons too confusing to mention, the mysql program doesn’t ask you for a password, unless you instruct it to. Add the -p option, and you’ll be prompted for a password

$ /Applications/MAMP/library/bin/mysql .uroot -p
Enter password:

To find your root user password, go to the MAMP start page. That’s the web page that fires up every time you start MAMP. If MAMP is already running, you can click “Open start page.” You’ll see something like Figure 4-3, and there, clear as canbe, is your password.

Welcome to MAMP

Welcome to MAMP

A root user password of “root” isn’t particularly secure, but again, you’re on your local computer, not the NASA user store. Now, you can give that password to the mysql prompt. You should see something like this in return

$ /Applications/MAMP/Library/bin/mysql -uroot -p
Enter password:
Welcome to the MySOL monitor. Commands end with or \g.

Your MySQL connection id is 6
Server version: 5.5.9 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
Once you’re logged in, you can type exit to quit:
mysql> exit
Bye
$

That’s all there is to it! Now you’re ready to start talking to MySQ

Run Your First SQL Query

MySQL has installed a number of pre-created databases on your system. To see them.tall you have to do is ask. Fire up the mysql command line tool again, and type this command:

show databases;

You should get a text response from MySQL that looks a bit like this:

mysql show databases

mysql show databases

You might not have as many databases that come back, or you might have different databases. That’s OK.

The show command does just what you might expect: it shows you everything for a particular keyword; in this case, databases. To sum it up, show databases is just a ” way you can ask MySQL to show you all the databases installed on your machine.

On top of that, now you know something really important: MySQL really isn’t so much a database, but a piece of software that can store and create databases. In .. this example, there are eight rows returned as a result of running the show databases

command, which means there are eight databases on the system, not just one. Before you’re done, you’ll have created several databases, all running within MySQL.

For now, tell MySQL you want to work with the mysql database, which you have on your system even if you’ve only installed MySQL. You do that with the use command, like so

use mysql;

Now, you’re i” the mysql database. This means that any commands you give to .
MySQL are run against just the mysql database

At the beginning of this section, you asked MySQL to show you all the databases it has; now tell itto show you all the tables in the database you’re currently using

show tables;

You should get a nice long list, as illustrated here:

mysql show tables

mysql show tables

mysql show tables

mysql show tables

A lot of these. table names appear odd, but that’s mostly because these are internal tables used by MySQL. As you create new tables and users and set up your database, all of that information is stored within another database: the mysql database

To see some of this information, you must use the select command to access information from a specific table-for example, the user table. Type this command at your MySQL command prompt:

mysql> select * from user

The asterisk (*) means “select everything.” Then, from specifies to MYSQL where to get the information: in this example, user, which is a table in your database.

Don’t be surprised when you get a confusing stream of information back. In fact. it might look like something out of the Matrix; check out Figure 4-4 for an example

MySOl and PHP

MySOl and PHP

The problem here isn’t in anything you typed. It’s just that you instructed MySQL to select c. ~,’ .,(‘> from the ( ·,:>,table, and in this case, everything is a cr of information. ln fact. it’s so much information that it won’t all nicely fit into your command-line client, which is why you got all the strange looking lines in your response

To tame this beast a bit. you can select just a little information from a table. You do this be replacing the * in the command with the specific column names you want, separated by commas:

So, what’s a column? A column is a 5;’19’ > ~3’n”1:;, ‘i of i!,f v+etio. in your table. For
example, in a table that stores users, you might have a first_name and a last_name
column

Now that you’ve dipped your feet into the MySQL pool, it’s time to start to create your own tables and columns, and fill those tables and columns with your own information

Going Local

You saw it already in the first few chapters on PHP,and now you’re about to see it again: most of your programming is meant to berun on a web server. You might pay a monthly fee for hosting to a service like Kilti:1f1l.fcm for your own domain, you might own your own server that is connected to the Internet, or you might deploy your code to your company’s servers, housed in a room that’s kept too cold for normal human beings and requires a key card to even make it through the door. In all these cases, though, your code ends up somewhere other than your own desktop or laptop.

But, if that’s the case, why go through the trouble of installing PHP and MySOLon your own computer? Truth be told, you could ask a lot of PHPdevelopers and they’d admit that they don’t even have PHP (let alone MySOL) on their own devices. Their programming lives are lived through tel net and sshsessions, writing code on a distant server, somewhere out on the Web.

Although your code rarely will ultimately run from your own computer, there are some really good reasons to install your entire development setup on it. First, you’re not always in a place where you can connect to the Internet. You might be on a plane, in the back of a taxi, or lost in West Texas with nothing but an old leather-covered compass and a MacBook Pro. In all these cases, if you’ve got PHPand MySOLon your laptop, you can code away, testing your code against a real database, and never miss a beat

And finally, you learn a ton by installing these programs from scratch. You get a better handle not just on your own device, but how these programs work. If someone is getting a particular error, you might recognize that same error as something you got when a Windows service wasn’t running, or the MySOL instance on a Mac OSX computer didn’t have the right table
permissions set up. Your installation is a way to learn more about the tools you use, and that’s always a good thing

You can run the examples in this book on your own computer and on your web server. Just make sure that if you’re working on your own machine you can either get to its code with a web browser or you upload your code every time you’re ready to make sure things are working correctly. That way, you can follow along with all the examples. Beyond that, it’s up to you where you develop your code, test it, and run it.

Posted on January 12, 2016 in MySQL and SQL Database and Language

Share the Story

Back to Top