PHP Knowledge Base: PHP Database Connection Reference

Create and close a Connection to a MySQL DB

mysql_connect(server,user,pwd,newlink,clientflag)
http://www.w3schools.com/php/func_mysql_connect.asp
-----------------------------------------
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// some code
mysql_close($con);
?>
-----------------------------------------

Create a Database
-----------------------------------------

if (mysql_query("CREATE DATABASE my_db",$con))
{
echo "Database created";
}
else
{
echo "Error creating database: " . mysql_error();
}
-----------------------------------------
Selecting a database
mysql_select_db("my_db", $con);

Create a Table
-----------------------------------------

//A database must be selected before a table can be created.
mysql_select_db("my_db", $con);
// Create table
$sql = "CREATE TABLE Persons
(
personID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)";

// Execute query
mysql_query($sql,$con);
-----------------------------------------
List of MySQL Data types :http://www.w3schools.com/sql/sql_datatypes.asp

Insert Into a Database Table
-----------------------------------------
$con = mysql_connect("localhost","peter","abc123");

if (!$con)
{die('Could not connect: ' . mysql_error());}

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin', '35')");
mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire', '33')");

mysql_close($con);
-----------------------------------------
Insert Data from a form
HTML Form
-----------------------------------------
<html>
<body>
<form action="insert.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>
</body>
</html>
-----------------------------------------
insert.php script
-----------------------------------------
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{ die('Could not connect: ' . mysql_error()); }

mysql_select_db("my_db", $con);

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

if (!mysql_query($sql,$con))
{ die('Error: ' . mysql_error()); }
echo "1 record added";

mysql_close($con)
?>
-----------------------------------------
Select Data from a table
-----------------------------------------
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{die('Could not connect: ' . mysql_error());}

mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM Persons");

echo "<table border='1'>
<tr>
<th>Firstname</th>

<th>Lastname</th>

  </tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>
-----------------------------------------

Select where...

-----------------------------------------
$result = mysql_query("SELECT * FROM Persons WHERE FirstName='Peter'");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br />";
}
-----------------------------------------

ORDER BY
-----------------------------------------

order by syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

Order by multiple columns.
SELECT column_name(s)
FROM table_name
ORDER BY column1, column2-----------------------------------------
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM Persons ORDER BY age");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
  echo " " . $row['LastName'];
  echo " " . $row['Age'];
  echo "<br />";
}
-----------------------------------------
Update Data
Syntax
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!-----------------------------------------
mysql_select_db("my_db", $con);
mysql_query("UPDATE Persons SET Age = '36'WHERE FirstName = 'Peter' AND LastName = 'Griffin'");
-----------------------------------------

Delete Data in the database
Syntax
DELETE FROM table_name
WHERE some_column = some_value
The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
-----------------------------------------
mysql_select_db("my_db", $con);
mysql_query("DELETE FROM Persons WHERE LastName='Griffin'");
-----------------------------------------

PHP Knowledge Base

Thursday, November 17, 2011

PHP Database Connection Reference

No comments:

Post a Comment