Connecting PHP to MySQL PHP Help

Now that you’ve seen a bit of the power of PHP and MySQL, it’s time to bring these two juggernauts together, With many programming languages, any time you want to interact with a database, you have to download and install extra code or small plug-ins, PHP isn’t like that, though; it comes ready to connect to MySQL from the moment you run the php command,

Even though you’ve only recently begun your journey to PHP mastery, you’re ready to use a database from within your scripts. You’ll just need to learn a few new commands and how to deal with the problems that can come up when you’re working with a database. In fact, you’re going to build a simple form with which you can enter

SQL and run it against your MySQL database. Who needs the mysql command-line tool when you’re a PHP programmer? Then, to put a cherry on top of your towering sundae of PHP and MySQL goodness, you’ll write another script. This one takes all the information from the forms you’ve already been building, adds that information into a database, and then adds one more form to with which your users can search for another user by name. All that in one ? Yes indeed

Writing a Simple PHP Connection Script

No matter how simple or advanced your PHP scripts, if they communicate with a database, they’ll begin with the same few steps:

1. Connect to a MySQL installation.
2. USE the correct MySQL database.
3. Send SQL to the database.
4. Get the results back.
5. Do something with the results

Depending on the application you’re writing, steps 3, 4, and 5 will change a bit based on what you’re doing. A script that creates tables looks different than a script that searches through existing tables

But. those first couple of steps-connecting to MySQL and using the right database are always the same, no matter how fancy your script is. Just think, then: the code you’re a!Jout to write is the same code that programmers making $150 or $200 an hour are writing somewhere. (They’re just writing that code in expensive houses with robots serving them ice tea as they lounge by the pool.)

Connect to a MySQL Database

Because your form is going to take in SQL and run it against your MySQL database, first you’ve got to instruct your PHP script how to connect to a database. Essentially, you’re directing PHP to do the same thing you did when you started up your MySQL command-line client When you connected to your web server’s database, you probably used a command like this:

You’ll need to give PHP the same pieces of information (database host, your user name, and a password) so that it can connect

Fire up your text editor and create a new script. Call it ore. This script is going to be as simple as you’ll ever see because all you need it to do is connect to your database, USE the right database, and then run a sample SQL query to ensure that things are working correctly

In your script, type the following lines:

<?php
mysql_connect(“your.database.host”,
“your-username”, “your-password”)
or die(“<p>Error connecting to database: ”
mysql_errorO . “</p>”);
echo “<p>Connected to MySQL!</p>”;
?>

Yes, it’s really that simple! And, like most of the other PHP scripts you’ve been writing, although there are some new commands, you probably already know almost exactly what’s going on here

First, there’s a new command: mysql_ connect. No surprises here; this just takes in a database host, a user name, and a password, and makes a connection. It’s just as if you’re running y_ourmysql tool and connecting to a remote database

But before you can understand die, you need know a little bit about the inner workings of mysql_ connect. When mysql_ connect runs, it either creates or reuses an existing connection to your database. It then returns that connection to your PHP program and makes all the other PHP-to-MySQL commands you’ll learn about soon available. But, if mysql_connect can’t create that connection-for example, if your database isn’t running or you have a bad host or user name-mysql_ connect returns a very different value: false

What’s really happening in your script is something like this:

