How to find and update a post with SQLite

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