- Creating the Database
- Making a Request
- Interacting with the Database
- Tying It All Together
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Interacting with the Database
To interact with the database, you need to create the methods for retrieving, inserting, replacing, and deleting posts. I chose to create a Post class with get, save, and delete methods to handle these interactions. This class also has a reference to the database connection file that’s used to connect to the database. You’ll have to replace the login, password, and database name with your database information.
DEFINE (’DB_USER’, ’USERNAME’); DEFINE (’DB_PASSWORD’, ’PASSWORD’); DEFINE (’DB_HOST’, ’localhost’); DEFINE (’DB_NAME’, ’DATABASE’); $dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die (’Could not connect to MySQL: ’ . mysql_error() );
The reference to the connection file is located in the constructor of the class, along with the name of the database. Your constructor should look similar to the following code:
function Post()
{
require_once(’mysql_connect.php’);
$this->table = "informit_ajax";
}
The dbConnect method handles creating the connection by passing the login information to the database; this method is reused in all of the core methods before querying the database:
function dbConnect()
{
DEFINE (’LINK’, mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
}
The get method loops through the table, creates an XML string based on the database rows, and returns the XML string to the requester:
function get()
{
$this->dbConnect();
$query = "SELECT * FROM $this->table ORDER BY id";
$result = mysql_db_query (DB_NAME, $query, LINK);
$xml = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n";
$xml .= "<posts>\n";
while($row = mysql_fetch_array($result))
{
$xml .= "<post>\n";
$xml .= "<id>" . $row[’id’] . "</id>\n";
$xml .= "<date>" . $row[’date’] . "</date>\n";
$xml .= "<title><![CDATA[" . $row[’title’] . "]]></title>\n";
$xml .= "<description><![CDATA[" . $row[’description’] . "]]></description>\n";
$xml .= "</post>\n";
}
$xml .= "</posts>";
mysql_close();
header("Content-Type: application/xml; charset=UTF-8");
echo $xml;
}
The save method serves two purposes, by handling updating and inserting posts:
function save($id, $title, $description)
{
$this->dbConnect();
$query = "SELECT * FROM $this->table WHERE id=’$id’";
$result = @mysql_query ($query);
if (mysql_num_rows($result) > 0)
{
$query = "UPDATE $this->table SET title=’$title’, description=’$description’, date=NOW() WHERE id=’$id’";
$result = @mysql_query($query);
}
else
{
$query = "INSERT INTO $this->table (title, description, date) VALUES (’$title’, ’$description’, NOW())";
$result = @mysql_query($query);
}
mysql_close();
$this->get();
}
The delete method handles removing a post based on the ID that’s passed as the parameter. Then the get method is called to return the new data to the requesting file:
function delete($id)
{
$this->dbConnect();
$query = "DELETE FROM $this->table WHERE id=’$id’";
$result = @mysql_query($query);
mysql_close();
$this->get();
}