Find and edit an entry in SQLite

The same PHP functions are used to pass all queries to the SQLite database: exec or query, depending on whether you want to encumber or receive data. It is the contents of the SQL command that will change depending on whether you want to find the record or change it. And SQL allows you to find a record in different ways.

The query function is followed by either a loop or a simple $ row = $ results -> fetchArray () statement; when only one row of the table is returned.

To describe these commands, we added two scenarios to our list: populating the database, displaying its entire content.

Database population

See SQLite Fill script.

For example, a table is created with a list of records inserted by consecutive calls to the INSERT command, as shown in the previous chapter .

View database contents, dump

See SQLite Dump script.

The SELECT command is executed without special conditions using the PHP arrayQuery command, which assigns the found records to the array, including the entire contents of the database .

Record access

An item in the database is accessed from its ID assigned to the ID field. The query is the one we saw to do the reading, with the conditional clause added:

WHERE ID = $id

$ id is the variable that contains the ticket ID. Thus, the code will be:

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

See ID access script.

Search for a message

The user never knows the record ID, he accesses the ticket from the list, in which case the manager knows what the ticket ID is, or he searches, in which case the manager receives the ID as the search result. In the second case, the query will include a WHERE clause adapted for the search. The LIKE element is used to find a character string in the base field.

LIKE '%$word%'

The variable $ word contains the search string, the% symbol means that all of the above or the next is undefined. The code will be:

$word =  "post 3";

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

The query concerns the ID and post_content fields in the $ mytable, and the condition is that the post_content field (ticket content) contains a string assigned to the $ word variable.

The ID is then retrieved from the array generated by the arrayQuery function. 

$row['ID']

See ID search script code.

Change record

The post is edited with a command that affects the contents of the tables, UPDATE, is associated with SET elements to assign new content and WHERE to select a ticket.

$myid = "2";
$changed="Nouveau contenu du billet";

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

As an example, you can directly assign the variable ID $ myid and the new content $ changed.

The UPDATE command refers to the $ mytable, where the post_content field with new content is assigned, and, as before, a ticket with a WHERE clause is selected.

See the source code for updating the message.

Loading