PHP Knowledge Base

Thursday, November 17, 2011

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'");
-----------------------------------------

Email (PHP)

http://www.w3schools.com/php/func_mail_mail.asp
mail(to,subject,message,headers,parameters)

Simple Mail
<?php
$to = "someone@example.com";
$subject = "Test mail";
$message = "Hello! This is a simple email message.";
$from = "someonelse@example.com";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
echo "Mail Sent.";
?>

Mail Form
<html>
<body>
<?php
if (isset($_REQUEST['email']))
//if "email" is filled out, send email
{
//send email
$email = $_REQUEST['email'] ;
$subject = $_REQUEST['subject'] ;
$message = $_REQUEST['message'] ;
mail("someone@example.com", "$subject",
$message, "From:" . $email);
echo "Thank you for using our mail form";
}
else
//if "email" is not filled out, display the form
{
echo "<form method='post' action='mailform.php'>
Email: <input name='email' type='text' /><br />
Subject: <input name='subject' type='text' /><br />
Message:<br />
<textarea name='message' rows='15' cols='40'>
</textarea><br />
<input type='submit' />
</form>";
}
?>
</body>
</html>

Session Variables

A PHP session variable is used to store information about, or change settings for a user session. Session variables hold information about one single user, and are available to all pages in one application.

When you are working with an application, you open it, do some changes and then you close it. This is much like a Session. The computer knows who you are. It knows when you start the application and when you end. But on the internet there is one problem: the web server does not know who you are and what you do because the HTTP address doesn't maintain state.

A PHP session solves this problem by allowing you to store user information on the server for later use (i.e. username, shopping items, etc). However, session information is temporary and will be deleted after the user has left the website. If you need a permanent storage you may want to store the data in a database.

Sessions work by creating a unique id (UID) for each visitor and store variables based on this UID. The UID is either stored in a cookie or is propagated in the URL.

Start a PHP Session
the session start function must appear before the <html> tag:

<? php session_start (); ?>
<html>
......
</html>

Storing a session variable
<?php
session_start();
// check if session variable views is already set
if(isset($_SESSION['views']))
// yes update the value by 1
$_SESSION['views']=$_SESSION['views']+1;
else
// no set a new session variable views to 1
$_SESSION['views']=1;
echo "Views=". $_SESSION['views'];
?>

Free the session variable
unset($_SESSION['views']);

completely destroy session

<?php

session_destroy();

Sunday, November 13, 2011

Cookies

A cookie is often used to identify a user.
The setcookie() function must appear BEFORE the <html> tag.
A cookie is a small file that the server embeds on the user's computer.
Each time the same computer requests a page with a browser, it will send the cookie too.
With PHP, you can both create and retrieve cookie values.

SET Cookie:
setcookie(name, value, expire, path, domain);
------------------------------
<?php
setcookie ("user", "Alex Parter", time()+3600); //cookie expire after an hour
?>
<html>
.....
------------------------------
<?php
$expire=time()+60*60*24*30; //set expire time to a month
setcookie("user", "Alex Porter", $expire);
?>
<html>
.....
------------------------------

Get Cookie
------------------------------
<?php
//check if cookie has been set
if (isset($_COOKIE["user"]))
// get the cookie and print it
echo "Welcome " . $_COOKIE["user"] . "!<br />";
else
echo "Welcome guest!<br />";
?>
------------------------------
Print all cookies
print_r($_COOKIE);
------------------------------

Delete a Cookie (set the expiration date of cookie in the past)
<?php
// set the expiration date to one hour ago
setcookie("user", "", time()-3600);
?>

Thursday, November 10, 2011

Php Upload a file

HTML Upload form

<form action="upload_file.php" method="post"
enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" />
<br />
<input type="submit" name="submit" value="Submit" />
</form>

The enctype attribute of the <form> tag specifies which content-type to use when submitting the form. "multipart/form-data" is used when a form requires binary data, like the contents of a file, to be uploaded
The type="file" attribute of the <input> tag specifies that the input should be processed as a file. For example, when viewed in a browser, there will be a browse-button next to the input field

By using the global PHP $_FILES array you can upload files from a client computer to the remote server.

$_FILES["file"]["name"] - the name of the uploaded file
$_FILES["file"]["type"] - the type of the uploaded file
$_FILES["file"]["size"] - the size in bytes of the uploaded file
$_FILES["file"]["tmp_name"] - the name of the temporary copy of the file stored on the server
$_FILES["file"]["error"] - the error code resulting from the file upload

------------------------------------------------------

/*Check for the file type to upload gif/jpeg/pjpeg and size less than 20 MB */
<?php
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/pjpeg"))
&& ($_FILES["file"]["size"] < 20000))
{
/*check for errors finding the file or read permissions*/
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}

else
{
echo "Upload: " . $_FILES["file"]["name"] . "<br />";   echo "Type: " . $_FILES["file"]["type"] . "<br />";   echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";   echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";
  /*check if the file already exists in the upload directory*/   if (file_exists("upload/" . $_FILES["file"]["name"]))
{       echo $_FILES["file"]["name"] . " already exists. ";

}

else { /*move the uploaded file to upload directory*/

move_uploaded_file($_FILES["file"]["tmp_name"],"upload/". $_FILES["file"]["name"]);

echo "Stored in: " . "upload/" . $_FILES["file"]["name"];

}
}
}
else
{
echo "Invalid file";
}
?>

File Handling

PHP file functions reference : http://www.w3schools.com/php/php_ref_filesystem.asp

Open a file
$file=fopen("welcome.txt","r") or exit("Unable to open file!");

Modes	Description
r	Read only. Starts at the beginning of the file
r+	Read/Write. Starts at the beginning of the file
w	Write only. Opens and clears the contents of file; or creates a new file if it doesn't exist
w+	Read/Write. Opens and clears the contents of file; or creates a new file if it doesn't exist
a	Append. Opens and writes to the end of the file or creates a new file if it doesn't exist
a+	Read/Append. Preserves file content by writing to the end of the file
x	Write only. Creates a new file. Returns FALSE and an error if file already exists
x+	Read/Write. Creates a new file. Returns FALSE and an error if file already exists

If the fopen() function is unable to open the specified file, it returns 0 (false).

Closing a File

fclose($file);

Check for End of the file

if (feof($file)) echo "End of file";

You cannot read from files opened in w, a, and x mode!

fgets() : Read Line by Line

$file = fopen("welcome.txt", "r") or exit("Unable to open file!");

//Output a line of the file until the end is reached
while(!feof($file))
  {
  echo fgets($file). "<br />";
  }
fclose($file);

After a call to this function the file pointer has moved to the next line.

fgestc() : Read character by character

$file=fopen("welcome.txt","r") or exit("Unable to open file!");
while (!feof($file))
  {
  echo fgetc($file);
  }
fclose($file);

After a call to this function the file pointer has moved to the next character.

Include another PHP files

You can insert the content of one PHP file into another PHP file before the server executes it, with the include() or require() function.

The two functions are identical in every way, except how they handle errors:

include() generates a warning, but the script will continue execution
require() generates a fatal error, and the script will stop

These two functions are used to create functions, headers, footers, or elements that will be reused on multiple pages.

Include
<?php include("header.php"); ?>
<h1>Welcome to my home page!</h1>
<p>Some text.</p>

<div class="leftmenu">
<?php include("menu.php"); ?>
</div>

<h1>Welcome to my home page.</h1>
<p>Some text.</p>

Require

<?php
require("wrongFile.php");
echo "Hello World!";
?>

Include Vs. Require

In include : Notice that the echo statement is executed! This is because a Warning does not stop the script execution
-------------------------------------

Warning: include(wrongFile.php) [function.include]:
failed to open stream:
No such file or directory in C:\home\website\test.php on line 5

Warning: include() [function.include]:
Failed opening 'wrongFile.php' for inclusion
(include_path='.;C:\php5\pear')
in C:\home\website\test.php on line 5

Hello World!

-------------------------------------

In Require : The echo statement is not executed, because the script execution stopped after the fatal error.

-------------------------------------

Warning: require(wrongFile.php) [function.require]:
failed to open stream:
No such file or directory in C:\home\website\test.php on line 5

Fatal error: require() [function.require]:
Failed opening required 'wrongFile.php'
(include_path='.;C:\php5\pear')
in C:\home\website\test.php on line 5

-------------------------------------