Dimuthu’s Blog

If you want to store binary in database, you can use BLOB as the data type of that column. In Mysql you can use TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB depending on your space requirement. Here is an example of database table using BLOB as a column type.

CREATE TABLE BloBTest (
    id INT NOT NULL AUTO_INCREMENT,
    filename VARCHAR( 32 ) NOT NULL,
    content BLOB NOT NULL,
    PRIMARY KEY ( id )
)

Storing Data

PHP:

$filename = "myimage.png";
$filecontent = file_get_contents($filename);
$filecontent_escaped = mysql_real_escape_string($filecontent);

$sql = "INSERT INTO BloBTest(filename, content) " +
       "VALUES('$filename','$filecontent_escaped')";
mysql_query($sql, $link);

Java:

String filename = "myimage.png";
InputStream filecontent = new FileInputStream(filename);

String sql = "INSERT INTO BloBTest(filename, content) VALUES(?, ?)";

int size = filecontent.available();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, filename);
ps.setBinaryStream(2, filecontent, size);
ps.executeUpdate();

Retrieving Data

PHP

$sql = "SELECT filename, content FROM BloBTest";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_assoc($result)) {

    $filename = $row["filename"];
    $content = $row["content"];
    $new_filename = "new_" . $filename;
    file_put_contents($new_filename, $content);
}

Java:

String sql = "SELECT filename, content FROM BloBTest";

PrepareStatement ps  = conn.prepareStatement(resourceContentSQL);
ResultSet result = ps.executeQuery();

if (result.next()){
    String filename = result.getString("filename");
    InputStream contentStream = result.getBinaryStream("content");
    String newFilename = "new_" + filename;
    // storing the input stream in the file

    OutputStream out=new FileOutputStream(newFilename);
    byte buf[]=new byte[1024];
    int len;
    while((len=contentStream.read(buf))>0)

    out.write(buf,0,len);
    out.close();
}

Retrieving the Size of the Blob

After you store your data as a blob, you can manipulate or query the data with some of the in-built String functions in mysql. For an example if you want to query the size of the blob you just stored, you can use OCTET_LENGTH function. Here is an example,  (this will give you the size in bytes.)

SELECT OCTET_LENGTH(content) FROM BloBTest WHERE filename='myimage.png'

.

Now you can view the article I wrote titling “Introduction to PHP Data Services“. There I explain how you can design and implement Data Services in PHP using WSF/PHP Data Services Library.

This article covers,

1. Designing your Data Service API.
2. Writing the Data Service.
3. Deploying and Testing Data Service.
4. Make the Data Service available in both SOAP and RESTful form.
5. Use of WS-* features in your Data Service.

If you are thinking of adapting SOA in to your database backed PHP applications, this article will be a good starting point.

Wordpress has a very simple database schema. And it is well documented. You can access the complete description of the wordpress core database from here, http://codex.wordpress.org/Database_Description.

Anyway first time I looked at the database I was confused with the term and the term_taxonomy table, why we need two tables for term and taxonomies. In fact in wordpress, the table ‘posts’ is associated with the table ‘term_taxonomy’ and not the table ‘term’ itself. In the term taxonomy table the terms are associated to a link category, post category or a tag. So the associations of posts to a tag or category is something like this.

wp_term_post association

So in a case you try querying for posts with a given tag it will be like this. (Note that I have skipped the optional database table prefix which is by default ‘wp_’)

SELECT post_title,
       post_content,
       post_date
FROM posts p,
     terms t,
     term_relationships r,
     term_taxonomy tt
WHERE p.post_status='publish' AND
      tt.taxonomy = 'post_tag' AND
      p.id=r.object_id AND
      r.term_taxonomy_id=tt.term_taxonomy_id AND
      tt.term_id = t.term_id AND t.name LIKE ?