Installing PHP 5.2.3, MySql 5.0, IIS 6.0 on Windows XP

tag:blogger.com,1999:blog-61537708139424913772011-04-22T05:10:06.760+05:30Installing PHP 5.2.3, MySql 5.0, IIS 6.0 on Windows XPInstalling PHP 5.2.3, MySql 5.0, IIS 6.0 on Windows XPSunilnoreply@blogger.comBlogger41100tag:blogger.com,1999:blog-6153770813942491377.post-37335238500848619032008-09-06T13:38:00.000+05:302008-09-06T13:42:47.110+05:30PHP & MY SQL tutorial<meta equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><link rel="Edit-Time-Data" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_editdata.mso"><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="time"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="date"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="City"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PlaceName"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PlaceType"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="place"></o:smarttagtype><style>  </style> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>PHP / MySQL Tutorial<o:p></o:p></h1> </div> MySQL is currently the most popular open source database server in existence. On top of that, it is very commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications.<o:p></o:p> <script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>MySQL has been criticized in the past for not supporting all the features of other popular and more expensive DataBase Management Systems. However, MySQL continues to improve with each release (currently version 5), and it has become widely popular with individuals and businesses of many different sizes.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>What is a Database?<o:p></o:p></h1> </div> A database is a structure that comes in two flavors: a flat database and a relational database. A relational database is much more oriented to the human mind and is often preferred over the gabble-de-gook flat database that are just stored on hard drives like a text file. MySQL is a relational database. <o:p></o:p> In a relational structured database there are tables that store data. The columns define which kinds of information will be stored in the table. An individual column must be created for each type of data you wish to store (i.e. Age, Weight, Height). <o:p></o:p> On the other hand, a row contains the actual values for these specified columns. Each row will have 1 value for each and every column. For example a table with columns (Name, Age, Weight-lbs) could have a row with the values (Bob, 65, 165). If all this relational database talk is too confusing, don't despair. We will talk about and show a few examples in the coming lessons. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Why Use a Database?<o:p></o:p></h1> </div> Databases are most useful when it comes to storing information that fits into logical categories. For example, say that you wanted to store information of all the employees in a company. With a database you can group different parts of your business into separate tables to help store your information logically. Example tables might be: Employees, Supervisors, and Customers. Each table would then contain columns specific to these three areas. To help store information related to each employee, the Employees table might have the following columns: Hire, Date, Position, Age, and Salary. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Learn MySQL<o:p></o:p></h1> </div> Before you begin this tutorial you should have a basic knowledge of the information covered in our <a href="http://www.tizag.com/phpT/">PHP</a> and <a href="http://www.tizag.com/htmlT/">HTML</a> tutorials.<o:p></o:p> This tutorial focuses heavily on using MySQL in a PHP environment. It is aimed at teaching those who have web hosts with PHP and MySQL already installed. If you are unsure, please contact your web host.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Setup Guide<o:p></o:p></h1> </div> The easiest way to experiment with MySQL and PHP is to purchase some space on a shared web host. <o:p></o:p> <o:p> </o:p> Although you can set up MySQL manually on your home PC, it can be rather difficult for a beginner to do, and would require more than a few lessons! If you think you've got what it takes, or you're just mentally unstable, head on over to <a href="http://www.mysql.com/">MySQL.com</a> for more information on installing MySQL yourself.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Setting Up MySQL in CPanel<o:p></o:p></h1> </div> There are many different types of control panels that your shared hosting provider may have. This tutorial assumes that you are using the most popular, <a href="http://www.cpanel.net/">CPanel</a>.<o:p></o:p> First, find the link that allows you to administer MySQL. Within CPanel the icon is labeled MySQL Databases. Once there, you will need to do the following before you can start using MySQL.<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Create a new database <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Create a new user with password <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Assign the user to the database <o:p></o:p> If you have problems with this steps, seek help from your web hosting provider or ask a question in the <a href="http://forums.tizag.com/">Tizag Forums</a>.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Helpful Tool - phpMyAdmin!<o:p></o:p></h1> </div> Also supplied by most hosting services is <a href="http://www.phpmyadmin.net/home_page/index.php">phpMyAdmin </a>(you can also install it anywhere you want, as it's open source and free). This tool will allow you to view all the MySQL database, tables, and entries, as well as perform SQL queries remotely through a web browser.<o:p></o:p> Although we will be teaching how to create databases, tables and all other MySQL tasks through PHP, we encourage you to learn about phpMyAdmin. It's easy-to-use interface will allow you to do many common MySQL tasks quickly and easily, saving you many beginner headaches and helping you understand what's going on in a more visual manner. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Admin<o:p></o:p></h1> </div> This lesson covers the different options you have available to you for administering your MySQL service after it is successfully installed. If you already have that base covered feel free to skip on to the next lesson.<o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Command Line<o:p></o:p></h1> </div> If you are an old-school programmer that has no need for a graphical user interface, then you can simply use any command line interface to execute MySQL queries. <o:p></o:p> Those of you with MySQL installed on your Microsoft Windows operating system can reach the command line by going to the Start Menu and choosing "Run...". Type the keyword "cmd" into the text field and press Enter to launch Window's command line interface. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL GUI<o:p></o:p></h1> </div> With so many free MySQL administration tools available, many developers favor these free Graphical User Interfaces over the command line. The most popular options include:<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <a href="http://www.phpmyadmin.net/">phpMyAdmin</a> - A popular web interface that is included with almost every type of Shared, Virtual or Dedicated hosting solution. <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <a href="http://www.mysql.com/products/tools/administrator/">MySQL Administrator</a> - A powerful tool developed by the folks at MySQL.com. <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <a href="http://www.navicat.com/">Navicat</a> - A purchasable MySQL admin tool for Windows, Mac and Linux. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL phpMyAdmin<o:p></o:p></h1> </div> As previously mentioned, the very popular <a href="http://www.phpmyadmin.net/home_page/">phpMyAdmin</a> tool should come with your web hosting plan.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Administrator<o:p></o:p></h1> </div> This tool comes from the creators of MySQL, so you can be assured they have a solid understanding of database optimization and stability for power users. There are currently two versions of MySQL Administrator: 1.0 and 1.1. MySQL.com recommends you use 1.1 if your MySQL installation is 4.0, 4.1 or 5.0. Read more about the <a href="http://www.mysql.com/products/tools/administrator/">MySQL Administrator</a> on MySQL.com's web site. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Navicat<o:p></o:p></h1> </div> Navicat comes with a 30-day trial so you can play around and see if you feel like dropping the cash for this MySQL administration product. A brief overview of their product <a href="http://www.navicat.com/product.html">Navicat Admin</a> can be found on their website. The cost of this product is around $100. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Syntax<o:p></o:p></h1> </div> The great thing about everything you do in MySQL is that the "code" is very easy for humans to read, as opposed to harder programming languages like C or C++. Very few special characters and symbols are required to create a MySQL query, and most queries consist entirely of English words!<o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Strategies to Learn MySQL<o:p></o:p></h1> </div> The MySQL language is not as complicated as most programming languages, so the best way to learn is with direct examples. Because this tutorial focuses on the combination of MySQL and PHP, most of the examples are ready for you to copy, paste, and run on your web server. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>CAPITALIZATION in MySQL Queries<o:p></o:p></h1> </div> There are many keywords in MySQL, and a good programming habit when using ANY of these words is to capitalize them. This helps draw them out from the rest of the code and makes them much easier to read. Below is an example of a MySQL query written in PHP that retrieves all the data from a MySQL table named "example". <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> $result = mysql_query("SELECT * FROM example") <o:p></o:p> That line of code is valid PHP, but it also contains valid MySQL. The text that appears between the quotations "SELECT * FROM example", is the MySQL code.<o:p></o:p> As you probably can tell "SELECT" and "FROM" are the MySQL keywords used in this query. Capitalizing them allows you to tell from a quick glance that this query selects data from a table.<o:p></o:p> You can view a complete list of MySQL keywords at <a href="http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html">List of Reserved MySQL Words</a>, but don't worry about memorizing them. You could program relentlessly in MySQL for years without ever using all of the MySQL keywords. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Learn at Your Own Pace<o:p></o:p></h1> </div> When learning MySQL, it is best to take it one lesson at a time and stop when you feel frustrated. Do not worry if it takes you more than a week to finish this tutorial. If you take the time to progress slowly, you will be much more well-informed about the MySQL database system than if you rushed through it in one sitting.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Database<o:p></o:p></h1> </div> A MySQL database is nothing in itself. Rather a MySQL database is a way of organizing a group of tables. If you were going to create a bunch of different tables that shared a common theme, you would group them into one database to make the management process easier.<o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Creating Your First Database<o:p></o:p></h1> </div> Most web hosts do not allow you to create a database directly through a PHP script. Instead they require that you use the PHP/MySQL administration tools on the web host control panel to create these databases. Create a database and assign a new user to this database. For all of our beginning examples we will be using the following information: <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Server - localhost <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Database - test <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Table - example <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Username - admin <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Password - 1admin <o:p></o:p> Note: The table may change in the advanced lessons, but everything else will remain the same!<o:p></o:p> The server is the name of the server we want to connect to. Because all of our scripts are going to be placed on the server where MySQL is located the correct address is localhost. If the MySQL server was on a different machine from where the script was running, then you would need to enter the correct url (ask your web host for specifics on this).<o:p></o:p> Your database, table, username, and password do not have to match ours. If you choose a different set of login information, remember to insert your own information when copying the scripts in this tutorial.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Status Check<o:p></o:p></h1> </div> So far, you should have created a new database and assigned a user to it. You should not have created a table yet. If you are up-to-date, then continue the tutorial. We will be making our first table in an upcoming lesson.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Connect<o:p></o:p></h1> </div> Before you can do anything with MySQL in PHP you must first establish a connection to your web host's MySQL database. This is done with the MySQL connect function. <o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL localhost<o:p></o:p></h1> </div> If you've been around the internet a while, you'll know that IP addresses are used as identifiers for computers and web servers. In this example of a connection script, we assume that the MySQL service is running on the same machine as the script.<o:p></o:p> When the PHP script and MySQL are on the same machine, you can use localhost as the address you wish to connect to. localhost is a shortcut to just have the machine connect to itself. If your MySQL service is running at a separate location you will need to insert the IP address or URL in place of localhost. Please contact your web host for more details if localhost does not work.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Connected to MySQL ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Connected to MySQL <o:p></o:p> If you load the above PHP script to your webserver and everything works properly, then you should see "Connected to MySQL" displayed when you view the .php page. <o:p></o:p> The mysql_connect function takes three arguments. Server, username, and password. In our example above these arguments were: <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Server - localhost <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Username - admin <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Password - 1admin <o:p></o:p> The "or die(mysql..." code displays an error message in your browser if --you've probably guessed it -- there is an error in processing the connection! Double-check your username, password, or server if you receive this error.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Choosing the Working Database<o:p></o:p></h1> </div> After establishing a MySQL connection with the code above, you then need to choose which database you will be using with this connection. This is done with the mysql_select_db function. <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Connected to MySQL ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Connected to Database";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Connected to MySQL Connected to Database <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Status Check<o:p></o:p></h1> </div> So far you should have made a MySQL connection and chosen the working database. If you are up-to-date then continue the tutorial. We will be making our first table in the next lesson. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Tables<o:p></o:p></h1> </div> A MySQL table is completely different than the normal table that you eat dinner on. In MySQL and other database systems, the goal is to store information in an orderly fashion. The table gets this done by making the table up of columns and rows. <o:p></o:p> <script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>The columns specify what the data is going to be, while the rows contain the actual data. Below is how you could imagine a MySQL table. (C = Column, R = Row)<o:p></o:p> <div align="center"> <table class="MsoNormalTable" style="background: white none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> <td style="padding: 0.75pt;"> C1 (Namge)<o:p></o:p> </td> <td style="padding: 0.75pt;"> C2 (Age)<o:p></o:p> </td> <td style="padding: 0.75pt;"> C3 (Weight)<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> R1<o:p></o:p> </td> <td style="padding: 0.75pt;"> R1 C1 (John)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R1 C2 (21)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R1 C3 (120)<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> R2<o:p></o:p> </td> <td style="padding: 0.75pt;"> R2 C1 (Big Sally)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R2 C2 (27)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R2 C3 (400)<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> R3<o:p></o:p> </td> <td style="padding: 0.75pt;"> R3 C1 (Tiny Tim)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R3 C2 (6)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R3 C3 (35)<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> R4<o:p></o:p> </td> <td style="padding: 0.75pt;"> R4 C1 (<st1:place st="on">Normal</st1:place> Ned)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R4 C2 (35)<o:p></o:p> </td> <td style="padding: 0.75pt;"> R4 C3 (160)<o:p></o:p> </td> </tr> </tbody></table> </div> We added the row and column number (R# C#) so that you can see that a row is side-to-side, while a column is up-to-down. In a real MySQL table only the value would be stored, not the R# and C#!<o:p></o:p> This table has three categories, or "columns", of data: Name, Age, and Weight. This table has four entries, or in other words, four rows. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Create Table MySQL<o:p></o:p></h1> </div> Before you can enter data (rows) into a table, you must first define what kinds of data will be stored (columns). We are now going to design a MySQL query to summon our table from database land. In future lessons we will be using this table, so be sure to enter this query correctly! <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Create a MySQL table in the selected database<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query("CREATE TABLE example(<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">id INT NOT NULL AUTO_INCREMENT, <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">PRIMARY KEY(id),<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> name VARCHAR(30), <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> age INT)")<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Table Created!";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Table Created! <o:p></o:p> Wow! That's a lot of code all at once! Let's get down in the dirt and figure this stuff out. We will be going through the code line by line.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'mysql_query ("CREATE TABLE example'<o:p></o:p></h1> </div> The first part of the mysql_query told MySQL that we wanted to create a new table. The two capitalized words are reserved MySQL keywords. <o:p></o:p> The word "example" is the name of our table, as it came directly after "CREATE TABLE". It is a good idea to use descriptive names when creating a table, such as: employee_information, contacts, or customer_orders. Clear names will ensure that you will know what the table is about when revisiting it a year after you make it. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'id INT NOT NULL AUTO_INCREMENT'<o:p></o:p></h1> </div> Here we create a column "id" that will automatically increment each time a new entry is added to the table. This will result in the first row in the table having an id = 1, the second row id = 2, the third row id = 3, and so on.<o:p></o:p> The column "id" is not something that we need to worry about after we create this table, as it is all automatically calculated within MySQL. <o:p></o:p> Reserved MySQL Keywords: Here are a few quick definitions of the reserved words used in this line of code: <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> INT - This stands for integer or whole number. 'id' has been defined to be an integer. <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> NOT NULL - These are actually two keywords, but they combine together to say that this column cannot be null. An entry is NOT NULL only if it has some value, while something with no value is NULL. <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> AUTO_INCREMENT - Each time a new entry is added the value will be incremented by 1. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'PRIMARY KEY (id)'<o:p></o:p></h1> </div> PRIMARY KEY is used as a unique identifier for the rows. Here we have made "id" the PRIMARY KEY for this table. This means that no two ids can be the same, or else we will run into trouble. This is why we made "id" an auto-incrementing counter in the previous line of code. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'name VARCHAR(30),'<o:p></o:p></h1> </div> Here we make a new column with the name "name"! VARCHAR stands for "variable character". "Character" means that you can put in any kind of typed information in this column (letters, numbers, symbols, etc). It's "variable" because it can adjust its size to store as little as 0 characters and up to a specified maximum number of characters.<o:p></o:p> We will most likely only be using this name column to store characters (A-Z, a-z). The number inside the parentheses sets the maximum number of characters. In this case, the max is 30. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'age INT,'<o:p></o:p></h1> </div> Our third and final column is age, which stores an integer. Notice that there are no parentheses following "INT". MySQL already knows what to do with an integer. The possible integer values that can be stored in an "INT" are -2,147,483,648 to 2,147,483,647, which is more than enough to store someone's age! <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'or die(mysql_error());'<o:p></o:p></h1> </div> This will print out an error if there is a problem in the table creation process.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Your Homework<o:p></o:p></h1> </div> Using the MySQL administration tool that your web host has, check to see if the table was created correctly. Afterwards, try creating a few of your own, with PHP or with a MySQL administration tool, to be sure that you have gotten the hang of it. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Insert<o:p></o:p></h1> </div> When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember the exact names and types of the table's columns. If you try to place a 500 word essay into a column that only accepts integers of size three, you will end up with a nasty error! <o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Inserting Data Into Your Table<o:p></o:p></h1> </div> Now that you have created your table, let's put some data into that puppy! Here is the PHP/MySQL code for inserting data into the "example" table we created in the previous lesson. <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Insert a row of information into the table "example"<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query("INSERT INTO example <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">(name, age) VALUES('Timmy Mellowman', '23' ) ") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query("INSERT INTO example <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">(name, age) VALUES('Sandy Smith', '21' ) ") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query("INSERT INTO example <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">(name, age) VALUES('Bobby Wallace', '15' ) ") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Data Inserted!";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Data Inserted! <o:p></o:p> This code is much simpler to understand than the create table code, as will be most of the MySQL queries you will learn in the rest of this tutorial. Once again, we will cover the code line by line.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'mysql_query("INSERT INTO example'<o:p></o:p></h1> </div> Again we are using the mysql_query function. "INSERT INTO" means that data is going to be put into a table. The name of the table we specified to insert data into was "example". <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'(name, age) VALUES('Timmy Mellowman', '23' ) ")' <o:p></o:p></h1> </div> "(name, age)" are the two columns we want to add data into. "VALUES" means that what follows is the data to be put into the columns that we just specified. Here we enter the name Timmy Mellowman for "name", and 23 for "age". <o:p></o:p> Be sure to note the location and number of apostrophes and parentheses in the PHP code, as this is where a lot of beginner PHP/MySQL programmers run into problems. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Review & Homework<o:p></o:p></h1> </div> If all goes as well, this .php page will add a three people to the "example" table every time it is run. Be sure to use your MySQL administration program provided by your web host to ensure that the data was inserted into your table correctly.<o:p></o:p> Be careful not to run this script more than once, otherwise you will be inserting the same people, multiple times. This is called inserting duplicate records and is usually avoided.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Query<o:p></o:p></h1> </div> So far we have seen a couple different uses of PHP's mysql_query function and we'll be seeing more of it as nearly all MySQL in PHP is done through the MySQL Query function. We have already created a new table and inserted data into that table. In this lesson we will cover the most common MySQL Query that is used to retrieve information from a database. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>Retrieving Data With PHP & MySQL<o:p></o:p></h1> </div> Usually most of the work done with MySQL involves pulling down data from a MySQL database. In MySQL, data is retrieved with the "SELECT" keyword. Think of SELECT as working the same way as it does on your computer. If you wanted to copy some information in a document, you would first select the desired information, then copy and paste. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Using MySQL SELECT & FROM<o:p></o:p></h1> </div> Before attempting this lesson, be sure that you have created a table that contains some data, preferably the same data that we had in the <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">MySQL Insert lesson</a>. In this example, we will output the first entry of our MySQL "examples" table to the web browser. <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Retrieve all the data from the "example" table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example")<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// store the record of the "example" table into $row<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$row = mysql_fetch_array( $result );<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out the contents of the entry <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Name: ".$row['name'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo " Age: ".$row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Name: Tim Mellowman Age: 23 <o:p></o:p> This is an example of how to use MySQL's SELECT statement in PHP. Although the MySQL code is simple, printing out the information with PHP is somewhat more involved.<o:p></o:p> Below is a step-by-step walkthrough of the code.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'$result = mysql_query("SELECT * FROM example")'<o:p></o:p></h1> </div> When you perform a SELECT query on the database it will return a MySQL Resource that hols everything from your MySQL table, "example". We want to use this Resource in our PHP code, so we need to store it in a variable, $result. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'SELECT * FROM example'<o:p></o:p></h1> </div> Yes, this is a partial repeat of the same line of code, but we wanted to explain this MySQL statement in greater detail again!<o:p></o:p> In English, this line of code reads "Select every entry from the table example". The asterisk is the wild card in MySQL which just tells MySQL to include every single column for that table. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'$row = mysql_fetch_array( $result );'<o:p></o:p></h1> </div> $result is now a MySQL Resource containing data from your MySQL table, "example". Data is being stored in $result, but it is not yet visible to visitors of your website. When we pass $result into the mysql_fetch_array function -- mysql_fetch_array($result) -- an associative array (name, age) is returned.<o:p></o:p> In our MySQL table "example," there are only two fields that we care about: name and age. These names are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age']. <o:p></o:p> PHP is case sensitive when you reference MySQL column names, so be sure to use capitalization in your PHP code that matches the MySQL column names!<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Continuing the Example<o:p></o:p></h1> </div> In this lesson, we learned how to get the first entry from a MySQL table and output to the browser using PHP. In the next lesson we will see how to retrieve every entry of a table and put it into a nicely formatted table. However, we recommend that you first understand the PHP and MySQL code in this lesson before proceeding. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Fetch Array<o:p></o:p></h1> </div> MySQL doesn't have a Fetch Array function. mysql_fetch_array is actually a PHP function that allows you to access data stored in the result returned from a successful mysql_query. If you have been jumping around our MySQL Tutorial then you would have already seen this function popping up all over the place.<o:p></o:p> <script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>This lesson will teach you how and why to use mysql_fetch_array in your PHP Scripts.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>mysql_fetch_array: Why Use It?<o:p></o:p></h1> </div> Do you know what is returned when you used the mysql_query function to query a MySQL database? It isn't something you can directly manipulate, that is for sure. Here is a sample SELECT query of a table we created in the <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">MySQL Create Table</a> lesson.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example");<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> The value that mysql_query returns and stores into $result is a special type of data, it is a MySQL Resource. Additional PHP functions are required to extract the data from this Resource.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>A Row of Data<o:p></o:p></h1> </div> The mysql_fetch_array function takes a MySQL query resource as an argument ($result) and returns the first row of data returned by the mysql_query. Our table example basically looks like the table below.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">example MySQL Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Timmy Mellowman<o:p></o:p> </td> <td style="padding: 0.75pt;"> 23<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Sandy Smith<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Bobby Wallace<o:p></o:p> </td> <td style="padding: 0.75pt;"> 15<o:p></o:p> </td> </tr> </tbody></table> The first row of data in this table is "Timmy Mellowman" and "23". When we fetch an array from our MySQL Resource $result it should have Timmy's name and age in it.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Getting a Row of Data using mysql_fetch_array<o:p></o:p></h1> </div> mysql_fetch_array returns the first row in a MySQL Resource in the form of an <a href="http://www.tizag.com/phpT/arrays.php">associative array</a>. The columns of the MySQL Result can be accessed by using the column names of the table. In our table example these are: name and age. Here is the code to print out the first MySQL Result row.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT * FROM example"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$row = mysql_fetch_array($result) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo $row['name']. " - ". $row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Timmy Mellowman - 23 <o:p></o:p> This is just what we expected would happen! Now, the cool thing about mysql_fetch_array is that you can use it again on the same MySQL Resource to return the second, third, fourth and so on rows. You can keep doing this until the MySQL Resource has reached the end (which would be three times in our example). <o:p></o:p> Sounds like an awfully repetitive task. It would be nice if we could get all our results from a MySQL Resource in an easy to do script.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Fetch Array While <st1:place st="on">Loop</st1:place><o:p></o:p></h1> </div> As we have said, the mysql_fetch_array function returns an associative array, but it also returns FALSE if there are no more rows to return! Using a <a href="http://www.tizag.com/phpT/whileloop.php">PHP While Loop</a> we can use this information to our advantage.<o:p></o:p> If we place the statement "$row = mysql_fetch_array()" as our while loop's conditional statement we will accomplish two things:<o:p></o:p> 1. We will get a new row of MySQL information that we can print out each time the while loop checks its conditional statement. <o:p></o:p> 2. When there are no more rows the function will return FALSE causing the while loop to stop! <o:p></o:p> Now that we know what we need to do and how to go about doing it, the code pretty much writes itself, so let's move on to the next lesson. Just kidding! Here is the code that will print out all the rows of our MySQL Resource.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT * FROM example"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['name']. " - ". $row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Timmy Mellowman - 23 <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city> Smith - 21 Bobby Wallace - 15 <o:p></o:p> And there we have all the rows from our example table! You could apply this script to any MySQL table as long as you change both the table name in the query and the column names that we have in the associative array.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Select<o:p></o:p></h1> </div> You have seen two types of MySQL queries thus far: the query which we used to create a table and the query we used to insert data into our newly created table. The query in this lesson is SELECT, which is used to get information from the database, so that its data can be used in our PHP script. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>Retrieving Information from MySQL<o:p></o:p></h1> </div> Finally, we get to use the data in our MySQL database to create a dynamic PHP page. In this example we will select everything in our table "example" and put it into a nicely formatted HTML table. Remember, if you don't understand the HTML or PHP code, be sure to check out the <a href="http://www.tizag.com/htmlT/">HTML</a> and/or <a href="http://www.tizag.com/phpT/">PHP</a> Tutorial(s). <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Get all the data from the "example" table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<table border="'1'">";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<tr> <th>Name</th> <th>Age</th> </tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// keeps getting the next row until there are no more to get<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array( $result )) {<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> // Print out the contents of each row into a table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<tr><td>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['name'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</td><td>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</td></tr>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">} <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</table>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Name<o:p></o:p> </td> <td style="padding: 0.75pt;"> Age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Timmy Mellowman<o:p></o:p> </td> <td style="padding: 0.75pt;"> 23<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Sandy Smith<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Bobby Wallace<o:p></o:p> </td> <td style="padding: 0.75pt;"> 15<o:p></o:p> </td> </tr> </tbody></table> Because we only had three entries in our table, three rows appeared above. If you added more entries to your database's table, then you would see each additional row appear in the above table. If you do not understand the above PHP, you can view our <a href="http://www.tizag.com/phpT/arrays.php">PHP Array Tutorial</a> & <a href="http://www.tizag.com/phpT/whileloop.php">PHP Loop Tutorial</a>.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'$result = mysql_query...'<o:p></o:p></h1> </div> When you select items from a database using mysql_query, the data is returned as a MySQL result. Since we want to use this data in our table we need to store it in a variable. $result now holds the result from our mysql_query.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'SELECT * FROM example'<o:p></o:p></h1> </div> In English, this line of code reads "Select everything from the table example". The asterisk is the wild card in MySQL which just tells MySQL to retrieve every single field from the table. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>'while($row = mysql_fetch_array( $result )'<o:p></o:p></h1> </div> The mysql_fetch_array function gets the next-in-line associative array from a MySQL result. By putting it in a while loop it will continue to fetch the next array until there is no next array to fetch. This function can be called as many times as you want, but it will return FALSE when the last associative array has already been returned.<o:p></o:p> By placing this function within the conditional statement of the while loop, we can kill two birds with one stones. <o:p></o:p> 1. We can retrieve the next associative array from our MySQL Resource, $result, so that we can print out the name and age of that person. <o:p></o:p> 2. We can tell the while loop to stop printingn out information when the MySQL Resource has returned the last array, as False is returned when it reaches the end and this will cause the while loop to halt. <o:p></o:p> In our MySQL table "example" there are only two fields that we care about: name and age. These fields are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age'].<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Practice What You Have Learned<o:p></o:p></h1> </div> Use the query that we have provided or make a new one and try putting it into a formatted HTML table. It might be useful to try out other methods of HTML formatting as well. See which one you like best!<o:p></o:p> By now you should be starting to understand how powerful PHP and MySQL are when used together. The tasks that you can complete with MySQL and PHP would be nearly impossible to do by hand in HTML. Imagine trying to create an HTML table of 6000 entries without using a MySQL database and a PHP while loop! <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Where<o:p></o:p></h1> </div> In a previous lesson we did a SELECT query to get all the data from our "example" table. If we wanted to select only certain entries of our table, then we would use the keyword WHERE.<o:p></o:p> <script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>WHERE lets you specify requirements that entries must meet in order to be returned in the MySQL result. Those entries that do not pass the test will be left out. We will be assuming the data from a <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">previous lesson</a> for the following examples.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Being Selective With Your MySQL Selection<o:p></o:p></h1> </div> There are three entries in our "example" table: Tim, Sandy, and Bobby. To select <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city> only we could either specify <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city>'s age (21) or we could use her name (Sandy Smith). In the future there may be other people who are 21, so we will use her name as our requirement.<o:p></o:p> WHERE is used in conjuction with a mathematical statement. In our example we will want to select all rows that have the string "Sandy Smith" in the "names" column (mathematically: {name column} = "Sandy Smith"). Here's how to do it.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Get a specific result from the "example" table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> WHERE name='Sandy Smith'") or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// get the first (and hopefully only) entry from the result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$row = mysql_fetch_array( $result );<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out the contents of each row into a table <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo $row['name']." - ".$row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Sandy Smith - 21 <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Wildcard Usage '%'<o:p></o:p></h1> </div> If you wanted to select every person in the table who was in their 20's, how could you go about doing it? With the tools you have now, you could make 10 different queries, one for each age 20, 21, 22...but that seems like more work than we need to do.<o:p></o:p> In MySQL there is a "wildcard" character '%' that can be used to search for partial matches in your database. The '%' tells MySQL to ignore the text that would normally appear in place of the wildcard. For example '2%' would match the following: 20, 25, 2000000, 2avkldj3jklsaf, and 2! <o:p></o:p> On the other hand, '2%' would not match the following: 122, a20, and 32.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Query WHERE With Wildcard<o:p></o:p></h1> </div> To solve our problem from before, selecting everyone who is their 20's from or MySQL table, we can utilize wildcards to pick out all strings starting with a 2.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Connect to MySQL  <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Insert a row of information into the table "example"<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example WHERE age LIKE '2%' ") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// keeps getting the next row until there are no more to get<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array( $result )) {<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> // Print out the contents of each row<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['name']." - ".$row['age']. " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">} <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Timmy Mellowman - 23 <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city> Smith - 21 <o:p></o:p> You can use this wildcard at the beginning, middle, and end of the string. Experiment with it so you can see for yourself how powerful this little trick can be.<o:p></o:p> Note: The wildcard was used for example purposes only. If you really wanted to explicilty select people who are in their 20's you would use greater than 19 and less than 30 to define the 20's range. Using a wildcard in this example would select unwanted cases, like a 2 year old and your 200 year old great-great-great-grandparents.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Order By<o:p></o:p></h1> </div> It would be nice to be able to make MySQL results easier to read and understand. A common way to do this in the real world is to order a big list of items by name or amount. The way to order your result in MySQL is to use the ORDER BY statement.<o:p></o:p> <script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>What ORDER BY does is take the a column name that you specify and sort it in alphabetical order (or numeric order if you are using numbers). Then when you use mysql_fetch_array to print out the result, the values are already sorted and easy to read.<o:p></o:p> Ordering is also used quite frequently to add additional functionality to webpages that use any type of column layout. For example, some forums let you sort by date, thread title, post count, view count, and more.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Sorting a MySQL Query - ORDER BY<o:p></o:p></h1> </div> Let's use the same query we had in <a href="http://www.tizag.com/mysqlTutorial/mysqlselect.php">MySQL Select</a> and modify it to ORDER BY the person's age. The code from MySQL Select looked like...<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Get all the data from the "example" table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<table border="'1'">";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<tr> <th>Name</th> <th>Age</th> </tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// keeps getting the next row until there are no more to get<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array( $result )) {<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> // Print out the contents of each row into a table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<tr><td>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['name'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</td><td>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</td></tr>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">} <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</table>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Name<o:p></o:p> </td> <td style="padding: 0.75pt;"> Age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Timmy Mellowman<o:p></o:p> </td> <td style="padding: 0.75pt;"> 23<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Sandy Smith<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Bobby Wallace<o:p></o:p> </td> <td style="padding: 0.75pt;"> 15<o:p></o:p> </td> </tr> </tbody></table> What we need to do is add on to the existing MySQL statement "SELECT * FROM example" to include our new ordering requirement. When you choose to order a column, be sure that your ORDER BY appears after the SELECT ... FROM part of the MySQL statement.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_connect("localhost", "admin", "1admin") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_select_db("test") or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Get all the data from the "example" table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example ORDER BY age") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<table border="'1'">";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<tr> <th>Name</th> <th>Age</th> </tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// keeps getting the next row until there are no more to get<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array( $result )) {<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> // Print out the contents of each row into a table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<tr><td>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['name'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</td><td>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['age'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</td></tr>"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">} <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</table>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Name<o:p></o:p> </td> <td style="padding: 0.75pt;"> Age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Bobby Wallace<o:p></o:p> </td> <td style="padding: 0.75pt;"> 15<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Sandy Smith<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Timmy Mellowman<o:p></o:p> </td> <td style="padding: 0.75pt;"> 23<o:p></o:p> </td> </tr> </tbody></table> Presto! We have an ordered MySQL result! Notice that we didn't have to change any of our PHP code. Remember this whenever you're editing a PHP script that uses MySQL. Sometimes it may be easier to just tweak your MySQL query instead of trying to mess around in PHP.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Joins<o:p></o:p></h1> </div> Thus far we have only been getting data from one table at a time. This is fine for simple takes, but in most real world MySQL usage you will often need to get data from multiple tables in a single query.<o:p></o:p> <o:p> </o:p> The act of joining in MySQL refers to smashing two or more tables into a single table. This means everything you have learned so far can be applied after you've created this new, joined table.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Join Table Setup<o:p></o:p></h1> </div> We like to show examples and code before we explain anything in detail, so here is how you would combine two tables into one using MySQL. The two tables we will be using relate to a families eating habits.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">family Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Position<o:p></o:p> </td> <td style="padding: 0.75pt;"> Age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> <td style="padding: 0.75pt;"> 41<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Mom<o:p></o:p> </td> <td style="padding: 0.75pt;"> 45<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Daughter<o:p></o:p> </td> <td style="padding: 0.75pt;"> 17<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Dog<o:p></o:p> </td> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> </tr> </tbody></table> <h2 style="margin: 7.5pt 0in 0.0001pt;">food Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Meal<o:p></o:p> </td> <td style="padding: 0.75pt;"> Position<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Steak<o:p></o:p> </td> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Salad<o:p></o:p> </td> <td style="padding: 0.75pt;"> Mom<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Spinach Soup<o:p></o:p> </td> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Tacos<o:p></o:p> </td> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> </tr> </tbody></table> The important thing to note here is that the column Position contains information that can tie these two tables together. In the "family" table, the Position column contains all the members of the family and their respective ages. In the "food" table the Position column contains the family member who enjoys that dish.<o:p></o:p> It's only through a shared column relationship such as this that tables can be joined together, so remember this when creating tables you wish to have interact with each other.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Join Simple Example<o:p></o:p></h1> </div> Let's imagine that we wanted to SELECT all the dishes that were liked by a family member. If you remember from the previous lesson, this is a situation when we need to use the <a href="http://www.tizag.com/mysqlTutorial/mysqlwhere.php">WHERE</a> clause. We want to SELECT all the dishes WHERE a family member likes it. <o:p></o:p> We will be performing a generic join of these two tables using the Position column from each table as the connector. <o:p></o:p> Note: This example assumes you have created the MySQL tables "food" and "family". If you do not have either of them created, you can either create them using our <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">MySQL Create Table</a> lesson or do it manually yourself.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Construct our join query<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT family.Position, food.Meal ".<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> "FROM family, food ".<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> "WHERE family.Position = food.Position";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out the contents of each row into a table <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['Position']. " - ". $row['Meal'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> The statement "WHERE family.Position = food.Position" will restrict the results to the rows where the Position exists in both the "family" and "food" tables. <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Dad - Steak Mom - Salad Dad - Tacos <o:p></o:p> Those are the results of our PHP script. Let's analyze the tables to make sure we agree with these results.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Compare the Tables:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Position<o:p></o:p> </td> <td style="padding: 0.75pt;"> Age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> <td style="padding: 0.75pt;"> 41<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Mom<o:p></o:p> </td> <td style="padding: 0.75pt;"> 45<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Daughter<o:p></o:p> </td> <td style="padding: 0.75pt;"> 17<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Dog<o:p></o:p> </td> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> </tr> </tbody></table> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Meal<o:p></o:p> </td> <td style="padding: 0.75pt;"> Position<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Steak<o:p></o:p> </td> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Salad<o:p></o:p> </td> <td style="padding: 0.75pt;"> Mom<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Spinach Soup<o:p></o:p> </td> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Tacos<o:p></o:p> </td> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> </tr> </tbody></table> Our results show that there were three meals that were liked by family members. And by manually perusing the tables it looks like there were indeed three meals liked by family members.<o:p></o:p> Note: This is a very simple example of a join. If you do not understand it yet do not despair. Joins are a very hard concept to grasp for beginning MySQL developers.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL LEFT JOIN<o:p></o:p></h1> </div> In the previous lesson, <a href="http://www.tizag.com/mysqlTutorial/mysqljoins.php">Mysql Joins</a> we learned how to do a basic join of two tables. This lesson will teach you how to do a specialized join: left join.<o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL LEFT JOIN Explanation<o:p></o:p></h1> </div> How is a LEFT JOIN different from a normal join? First of all, the syntax is quite different and somewhat more complex. Besides looking different, the LEFT JOIN gives extra consideration to the table that is on the left.<o:p></o:p> Being "on the left" simply refers to the table that appears before the LEFT JOIN in our SQL statement. Nothing tricky about that. <o:p></o:p> This extra consideration to the left table can be thought of as special kind of preservation. Each item in the left table will show up in a MySQL result, even if there isn't a match with the other table that it is being joined to.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Join and LEFT JOIN Differences<o:p></o:p></h1> </div> Here are the tables we used in the previous <a href="http://www.tizag.com/mysqlTutorial/mysqljoins.php">Mysql Joins</a> lesson.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">MySQL family and food Tables:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Position<o:p></o:p> </td> <td style="padding: 0.75pt;"> Age<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> <td style="padding: 0.75pt;"> 41<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Mom<o:p></o:p> </td> <td style="padding: 0.75pt;"> 45<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Daughter<o:p></o:p> </td> <td style="padding: 0.75pt;"> 17<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Dog<o:p></o:p> </td> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> </tr> </tbody></table> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> Meal<o:p></o:p> </td> <td style="padding: 0.75pt;"> Position<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Steak<o:p></o:p> </td> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Salad<o:p></o:p> </td> <td style="padding: 0.75pt;"> Mom<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Spinach Soup<o:p></o:p> </td> <td style="padding: 0.75pt;"> <o:p> </o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> Tacos<o:p></o:p> </td> <td style="padding: 0.75pt;"> Dad<o:p></o:p> </td> </tr> </tbody></table> We executed a simple query that selected all meals that were liked by a family member with this simple join query:<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Simplified MySQL Query:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">SELECT food.Meal, family.Position <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">FROM family, food <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">WHERE food.Position = family.Position<o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Result:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Dad - Steak Mom - Salad Dad - Tacos <o:p></o:p> When we decide to use a LEFT JOIN in the query instead, all the family members be listed, even if they do not have a favorite dish in our food table. <o:p></o:p> This is because a left join will preserve the records of the "left" table.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL LEFT JOIN Example<o:p></o:p></h1> </div> The code below is the exact same as the code in the previous lesson, except the LEFT JOIN has now been added to the query. Let's see if the results are what we expected.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Construct our join query<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT family.Position, food.Meal ".<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> "FROM family LEFT JOIN food ".<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> "ON family.Position = food.Position"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out the contents of each row into a table <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['Position']. " - ". $row['Meal'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Dad - Steak Dad - Tacos Mom - Salad Daughter - Dog - <o:p></o:p> Success! The LEFT JOIN preserved every family member, including those who don't yet have a favorite meal in the food table! Please feel free to play around with LEFT JOIN until you feel like you have a solid grasp of it. This stuff isn't easy!<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Update<o:p></o:p></h1> </div> Imagine that you have a MySQL table that holds the information of all the employees in your company. One of the columns in this table is called "Seniority" and it holds an integer value of how many months an employee has worked at your company. Unfortunately for you, your job is to update these numbers every month.<o:p></o:p> <o:p> </o:p> You may be thinking that you'll have to open up your MySQL administration tool and edit each entry by hand. That would take hours. On the other hand, you could master MySQL and have an automated script that you run each month to get the job done for you. <o:p></o:p> In this lesson you will learn how to replace the existing data of a MySQL table with freshly supplied up-to-date data using the UPDATE MySQL query. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Update Example<o:p></o:p></h1> </div> Once again we will be working with the data from a <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">previous example</a>. <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city> has just had a birthday and she now 22 years old. Our job now is to update her age using MySQL commands like UPDATE, SET, and WHERE. <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> UPDATE - Performs an update MySQL query <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> SET - The new values to be placed into the table follow SET <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> WHERE - Limits which rows are affected <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Connect to MySQL  <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Get <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city>'s record from the "example" table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("UPDATE example SET age='22' WHERE age='21'") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query("SELECT * FROM example WHERE age='22'") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// get the first (and hopefully only) entry from the result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$row = mysql_fetch_array( $result );<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo $row['name']." - ".$row['age']. " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Sandy Smith - 22 <o:p></o:p> Now it is important to note that this query would have updated ALL records that had an age of 21 to the new age of 22. In a table where <st1:city st="on"><st1:place st="on">Sandy</st1:place></st1:city> is not the onlyl entry, this may become a problem, and a more sophisticated solution would be necessary.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Delete<o:p></o:p></h1> </div> Maintenance is a very common task that is necessary for keeping MySQL tables current. From time to time, you may even need to delete items from your database. Some potential reasons for deleting a record from MySQL include when: someone deletes a post from a forum, an employee leaves a company, or you're trying to destroy your records before the federalies come! <o:p></o:p> <o:p> </o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL DELETE Example<o:p></o:p></h1> </div> The DELETE query is very similar to the <a href="http://www.tizag.com/mysqlTutorial/mysqlupdate.php">UPDATE Query in the previous lesson</a>. We need to choose a table, tell MySQL to perform the deletion, and provide the requirements that a record must have for it to be deleted. <o:p></o:p> Say we want to delete the youngest employee from our <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">previously created table</a> because he has to go back to school. This is how we do it.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Connect to MySQL  <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Delete Bobby from the "example" MySQL table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query("DELETE FROM example WHERE age='15'") <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">or die(mysql_error()); <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> It is important to note that this query would have deleted ALL records that had an age of 15. Since Bobby was the only 15 year old this was not a problem.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL DELETE Tips<o:p></o:p></h1> </div> Before performing a large delete on a database, be sure to back up the table/database in case your script takes off a little more than desired. Test your delete queries before even thinking about using them on your table. As long as you take caution when using this powerful query you should not run into any problems.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Database Backups<o:p></o:p></h1> </div> If you're storing anything in MySQL databases that you do not want to lose, chances are you should be doing weekly or even daily backups. Depending on what you're using your databases for -- be it to store forum messages, employee information, or your spending information -- you are going to need to choose a backup schedule that meets your needs. <o:p></o:p> <o:p> </o:p> You may or may not know that MySQL databases are just files that are stored on your web server. This fact makes the whole backup and restore process extremely simple and painless once you have figured out how to do it. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Different Ways to Get That Backup Done<o:p></o:p></h1> </div> There are many paths you can take to create a MySQL backup. However, no matter which application, control panel tool, or SSH script you use, all of the backups will fit into two types of backups: a dump or raw backup. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Dump<o:p></o:p></h1> </div> A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database's tables.<o:p></o:p> If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following (taken from <a href="http://dev.mysql.com/doc/refman/5.0/en/backup.html">MySql.com</a>): <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> mysqldump --tab=/path/to/some/dir --opt db_name <o:p></o:p> If you were to open up a MySQL dump file you would see a slew of SQL queries that you would probably be able to understand (if you've already read through this whole tutorial!). <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Raw Backup<o:p></o:p></h1> </div> A MySQL Raw Backup is quicker because it does not translate the contents of the database into human readable SQL queries. However, not many control panels support this type of backup, so do not worry if your hosting provider doesn't have this option set up for you.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Backup in Control Panel cPanel<o:p></o:p></h1> </div> <a href="http://www.cpanel.net/">cPanel</a> is the most widely used web host control panel at this time, so we thought it would make sense to provide a walkthrough specifically for cPanel.<o:p></o:p> From the application selection screen click "Backup". This will bring you to the backup application that allows you to generate and download complete backups for your site. <o:p></o:p> To back up a database individually, look for the title "Download a SQL Database Backup" or something similar. Below that title should be a listing of every database that you have created. Simply click on the name of the database you want to backup and save it to your computer.<o:p></o:p> That's it! Now just be sure that you have a regular backup schedule, just in case the unthinkable happens and your web host loses all your database information!<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL GROUP BY - Aggregate Functions<o:p></o:p></h1> </div> After you have mastered the basics of MySQL, it's time to take the next step and take on Aggregate Functions. Before we talk about what they are, let's review the definition of aggregate, as it relates to MySQL:<o:p></o:p> <o:p> </o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Aggregate - Constituting or amounting to a whole; total. ~American Heritage Dictionary <o:p></o:p> With this type of wording, we can assume that MySQL's aggregate functions are something that will be very top-level, or in other words, the opposite of detailed.<o:p></o:p> The most common types of aggregate functions let you find out things like the minimum, maximum and even the average of a "grouped" set of data. The trick to understanding aggregate functions is often understanding what kind of data is being grouped and analyzed. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL GROUP BY - The Data<o:p></o:p></h1> </div> Before we can start throwing around these fancy functions, let's build an appropriate table that has enough data in it to be meaningful to us. Below is the SQL for our "products" table. You can either run this SQL statement in your MySQL administrator software or use MySQL to execute the queries (i.e. create table, then each of the records).<o:p></o:p> You can download the <a href="http://www.tizag.com/files/mysql/products.sql">products.sql</a> file from our website. If you are new to MySQL you will need to know how to <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">Create a MySQL Table</a> and <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">Insert a MySQL Row</a>.<o:p></o:p> Below is the MySQL table products.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Products Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> id<o:p></o:p> </td> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> type<o:p></o:p> </td> <td style="padding: 0.75pt;"> price<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123451<o:p></o:p> </td> <td style="padding: 0.75pt;"> Park's Great Hits<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 19.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123452<o:p></o:p> </td> <td style="padding: 0.75pt;"> Silly Puddy<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123453<o:p></o:p> </td> <td style="padding: 0.75pt;"> Playstation<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 89.95<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123454<o:p></o:p> </td> <td style="padding: 0.75pt;"> Men's T-Shirt<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 32.50<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123455<o:p></o:p> </td> <td style="padding: 0.75pt;"> Blouse<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 34.97<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123456<o:p></o:p> </td> <td style="padding: 0.75pt;"> Electronica 2002<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123457<o:p></o:p> </td> <td style="padding: 0.75pt;"> Country Tunes<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21.55<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123458<o:p></o:p> </td> <td style="padding: 0.75pt;"> Watermelon<o:p></o:p> </td> <td style="padding: 0.75pt;"> Food<o:p></o:p> </td> <td style="padding: 0.75pt;"> 8.73<o:p></o:p> </td> </tr> </tbody></table> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>GROUP BY - Creating Your First "Group" <o:p></o:p></h1> </div> Imagine that our store was running an advertisement in the newspaper and we wanted to have a "bargain basement" section that listed the lowest price of each product type. In this case we would be "grouping" by the product type and finding the minimum price of each group.<o:p></o:p> Our query needs to return two columns: product type and minimum price. Additionally, we want to use the type column as our group. The SELECT statement we are about to use will look different because it includes an aggregate function, MIN, and the GROUP BY statement, but otherwise it isn't any different than a normal SELECT statement.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT type, MIN(price) FROM products GROUP BY type"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo $row['type']. " - $". $row['MIN(price)'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> Our "products" table has four types of products: Music, Toy, Clothing and Food. When we GROUP BY type then we get one result for each of these types.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Clothing - $32.50 Food - $8.73 Music - $3.99 Toy - $3.99<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL GROUP BY - Review<o:p></o:p></h1> </div> Group BY is good for retrieving information about a group of data. If you only had one product of each type, then GROUP BY would not be all that useful.<o:p></o:p> GROUP BY only shines when you have many similar things. For example, if you have a number of products of the same type, and you want to find out some statistical information like the minimum, maximum, or other top-level info, you would use GROUP BY.<o:p></o:p> Some technical rules of GROUP BY:<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> The column that you GROUP BY must also be in your SELECT statement. <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the type column and the aggregate function was applied to the price column. <o:p></o:p> The next few lessons will provide a walkthrough for using other popular MySQL aggregate functions in conjunction with the GROUP BY statement.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Aggregate Functions - COUNT()<o:p></o:p></h1> </div> This lesson will teach you how to use the aggregate function COUNT(). If you missed the <a href="http://www.tizag.com/mysqlTutorial/mysqlgroupby.php">Aggregate Introduction Lesson</a>, please check it out now, as it explains many concepts used in this lesson!<o:p></o:p> We will be using the "products" table that we constructed to display the use of MySQL's COUNT function.<o:p></o:p> You can download the <a href="http://www.tizag.com/files/mysql/products.sql">products.sql</a> file from our website. If you are new to MySQL you will need to know how to <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">Create a MySQL Table</a> and <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">Insert a MySQL Row</a> first.<o:p></o:p> Below is the MySQL table "products".<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Products Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> id<o:p></o:p> </td> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> type<o:p></o:p> </td> <td style="padding: 0.75pt;"> price<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123451<o:p></o:p> </td> <td style="padding: 0.75pt;"> Park's Great Hits<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 19.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123452<o:p></o:p> </td> <td style="padding: 0.75pt;"> Silly Puddy<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123453<o:p></o:p> </td> <td style="padding: 0.75pt;"> Playstation<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 89.95<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123454<o:p></o:p> </td> <td style="padding: 0.75pt;"> Men's T-Shirt<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 32.50<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123455<o:p></o:p> </td> <td style="padding: 0.75pt;"> Blouse<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 34.97<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123456<o:p></o:p> </td> <td style="padding: 0.75pt;"> Electronica 2002<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123457<o:p></o:p> </td> <td style="padding: 0.75pt;"> Country Tunes<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21.55<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123458<o:p></o:p> </td> <td style="padding: 0.75pt;"> Watermelon<o:p></o:p> </td> <td style="padding: 0.75pt;"> Food<o:p></o:p> </td> <td style="padding: 0.75pt;"> 8.73<o:p></o:p> </td> </tr> </tbody></table> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL COUNT - Counting Records<o:p></o:p></h1> </div> The COUNT function is an aggregate function that simply counts all the items that are in a group. The "products" table that is displayed above has several products of various types. One use of COUNT might be to find out how many items of each type there are in the table.<o:p></o:p> Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. For a slight change of pace, let's count the name column to find how many products there are per type.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT type, COUNT(name) FROM products GROUP BY type"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">There are 2 Clothing items. There are 1 Food items. There are 3 Music items. There are 2 Toy items. <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Aggregate Functions - SUM()<o:p></o:p></h1> </div> This lesson will teach you how to use the aggregate function SUM(). If you haven't already read through Tizag's <a href="http://www.tizag.com/mysqlTutorial/mysqlgroupby.php">Aggregate Introduction Lesson</a>, please check it out now. It explains concepts used in this lesson.<o:p></o:p> <script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>We will be using the "products" table again -- this time to display the use of MySQL's SUM function.<o:p></o:p> You can download the <a href="http://www.tizag.com/files/mysql/products.sql">products.sql</a> file from our website. If you are new to MySQL you will need to know how to <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">Create a MySQL Table</a> and <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">Insert a MySQL Row</a>.<o:p></o:p> Here's a visual of the "products" table.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Products Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> id<o:p></o:p> </td> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> type<o:p></o:p> </td> <td style="padding: 0.75pt;"> price<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123451<o:p></o:p> </td> <td style="padding: 0.75pt;"> Park's Great Hits<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 19.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123452<o:p></o:p> </td> <td style="padding: 0.75pt;"> Silly Puddy<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123453<o:p></o:p> </td> <td style="padding: 0.75pt;"> Playstation<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 89.95<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123454<o:p></o:p> </td> <td style="padding: 0.75pt;"> Men's T-Shirt<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 32.50<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123455<o:p></o:p> </td> <td style="padding: 0.75pt;"> Blouse<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 34.97<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123456<o:p></o:p> </td> <td style="padding: 0.75pt;"> Electronica 2002<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123457<o:p></o:p> </td> <td style="padding: 0.75pt;"> Country Tunes<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21.55<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123458<o:p></o:p> </td> <td style="padding: 0.75pt;"> Watermelon<o:p></o:p> </td> <td style="padding: 0.75pt;"> Food<o:p></o:p> </td> <td style="padding: 0.75pt;"> 8.73<o:p></o:p> </td> </tr> </tbody></table> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL SUM - Totaling Groups<o:p></o:p></h1> </div> SUM is an aggregate function that totals a specific column for a group. The "products" table that is displayed above has several products of various types. One use of SUM might be to find the total of all the items' price for each product type.<o:p></o:p> Just as we did in the aggregate introduction lesson, we are going to apply the aggregate function to price and GROUP BY type to create four groups: Music, Toy, Clothing and Food.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT type, SUM(price) FROM products GROUP BY type"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "Total ". $row['type']. " = $". $row['SUM(price)'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Total Clothing = $67.47 Total Food = $8.73 Total Music = $45.53 Total Toy = $93.94 <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Aggregate Functions - AVG()<o:p></o:p></h1> </div> This lesson will teach you how to use the aggregate function AVG(). If you missed the <a href="http://www.tizag.com/mysqlTutorial/mysqlgroupby.php">Aggregate Introduction Lesson</a>, please check it out now. It explains the meaning of aggregates and describes the GROUP BY statement.<o:p></o:p> The table we will be using is "products" and you can download the <a href="http://www.tizag.com/files/mysql/products.sql">products.sql</a> file so you can follow along. The table can be entered through you MySQL interface or through PHP.<o:p></o:p> If you are new to MySQL/PHP programming you will need to know how to <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">Create a MySQL Table</a> and <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">Insert a MySQL Row</a>.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Products Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> id<o:p></o:p> </td> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> type<o:p></o:p> </td> <td style="padding: 0.75pt;"> price<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123451<o:p></o:p> </td> <td style="padding: 0.75pt;"> Park's Great Hits<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 19.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123452<o:p></o:p> </td> <td style="padding: 0.75pt;"> Silly Puddy<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123453<o:p></o:p> </td> <td style="padding: 0.75pt;"> Playstation<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 89.95<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123454<o:p></o:p> </td> <td style="padding: 0.75pt;"> Men's T-Shirt<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 32.50<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123455<o:p></o:p> </td> <td style="padding: 0.75pt;"> Blouse<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 34.97<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123456<o:p></o:p> </td> <td style="padding: 0.75pt;"> Electronica 2002<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123457<o:p></o:p> </td> <td style="padding: 0.75pt;"> Country Tunes<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21.55<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123458<o:p></o:p> </td> <td style="padding: 0.75pt;"> Watermelon<o:p></o:p> </td> <td style="padding: 0.75pt;"> Food<o:p></o:p> </td> <td style="padding: 0.75pt;"> 8.73<o:p></o:p> </td> </tr> </tbody></table> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Average - Finding a Middle Ground<o:p></o:p></h1> </div> The AVG function returns the average value for the specified column of a group. <o:p></o:p> Our imaginary customers have been complaining recently that our prices are too high, so we would like to find out the average price of each product type to see if this is in fact the truth.<o:p></o:p> To find out this metric we are going to apply the aggregate function to the price and GROUP BY type to create four price groups: Music, Toy, Clothing and Food.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT type, AVG(price) FROM products GROUP BY type"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "The average price of ". $row['type']. " is $".$row['AVG(price)'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">The average price of Clothing is $33.735000 The average price of Food is $8.730000 The average price of Music is $15.176667 The average price of Toy is $46.970000 <o:p></o:p> Those prices seem very reasonable, in my opinion. I think our imaginary customers should change their view and keep buying products from us.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Aggregate Functions - MIN()<o:p></o:p></h1> </div> This lesson will teach you how to use the aggregate function MIN(). If you missed the <a href="http://www.tizag.com/mysqlTutorial/mysqlgroupby.php">Aggregate Introduction Lesson</a>, you might want to check it out to learn about the GROUP BY statement and its use with MySQL aggregate functions.<o:p></o:p> You can download the table used in this example, <a href="http://www.tizag.com/files/mysql/products.sql">products.sql</a>, from our website. A SQL file can be run through your MySQL administrator interface to create the table.<o:p></o:p> However, if you would like to create the table with PHP/MySQL, check out our <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">Create a MySQL Table</a> and <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">Insert a MySQL Row</a> lessons.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Products Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> id<o:p></o:p> </td> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> type<o:p></o:p> </td> <td style="padding: 0.75pt;"> price<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123451<o:p></o:p> </td> <td style="padding: 0.75pt;"> Park's Great Hits<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 19.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123452<o:p></o:p> </td> <td style="padding: 0.75pt;"> Silly Puddy<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123453<o:p></o:p> </td> <td style="padding: 0.75pt;"> Playstation<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 89.95<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123454<o:p></o:p> </td> <td style="padding: 0.75pt;"> Men's T-Shirt<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 32.50<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123455<o:p></o:p> </td> <td style="padding: 0.75pt;"> Blouse<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 34.97<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123456<o:p></o:p> </td> <td style="padding: 0.75pt;"> Electronica 2002<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123457<o:p></o:p> </td> <td style="padding: 0.75pt;"> Country Tunes<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21.55<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123458<o:p></o:p> </td> <td style="padding: 0.75pt;"> Watermelon<o:p></o:p> </td> <td style="padding: 0.75pt;"> Food<o:p></o:p> </td> <td style="padding: 0.75pt;"> 8.73<o:p></o:p> </td> </tr> </tbody></table> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL MIN<o:p></o:p></h1> </div> The MIN function is an aggregate function that finds the smallest value in a group. The products table that is displayed above has several products of various types. One use of MIN might be to find out the cheapest item in each group.<o:p></o:p> Just as we did in the <a href="http://www.tizag.com/mysqlTutorial/mysqlgroupby.php">Aggregate Introduction Lesson</a>, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. The column that will have the MIN function applied to it is, of course, price.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT type, MIN(price) FROM products GROUP BY type"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "The cheapest ". $row['type']. " is $" .$row['MIN(price)'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">The cheapest Clothing is $32.50 The cheapest Food is $8.73 The cheapest Music is $3.99 The cheapest Toy is $3.99 <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Aggregate Functions - MAX()<o:p></o:p></h1> </div> This lesson will teach you how to use the MAX() aggregate function . If you missed the <a href="http://www.tizag.com/mysqlTutorial/mysqlgroupby.php">Aggregate Introduction Lesson</a>, please check it out now, as it explains many concepts used in this lesson!<o:p></o:p> You can download the example the <a href="http://www.tizag.com/files/mysql/products.sql">products.sql</a> file from our website, which contains the SQL for the table below. <o:p></o:p> If you would like to use PHP/MySQL to create the table, then you will need to know how to <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">Create a MySQL Table</a> and <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">Insert a MySQL Row</a>.<o:p></o:p> Below is the MySQL table "products".<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">Products Table:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> id<o:p></o:p> </td> <td style="padding: 0.75pt;"> name<o:p></o:p> </td> <td style="padding: 0.75pt;"> type<o:p></o:p> </td> <td style="padding: 0.75pt;"> price<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123451<o:p></o:p> </td> <td style="padding: 0.75pt;"> Park's Great Hits<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 19.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123452<o:p></o:p> </td> <td style="padding: 0.75pt;"> Silly Puddy<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123453<o:p></o:p> </td> <td style="padding: 0.75pt;"> Playstation<o:p></o:p> </td> <td style="padding: 0.75pt;"> Toy<o:p></o:p> </td> <td style="padding: 0.75pt;"> 89.95<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123454<o:p></o:p> </td> <td style="padding: 0.75pt;"> Men's T-Shirt<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 32.50<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123455<o:p></o:p> </td> <td style="padding: 0.75pt;"> Blouse<o:p></o:p> </td> <td style="padding: 0.75pt;"> Clothing<o:p></o:p> </td> <td style="padding: 0.75pt;"> 34.97<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123456<o:p></o:p> </td> <td style="padding: 0.75pt;"> Electronica 2002<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 3.99<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123457<o:p></o:p> </td> <td style="padding: 0.75pt;"> Country Tunes<o:p></o:p> </td> <td style="padding: 0.75pt;"> Music<o:p></o:p> </td> <td style="padding: 0.75pt;"> 21.55<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> 123458<o:p></o:p> </td> <td style="padding: 0.75pt;"> Watermelon<o:p></o:p> </td> <td style="padding: 0.75pt;"> Food<o:p></o:p> </td> <td style="padding: 0.75pt;"> 8.73<o:p></o:p> </td> </tr> </tbody></table> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL MAX - Finding the Big One<o:p></o:p></h1> </div> MySQL's MAX aggregate function will find the largest value in a group. The "products" table that is displayed above has several products of various types. We could use the MAX function to find the most expensive item for each type of product.<o:p></o:p> Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. We will also be applying the aggregate function to the price column.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP and MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Make a MySQL Connection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT type, MAX(price) FROM products GROUP BY type"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// Print out result<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "The most expensive ". $row['type']. " is $" .$row['MAX(price)'];<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">The most expensive Clothing is $34.97 The most expensive Food is $8.73 The most expensive Music is $21.55 The most expensive Toy is $89.95 <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL - SQL Injection Prevention<o:p></o:p></h1> </div> If you have ever taken raw user input and inserted it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><script src="http://tizag.com/oads/www/delivery/ag.php" type="text/javascript"> </script>What is SQL Injection<o:p></o:p></h1> </div> SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>SQL Injection Example<o:p></o:p></h1> </div> Below is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">MySQL & PHP Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// a good user's name<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name = "timmy"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT * FROM customers WHERE username = '$name'";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<st1:city st="on"><st1:place st="on">Normal</st1:place></st1:city>: " . $query . " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// user input that uses SQL Injection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name_bad = "' OR 1'"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// our MySQL query builder, however, not a very safe one<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// display what the new query will look like, with injection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Injection: " . $query_bad;<o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Normal: SELECT * FROM customers WHERE username = 'timmy' Injection: SELECT * FROM customers WHERE username = '' OR 1'' <o:p></o:p> The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to timmy.<o:p></o:p> However, the injection attack has actually made our query behave differently than we intended. By using a single quote (') they have ended the string part of our MySQL query<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> username = ' ' <o:p></o:p> and then added on to our WHERE statement with an OR clause of 1 (always true). <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> username = ' ' OR 1 <o:p></o:p> This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>More Serious SQL Injection Attacks<o:p></o:p></h1> </div> Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse. For example an attacker could empty out a table by executing a DELETE statement.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">MySQL & PHP Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// our MySQL query builder really should check for injection<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// the new evil injection query would include a DELETE statement<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Injection: " . $query_evil;<o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' ' <o:p></o:p> If you were run this query, then the injected DELETE statement would completely empty your "customers" table. Now that you know this is a problem, how can you prevent it?<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Injection Prevention - mysql_real_escape_string()<o:p></o:p></h1> </div> Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string. <o:p></o:p> What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.<o:p></o:p> Lets try out this function on our two previous injection attacks and see how it works.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">MySQL & PHP Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//NOTE: you must be connected to the database to use this function!<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">// connect to MySQL<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name_bad = "' OR 1'"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name_bad = mysql_real_escape_string($name_bad);<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Escaped Bad Injection: " . $query_bad . " ";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$name_evil = mysql_real_escape_string($name_evil);<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "Escaped Evil Injection: " . $query_evil;<o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Display:<o:p></o:p></h2> <p class="MsoNormal" style="background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Escaped Bad Injection: SELECT * FROM customers WHERE username = '\' OR 1\'' Escaped Evil Injection: SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \'' <o:p></o:p> Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Bad: \' OR 1\' <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Evil: \'; DELETE FROM customers WHERE 1 or username = \' <o:p></o:p> And I don't think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites. You have no excuse not to use it after reading this lesson!<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Date - Formats<o:p></o:p></h1> </div> MySQL comes with several data types for storing a date in its database system: DATETIME, DATE, TIMESTAMP, and YEAR. This lesson will show you the proper formats of each type, show their related MySQL functions, and give an <a href="http://www.tizag.com/mysqlTutorial/mysqlinsert.php">INSERT example</a> of each.<o:p></o:p> These date types are chosen for a column when you <a href="http://www.tizag.com/mysqlTutorial/mysqltables.php">create a new table in MySQL</a>. Often the most difficult part of using dates in MySQL is to be sure the format of the date you are trying to store matches the format of your table's date column. If you haven't already, try to create a new MySQL table with the date types we mentioned above.<o:p></o:p> We have assembled a "date playground" MySQL table that can be used to follow along with this lesson. <a href="http://www.tizag.com/mysqlTutorial/dateplayground.sql">dateplayground.sql</a>. Also, the following acronyms are used in this lesson:<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Y - year segment <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> M - month segment <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> D - day segment <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> H - hour segment <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> m - minute segment, note the lower case <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> S - sec segment <o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Date - DATE<o:p></o:p></h1> </div> The default way to store a date in MySQL is with the type DATE. Below is the proper format of a DATE.<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> YYYY-MM-DD <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <st1:place st="on"><st1:placename st="on">Date</st1:placename> <st1:placetype st="on">Range</st1:placetype></st1:place>: 1000-01-01 to 9999-12-31 <o:p></o:p> If you try to enter a date in a format other than the Year-Month-Day format then it might work, but it won't be storing them as you expect.<o:p></o:p> To insert the current date into your table you can use MySQL's built-in function CURDATE() in your query. Below we have created 2 dates, one manually and one using CURDATE().<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//This assumes you have already created the 'dateplayground' table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//Connect to DB<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual = "INSERT INTO dateplayground (dp_name, dp_date)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('DATE: Manual Date', '2020-2-14')";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_auto = "INSERT INTO dateplayground (dp_name, dp_date)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('DATE: Auto CURDATE()', CURDATE() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_auto) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Date - YEAR<o:p></o:p></h1> </div> If you just need to store the year of an event, MySQL also has a date type just for that. YEAR's format is simply:<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> YYYY <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <st1:place st="on"><st1:placename st="on">Date</st1:placename> <st1:placetype st="on">Range</st1:placetype></st1:place>: 1901 to 2155 <o:p></o:p> It should be noted that the range of years that can be stored are from 1901 to 2155. If you need to store years outside that range then use DATE instead of YEAR.<o:p></o:p> Below we have created another manual and automatic example to show off YEAR's use. We have used CURDATE() again, even though it provides a lot more information than YEAR requires. All the date information, besides the year, is just ignored by YEAR. <o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual = "INSERT INTO dateplayground (dp_name, dp_year)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('YEAR: Manual Year', '2011')";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_auto = "INSERT INTO dateplayground (dp_name, dp_year)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('YEAR: Auto CURDATE()', CURDATE() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_auto) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Date - DATETIME<o:p></o:p></h1> </div> DATETIME actually stores both the current date and time, meaning it has the ability to store the year, month, day, hour, minute, and second inside it. DATETIME's format is:<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> YYYY-MM-DD HH:mm:SS <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <st1:place st="on"><st1:placename st="on">Date</st1:placename> <st1:placetype st="on">Range</st1:placetype></st1:place>: 1000-01-01 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time> to 9999-12-31 <st1:time minute="59" hour="23" st="on">23:59:59</st1:time> <o:p></o:p> The hyphen and the colon are the standard character to separate a date and time respectively, but MySQL allows for you to choose your own delimiters if you wish.<o:p></o:p> With DATETIME you can choose to store the date or the time and date together, but you cannot store just the time.<o:p></o:p> In our example below we have manually stored a complete DATETIME and also used three different MySQL functions: CURDATE(), CURTIME(), and NOW().<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual = "INSERT INTO dateplayground (dp_name, dp_datetime)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('DATETIME: Manual DateTime', '1776-7-4 <st1:time minute="13" hour="16" st="on">04:13:54</st1:time>')";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_autodate = "INSERT INTO dateplayground (dp_name, dp_datetime)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('DATETIME: Auto CURDATE()', CURDATE() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_autotime = "INSERT INTO dateplayground (dp_name, dp_datetime)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('DATETIME: Auto CURTIME()', CURTIME() )"; //This will fail<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_autonow = "INSERT INTO dateplayground (dp_name, dp_datetime)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('DATETIME: Auto NOW()', NOW() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_autodate) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_autotime) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_autonow) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Date - TIMESTAMP<o:p></o:p></h1> </div> TIMESTAMP is a format popularized by the *NIX operating systems that stores the amount of time that has passed since <st1:date year="1970" day="1" month="1" st="on">January 1, 1970</st1:date>. If you want more versatility than this date type provides, remember to try DATETIME.<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> YYYY-MM-DD HH:mm:SS <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <st1:place st="on"><st1:placename st="on">Date</st1:placename> <st1:placetype st="on">Range</st1:placetype></st1:place>: 1970-01-01 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time> to 2037-12-31 <st1:time minute="59" hour="23" st="on">23:59:59</st1:time> <o:p></o:p> The big difference between DATETIME and TIMESTAMP is the date ranges that can be stored. Below we have purposely entered an erroneous date, manually, so you can see what happens when you enter a date that is outside the boundaries of a this type.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//This will fail<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual = "INSERT INTO dateplayground (dp_name, dp_timestamp)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('TIMESTAMP: Manual Timestamp', '1776-7-4 <st1:time minute="13" hour="16" st="on">04:13:54</st1:time>')"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_autodate = "INSERT INTO dateplayground (dp_name, dp_timestamp)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('TIMESTAMP: Auto CURDATE()', CURDATE() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//This will fail<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_autotime = "INSERT INTO dateplayground (dp_name, dp_timestamp)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('TIMESTAMP: Auto CURTIME()', CURTIME() )"; <o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_autonow = "INSERT INTO dateplayground (dp_name, dp_timestamp)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('TIMESTAMP: Auto NOW()', NOW() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_autodate) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_autotime) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_autonow) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Viewing dateplayground in PHP<o:p></o:p></h1> </div> Below is a quick script that will spit out the MySQL table dateplayground in HTML.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT * FROM dateplayground";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<table border="'1'"><tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">for($i = 0; $i <></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<th>".mysql_field_name($result, $i)."</th>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> for($i = 0; $i <></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<td>". $row[$i] ."</td>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> }<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</table>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Finished dateplayground.sql Display:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> dp_name<o:p></o:p> </td> <td style="padding: 0.75pt;"> dp_year<o:p></o:p> </td> <td style="padding: 0.75pt;"> dp_date<o:p></o:p> </td> <td style="padding: 0.75pt;"> dp_datetime<o:p></o:p> </td> <td style="padding: 0.75pt;"> dp_timestamp<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> DATE: Manual Date<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 2020-02-14<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> DATE: Auto CURDATE()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 2006-09-19<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> YEAR: Manual Year<o:p></o:p> </td> <td style="padding: 0.75pt;"> 2011<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> YEAR: Auto CURDATE()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 2006<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> DATETIME: Manual DateTime<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 1776-07-04 <st1:time minute="13" hour="16" st="on">04:13:54</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> DATETIME: Auto CURDATE()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 2006-09-19 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> DATETIME: Auto CURTIME()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> DATETIME: Auto NOW()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 2006-09-19 <st1:time minute="56" hour="16" st="on">16:56:56</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIMESTAMP: Manual Timestamp<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIMESTAMP: Auto CURDATE()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 2006-09-19 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIMESTAMP: Auto CURTIME()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIMESTAMP: Auto NOW()<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00<o:p></o:p> </td> <td style="padding: 0.75pt;"> 0000-00-00 <st1:time minute="0" hour="0" st="on">00:00:00</st1:time><o:p></o:p> </td> <td style="padding: 0.75pt;"> 2006-09-19 <st1:time minute="56" hour="16" st="on">16:56:56</st1:time><o:p></o:p> </td> </tr> </tbody></table> Notice that the rows DATETIME: Auto CURTIME(), TIMESTAMP: Manual Timestamp, and TIMESTAMP: Auto CURTIME() have all zeros. This is because they were the INSERTs that were erroneous. When you enter dates that are out of the range or in the wrong format for a given date type, MySQL will often just enter in the default value of all zeros.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Time - Formats<o:p></o:p></h1> </div> There are three different types of time data types in MySQL: TIME, DATETIME, and TIMESTAMP. If you would like to learn more about DATETIME and TIMESTAMP, then check out our <a href="http://www.tizag.com/mysqlTutorial/mysql-date.php">MySQL Date</a> section, as we've covered them there. This lesson will just be covering the basics of using TIME.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Time - TIME<o:p></o:p></h1> </div> First you need to create a MySQL table with a TIME type. We have one already created if you want to use it: <a href="http://www.tizag.com/mysqlTutorial/timeplayground.sql">timeplayground.sql</a>.<o:p></o:p> The TIME data type can be used to store actual times as well as the amount of time between two points in time (like the time between now and the weekend) that may sometimes be larger than 23 hours. H - Hour; M - Minute; S - Second.<o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Standard format: HH:MM:SS <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> Extended hour format: HHH:MM:SS <o:p></o:p> <img src="PicExportError" alt="*" width="10" height="10" /> <st1:place st="on"><st1:placename st="on">Time</st1:placename> <st1:placetype st="on">Range</st1:placetype></st1:place>: -838:59:50 to 838:59:59 <o:p></o:p> When manually entering a time into MySQL it is highly recommended that you use the exact format show above. MySQL allows for many different ways to enter a time, but they don't always behave as you would expect. Using the standard/extended format we have shown above will help you avoid annoying problems.<o:p></o:p> Below we have entered 3 manual times into MySQL. The first is done in the recommended format, the second is a shorthand version of the first and the final example is outside the allowed time range.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//This assumes you have already created the 'dateplayground' table<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">//Connect to DB<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual1 = "INSERT INTO timeplayground (dp_name, dp_time)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('TIME: Manual Time', '<st1:time minute="10" hour="12" st="on">12:10:00</st1:time>')"; //perfectly done<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual2 = "INSERT INTO timeplayground (dp_name, dp_time)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('TIME: Manual Time', '1210')"; // will this shorthand work?<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_manual3 = "INSERT INTO timeplayground (dp_name, dp_time)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUES ('TIME: Manual Time', '978:31:12')"; //how about this?<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual1) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual2) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_manual3) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Time - NOW()<o:p></o:p></h1> </div> To get the current time, use MySQL's built in function NOW(). NOW() contains both the date and time information, but MySQL is smart enough to just use the data needed for TIME.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query_auto = "INSERT INTO timeplayground (dp_name, dp_time)<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> VALUE ('TIME: Auto NOW()', NOW() )";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">mysql_query($query_auto) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL timeplayground.sql Displayed<o:p></o:p></h1> </div> Below is a small PHP script to spit out a rough version of our timeplayground.sql table.<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">PHP & MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><?php<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$query = "SELECT * FROM timeplayground";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">$result = mysql_query($query) or die(mysql_error());<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "<table border="'1'"><tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">for($i = 0; $i <></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<th>".mysql_field_name($result, $i)."</th>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">while($row = mysql_fetch_array($result)){<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> for($i = 0; $i <></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "<td>". $row[$i] ."</td>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> }<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> echo "</tr>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">}<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><o:p> </o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">echo "</table>";<o:p></o:p></pre><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">?><o:p></o:p></pre></div> <h2 style="margin: 7.5pt 0in 0.0001pt;">Finished timeplayground.sql Display:<o:p></o:p></h2> <table class="MsoNormalTable" style="" border="1" cellpadding="0"> <tbody><tr style=""> <td style="padding: 0.75pt;"> dp_name<o:p></o:p> </td> <td style="padding: 0.75pt;"> dp_time<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIME: Manual Time<o:p></o:p> </td> <td style="padding: 0.75pt;"> <st1:time minute="10" hour="12" st="on">12:10:00</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIME: Manual Time<o:p></o:p> </td> <td style="padding: 0.75pt;"> <st1:time minute="12" hour="0" st="on">00:12:10</st1:time><o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIME: Manual Time<o:p></o:p> </td> <td style="padding: 0.75pt;"> 838:59:59<o:p></o:p> </td> </tr> <tr style=""> <td style="padding: 0.75pt;"> TIME: Auto NOW()<o:p></o:p> </td> <td style="padding: 0.75pt;"> <st1:time minute="30" hour="14" st="on">14:30:36</st1:time><o:p></o:p> </td> </tr> </tbody></table> Our first manual time was handled just fine, but our second one did not. MySQL interpreted 1210 as MM:SS instead of HH:MM as we assumed. This is why it's best to use the formats we've described at the beginning.<o:p></o:p> The third manual entry was changed from 978:31:12 to 838:59:59, so that it would be within TIME's range.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1><script type="text/javascript">  </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>MySQL Index - Overclock Your Tables<o:p></o:p></h1> </div> Hardware enthusiasts have been overclocking their PCs for years now, trying to push the limits of their hardware for maximum performance. Sometimes they are successful and their applications run speedy fast, while other times they push a little too hard and end up damaging the computer!<o:p></o:p> Although it isn't quite as extreme, indexes in MySQL can increase the speed of your MySQL queries to squeeze a bit more performance out of your database.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>MySQL Index - Speed and Extra Overhead<o:p></o:p></h1> </div> Indexes are created on a per column basis. If you have a table with the columns: name, age, birthday and employeeID and want to create an index to speed up how long it takes to find employeeID values in your queries, then you would need to create an index for employeeID. When you create this index, MySQL will build a lookup index where employeeID specific queries can be run quickly. However, the name, age and birthday queries would not be any faster.<o:p></o:p> Indexes are something extra that you can enable on your MySQL tables to increase performance,cbut they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.<o:p></o:p> <div style="border-style: none none dotted; border-color: -moz-use-text-color -moz-use-text-color rgb(10, 67, 105); border-width: medium medium 1pt; padding: 0in;"> <h1>Creating a MySQL Index - New Table<o:p></o:p></h1> </div> If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).<o:p></o:p> <h2 style="margin: 7.5pt 0in 0.0001pt;">MySQL Code:<o:p></o:p></h2> <div style="border: 1.5pt solid rgb(0, 59, 98); padding: 4pt 4pt 15pt; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><pre style="border: medium none ; padding: 0in; background: rgb(181, 207, 224) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">CREATE TABLE employee_records (<o:p></o:p></pre><pre style="border: