Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Design > Voices That Matter

  • Print
  • + Share This
  • 💬 Discuss
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();
}
  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus