LIBRARY: Tutorials Reviews Interviews Editorials Features Business Authors RSS Feed

Start Using MySQL with PHP

A MySQL Tutorial with PHP and PHPMyAdmin


Using MySQL with PHP

Start Using MySQL with PHP - MySQL Tutorial with PHP and PHPMyAdmin

This tutorial is designed to give the beginning web designer some basic knowledge and tools to start using MySQL with PHP. This tutorial includes a PHPMyAdmin tutorial, a simple Content Management System for your MySQL table, and file templates to start displaying MySQL data on web pages.


Abraham ChaffinAbraham Chaffin
CreativeCOW.net,
Cambria, California USA

©2008 CreativeCOW.net. All rights reserved.


Background:

MySQL is used by the majority of web developers to store data online. Blogs, forums, images, movies, search engine information, user account information, and even entire web sites are stored, and accessed using MySQL.

Installation:
For information on installing MySQL onto a web server go to http://dev.mysql.com/doc/refman/5.0/en/installation-overview.html

Database and User Setup:
The main structure of the data in MySQL starts with databases. Databases are the main storage bins for the data inside MySQL. Users are granted permissions to a database that allows them to read, write, or delete from the database. Inside each database is a given number of tables that hold the data for that database. Creating databases and assigning users to it is done only by users with higher level permissions. If you are using a 3rd party host such as Network Solutions or GoDaddy, setting up your database and users for MySQL is done through your hosting control panel. Call your hosting company for specific details.


Brief PHPMyAdmin Tutorial:

Once you have a MySQL database and user account setup you are generally given a link or URL to a PHPMyAdmin control panel. This is a GUI that allows you to manage your MySQL database. There are other GUIs available for MySQL but PHPMyAdmin is by far the most popular.

When you go to the URL provided to you, you will be asked to put in your user name and password. This will be your MySQL user name, and password which may differ from other passwords though some service providers make them the same as your main hosting account user name and password.

MySQL PHPMyAdmin Login Screen

Once you are logged in, a PHPMyAdmin screen appears as shown below. This screen may differ depending on the version of PHPMyAdmin that is running. As you see by the image permission to create new databases is not generally set so you will need to select from the list of available databases to begin to create and manage your MySQL tables.

MySQL PHPMyAdmin Splash Screen

To begin adding data into your database you must create a table. For best results your table name should be all lowercase with no spaces. Use and under-score "_" to separate words. e.g. my_table_name. For this example we will create a table with 5 fields called my_new_table inside the database called test.

MySQL PHPMyAdmin Create Table

When you click Go you will be brought to the Create a new table page with a form you must fill out to create the new table. Each field has options applied to it on this page that will define the field type, size, name, and more:
  • Field - is the name of the field - this should be lower case with no spaces and an underscore used for spaces for best results. e.g. my_field_name.
  • Type - is the type of data that will be stored inside this field - default is VARCHAR. You can learn more about data types in MySQL by going to http://dev.mysql.com/doc/refman/5.0/en/data-types.html
  • Length/Values - is generally the number of characters allowed in the field - i.e. for a zip code you might have a length of 5.
  • Collation - is the character set for the field - generally this is not set and the default for the table is automatically used.
  • Attributes - are special attributes that can be set for the field - this may not apply for all field types and is not necessary in this tutorial.
  • Null - choose between not null and null - not important for most users.
  • Default - is the default value for the field - for instance if you wanted a zip code to be placed in if no value is given you would put the desired default in here.
  • Extra - choose to auto_increment a fields value which is very useful and most users use for every table. This is used most commonly to assign a unique id to each row so that a specific row can be called for using only one unique identifier. The first value automatically put into this type of field is generally 0, the next would be 1, then 2, 3, etc.
  • Primary - both index and unique.
  • Index - an index field is like a tab in a binder that allows MySQL to find all the related data on that topic faster.
  • Unique - a unique field is a field that has a value in its row that is not found in any other row. If an attempt is made to add a row with a duplicate value for that row an error is returned.
  • Fulltext - This is rarely used but can be read about at http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html.
  • The rest of the parameters for fields will not be covered and are not used for most database tables.
