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.
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.
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.