 |
|
 |
            |
| FORUMS | TUTORIALS | MAGAZINE | TRAINING | VIDEOS - REELS | PODCASTS | EVENTS | SERVICES | NEWSLETTER | NEWS | BLOGS |
|
Start Using MySQL with PHP
Start Using MySQL with PHP - MySQL Tutorial with PHP and PHPMyAdmin This tutorial is designed to give the beginning web designer some basic knowledge and tools to start using MySQL with PHP. This tutorial includes a PHPMyAdmin tutorial, a simple Content Management System for your MySQL table, and file templates to start displaying MySQL data on web pages.
Background:MySQL is used by the majority of web developers to store data online. Blogs, forums, images, movies, search engine information, user account information, and even entire web sites are stored, and accessed using MySQL.Installation: For information on installing MySQL onto a web server go to http://dev.mysql.com/doc/refman/5.0/en/installation-overview.html Database and User Setup: The main structure of the data in MySQL starts with databases. Databases are the main storage bins for the data inside MySQL. Users are granted permissions to a database that allows them to read, write, or delete from the database. Inside each database is a given number of tables that hold the data for that database. Creating databases and assigning users to it is done only by users with higher level permissions. If you are using a 3rd party host such as Network Solutions or GoDaddy, setting up your database and users for MySQL is done through your hosting control panel. Call your hosting company for specific details. Brief PHPMyAdmin Tutorial:Once you have a MySQL database and user account setup you are generally given a link or URL to a PHPMyAdmin control panel. This is a GUI that allows you to manage your MySQL database. There are other GUIs available for MySQL but PHPMyAdmin is by far the most popular.When you go to the URL provided to you, you will be asked to put in your user name and password. This will be your MySQL user name, and password which may differ from other passwords though some service providers make them the same as your main hosting account user name and password. 
Once you are logged in, a PHPMyAdmin screen appears as shown below. This screen may differ depending on the version of PHPMyAdmin that is running. As you see by the image permission to create new databases is not generally set so you will need to select from the list of available databases to begin to create and manage your MySQL tables. 
To begin adding data into your database you must create a table. For best results your table name should be all lowercase with no spaces. Use and under-score "_" to separate words. e.g. my_table_name. For this example we will create a table with 5 fields called my_new_table inside the database called test. 
When you click Go you will be brought to the Create a new table page with a form you must fill out to create the new table. Each field has options applied to it on this page that will define the field type, size, name, and more:
When you click Save it should create the table successfully and display the code that PHPMyAdmin generated to perform the requested operation. By looking at the code you start to get the basic understanding of a MySQL query syntax. 
Once you have a table created you have additional options given to you in the top navigation. To follow is an overview of what each page does when you click on the new tabs.
A Basic PHP Database Management System (DBMS):A database management system is a system such as PHPMyAdmin that allows you to control your database. Many times it is nice to have a simple DBMS for a client who wishes to control elements on their web page without having to call on the web designer for each change. Rather than giving them access to the entire PHPMyAdmin a basic DBMS can be used. Here is a functional, simple PHP DBMS that you can download and use. Simply view the source code, modify the variables at the top and upload it to your web server. I suggest putting a bit of security around the script so someone doesn't stumble across the page and is able to edit your database. Technically it is only a table management system as it only edits a single table but you can duplicate the file and use it for every table in your database. This is also called a Content Management System (CMS).Displaying MySQL Data in a Web Page:Displaying the Data in your MySQL database is of course the end result we want. Below are two different yet similar functions to show multiple rows or a single row. Showing multiple rows would show a list of items or entries while a single row would be showing one item, or entry. This method can be used and is used in infinite ways across the internet.For either of these functions to work you must have a MySQL database and table setup with rows in it and know the user information. Establishing the proper connection to the MySQL database is required for both functions. The initial connection is done by using the PHP function mysql_connect which links your web page to MySQL under your user authentication. Next the database is selected that holds the table we will be asking for this is done using the PHP function mysql_select_db which sets the active database for the connection. <? $databasename='database_name'; // Name of the database $tablename='table_name'; // Name of the table $mysqladd='localhost'; // Address to the MySQL Server - Usually localhost or an IP address $mysqluser='MySQL_username'; // Your MySQL UserName $mysqlpass='MySQL_password'; // Your MySQL PasswordNext is the querying of the database and the building of the $results array from the MySQL $result. Built from these lines below is an array called $results that contains the resulting row(s) from the mysql_query that was performed. Method 1 Multiple Results Possible: This will return all rows and fields from the table. Download the PHP file here. <?
$results=array();
$sql="SELECT * FROM `".$tablename."`";
$result = mysql_query($sql, $link) or die('Error: ' . mysql_error());
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);
?>
Method 2 Single Result:This will return the row that has the requested id. To request the id you would use a URL like: http://www.mydomain.com/single.php?id=5. Download the PHP file here. <?
$results=array();
$sql="SELECT * FROM `".$tablename."` WHERE `id`=".$_REQUEST[id]." LIMIT 1";
$result = mysql_query($sql, $link) or die('Error: ' . mysql_error());
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);
?>
MySQL SELECT:The SELECT statement is used to select data from a database. The * indicates that you want all fields. To only return a single field from your query you replace the * with a field name. "SELECT field_name_1 FROM table_name"Additionally you can comma separate field names to only return specific fields from your query. SELECT field_name_1,field_name_2,field_name_3 FROM table_nameOnly selecting fields needed for your script can help keep the memory used by your server lower. Otherwise PHP must remember the entire row which could be must larger than the fields you really need. If you want to filter your results to only return certain rows you can use the WHERE statement. SELECT * FROM table_name WHERE field_name1='what ever'This statement would only return rows where 'what ever' is the value in field_name1. You can do more complex WHERE filters by adding AND / OR operators in the query SELECT * FROM table_name WHERE field_name1='what ever' OR id < '5'To order your results in a specific order you use the ORDER BY statement - ASC or DESC can be used to choose a direction for the sort. SELECT * FROM table_name ORDER BY field_name1 ASCA larger table with thousands of rows would need a limit on the results so the results are not too large. To limit to a certain count you only need one value but if you wish to start at a given row and then limit you can give two values comma separated. LIMIT 5,20 would start at row 6 and give 20 results, LIMIT 0,10 starts at row 1 and gives 10 results. SELECT * FROM `table_name` WHERE `field_name1`='what ever' ORDER BY `field_name` DESC LIMIT 50Note: The tilde characters are added around field and table names in case there are spaces in the name. The single quotes are added around values in case their are spaces in the value. Capitalization of operators such as 'select', 'desc', or 'where' is not important but is used for the differentiation of the text. Displaying the $results to the Web Browser: Method 1 Multiple Results Possible: The code below displays the results in an ordered list with field_name1 as a link to view the single item in a separate page. <?
if(count($results)){ //IF THERE ARE RESULTS
//DISPLAY THE RESULTS
foreach($results as $r){
echo('<li><a href="single.php?id='.$r[id].'">'.$r[field_name1].'</a>');
echo(' -- '.$r[field_name2].' -- '.$r[field_name3].'<br><br>');
}
}else{
echo('Sorry - no results found');
}
?>
Method 2 Single Result:The single results are the same as the multiple results except it only contains one row that are in the array $results[0] as the first and only row. Multiple results also contain other potential arrays such as $results[1] and $results[2]. $results[0][field_name] accesses the field_name value for the first row in the results. <?
if(count($results)){
echo('<a href="single.php?id='.$results[0][id].'">'.$results[0][field_name1].'</a>');
echo('<br> '.$results[0][field_name2].' <br> '.$results[0][field_name3]);
}else{
echo('Sorry - no result found');
}
?>
I hope you've learned a few things in this tutorial and can use these methods / tools in your future projects. Feel free to leave feedback, questions, or comments below. If you'd like any sections expanded let me know.
Comments
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
FORUMS • TUTORIALS • MAGAZINE • TRAINING • VIDEOS - REELS • PODCASTS • EVENTS • SERVICES • NEWSLETTER • NEWS • BLOGS
© CreativeCOW.net All rights are reserved.
[Top]