For this example we will create a few commonly used fields. These field names are not required for a new table but give you a basic idea of how to use this system. The "id" field is strongly suggested and is required if you plan on using the tools and methods used at the end of this tutorial:
  • id - this is a commonly used field name and refers to the unique id for the row in this table. It will help identify one row of data for quick access to that data. The type is set to INT to indicate the value for this field is an integer. The length is set to 6 as we aren't intending this table's ids to go over 6 digits (999999 would be the highest value for this field but the length can always be altered later). This field will be our primary field and will auto_increment.
  • name - this will be the name we give to the row. If you were storing information about people such as user information it would be a persons name here. It could also be the name of a subject or anything. VARCHAR indicates it is a mixed type of data that will be in the character set defined here or by the table or database. You would want the character set of the data stored to be the same as the site the data would eventually be displayed in. 100 characters long is usually sufficient for a persons name or the like.
  • short_info - this could be a head line for a persons bio and we assigned 255 characters in length which is the max for a VARCHAR field. VARCHAR fields are a good type of field to use because they are searched relatively quickly by MySQL.
  • description - most description fields are longer (text or blob fields) to allow abundant data to be stored there. This field could be pages of text.
  • modified - it's good to have some sort of timestamp on your row to keep track of when the last modification of the row was made. The timestamp is in YYYY-MM-DD HH:MM:SS format. This field is set to update when the row is created and every time the row is updated.
MySQL PHPMyAdmin Table Fields

When you click Save it should create the table successfully and display the code that PHPMyAdmin generated to perform the requested operation. By looking at the code you start to get the basic understanding of a MySQL query syntax.

MySQL PHPMyAdmin Table Created

Once you have a table created you have additional options given to you in the top navigation. To follow is an overview of what each page does when you click on the new tabs.
  • Browse - allows you to browse the entries of your table (if you have any) and edit or delete those entries.
  • Structure - allows you to manage the table fields that you just created above. You can add, edit and delete the table fields.
  • SQL - the SQL page gives you the ability to input sql commands and run them via PHPMyAdmin.
  • Search - Perform searches on the table results return rows that match your search.
  • Insert - Add a row to the table.
  • Export - Export / Backup your table data.
  • Import - Import a backup of your table.
  • Operations - Modify the overall structure of the table or perform operations like duplicating the table.
  • Empty - Clear all rows in the table leaving only the table structure.
  • Drop - Delete the entire table.
MySQL PHPMyAdmin Navigation


A Basic PHP Database Management System (DBMS):

A database management system is a system such as PHPMyAdmin that allows you to control your database. Many times it is nice to have a simple DBMS for a client who wishes to control elements on their web page without having to call on the web designer for each change. Rather than giving them access to the entire PHPMyAdmin a basic DBMS can be used. Here is a functional, simple PHP DBMS that you can download and use. Simply view the source code, modify the variables at the top and upload it to your web server. I suggest putting a bit of security around the script so someone doesn't stumble across the page and is able to edit your database. Technically it is only a table management system as it only edits a single table but you can duplicate the file and use it for every table in your database. This is also called a Content Management System (CMS).


Displaying MySQL Data in a Web Page:

Displaying the Data in your MySQL database is of course the end result we want. Below are two different yet similar functions to show multiple rows or a single row. Showing multiple rows would show a list of items or entries while a single row would be showing one item, or entry. This method can be used and is used in infinite ways across the internet.

For either of these functions to work you must have a MySQL database and table setup with rows in it and know the user information.

Establishing the proper connection to the MySQL database is required for both functions. The initial connection is done by using the PHP function mysql_connect which links your web page to MySQL under your user authentication. Next the database is selected that holds the table we will be asking for this is done using the PHP function mysql_select_db which sets the active database for the connection.
<?
$databasename='database_name'; // Name of the database
$tablename='table_name'; // Name of the table
$mysqladd='localhost'; // Address to the MySQL Server - Usually localhost or an IP address
$mysqluser='MySQL_username'; // Your MySQL UserName
$mysqlpass='MySQL_password'; // Your MySQL Password

//CONNECT TO MYSQL $link=mysql_connect($mysqladd,$mysqluser,$mysqlpass) or die('Database Error: ' . mysql_error());
//CONNECT TO DATABASE mysql_select_db($databasename, $link) or die('Could not connect to table: ' . mysql_error()); ?>
Next is the querying of the database and the building of the $results array from the MySQL $result. Built from these lines below is an array called $results that contains the resulting row(s) from the mysql_query that was performed.

Method 1 Multiple Results Possible:
This will return all rows and fields from the table. Download the PHP file here.
<?
$results=array();
$sql="SELECT * FROM `".$tablename."`";
$result = mysql_query($sql, $link) or die('Error: ' . mysql_error()); 
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);
?>
Method 2 Single Result:
This will return the row that has the requested id. To request the id you would use a URL like: http://www.mydomain.com/single.php?id=5. Download the PHP file here.
<?
$results=array();
$sql="SELECT * FROM `".$tablename."` WHERE `id`=".$_REQUEST[id]." LIMIT 1";
$result = mysql_query($sql, $link) or die('Error: ' . mysql_error()); 
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);
?>
MySQL SELECT:
The SELECT statement is used to select data from a database.
The * indicates that you want all fields. To only return a single field from your query you replace the * with a field name.
"SELECT field_name_1 FROM table_name"
Additionally you can comma separate field names to only return specific fields from your query.
SELECT field_name_1,field_name_2,field_name_3 FROM table_name
Only selecting fields needed for your script can help keep the memory used by your server lower. Otherwise PHP must remember the entire row which could be must larger than the fields you really need.