<?php
II This isn’t working code, but you get the idea
if (i_can_connect_to_mysql_with(“my.database.host”,
“my-username”, “my-password”»
go_do_cool_database_stuff();
else
~end_error_to_user_using_die
?>

That’s a lot of typing, though, so PHP lets you shorten it to this:

<?php
..
mysql_connect(“your.database. host” ,
“your-username”, “your-password”)
or die(“<p>Error connecting to database: ”
mysql_errorO . “</p>”);
echo “<p>Connected to MySQL! </p>”;
?>

Not only is this shorter, but it flips things around a bit. It’s saying, “try to connect (using mysql_connect), and if the result isn’t true (the or part of the code), implement die.” Now, die prints out an error message, but it also “dies.” In other words, it ends your script. So, if mysql_ connect returns false, and die runs, your script will exit. Your users won’t ever see the “Connected to MySQL!” line beCause the script will have stopped running. It’s dead on the server room floor, in search of a working database connection.

Not only that, but mysql_ connect sets up another function when it can’t connect. It makes available the errors it ran into while trying to connect by using another command, mysql_ error. Thus, you can call mysql_ error as part of your die statement to show what really happened.

If mysql_ connect does connect without any problems, it will return that connection. That means the die line is skipped, and the next thing PHP does is execute this line:

echo “<p>Connected to MySQL! </p>”;

To see this script in action, create a simple HTML form and call it connect.iurni. You can use this HTML to get started:

<html>
<head>
<link href=” ../css/phpMM.css” rel=”stylesheet” type=”text/css” />
</head>

<body>
<div id-“header”><hl>PHP & MySQL: The Missing Manual</hl></div>
<div id-“example”>Example 5-1</div>
<div id-“content”>
<hl>SQL Connection test</hl>
<form action-“scripts/connect.php” method-“POST”>
dieldset class-“center”>
<input type-“submit” value-“Connect to MySQL” I>
</fieldset>
</form>
</div>
<div id-“footer”></div>
</body>
</html>

This form is about as simple as it gets: build the form. drop a single button into place. and attac_h that button to your new Cu,”’. c· .’no script. Load up your form in a browser (see Figure 5-1). and click “Connect to MySQL

php and MySQL

php and MySQL

Hopefully, you see one of the simplest, happiest messages of your bu.rgeoning PHP and MySQL programming career: you’re connected! Check out Figure 5-2 to see the triumphant, if simple, result.

php and MySQL

php and MySQL

Everybody Dies at Some Point

It’s really, really, really easy to forget to add those die statements to your PHP scripts. PHP doesn’t require them, so it’s perfectly happy to take in something like this

mysql_connect(“database.host.com”, “username”, “password”);

That’s the same code you’ve already written, except it leaves off the die part.

But, here’s the thing: leave off that die, and when something goes wrong, your script is going to crash and provide something that’s either a really useless error or something so cryptic that you can’t even tell wnai it is. For example, drop off your die and enter in a wrong password, run your script, and you’ll get something like this as an error:

Can’t connect to local MySQL server
through socket ‘/tmp/mysql.sock’ (2)

Believe it or not, this is actually a pretty good error message, as messages go when you don’t use die statements. So, adding that one line of error ha’ndling can make a huge difference for a user when things go wrong

Now, at this point, some of you-already flush with PHP power-are already thinking about how few errors you’re making. You’re thinking that die is for rank amateurs who don’t write flawless code. Unfortunately, when you’re up at 2 a.m. trying to hit a deadline so that you can get paid, your brain starts to resemble a rank amateur. Everyone makes mistakes, and die (and other error handling techniques) is one of those lifesavers that helps you look prepared and professional when those inevitable mistakes do occur

In fact, the slickest, highest-paid programmers in the world are error-handling gurus. At the sam~ime, they’re probably ,’at using die. They’re more likely to use a more robust error-handling system; something like the error handling in tnanter 8. For now, though, a healthy and liberal use of die will get you used to adding in a form of error handling. You can come back and improve upon it later.

Select the Database with PHP

There’s something wonderful waiting around the programming corner now. Almost all of the mysql_ family of functions works the same: you give them some values, and they give back something useful. If something bad happens, you usually get back either false or a non-existent object (something most programmers

Now, you need to instruct MySQL which database your PHP script wants to use. There’s a function for that: mysql_ select _db.

You should already see the pattern. The die command ensures that if bad things happen, an error displays, your users can actually read that error, and then the script exits. If things do go well, another happy message should print

talking SQL to your database.

talking SQL to your database.

Viewing Your Database’s Tables by Using SHOW

Now that you have a connection, and you’re tied in to the right database, you need to see which tables are available on your hosting provider. When you were working directly with the MySQL command-line tool, one of the first things you did was to see what tables existed and then start creating tables of your own (page 100).
You can do that same thing now with a PHP script and a little bit of simple output.

But before diving into that, you can easily have your script reveal which tables are available in your database. Open connect.otu: again, and add in this line:

<?php
// All your existing database connection code
$result ” mysql_query(“SHOW TABLES;”);
?>

Here’s another new PHP-to-MySQL function: mysql_query. You’ll become very familiar and friendly with this one; it’s the key to passing SQL in to your database. This function takes in SOL, and you’ve given it some really slrnple SQL

This command does exactiy the same thing as when you type the SOL SHOW TABLES command into your command-line tool.

HANDLING ERRORS BY DETERMINING IF YOUR RESULTS ARE NOT

But what about dieiWhat about error handling? There’s none of that yet. and by now, you know there should be. But there’s something different about this line: whatever comes back from mysql_query is stuffed into a variable called $result.

It’s really $result that you want to examine. The result should either have a list of tables, from SHOW TABLES, or report an error of some sort. If it’s reporting an error, $result is false because the mysql_ functions return false when there’s a problem

You know how to check for a false value, though so you can add the following code to handle problems

This code works, but it’s really not how most PHP programmers do things. The three equal signs (===) is an unusual thing to use in PHP, at least for checking to see wh’ether a variable is false. What’s a lot more common-and the way it’s usually done in PHP-is to use the l’ang or reaeuo» operator, which is an exclamation mark (!). So, if you want to see whether a variable called $some-variable is false, you could say if (! $some-variable). By adding that exclamation mark, you’re saying something like, “see if $some-variable is false.”

Even better, think of ! as meaning not. So, what you really want to say in your code is, “If net $resul t, then die.” That means you could rewrite your code to look like this

In fact. to ensure that your code deals with errors, change your SQL query to include a typo:

<?php
II All your existing database connection code
$result = mysql_query(“SHOWN TABLES;”);
if -(!$result) {
die(“<p>Error in listing tables: ” . mysql_errorO . “</p>”);
?>

This code works, but it’s really not how most PHP programmers do things. The three equal signs (===) is an unusual thing to use in PHP, at least for checking to see wh’ether a variable is false. What’s a lot more common-and the way it’s usually done in PHP-is to use the l’ang or reaeuo» operator, which is an exclamation mark (!). So, if you want to see whether a variable called $some-variable is false, you could say if (! $some-variable). By adding that exclamation mark, you’re saying something like, “see if $some-variable is false

Even better, think of ! as meaning not. So, what you really want to say in your code is, “If net $resul t, then die.” That means you could rewrite your code to look like this:

<?php
II All your existing database connection code
$result ; mysql_query(“SHOW TABLES;”);
if (!$result) {
die(“<p>Error in listing tables: u mysql_error() . “</p>”);
?>

This example shows much better PHP, and now you have any problems covered .

In fact. to ensure that your code deals with errors, change your SQL query to include a typo:

<?php
II All your existing database connection code
$result = mysql_query(“SHOWN TABLES;”);
if -(!$result) {
die(“<p>Error in listing tables: ” . mysql_errorO . “</p>”);
?>

Load conoect.Html in a browser and run your connection test. Figure 5-4 is similar to what you should see: still a little cryptic, but clearly your code realized there was  a problem and handled it with an error message rather than a massive meltdown

connected MySQL

connected MySQL

PRINT OUT YOUR SQL RESULTS

So far, the PHP script you’ve created in this chapter handles errors, reports problems, and lets you deal with what’s in $resul t when things don’t go wrong. Unfortunately, that’s where things get trickier. $resul t is actually not a PHP type that you’ve used, or even one that you’ll need to learn how to work with directly. It’s something called a resource, which is PHP-speak for a special variable that’s related to something outside of PHP

Think about it this way: In the case of mysql_ query, you’ve asked for the SOL results from running the query SHOW TABLES. But, although PHP can talk to MySOL, it
doesn’t know how to interpret SOL. Therefore, it can’t know that $result should
hold a list of rows, each of which containing one value: a table name. All it knows
is that something else-your MySOL database-is getting a query through the
mysql_ query function. Think about it for a moment. Depending on what query you
pass mysql_ query, $resul t might hold rows with multiple pieces of information, like
a first name and Facebook URL, or just an indication of whether a CREATE TABLE
statement worked or not

In these cases, you usually end up with a PHP resource. That resource means something;
it’s just that PHP doesn’t really know what that something is. So, your PHP
needs help. What it needs is something that knows about MySOL and can figure out
how to work with $resul t. That’s exactly what you get with another MySOL function,
mysql_ fetch JOw. You pass this function in a resource returned from mysql_ query,
and it’lets you cycle through each row in the results returned from your SOL query.

Here’s the basic pattern:

1. Write your SQL query and store it in a string or a variable.
2. Pass your query into mysql_query and get back a PHP resource.
3. Pass that resource into mysql_fetch_row to get back rows of results,
at a time.
4. Cycle through those rows and pull out the information you need.
5. Buy a really nice musical instrument with all the cash you’re making.

You’ve got a resource in $result, now pass it in to mysql_ fetch JOw, like this

<?php
II All your existing database connection code
$result = mysql_query(“SHOW TABLES;”);
if (!$result) {
die(“<p>Error in listing tables: ” . mysql_errorO . “</p>”);
}
echo “<p>Tables in database:</p>”;
echo “<ul>”;
while ($row = mysql_fetch_row($result)} {
II Do something with $row
}
echo “</ul>”;
?>

Even though PHP doesn’t know what to do with the resource returned from mysql_
query, mysql_ fetch_row does. It takes in your $resul t resource and starts creating
rows, one at a time, in an array

And then there’s that while loop, something else that’s new, but not tough to grasp.
A while loop continues to loop for as long as a specified test condition is true. In
this case, it keeps looping while $row-which is the next row of results from your
SOL query-is getting a value from mysql_ fetch Jow($result). When there are no
more result rows, mysql_ fetch JOw doesn’t return anything, so $row is empty, and
the while loop says, “Ok, I’m done. I’ll stop looping now.”

Finally, you’ve got a nice unordered list (cul > ready to emerge from each row.
There’s just one thing left to add

<?php
II All your existing database connection code
$result = mysql_query(“SHOW TABLES;”);
if (! $result) {
die(“<p>Error in listing tables:” my”sql_errorO. “</p>”);
echo “<p>Tables in database:</p>”;
echo “<ul>”;
while ($row = mysql_fetch_row($result»
echo “<li>Table: {$row[o]}</li>”;
echo “</ul>”;
?>

Each time mysql_ fetch JOw returns $row, it’s actually returning an array, something
with which you’ve already worked (page 83). That array has all the different pieces
of information from your SOL query. For SHOW TABLES, that’s just one thing, at

$row[o]: the table name. Pretty soon, you’ll write some more complex queries, and
you might need to grab the value in $row[l], $row[2], or even $row[10].

In this case, you get back $row, you grab the table name by getting the first item in
the array (index 0), and then you print that out by using echo. There’s just one other
wrinkle here: those curly braces inside the string that’s passed to echo. What’s up
with those?

Well, you could rewrite this line like this:

while ($row = mysql_fetch_row($result)) {
echo “<Ii>Table: ”  $row[o] . “</li>”;
}

Nothing wrong there, except for all the extra quotation marks and periods to stick
strings together.

But PHP is pretty savvy, and the folks that wrote the language are programmers,
too. They realized, like you do, that you constantly need to drop variables into the
middle of strings. So, instead of constantly ending a string and adding a variable,
you can just wrap a variable inside of { and }, and PHP will print the value of that
variable instead of “$row[O]”.lt makes for a lot simpler code, and that’s a good thing

At this point, save yr revisit c ‘n. =r=r+: in your browser, and see what
tables are in your database. Figure 5-5 shows rcorec )h] running against a database
with a lot of tables. You might have only one or two, or none at all, and that’s fine.
Just so long as you get a list of the tables that you do have or an empty response.
What you )01; want here is an error.

connected MySQL

connected MySQL

 

Posted on January 13, 2016 in Connecting PHP to MySQL

Share the Story

Back to Top
Share This