The other day I was browsing Amazon.com trying to find a good PHP/MySQL book for my easily distracted mind to focus on. I have been making starts and stops in my PHP journey for some time now mostly because I was busy with school, but also because I couldn’t seem to find a good resource to help me get started and moving fast. The book that I had contemplated using before when I started working on improving my PHP skills fell short because it covered outdated versions of PHP and MySQL so I was constantly running into all kinds of errors.

Well, I think I finally solved my problem! I think I finally found the book that I’ve been looking for:

This book is great for several reasons. One, it’s laid out in a very easy to follow manner. It starts you off with just PHP, and has you building a nice dynamic website just with HTML/CSS and PHP by the second chapter. You get all the code so you can type it all out, but you can also download it from the accompanying website. I prefer to type it out because then I learn better. After you’ve got the basics of PHP under your belt, Larry moves you into MySQL and databases, and while I haven’t got there yet, I can tell by browsing through that he has done a thorough job covering as many details as he can in a book of that size. At the end, everything comes together, and you build several applications to test your knowledge and to whet your appetite for more.

Being the geek I am, I never buy a book or a gadget without reading as many reviews as I can find, and PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition) (Visual QuickPro Guide) has excellent reviews at Amazon.com, and I definitely highly recommend it.

By the way, the second edition covers PHP 5 and MySQL 4.1, so you’re pretty up to date, and Appendix A guides you through the installation process. I’m excited to start working through this book!!

If you are serious about learning PHP and MySQL, you need good resources. There are many online tutorials and websites, and I have listed some really useful resources in a previous post. If you are like me and prefer to not read off a screen, and instead like to use a book, there are many excellent books out there that range from novice to expert level. I have collected what I believe to be some of the best books to have and added them my Programmers Store, so that they are in one easy place for you to find. So go ahead and check it out.

You can also find some on your library bookshelf for free, which is always better… free is good. The one that I am using is Beginning PHP5, Apache, and MySQL Web Development (Programmer to Programmer), and I love it. It starts you from scratch, although it does help to have some HTML/CSS knowledge. The Appendix has instructions on installing Apache, PHP, and MySQL, which came in really handy for me.

So check out the store!!

I am still working on the starting stages of PHP, with real life and other obligations interfering frequently, so progress has been a tad slow. However, I have been spending a lot of time on different websites and forums learning, and getting motivated. In this post I will share some of these useful links and resources, and will be adding them as I go, so keep an eye on the Useful Links and Resources category. The ordering of these links is pretty random and does not reflect any kind of ranking.

So here goes:

• PHP Official website - This is the home of PHP, and your best source for information on bugs, updates, and PHP documentation. There is also an extensive faq, mailing lists, and a page of useful links. Definitely your best first stop for all things PHP.

• Digital Point Forums - Webmaster forums with a section for PHP. This is an excellent site to join and ask questions about any coding problems, and to learn about web design and webmaster issues in general. Excellent responses from members.

• Sitepoint Forums - Another excellent webmaster forum and great resource for anyone venturing into PHP. Very helpful place.

• HypertextFever Forums - This is a new forum that’s really cool and shows great promise for being a great resource. It has a lot more of a personal feel to it. While the bigger more established forums are awesome, sometimes when you’re a novice you need a smaller setting so that you don’t feel too intimidated… I know I do. Great quality responses, and the owner of the forum is a PHP/MySQL guru, and responds fast and extensively to all questions, so this is a great place for anyone who wants to learn and grow.

• Joe Watkins - PHP Programmer - This website is a bit more advanced but has useful PHP scripts and interesting tips and code. You can register and subscribe to the feed to get the latest posts.

There are numerous more and I will be adding them as I go, after I evaluate them. I may eventually build a link page to list them all. If you know any exceptional ones that should absolutely not be left out please feel free to leave a comment and I will add them to my growing list!

For now… happy coding!

In my last post I talked about dealing with a .sql file from the command line. This worked well, but there are other ways to interact with your databases that are GUI based, and perhaps easier for someone accustomed to dealing with the GUI interface. The two that I am currently playing with are PHPMyAdmin and Heidi SQL. Both are free to download and use, keeping with my theme of using free tools and utilities whenever I possibly can.

I have worked with phpMyAdmin before on the web but now have it installed on my laptop along with the Apache Server, MySQL and PHP. I have never used Heidi SQL before so it will be interesting to see what these tools have to offer in terms of working with and manipulating databases.

As I wrote in response to Bo’s comment to my tutorial on working with .sql dump files, phpMyAdmin has been known to fail when importing larger databases, and this is where the command line comes in handy. For some, working from the command line is just preferable to using a GUI. However, not all hosts allow you to have shell access to your account, so it’s a good thing to get accustomed to using both the command line and GUI tools.

One thing about installing phpMyAdmin with PHP5 is that it’s a bit of a chore getting it to talk to MySQL. As I discovered after a lot of head banging, googling, pounding my keyboard, and a swearing, this is primarily a PHP installation problem.

Since v.5 onwards, PHP does not come inbuilt with support for MySQL as a default anymore, therefore if you have trouble connecting to MySQL from phpMyAdmin at your initial installation, chances are that the problem lies in the fact that your PHP is not talking to MySQL. There’s a lot of information in different forums about this, and google is your best friend.

My solution was to delete my manual installation of PHP, and install PHP5 using the msi installer from php.net with its default settings. If you enable all the extensions you will have trouble since some of them require other software to be installed. Your best bet is to install it as it is (selecting your version of the server of course), and then add extensions. You do this by going to “Add/Remove Programs” in Control Panel, choose Change the installation, and enable MySQL, MySQLi, Mcrypt, and Multi-Byte String to run from the hard drive. This step ensures that you don’t need to edit your php.ini file as suggested in many forums, and which you would need to do if you were installing manually. You should also add the extension folder (usually C:/Program Files/PHP/ext) to the PATH. While you are there double check that your installation folder (C:/Program Files/PHP/) is also in the path. Remember that each time you make a change to your PHP install you need to restart your server (Apache in my case) to load the new configuration. And whenever you make changes to your PATH variables, ideally you need to restart your computer for the changes to take effect.

You can test if your PHP install is talking to MySQL by creating and running a PHP file such as the one below, provided kindly to me by Bo.

(You will need to insert your own values for “mysql_username”, “mysql_password” and “mysql_db_name”).

If your install was successful, you should see something like

After PHP is installed correctly and works with MySQL, you can then install (or run) phpMyAdmin following the instructions given on the web page.

Now that I have finally successfully installed phpMyAdmin, I have all the tools that I need to start doing some serious stuff, and I am going to start writing some code in PHP and also interacting with databases using the three tools that I have at my disposal - phpMyAdmin, Heidi SQL, and the good old command line.

I have some .sql dump files that I wanted to open and play with. After googling and doodling, I finally accepted that I was going to have to overcome my innate need for orderly progression and jump into doing stuff with mySQL before actually getting to that chapter in the book!!

So here are the steps I used. I have mySQL Server 5.0 installed on my laptop, and I will go step by step to describe what I did. You will need to use your directory path and file names to do this yourself.

My sql dump file was named directory.sql and was in a folder on my desktop. My mySQL install is under C:\Program Files\MySQL\MySQL Server 5.0. My ultimate goal is to get the data in the sql dump file into csv format. I know there are probably easier ways to do it, and many tools, but I figured going step by step through the method I am going to describe was a good way to entertain myself and learn some MySQL in the process.

So let’s do it:

1. First, of course, I got myself a cold coke.

2. I opened the mySQL command line and entered my root password. I have found that it’s easier to actually work from the Command Line Client in the mySQL menu on my Start menu than it is to actually open the command line and try to navigate to the directory where mySQL is installed.

If you have set root password, which you always should, the client will ask for your password and then proceed to the

prompt.

2. Next step was to create the table that the data in my sql file would go into. To do this, I first opened the sql dump file in Wordpad and examined it to determine what to name the table. Don’t use notepad for this because it will be much harder to read. If you have Textpad installed it’s even better, but I used Wordpad. I then typed in the command prompt:

3. If this is done correctly with no typos, you will get a success statement to the effect:

Query OK, 1 row affected <0.00 sec>

4. Switch to the database you just created using the USE command.

You should see the text

Database Changed

5. So far so good. Next step is to determine if your sql file already has the statements to create the tables you need or if you have to do it yourself. In my case, the statements were already there, so it was simply a matter of invoking the sql file to create and populate the tables. For this you use the SOURCE command. You can make it easy and move your sql file to your mySQL directory, but to make it interesting, let’s leave the sql file on the desktop and specify the path:

Remember you need to replace these paths with your own path and filename.

You will then see on the screen a series of lines as mySQL executes the queries to create and populate your tables.

You can see the tables that have been created using:

Once the tables are listed, you can check the details of any particular table using the DESCRIBE command,. So for one of my tables named caterers I typed:

So I now have a mySQL database that I can use. The mySQL databases are created, by default, in the data directory. So under C:\Program Files\MySQL\MySQL Server 5.0\data there is a folder titled MaryDir.

If you want a sample file to play with you can download one from the mySQL website here: http://dev.mysql.com/doc/

Next I will explore different ways to view and interact with the database I just created.

So now I have my laptop all set up so that I can write and test php code. My next step was to collect the remaining tools I need to start, so first I had to decide what I’m going to use to write and edit my code. I’ve always liked Notepad for HTML coding because it doesn’t leave behind any codes or stuff. It’s just a plain clean text editor. But the disadvantage with Notepad is that a lot of debuggers will report errors by line number. Notepad doesn’t insert line numbers, and the last thing I want to do is start counting lines of code. So I caved in and decided to find a better editor that’s more suited for programming.

Several options were suggested to me, but I decided to go with two of them for now, because I have commitment issues and like to have options :). My choices are Notepad++ and PHP Designer 2007 Personal, both free. I also have Programmers Notepad that I’ve used before for other stuff, I might try it for PHP too - alternatives are a good thing.

Finally, before getting down and dirty, I need some kind of goal, a project. I learn best when I have something that I am building, so I picked a book that approaches the learning process by creating something. The book is Beginning PHP5, Apache, and MySQL Web Development (Programmer to Programmer), and I’m going to start with that and see if it works for me. I do have some of my own projects in mind, but they’re still wisps of ideas, so I’m going to borrow the author’s projects for now. There are other excellent books out there, but this is one that I grabbed off the shelf at the Free Library.