How to find and update a post with SQLite

The same two PHP functions are used to send all request to the SQLite database: exec ou query, depending on we want to send or receive data. This is mainly the content of the query that changes, depending on whether we want to find a record of change it. And SQL can find a record in numerous ways.

The query function is followed by either a loop of a single $row = $results->fetchArray(); statement when a single row is returned.

To describe these commands, we have created two more scripts: a script to fill a database and another to dump its content.

Filling a database

See the script SQLite Fill.

For the purpose of the example we have defined an array that holds a list of records that are inserted by successive calls to the INSERT command, already seen in the previous chapter.

Showing the contents of a database, dump

See the script SQLite Dump.

A SELECT is performed without condition, with the PHP function arrayQuery that assigns to an array the records found, in this case the whole content of the database.

Retrieving a record

An article may be retrieved in the base from its identifier, assigned to the ID field . The query is the same we have seen to do a read, with the addition of a condition:

WHERE ID = $id

$id is the variable that contains the identifier of the post. The code will be:

$myid = "2";

$query = "SELECT post_title, post_content, post_author, post_date, guid 
          FROM $mytable WHERE (id=$myid)";
$results = $base->qQuery($query);   
$row = $results->fetchArray();

See the script to find a post by its ID.

Searching for a post

The user do not know the ID of a record, he accesses post from a list and in this case the manager knows which is the ID of the post, otherwise the user conducts a search and in this case the manager gets the ID as a result of the search. In this second case the query includes a WHERE clause adapted to the search. The LIKE element is used to find a string inside a text. 

LIKE '%$word%'

The $word variable contains the string being sought, the % symbols mean that the data before and after this string are undefined. The code becomes:

$word =  "post 3";

$query = "SELECT ID, post_content FROM $mytable WHERE post_content LIKE '%$word%' ";
$results = $base->arrayQuery($query);
$row = $results->fetchArray();

The query is related to the ID and post_content columns, in the $mytable table, and the condition is that post_content must contain something that is assigned to $word.

We retrieve the ID in the array generated by arrayQuery. 

$arr['ID']

See the code of the script to find the ID of a post.

Updating a record

A post is modified by the UPDATE SQL command, combined with the SET element to assign a new content to a field, and the WHERE part to select the record to modify.

$myid = "2";
$changed="New content of the post";

$query = "UPDATE $mytable SET post_content = '$changed' WHERE (id=$myid)";
$results = $base->exec($query);

For the purpose of the example, the ID is assigned directly to the $myid variable and a new text to $changed.

The UPDATE command concerns the $mytable table, where we assign the column post_content with the content of $changed, and as previously the post is selected by its ID with the WHERE clause.

See the source code to update a post.

Download