1 / 68

PHP MySQL

PHP MySQL. The tutorials…. MySQL. MySql  is a powerful database. It's very good and free of charge. Many developers in the world selected mysql and php for developing their website. . Top Ten Reasons to Use MySQL. Scalability and Flexibility High Performance

thao
Download Presentation

PHP MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PHP MySQL The tutorials…

  2. MySQL • MySql is a powerful database. • It's very good and free of charge. • Many developers in the world selected mysql and php for developing their website. 

  3. Top Ten Reasons to Use MySQL • Scalability and Flexibility • High Performance • High Availability •  Robust Transactional Support • Web and Data Warehouse Strengths • Strong Data Protection • Comprehensive Application Development • Management Ease • Open Source • Freedom and 24 x 7 Support • Lowest Total Cost of Ownership

  4. Create database, table and managing MySQL database using phpMyAdmin • phpMyAdmin is a tool for managing MySQL database and free of charge, it's a web base tool.  • If you install Wamp, Xammp or EasyPHP, phpMyAdmin is included in the package. • If you manually installed Apache, PHP and MySQL, you can download phpMyAdmin at their officail site.

  5. Create database, table and managing MySQL database using phpMyAdmin • phpMyAdmin

  6. Create database, table and managing MySQL database using phpMyAdmin • Create database using phpMyAdmin • To create new database use this form, type database name then click "Create" button. In this example I create database name "test_create_DB".

  7. Create database, table and managing MySQL database using phpMyAdmin • Create table • After created database. this form'll be display. Enter the name of table and number of field. In this example, I create table name "web_members" for store my web members and it have 4 fields (id, name, lastname, email)

  8. Create database, table and managing MySQL database using phpMyAdmin • Result after created table on database

  9. Create database, table and managing MySQL database using phpMyAdmin • Create table by runing SQL query • ou can create table by run SQL query for example, put MySqlcode in the form and click "Go“or browse from text file (read export database for creating text file (.sql))

  10. Create database, table and managing MySQL database using phpMyAdmin • MySQL code CREATE TABLE `web_members` (`id` int(4) NOT NULL auto_increment,`name` varchar(65) NOT NULL default '',`lastname` varchar(65) NOT NULL default '',`email` varchar(65) NOT NULL default '',PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=1 ;

  11. Create database, table and managing MySQL database using phpMyAdmin • Export database

  12. Create database, table and managing MySQL database using phpMyAdmin • In tab Export. You can export your database in many format like- SQL (text file .sql)- Latex- Microsoft Exel 2000- Microsoft Word 2000 - CVS for MS Exel- CVS - XML

  13. Create database, table and managing MySQL database using phpMyAdmin • Steps • Select table you want to export or select all table. • Select file format • Select save as file(if you want to save as a file if not select it'll show you only Sqlquery) • Select compression  • None • zipped (.zip)  • gzipped(.gzip)

  14. Connect to MySQL database

  15. Syntax mysql_connect("host", "username", "password")or die("cannot connect to server"); • Overview • host="localhost" you don't have to change it. When it's on your computer or server it still be localhostUsername = database usernamePassword = database passwordDatabase = database name

  16. Example $host="localhost";$username=“root";$password="1234";$db_name="test"; mysql_connect("$host", "$username", "$password")or die("cannot connect to server");mysql_select_db("$db_name")or die("cannot select db");ormysql_connect("localhost", “root", "1234")or die("cannot connect to server");mysql_select_db("test")or die("cannot select db");

  17. Creating file config.php $host="localhost";$username=“root";$password="1234";$db_name="test";mysql_connect("$host", "$username", "$password")or die("cannot connect to server");mysql_select_db("$db_name")or die("cannot select db"); 

  18. Creating file config.php • Save this code as file "config.php", connect.php or whatever you want. • When you want to use this code include it to your main phpfile • Example<?phpinclude("config.php");$tbl_name="member";$sql="SELECT * FROM $tbl_name";$result=mysql_query($sql);...?>

  19. Insert data into mysql

  20. Syntax "INSERT INTO table_name(column_name1, column_name2)VALUES('value1, 'value2')" ;

  21. Overview • In this tutorial, create 2 files1. insert.php2. insert_ac.phpSteps1. Create table "test_mysql" in database "test".2. Create file insert.php.3. Create file insert_ac.php.

  22. STEP1: Create table "test_mysql" CREATE TABLE `test_mysql` (`id` int(4) NOT NULL auto_increment,`name` varchar(65) NOT NULL default '',`lastname` varchar(65) NOT NULL default '',`email` varchar(65) NOT NULL default '',PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=0 ;

  23. STEP2: Create file insert.php • View In Browser

  24. STEP2: Create file insert.php <table width="300" border="0" align="center" cellpadding="0" cellspacing="1"><tr><td><form name="form1" method="post" action="insert_ac.php"><table width="100%" border="0" cellspacing="1" cellpadding="3"><tr><td colspan="3"><strong>Insert Data Into mySQL Database </strong></td></tr><tr><td width="71">Name</td><td width="6">:</td><td width="301"><input name="name" type="text" id="name"></td></tr><tr><td>Lastname</td><td>:</td><td><input name="lastname" type="text" id="lastname"></td></tr><tr><td>Email</td><td>:</td><td><input name="email" type="text" id="email"></td></tr><tr><td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td></tr></table></form></td></tr></table>

  25. $datetime= date('Y-m-d H:i:s'); $sql="INSERT INTO $tbl_name(name, lastname, email, datetime)VALUES('$name', '$lastname', '$email','$datetime')";$result=mysql_query($sql);

  26. STEP3: Create file insert_ac.php • Diagram

  27. STEP3: Create file insert_ac.php <?php$host="localhost"; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name="test"; // Database name $tbl_name="test_mysql"; // Table name // Connect to server and select database.mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB");// Get values from form $name=$_POST['name'];$lastname=$_POST['lastname'];$email=$_POST['email'];// Insert data into mysql$sql="INSERT INTO $tbl_name(name, lastname, email)VALUES('$name', '$lastname', '$email')";$result=mysql_query($sql);// if successfully insert data into database, displays message "Successful". if($result){echo "Successful";echo "<BR>";echo "<a href='insert.php'>Back to main page</a>";}else {echo "ERROR";}?> <?php// close connection mysql_close();?>

  28. Another way connect to server and select database • Use confing.php to connect to server and select database. include("config.php");

  29. Select data From mysql

  30. Syntax • When you need data from your mysql database to show in your web page, you need to select database, table and what's row you want to pull its data first. // Select all columns from all rows."SELECT * FROM table_name"; or// Select some column from all rows."SELECT column_name1, column_name2 FROM table_name";or// Select all coulumns from one row. "SELECT * FROM table_name WHERE column_name=' value in column '"; 

  31. Overview • In this tutorial, we'll create only 1 file.1. select.php Steps1. Create table "test_mysql" in database "test".2. Create file select.php.3. test it! 

  32. Overview • If you don't want looping rows in mysql, replace while($rows=mysql_fetch_array($result)){........<?php}mysql_close();?>replace with this$rows=mysql_fetch_array($result);.........<?phpmysql_close();?>

  33. STEP1: Insert data into table "test_mysql" • In this step, you have to insert data for testing our code. INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', 'bb5@phpeasystep.com');INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', 'jame@somewhere.com');INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mark@phpeasystep.com');

  34. STEP2: Create file - select.php

  35. STEP2: Create file - select.php <?php$host="localhost"; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name="test"; // Database name $tbl_name="test_mysql"; // Table name // Connect to server and select database.mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // Retrieve data from database $sql="SELECT * FROM $tbl_name";$result=mysql_query($sql);?> <table width="400" border="1" cellspacing="0" cellpadding="3">  <?php // Start looping rows in mysql database.while($rows=mysql_fetch_array($result)){?><tr><td width="10%"><? echo $rows['id']; ?></td><td width="30%"><? echo $rows['name']; ?></td><td width="30%"><? echo $rows['lastname']; ?></td><td width="30%"><? echo $rows['email']; ?></td></tr><?php// close while loop } </table> ?> <?php// close MySQL connection mysql_close();?>

  36. STEP3: Run the Code

  37. Update data in mysql

  38. Syntax • Update/Edit data from mysql database, can do it easily. "UPDATE table_name SET column_name1=' value', column_name2=' value' WHERE column_name=' value' ";

  39. Overview • In this tutorial, we create 3 PHP files for testing our code.1. list_records.php2. update.php3. update_ac.phpSteps1. Create file list_records.php 2. Create file update.php3. Create file update_ac.php

  40. STEP1: Create file - list_records.php

  41. STEP2: Create file - update.php

  42. STEP3: Create file - update_ac.php

  43. Delete data From mysql

  44. Syntax • Delete data from your mysql database. "DELETE FROM table_name WHERE column_name=' value' ";

  45. Overview • Overview In this tutorial create 2 files1. delete.php2. delete_ac.phpStep1. Create file delete.php.2. Create file delete_ac.php.

  46. STEP1: Create file - delete.php

  47. STEP2: Create file delete_ac.php

  48. Order MySQLresults

  49. Overview • You can order MySQL results using "ORDER BY"1. ORDER BY column_name ASC 2. ORDER BY column_name DESC3. ORDER BY RAND(). 1. ORDER BY column_name ASC is order by ascending.2. ORDER BY column_name DESC is order results by descending.3. ORDE BY RAND() is order results by random.If you're not set this up the default is order results by ascending.

More Related