If you want to filter your results to only return certain rows you can use the WHERE statement.
SELECT * FROM table_name WHERE field_name1='what ever'
This statement would only return rows where 'what ever' is the value in field_name1. You can do more complex WHERE filters by adding AND / OR operators in the query
SELECT * FROM table_name WHERE field_name1='what ever' OR id < '5'
To order your results in a specific order you use the ORDER BY statement - ASC or DESC can be used to choose a direction for the sort.
SELECT * FROM table_name ORDER BY field_name1 ASC
A larger table with thousands of rows would need a limit on the results so the results are not too large. To limit to a certain count you only need one value but if you wish to start at a given row and then limit you can give two values comma separated. LIMIT 5,20 would start at row 6 and give 20 results, LIMIT 0,10 starts at row 1 and gives 10 results.
SELECT * FROM `table_name` WHERE `field_name1`='what ever' ORDER BY `field_name` DESC LIMIT 50
Note: The tilde characters are added around field and table names in case there are spaces in the name. The single quotes are added around values in case their are spaces in the value. Capitalization of operators such as 'select', 'desc', or 'where' is not important but is used for the differentiation of the text.

Displaying the $results to the Web Browser:
Method 1 Multiple Results Possible:
The code below displays the results in an ordered list with field_name1 as a link to view the single item in a separate page.
<?
if(count($results)){ //IF THERE ARE RESULTS
	//DISPLAY THE RESULTS
	foreach($results as $r){	
		echo('<li><a href="single.php?id='.$r[id].'">'.$r[field_name1].'</a>');
		echo(' -- '.$r[field_name2].' -- '.$r[field_name3].'<br><br>');
	}
}else{
	echo('Sorry - no results found');
}
?>
Method 2 Single Result:
The single results are the same as the multiple results except it only contains one row that are in the array $results[0] as the first and only row. Multiple results also contain other potential arrays such as $results[1] and $results[2].
$results[0][field_name] accesses the field_name value for the first row in the results.
<?
if(count($results)){
  echo('<a href="single.php?id='.$results[0][id].'">'.$results[0][field_name1].'</a>');
  echo('<br> '.$results[0][field_name2].' <br> '.$results[0][field_name3]);
}else{
	echo('Sorry - no result found');
}
?>

I hope you've learned a few things in this tutorial and can use these methods / tools in your future projects. Feel free to leave feedback, questions, or comments below. If you'd like any sections expanded let me know.
Comments

Re: Start Using MySQL with PHP
by Michael Bacter
I think this tutorial has shown me the easiest way of writing this code out so that its easy to make sense of,
however I seem to keep hitting one problem or another
I have a db and a table with 2 colums but I only want to retrieve 1 for now im not getting no error message but im also not getting the data that's in the colums either
i'm just not getting anything at all
Very nice tutorial
by jenny slootskin
My web hosting is GoDaddy, but in order to try some stuff on my mac before loading it up on the server, could I have MYSQL and myPHP Admin isntalled on my mac? or is it too much trouble? Thank you for your answer

Jenny
Start Using MySQL with PHP
by Abraham Chaffin
What you're looking for is inserting form data into the mysql database. Along with the form that allows them to insert the podcast file you want to include fields for their other information. In the MySQL database you want corresponding fields that will receive that data.

Fields such as:

<input type="text" name="persons_name">


You would then in the target script use a MySQL Insert call receiving that data from the post / get method. Use $_POST which is generally safer than $_GET. $_REQUEST allows you to grab from either.

Line would be something like:


$sql="INSERT * FROM `table_name` (`field_name1`,`field_name2`) ".
"VALUES('".addslashes($_POST[persons_name])."' , 'field_name2data')";


Or use the UPDATE method if you're just updating a mysql row.

Google MySQL insert or MySQL update for more examples.

Hope this helps,

Abraham
Start Using MySQL with PHP
by advantage
Hi Abraham,

I learned a lot from your PHP Tutorial. Would you have tutorials on creating forms and uploading podcasts and keeping records of those uploads in phpMyAdmin and MySQL.

I am trying to create a PHP web document form in Dreamweaver 8 that will allow people to upload their podcasts. I have the form created and can submit a file into a folder, but I do not know how to keep a record of the user who submits, time and day, and the name of their podcast.

Can you lead me to the right direction?

Appreciate any help. Thanks,
Gary
Start Using MySQL with PHP
by Ron Lindeboom
Abraham,

I think my brain just exploded but it's okay, we have you around the COW to do this stuff so that Kathlyn and I don't have to.

Nice tutorial.

Ron Lindeboom


Related Articles / Tutorials:
Web Design
Simple CSS Centered Layout

Simple CSS Centered Layout

This tutorial from CreativeCOW leader, Fernando Mol explains the principles of how to create an horizontally centered layout using HTML and CSS.

Tutorial
Fernando Mol
Web Design
Full Page Overlay WindowFull Page Overlay Window

In this tutorial you will learn how to create a full page pop-up window that overlays an existing html page. This technique is used often for displaying a video, movie file, picture, or form when a user clicks on an element in a web page.

Tutorial
Abraham Chaffin
Web Design
Embedding Quicktime MoviesEmbedding Quicktime Movies

Learn the basics and advanced methods of embedding Quicktime Movies into your web site, blog or any html page. Find out what it takes to maintain cross platform compatibility when publishing all your Quicktime Videos. A more advanced technique of using Dreamweaver CS3 to embed your movie using ActiveX javascript embedding is also discussed.

Tutorial
Abraham Chaffin
Web Design
Reality ColdFusion MX: Flash MX Integration

Reality ColdFusion MX: Flash MX Integration

Author/Web developer Michael Hurwicz examines Reality ColdFusion MX: Flash MX Integration by Ben Forta, Randy Drisgill, Dennis Baldwin, Matt Tatam, and Derek Lu. This book is published by Macromedia press. The book discusses goals, logic and development process behind four projects, for which full source code is available on-line. A great way to get a jump start on ColdFusion/Flash integration. Recommended for intermediate Flash and ColdFusion users -- not for beginners.

Review
Michael Hurwicz
Web Design
Mike Gondek reviews Total Training's LiveMotion2

Mike Gondek reviews Total Training's LiveMotion2

CreativeCOW leader, Mike Gondek examines a 3 DVD training series called Live Motion 2.0 by Steve Holmes and published by Total Training.

Review
Mike Gondek
Web Design
Controling Adobe GoLive using Hot-Key settings

Controling Adobe GoLive using Hot-Key settings

In this article, Ron Lindeboom shows newer and other GoLive users how to make GoLive act the way you want it to. Have you ever wished you could set the hot-key short-cuts the way you wanted instead of the way that the Adobe engineers set them? Well you can do it and it's very easy to do. Here's how...

Tutorial
Ron Lindeboom
Recent Articles / Tutorials:
Field Production
“Before I forget: don’t wear any underwear.”

“Before I forget: don’t wear any underwear.”

Before coming to Creative COW, before his lives in product marketing and product management at Avid and Boris FX, Creative COW Editor-in-Chief Tim Wilson ran a video production company. As we also observe the 100th Anniversary of the founding of the US Parks Service, Tim recalls one one especially memorable adventure to Everglades National Park, wherein he found himself quite literally up to his armpits in alligators. He had no idea that this was going to happen when the day began. At the time, he was focused on a brand new fear: getting sliced in half by burning underwear.

Editorial, Feature, People / Interview
Tim Wilson
Art of the Edit
The Science of Editing

The Science of Editing

Sven Pape, aka @ThisGuyEdits, joins Dr. Karen Pearlman -- former President of the Australian Screen Editors Guild and a three-time nominee for Best Editing at the Australian Screen Editors Guild Annual Awards -- for a provocative look at "Editor's Thinking," a cognitive skill set that you can use to improve your screenplay before you start principal photography of your film.


Sven Pape
Panasonic Cameras
Shooting MTV's Mary + Jane with Panasonic VariCam 35

Shooting MTV's Mary + Jane with Panasonic VariCam 35

To shoot the ½ hour scripted comedy series for MTV, Mary + Jane, the producers at Television 360 enlisted cinematographer Charles Papert (Crazy Ex-Girlfriend, Key and Peele), who found that Panasonic VariCam is a great fit for moving fast and getting great images when time and resources are scarce.


COW News
Adobe After Effects
Imagineer mocha Pro 5 Plug-In for Adobe: An In Depth Review

Imagineer mocha Pro 5 Plug-In for Adobe: An In Depth Review

Imagineer mocha Pro 5 Plug-in for Adobe brings all the amazing features of the professional version of the mocha Planar Tracker directly into After Effects and Premiere Pro in the form of a plugin. In this in-depth review, After Effects tutorial guru Tobias Gleissenberger of Surfaced Studio will show you what you can do with this new plug-in, and discuss what he likes and doesn't like about the new update.

Tutorial
Tobias Gleissenberger
MORE
© 2016 CreativeCOW.net All Rights Reserved
[TOP]