Getting Started With MySQL DB and PHP with PHPMyAdmin

Posted in Tutorials, Web Design • Posted on 15 Comments

Today I am going to show you how to get a new database setup with PHP/MySQL using PHPMyAdmin. For those of you who don’t know PHPMyAdmin is a great free tool to help you manage your databases. If you use cpanel or anything you have probably already seen/used it.

First Creating a Database With PHPMyAdmin

If you are using cpanel there is a link in there or for any other hosting they should provide you with a link to your PHPMyAdmin. I personally have an old machine that I turned into a LAMP (Linux,Apache,MySQL,PHP) box and installed PHPMyAdmin. I also set it up as a vhost and changed my windows etc/hosts to point phpmya.com to my Linux box. So for me I go to but your link will be different most likely.

So I open Firefox and go to and it prompts for a login. After entering my credentials this is the screen I see:

phpMyAdmin Main Page

From here you:

  1. Click “Privileges”
  2. Click “Add a new User”
  3. Enter a “User name:” (keep it simple use whatever you want to name your database)
  4. Select “Local” for “Host:”
  5. You can enter a password but I suggest clicking “Generate” (either way make sure you have the password safe somewhere)
  6. Under “Database for user” select “Create database with same name and grant all privileges”
  7. Click “Go”

You should now have a new database with the name that you have selected in the menu on the left. If you click on that database it will be empty and the page will look like this.

New Database In PHPMyAdmin

There you have it! You have successfully created a new database and database user with privileges to that database. From here you can create different tables that will suit your needs for you website.

Structuring Tables For Your New DB in PHPMyAdmin With RISE Editor

I am going to start with a “users” table which will be used for a site where multiple people will be able to login to change the content or post blogs. There are obviously a million different reasons why you might have a users section so this is a table that can vary a lot and can be used in many different ways, but this will be a pretty basic example.

First off, you need to think of the structure of your table. What will we want to store about each user? While no software is needed I like to use RISE Editor which is free software to help with database modeling. You can (and I have) use a pencil and paper for this. I will show you with RISE Editor.

  1. Open RISE Editor
  2. Click “New” icon in top left
  3. Select “Blank”
  4. Give it a name like the name of the site
  5. From the left toolbar click and drag “Entity” to the “Drawing” area
  6. Click where it says “Entity” on the element you just dragged over
  7. Change it to read “users” or whatever table you are creating
  8. At the bottom under “Attributes” add “id” and set it to “int”
  9. Repeat this for other attributes like (username,password,date_added,last_login,access_level,email) you may have more or less depending

You should end up with something that looks like this. This is not a completely necessary task but it is a good habit and makes it much easier to visualize what you have and/or might be missing. Also if you do it in RISE for each table you will have a good visual of your entire DB structure at the end of it.
New Table In RISE Editor

Creating Your New Table Structures

Looking back at the browser where we have PHPMyAdmin and have selected our new database from the left hand side you will see two input fields for “Name” and “Number of fields:”

  1. In the “Name” field put “users” and for “number of fields” put “6”, (there can be more or less just follow the structuring in RISE.)
  2. Click “Go”
  3. Enter the info as needed
    • id -> int,Index select “PRIMARY”,check “A_I”
    • username -> varchar,length (you choose how long you want I put 55)
    • password -> varchar,length (32 or greater: 32 is what md5 outputs)
    • date_added -> timestamp,default “CURRENT_TIMESTAMP”
    • last_login -> timestamp
    • access_level -> int
  4. Click “Save”

Viala! We have a new database with a users table. We could now create a secure login and dole out some username/passwords for people to login and mange our site for us.

Connecting to Our New Database

Now that we have a fancy new MySQL database in place, in order to use it for anything we will always need to connect to it. In today’s world of object oriented programming you see that whenever there is something that we will need to do more than once it is great to take advantage of classes and reusable code. So now I will go into a simple database class and “db.php” file that can be used and reused for you database connections.

db.class.php

<?php

class database {

	var $SQL;
	var $lastquery;
	var $count=0;

	function database($database, $server='localhost', $username='root', $password=''){
		$this->SQL = mysql_connect($server, $username, $password) or die('Error: '.mysql_error());
		mysql_select_db($database, $this->SQL);
	}

	function query($query, $return='true'){
		$this->lastquery = $query;
		$this->count++;
		$result = mysql_query($query, $this->SQL) or die('Error with Query('.$query.'): '.mysql_error());
		if ($return)
			return $result;
	}

	function num_rows(&$result){
		return @mysql_num_rows($result);
	}

	function fetch_array(&$result){
		return @mysql_fetch_array($result);
	}

	function fetch_assoc(&$result){
		return @mysql_fetch_assoc($result);
	}

	function insert_id(){
		return @mysql_insert_id();
	}

	function disconnect(){
		mysql_close($this->SQL);
	}

	function escape(&$string){
		return mysql_real_escape_string($string);
	}

	function result($query, $column, $id=0){
		return mysql_result($query, $id, $column);
	}
}
?>

