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, 467 views

Next post: 15. Query efficiency and denormalisation
Previous post: 13. Grouping data in SQL