My PHP Finally Creates A Database

I finally created a database using PHP. I just followed the example closely:

<?php
$link = mysql_connect('localhost', 'root', '*****');
if (!$link) {
die('Could not connect: ' . mysql_error());
}

$sql = 'CREATE DATABASE dental_office';
if (mysql_query($sql, $link)) {
echo "Database dental_office created successfully\n";
} else {
echo 'Error creating database: ' . mysql_error() . "\n";
}
?>

Using a string variable to capture the connection tag makes it a lot easier to call in the future. Soon I will be able to create code that allows patients to fill out information online and sent it to the dental office immediately. (This happens to be a very big convenience as I work at a dental office where that is needed :p)

My code went off without a hitch. I even found there are ways to connect directly to specific ports and sockets by replacing localhost with the site's name or IP. I only have one server and one data base so I don't think I'll be needing that I'm happy to finally be on track. Variables are running well, I'm connecting to MySQL through PHP, I've even gotten PHPMyAdmin to create tables for me on the database.

Everything is looking up for Milhouse, er Jay.

Hey! I just realized this is my 10th post! I've also reached another official benchmark. My Blog appears across two months, April and May!

To celebrate, here is Champagne!

Creative Commons License, photo from FreePhotoBank.org

Well I don't drink, but even non-drinkers can appreciate the majesty of Champagne, France!

Learning PHP-MySQL: Trudging Along

In my search for code I entered mysql php test on my Google search bar and the first result was PHP/MySQL Tutorial - Part 1 by freewebmasterhelp.com. Feeling lucky (pun intended) I clicked through and upon reading was convinced that a simple <?php phpinfo(); ?> was all I needed to check if MySQL was working properly. The tutorial tells me if MySQL is mentioned on the page then it has been installed. I see MySQL and MySQLi and decide to stop right there. Then I decide to start up again.
I see Part 4 - Displaying Data and decide to give their code a test drive. I get error messages that I do not even want to go into so I am going to install PHPMyAdmin instead.
Must...procrastinate.....longer.
PHPMyAdmin is an administrative tool that makes handling MySQL easier.

After downloading PHPMyAdmin I unzipped the contents into c:/myserver and entered http://localhost/phpMyAdmin-2.10.1-all-languages-utf-8-only/scripts/setup.php in my web browser.

Look at all these wonderful toys.