The above class adds error handling to all of our main PHP functions that are used for interacting with the database. There is argue as to whether you should show database errors or not but the reason that I show them is for development and living by the principle that good coding will never produce a MySQL error. Basically just make sure that you code can never produce a MySQL error and don’t worry about it or if you are really concerned then don’t show the errors. It is your call. This class will be used in your db.php below like such.

<?php ob_start();
	session_start();

	include $_SERVER['DOCUMENT_ROOT'].'/classes/db.class.php';
	$db = new database('database', 'localhost', 'user', 'password'); // new instance of database class
?>

This db.php first does ob_start() which is output buffering to make sure that we do not get errors related to the session_start(). Then of course the $db.class.php is inlcuded and we start a new instance of the database class called $db. You see that we are starting the class with parameters being pasted and that is because we created a function with the same name as the class.
NOTE: The ob_start() and session_start() might not be needed for something simple that you might use but sessions are often used and extremely helpful so I added it in here.

So to use this setup we would have something like this. In this example we have the basic template and we are going to display a list of users.

header.php

<?php include'db.php';?>
<html>
     <head>
     </head>
     <body>
          <div id="content">

footer.php

           </div>
     </body>
</html>

index.php

<?php include'header.php';

     echo '<ul id="user_list">';
     $user_query = $db->query("SELECT name,last_login FROM users");
     while($user = $db->fetch_assoc($user_query))
     {
          echo '<li>
                        <h3>'.stripslashes($user['name']).'</h3>
                        <p>'.date('Y-m-d H:i", $user['last_login']).'</p> // date is a built in php function to format dates
                  </li>';
     }

include'footer.php';
?>

You can see the use of a database class can be helpful for many reasons. This example class can cut back on a lot of code. With this you won’t have to do an or die and error message for each query and it also makes some functions just less typing like (mysql_real_escape_string() vs $db->escape())

7 Written ArticlesWebsite

I am a web developer by trade but originally went to school for Information Technology - Network Engineering Technology at Purdue University. Getting into web development as a student web developer I developed a passion for it that left networking seem a bit boring. Even though I finished up my networking degree I stuck with web development lately I have been a WP7 advocate. My Blog.

