Running SQL Queries
Querying a Database
Connect to the database
Execute the SQL statement
Check the number of results
If the number of results is 0 results message
Otherwise for each result fetch the results as an associative array
This code executes a SELECT query to return birthdays in a particular month. It then checks for the number of results using the mysql_num_rows function. If there are no results then we will display a suitable message. If not then we will display the results using a table.
Displaying the results
We will use an HTML table to present the results - more information can be found here. The code below assumes that our query has been executed successfully and has some results (using the SELECT code above for example).
Close the connection
INSERT Querys and PHP
INSERT Pseudocode
Connect to the database
If not successful kill the PHP script using die()
Evaluate the query result to check if query was successful ( that it executes - not that it necessarily changes any rows)
If it was then display suitable message
If not successful display suitable error message
Close the DB Connection
After connecting to the database and assigning variables as before:
INSERT PHP Example
Updating or Deleting Records
When we update (or delete) a record it is executed in the same way as an INSERT query. In that it will not return a result. An UPDATE or DELETE query that does executes successfully may still not affect any records.
We can use the mysqli_affected_rows() function to check how many rows were changed.
This is not in the Adv Higher course but can be useful.
Example Update Query
This code assumes a suitable UPDATE query has been executed. The logic for a DELETE query would be identical.