How to Find and Update a Post With SQLite

The same two PHP function are used to send all request to the SQLite database: queryExec ou arrayQuery, 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.

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, the 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->arrayQuery($query, SQLITE_ASSOC);   

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, SQLITE_ASSOC);

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->queryExec($query);

For the pupose 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

The complete source code of the scripts in a ZIP archive.