15 Comments Best Comments First
  • Eduardo Matos

    Wednesday, October 20th, 2010 16:55

    7

    Be careful, as of PHP 5.3.3 methods that has the same name as the class are no longer treated as constructor.

    +2
  • David Soares

    Thursday, February 2nd, 2012 13:46

    15


    — Table structure for table `users`

    CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL auto_increment,
    `username` varchar(55) default NULL,
    `password` varchar(23) default NULL,
    `date_added` timestamp NULL default CURRENT_TIMESTAMP,
    `last_login` timestamp NULL default NULL,
    `access_level` int(11) default NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    +2
  • Bartosz Pulkowski

    Thursday, October 14th, 2010 13:52

    2

    My favorite software for database modeling in MySQL is MySQL Workbench

    +1
    • Brad

      Friday, October 15th, 2010 20:02

      5

      Thanks I’ll have to look into that

      0
  • Sergey Lempert

    Friday, November 26th, 2010 14:26

    8

    Nice tutorial for complete beginners. Keep up the good work!

    +1
  • Adam

    Monday, January 9th, 2012 21:29

    14

    hi brad can u give us the Table Structures in database becouse i have prblem with that keep on error 1166 with incurrect

    +1
  • ryan

    Tuesday, October 4th, 2011 08:28

    12

    i’m a beginer for php, yesterday i got home work to make a simple CMS. can you help me once?

    0
    • Rean John Uehara

      Tuesday, October 4th, 2011 11:01

      11

      Hi Ryan, here’s a complete tutorial for creating a simple CMS using PHP.

      Part 1
      Part 2
      Part 3

      Some of the techniques used there are not updated because the articles have been published earlier last year, but they’re still usable. Good luck on your home work!

      0
  • peter

    Thursday, October 14th, 2010 16:15

    3

    DO AS I SAY, NOT AS I DO.

    You lost me here:

    “I turned into a LAMP (Linux,Apache,MySQL,PHP) box and installed PHPMyAdmin. I also set it up as a vhost and changed my windows etc/hosts to point phpmya.com to my Linux box. So for me I go to ”

    “open Firefox and go to and it prompts for a login. After entering my credentials this is the screen I see:”

    Does nothing for me, oh yes you didn’t explain how to:

    “I turned into a LAMP (Linux,Apache,MySQL,PHP) box and installed PHPMyAdmin. I also set it up as a vhost and changed my windows etc/hosts to point phpmya.com to my Linux box. So for me I go to “

    0
    • Brad

      Friday, October 15th, 2010 19:58

      4

      This is a beginner tutorial so I assume most of you do not have an old machine around to turn into a server. I am simply telling you what I have to avoid questions about how I did it. My setup is not relevant to what yours is. As stated this is assuming you having hosting with phpmyadmin installed. And I also stated your link will be different. If you would like a tutorial on setting up a LAMP box perhaps I can work on one. What hosting are you currently using so perhaps I can help you.

      0
  • Ian

    Sunday, January 1st, 2012 14:02

    13

    My database said:

    #1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

    what can I do?

    0
  • Brett Widmann

    Tuesday, January 25th, 2011 05:06

    10

    This was a very helpful tutorial! Thank you for the help. I love the final product.

    0
  • Guest

    Friday, December 3rd, 2010 14:39

    9

    Nice tutorial, but: If you are using a tool like the RISE Editor to create your information model, why would you choose not to use it to generate your database (e.g. MySQL) and application layer (e.g. PHP)?

    0
  • D. Hart

    Thursday, October 14th, 2010 13:43

    1

    Very nice site; values oriented. I haven’t heard mention of ‘mores’ since psychology class. Your color scheme is very pleasing and the layout is well organized. I think it looks great! Thanks for sharing.

    0
    • Brad

      Friday, October 15th, 2010 20:04

      6

      Thank you your comment is much appreciated.

      0
  • David Soares

    Thursday, February 2nd, 2012 13:46

    15


    — Table structure for table `users`

    CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL auto_increment,
    `username` varchar(55) default NULL,
    `password` varchar(23) default NULL,
    `date_added` timestamp NULL default CURRENT_TIMESTAMP,
    `last_login` timestamp NULL default NULL,
    `access_level` int(11) default NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    +2
  • Adam

    Monday, January 9th, 2012 21:29

    14

    hi brad can u give us the Table Structures in database becouse i have prblem with that keep on error 1166 with incurrect

    +1
  • Ian

    Sunday, January 1st, 2012 14:02

    13

    My database said:

    #1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

    what can I do?

    0
  • ryan

    Tuesday, October 4th, 2011 08:28

    12

    i’m a beginer for php, yesterday i got home work to make a simple CMS. can you help me once?

    0
    • Rean John Uehara

      Tuesday, October 4th, 2011 11:01

      11

      Hi Ryan, here’s a complete tutorial for creating a simple CMS using PHP.

      Part 1
      Part 2
      Part 3

      Some of the techniques used there are not updated because the articles have been published earlier last year, but they’re still usable. Good luck on your home work!

      0
  • Brett Widmann

    Tuesday, January 25th, 2011 05:06

    10

    This was a very helpful tutorial! Thank you for the help. I love the final product.

    0
  • Guest

    Friday, December 3rd, 2010 14:39

    9

    Nice tutorial, but: If you are using a tool like the RISE Editor to create your information model, why would you choose not to use it to generate your database (e.g. MySQL) and application layer (e.g. PHP)?

    0
  • Sergey Lempert

    Friday, November 26th, 2010 14:26

    8

    Nice tutorial for complete beginners. Keep up the good work!

    +1
  • Eduardo Matos

    Wednesday, October 20th, 2010 16:55

    7

    Be careful, as of PHP 5.3.3 methods that has the same name as the class are no longer treated as constructor.

    +2
  • peter

    Thursday, October 14th, 2010 16:15

    3

    DO AS I SAY, NOT AS I DO.

    You lost me here:

    “I turned into a LAMP (Linux,Apache,MySQL,PHP) box and installed PHPMyAdmin. I also set it up as a vhost and changed my windows etc/hosts to point phpmya.com to my Linux box. So for me I go to ”

    “open Firefox and go to and it prompts for a login. After entering my credentials this is the screen I see:”

    Does nothing for me, oh yes you didn’t explain how to:

    “I turned into a LAMP (Linux,Apache,MySQL,PHP) box and installed PHPMyAdmin. I also set it up as a vhost and changed my windows etc/hosts to point phpmya.com to my Linux box. So for me I go to “

    0
    • Brad

      Friday, October 15th, 2010 19:58

      4

      This is a beginner tutorial so I assume most of you do not have an old machine around to turn into a server. I am simply telling you what I have to avoid questions about how I did it. My setup is not relevant to what yours is. As stated this is assuming you having hosting with phpmyadmin installed. And I also stated your link will be different. If you would like a tutorial on setting up a LAMP box perhaps I can work on one. What hosting are you currently using so perhaps I can help you.

      0
  • Bartosz Pulkowski

    Thursday, October 14th, 2010 13:52

    2

    My favorite software for database modeling in MySQL is MySQL Workbench

    +1
    • Brad

      Friday, October 15th, 2010 20:02

      5

      Thanks I’ll have to look into that

      0
  • D. Hart

    Thursday, October 14th, 2010 13:43

    1

    Very nice site; values oriented. I haven’t heard mention of ‘mores’ since psychology class. Your color scheme is very pleasing and the layout is well organized. I think it looks great! Thanks for sharing.

    0
    • Brad

      Friday, October 15th, 2010 20:04

      6

      Thank you your comment is much appreciated.

      0

Comments are closed.

54.226.0.225 - unknown - unknown - US