That’s right, we can use SQL to find data that is LIKE a word!
And when we combine it with Wildcards (%)
1 | SELECT * FROM People WHERE Country LIKE 's%' |
This will return all of the records where the Country begins with S
1 | SELECT * FROM People WHERE Country LIKE '%d' |
This will return all of the records where the Country ends with S
And using both:
1 | SELECT * FROM People WHERE Country LIKE '%land%' |
This will return any records where the Country CONTAINS land
And technology is going to be great this year:
And with all this technology – is it possible that creating games and web development may also change. Maybe? Let’s see!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | if(isset($_GET["page"])){ $page = $_GET["page"]; }else{ $page=1; } $perPage = 20; $start = ($page-1) * $perPage; $results = mysql_query("SELECT * FROM people ORDER BY ID ASC LIMIT $start, $perPage"); echo "<table>"; echo "<tr><td>ID</td><td>FirstName</td></tr>"; while($row = mysql_fetch_assoc($results)){ echo "<tr>"; echo "<td>".$row["ID"]."</td>"; echo "<td>".$row["FirstName"]."</td>"; echo "</tr>"; } echo "</table>"; |
Line 1: Here we detect if a custom page is being used
Line 2: If there is we use it
Line 4: If not then jsut set to the default
Line 6: This is a variable for how many records to display per page
Line 7: A simple expression to work out which record to start at
Line 8: Get the query
And for now, that’s all you need to know! I do hope it’s useful!
So we don’t want to return all of the data?
LIMIT X, Y
Where X is the starting ID, the first is 0 and the amount to display is the second parameter.
1 | LIMIT 0, 10 |
shows the first 10 results
And
1 | LIMIT 5, 5 |
shows the 5 results: 6, 7, 8, 9, 10
Just put it on the end of your query and you’re good to go.
1 | DELETE FROM TABLE WHERE COLUMN=VALUE |
As with the update statement, if you ommit the WHERE clause – the whole lot will get deleted!
So, lets say we want to remove ‘Aaron Stothert’ from our table. Where possible wit pre-determined data always try to use your primary / unique key. Why, because there could be duplicates for what you are trying to delete.
1 | DELETE FROM people WHERE `ID`=6 |
And we no longer have that row…
1 | DELETE FROM people |
or
1 | DELETE * FROM people |
will remove ALL of the records!
So, we can insert, select using basic statements and now we’re going to learn how to update.
Infact – it’s really easy!
1 2 3 | UPDATE TABLE SET column1=VALUE, column2=value2,... WHERE COLUMN=VALUE |
And it’s just like that…
Let’s try completing our table…
1 | UPDATE people SET LastName='Watkins',Country='Scotland' WHERE ID=8 |
And we get:
READ MORE »
So, you have your table:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 3 | James | Fridge | 46 | America |
| 4 | Jeremy | Plotter | 13 | Scotland |
| 5 | Paul | Stavers | 96 | Canada |
| 6 | Aaron | Stothert | 32 | England |
| 7 | Priyan | Javan | 34 | India |
| 8 | Connor | 26 |
And you use
1 | SELECT * FROM people WHERE 1 ORDER BY ID |
Nothing will change, as:
The ORDER BY keyword sort the records in ascending order by default
and as we input them in the correct order it’ll do the same function.
To order ascending and descending you can use
1 | SELECT * FROM people WHERE 1 ORDER BY ID ASC |
(no change) or
1 | SELECT * FROM people WHERE 1 ORDER BY ID DESC |
which will simply do the following:
READ MORE »
Let us combine AND and OR
1 | SELECT * FROM people WHERE `LastName` = 'Fridge' AND (`Country` = 'England' OR `Country` = 'Scotland') |
So basically we are searching for:
LastName = Fridge and Country = England or,
LastName = Fridge and Country = Scotland
The result:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 2 | Simon | Fridge | 25 | England |
By combining these you just need to use brackets effectively to create the desired effect.
It is possible to search by more than one paramenter – simply by using the words:
AND or OR
1 | SELECT * FROM people WHERE `Country` = 'England' AND `FirstName` = 'Simon' |
Will show as:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
And;
1 | SELECT * FROM people WHERE `Country` = 'England' OR `Country` = 'America' |
and you get:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 3 | James | Fridge | 46 | America |
| 6 | Aaron | Stothert | 32 | England |
In the previous tutorials we have been using WHERE 1 however by changing this you can make it so you only get some of the data…
1 | SELECT * FROM people WHERE `Country` = 'England' |
And as a result you get:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 6 | Aaron | Stothert | 32 | England |
Written by