So after going through Servers and adding my own with info I found on <?php phpinfo(); ?> I went back to the drawing board and started echoing examples from the official PHP Manual. I have been able to connect to MySQL with the following code which I derived from their section on mysql_connect()
<?php
//Example 1352. mysql_connect() example
//http://us.php.net/manual/en/function.mysql-connect.php
$user = "root";
$password= "*****";
$link = mysql_connect('localhost', $user, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>
I am able to manipulate databases using PHPMyAdmin. Unfortunately, I still can't create a database using PHP. At least I know it is possible to connect.

Testing MySQL: Installing MySQL, configuring php.ini and removing deprecated tags.

Previously, I was testing MySQL and could not get a page to load at all. I was receiving the error message:

PHP Fatal error: Call to undefined function mysql_connect()

I added a couple extensions in php.ini and just now found a new error message appeared.

PHP Warning: PHP Startup: Unable to load dynamic library './php_mysql.dll'
- The specified module could not be found.\r\n in Unknown on line 0

It was not looking in the right directory, rather I never specified where to look. Now I have tried setting the extension directory with extension_dir = "c:\php\ext" in php.ini, I have received a new error message. (I'm getting somewhere!)

PHP Fatal error: Call to undefined function mysql_create_db() in
C:\\myserver\\test_mysql.php on line 33

I have a feeling I'll be doing a lot of php.ini configuring. After searching for the previous error in google I came across a list of emails in the archives of Neohapsis.com where someone mentioned that mysql_create_db() is deprecated. So much for using older tutorials. I searched for mysql_create-db() deprecated on Google and came across a comment on the manual that says mysql_query() should be used instead.

I went back into my test file and changed the two instances of mysql_create_db to mysql_query and finally something appears on the page. Unfortunately the variables are not appearing to be saved. Oh error looooog!,

PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
result resource in C:\\myserver\\test_mysql.php on line 66

Fetch doesn't have anything to fetch so it is reporting an error. I think I'll find someone elses file to test MySQL with (ignores the little voice saying "Make one yourself you lazy oaf")

Testing MySQL

I decided to run a test on MySQL to see if it has been configured properly.

WebThang's tutorial has a test file to use on the end of its MySQL installation tutorial that should work fine (the only change I made was changing shorthand error):

Cut and Paste the following code into 'notepad' and save it as 'c:\web\test_mysql.php'

<?php
/************************************************************
this script runs only once, it then drops the database before
mysql connection is closed.
************************************************************/

/***************************************************
string values passed on to the following 5 variables.
two of each are assigned to 'ice' and the rest three
tag along into 'mysql_connect'
***************************************************/
$iceCreamOne = "Vanilla"; // Type One Ice Cream
$iceCreamTwo = "Cookie Dough Ice Cream"; // Type Two Ice Cream
$host = "localhost"; // hostname...in our case localhost
$username = "root"; // root is our default
$password = "ranginyoka31"; // your mysql password please

//open connection to the MySQL database server.
$connection = mysql_connect($host,$username,$password);
//if connection fails, display the error involved
if ($connection == false){
echo mysql_errno().": ".mysql_error()."<BR>";
//echo("Your username or password is not correct.");
exit;
}

/************************************************
After supper we will have two kinds of desserts
create the database then select it. Two functions
called mysql_create_db() & mysql_select_db()
if statements to check success of create & select
************************************************/
$create_success = mysql_create_db("aftersupper");
if($create_success)echo("<b><font color=\"blue\">create database: success!</font></b><br>");
$select_success = mysql_select_db("aftersupper");
if($select_success)echo("<b><font color=\"blue\">selected the created database: success!</font></b>");

/*************************************************************
your choice for 2 desserts
for me I like vanilla[iceCreamOne] & cookie dough[iceCreamTwo]
*************************************************************/
mysql_query("CREATE TABLE desserts(iceCreamOne VARCHAR(25),
iceCreamTwo VARCHAR(25))");

/**********************************************************
i put my two favorite ice cream types into table desserts: vanilla & cookie dough
remember im using variables that have been assigned with strings up there ^^^.
*********************************************************/
mysql_query ("INSERT INTO desserts (iceCreamOne, iceCreamTwo) VALUES
('$iceCreamOne', '$iceCreamTwo')");

/********************************************************
as long as there is information in the table keep printing.
i have two values in table 'desserts', both rows are passed to variable 'result'
**********************************************************/
$result = mysql_query ("SELECT * FROM desserts");

//checking to see that select was successfull
//if ($result){echo "<h2>Successfully selected from table desserts!</h2>\n";}

//assign the number of rows from variable $result to $numOfRows
//$numOfRows = mysql_num_rows ($result);

//for ($i = 0; $i < $numOfRows; $i++)
//{
$row = mysql_fetch_array($result);
print ("<h3>My 2 most favorite Ice Cream are:</h3><br>\n");
print($row["iceCreamOne"]." and ");
print($row["iceCreamTwo"]. "<br>");
// }

//Database gets dropped. You can comment the line below if you wish to keep the database
mysql_query("DROP DATABASE aftersupper");

//close the connection to the db with the particular user :: $username
mysql_close();
?>

So I tried to run the program and ended up getting a blank page. I looked at the MainFrame.err file and didn't see anything peculiar. I then looked at Apache's access log and found nothing.

The problem appeared in Apache's error log:

[Sun Apr 29 11:30:43 2007] [error] [client ###.#.#.#] PHP Fatal error: Call to undefined function mysql_connect() in C:\\myserver\\test_mysql.php on line 19

I searched for "PHP Fatal error: Call to undefined function mysql_connect()" on Google and found this thread on MySQL. At the top of the thread the first poster has the same problem as many others do. At the end of the thread the poster says the solution is located in the second to last post (a lot of pointing going on). That thread finally leads me to siteinaweek.com which finally makes me say "Oh, duh".

I forgot to add extensions for MySQL in php.ini

I just removed the semi-colin before "extension=php_mysql.dll" like so

and removed it before "extension=php_mysqli.dll" as well.

I stopped and restarted the Apache server before testing test_mysql.php and again received a blank page. I'll work on this some more later.

Installing MySQL: Configuration

After installing MySQL I have to configure it. I have looked at both the MySQL Server Configuration Wizard Documentation and WebThang.co.uk's Tutorial for installing MySQL (I needed something a little less technical heavy to get me started and they've done a good job on tutorials).

When the window

this window

comes up I follow this sequence:
Next>Select Standard Configuration>Next>Next (Left Install As Windows Service and Launch the MySQL Server automatically on by default)>Entered my root password>Next>Execute>Finish

Now I test to see if it has been configured properly in good ol' Command Prompt
(Start>Programs>Accessories>Command Prompt)

I type in "c:/mysql/bin/mysql test" without the quotation marks on my prompt and...

then receive the following error message:
ERROR 1045 <28000>: Access denied for user 'ODBC'@'localhost'

I have no idea what this means. Going back to MySQL's "2.4.8.9. Starting the Server for the First Time" I see the suggestion to run mysqld.exe from the bin folder.

So I type in "c:/mysql/bin/mysqld. Instead of seeing a bunch of lines appear telling me something is being processed it quickly readies itself for the next prompt *blink blink*

Realizing my server is off I start Apache and attempt to run mysqld again. This time it waits for 5 or 6 seconds then readies itself for the next prompt without giving me any fanfare. It's supposed to give me fanfare. After reading 2.4.8.9 again I see that it will not show the lines I was expecting to see if I do not type "--console" at the end of the command line. That last part was hidden in section navigation on the documentation page. Maybe they should make it a little more visible.

So I look in /mysql/data/MainFrame.err (it's where all the error messages and, if you don't enter --console, diagnostic messages go. Everything I expected to see in command prompt was there.

I logged into my account by changing the directory to c:/mysql/bin/
then entering mysql -u root password ****** in Command Prompt.

mysql - Mysql.exe, the program I'm opening
"-u" - means I'm about to enter the username
root - the ultimate grandmaster username upon installation
password - I entered a password on set up so I have to put password here if I'm going to access anything
****** - I can never tell (said in the voice of Jim Carrey's Riddler)

So I've got everything configured. Now...now...what am I going to do with all this stuff?

Installing MySQL

So I began by unzipping zip file into a folder of the same name. It really doesn't matter where it is unzippped. I now have setup.exe ready to run.

After it goes through a little song and dance preparing installation files and preparing to install it shows me a window, MySQL Server 5.0 - Setup Wizard

I click next> select custom (because I want to install in in c:/mysql instead of the default directory in Program Files)> click next> click change directory> enter c:/mysql under Folder name: > Click OK> click Next> click Install

After installation "Configure the MySQL Server Now" should be checked and I'll just click Finish and move on.