14. Connecting to SQL in other JS and PHP
In each case:
- We make a connection
- We make a query
- The results usually come back as a container we can iterate over, like an array or object
MySQL listens on a network port. MySQL libraries in your programming environment make connections via that port.
14.01 Connecting to SQL in JS
Code for the express server in node.js:
const express = require('express'): const app = express(); const webPort = 8088; app.get ('/', f(req,res) { // things res.render(); } app.listen( webport, () => console.log('EMO app listening on port ' +webPort) );
We can use the Mustache templating language too, so using {{variableName}}. eg:
<p>The elephants are different sizes: <ul> {{#elephants}} <li>{{name}} is {{length}} metres long</li> {{/elephants}} </ul> </p>
Mustache iterates between the hash and the slash. i.e. between {{#elephants}} and {{/elephants}}.
To connect to a MySQL database:
const mysql = require('mysql'); const db = mysql.createConnection({ host: ???, user: ???, password: ???, database: ??? }); db.connect();
To make a query:
const querystring = "SELECT ..."; function useRes(err, res) { // code to use the result } db.query(queryString, useRes);
14.02 Connecting to SQL in PHP
We will be using the MySQLi library.
To connect:
$db = new mysqli(host, user, password, database);
If a connection already exists with the same details, it will use that connection.
To execute a query:
$result = $db->query("SELECT * FROM planets");
This returns a mysqli_result object.
To see the result:
We can either iterate over the object like this:
foreach ($result as $moon){ echo $moon['radius']; }
Or we can grab all the results in one go, and treat it as a big two dimensional array.
$moons = $result=>fetch_all(); echo $moons[5][2];
This will print out the 6th row and 2nd column. Note that the first row is row 0 but the first column is column 1.
By default, the fetch_all method fetches the results as a plane array, which is why we are using numerical indexes
But you can also provide it with an option Virtual MySQLi assoc, which allows you then to read the data as by the name of the table.
Tuesday 23 November 2021, 2317 views
Next post: 15. Query efficiency and denormalisation Previous post: 13. Grouping data in SQL
Databases and Advanced Data Techniques index
- 26. A very good guide to linked data
- 25. Information Retrieval
- 24. Triplestores and SPARQL
- 23. Ontologies – RDF Schema and OWL
- 22. RDF – Remote Description Framework
- 21. Linked Data – an introduction
- 20. Transforming XML databases
- 19. Semantic databases
- 18. Document databases and MongoDB
- 17. Key/Value databases and MapReduce
- 16. Distributed databases and alternative database models
- 15. Query efficiency and denormalisation
- 14. Connecting to SQL in other JS and PHP
- 13. Grouping data in SQL
- 12. SQL refresher
- 11. Malice and accidental damage
- 10. ACID: Guaranteeing a DBMS against errors
- 9. Normalization example
- 8. Database normalization
- 7. Data integrity and security
- 6. Database integrity
- 5. Joins in SQL
- 4. Introduction to SQL
- 3. Relational Databases
- 2. What shape is your data?
- 1. Sources of data
Leave a Reply