Client-side page updates

Searching the database

Only what I want to see.

Test out a SQL query that returns all the quotes that contain either of two keywords

The SQL like operator and % wildcard character let you create WHERE clauses that search for strings inside strings. For example:

SELECT * FROM example WHERE motto like 'eat%'

Returns all rows where the motto field starts with 'eat'.

SELECT * FROM example WHERE motto like '%eat'

Returns all rows where the motto field ends with 'eat'.

SELECT * FROM example WHERE motto like '%eat%'

Returns all rows where the motto field contains the substring 'eat' anywhere.

You can search for multiple strings by using AND or OR in your WHERE clause, like so:

SELECT * FROM example WHERE (motto like '%eat%') AND (motto like '%starve%')

For this part, figure out the working SQL statement in DB Browser. You should choose keywords that are in some of the quotes but not others so you can see if it is working or not. Take a screenshot of the working SQL query and add it to your repo.

Additional References:

Create a GET route /quote_search that returns all quote text (JSON) that contain the words listen OR read

It's easier to test a GET route first because you can just type it in the URL bar (e.g. http://localhost:8080/quote_search). In the next part you'll work on converting it to a POST. For now, hardcode this variable at the top of the route:

const fakestring = "listen read";

In the route, treat fakestring as if it came from the client. Split it up on spaces into an array and generate the proper SQL to query the database for all quote text that contains either word. Your code should work no matter how many space-separated words you put in fakestring. You must use a parameterized query, you cannot just put string content from the client into your SQL.

Additional References (these are common string manipulation functions in most any language that you should be aware of)

string split (split a string into an array of substrings)
join (create a string from an array of strings)
map (create a new array by transforming each value in an existing array)

Parameterized queries substitute values into the SQL after ensuring that they are safe. For example:

const fakedValue = 'die!';
db.get('SELECT * FROM example WHERE motto = ?', [fakedValue])

Will result in this SQL:

SELECT * FROM example WHERE motto = 'die!'

Notice that the substitution puts quotes around the value in order to be correct SQL. Unfortunately, this means that:

db.get('SELECT * FROM example WHERE motto like %?%', [fakedValue])

Would result in this incorrect SQL (note where the quotes are):

SELECT * FROM example WHERE motto like %'die!'%

Instead, consider that the % symbols should be part of the value, not part of the SQL string, like so:

db.get('SELECT * FROM example WHERE motto like ?', [`%${fakedValue}%`])

Which will result in the correct SQL:

SELECT * FROM example WHERE motto like '%die!%'