PHP & SQL Workshop & Information

JJ Bastida

General Info

Before we start the lesson...

Be sure to download either Brackets, Atom, or Visual Studio Code.

Download a code editor and use it when coding.

Be sure to also download either Cyberduck or FileZilla.

Download an FTP client manager to transfer files to your server.

Have a template HTML, PHP, CSS, and JS folder structure so you don’t have to start from scratch each time, I have included one here.

For SQL you will need a server and database of some sort to store your data. Our firebird servers, and most purchased hosting sites, have a plugin for mySql which gives you access to a database.

Php requires you to make your html files into .php files, they work and look identical but can now parse and render php. Be sure to also change the DOCTYPE at the top of the file to php as well.

Although we are being taught php, it is a dying language! Nowadays, the industry tends to use the fetch api, a simple request protocol that receives the JSON data in a quick way, only if you have proper request controllers setup. Which I will not show you how to do because that’s C#

It's The PHP

It's like JS but with more letters and more confusion!

PHP is a server scripting language, a powerful tool for making dynamic interactive websites and retrieving content for a page.

In terms of language, it is relatively close to JS with some minor differences in syntax, keywords, and scope.

Unlike JS, any php code will disappear after the page has rendered.

Can be written inline and produce raw html, css, or js, much faster than raw js.

Opens and closes with a <?php [CODE GOES HERE] ?> tag.

In terms of syntax, variables ALWAYS have a $ before them, and you don't have to declare them before you use them.

In addition to that, when joining together string you use a . as opposed to a +

echo mysqli('SELECT*FROM`boi`');


Be a good kid and play with your string.

Echo is a keyword that will output the result or value of whatever is inside of it's call. It's not really a function, so it can be called without it's brackets. If it is empty it will output nothing.

Print is a very close relative to Echo, it also returns values, but it is actually function and requires its brackets. When empty it will return a value of 1.

var_dump is also a relative to the likes of Print and Echo; it is a function that will output an object containing the variable types, values, and other properties all at once. Generally variables are passed into it.

$_POST & $_GET
Post and Get are both variables in a sense, that hold the POST and GET requests made by page forms. Get places all data into the page URL which in many cases can be unsafe, POST will not do this, and is generally the way to go.

Include inserts information from another file into the current location so that you can have separate files with your php.

foreach is a special kind of function that loops through array values, giving the current value a local variable that can be used in the function. The syntax for it is foreach($originalArray as $singleItem) { //... };

isset() & empty()
isset and empty are two very similar functions. Isset checking if the value has been set, meaning it is not null or undefined. Empty will check if the variable has a value of 0, an empty string, or an empty array. Both return either a true or a false boolean value.

exit() & die()
exit and die are two very identical functions. Both will end the current php parsing, meaning ending the code prematurely and return a value in their function call.

Databases & SQL

Imagine a table, and now imagine a table with columns and rows.

Structured Query Language or SQL, is a query language made for storing, manipulating and retrieving data stored in databases.

It follows the CRUD model of language, meaning it lets you create, read, update, and delete data in databases with ease.

In addition to that, you can give tables and columns properties. Like creating connections between tables, sort information, and auto incrementation.

It works by stringing together a set of keywords to form a query which then returns a result of some kind.

There are a lot of different keywords with strange syntax which can be reviewed at W3's List of SQL Keywords

Data tables look similar to that of a spreadsheet with columns, rows, and a bit extra. Storing all of the data in the table rows in their specified column.

CREATE TABLE `wow` (fire int)

Table: wow

fire (int)

Connecting to a Database

Paging Mr.Base, Mr.Data Base

Sign In to FTP & Quick Edit PHP

Using either Cyberduck or Filezilla, go into your public_html and add the blank php file into it.

In the Cyberduck or Filezilla settings, set Brackets or Visual Studio Code to always be the default editor.

Edit all of the files in your code editor through cyberduck, so that when you save it will automatically get uploaded to the server. Then just view your changes on a regular tab so that the php has a chance to run and render.

Understanding the Connect.php

The connect.php provided in the php folder of the blank php folder is the means by which you can connect to your database and make requests.

With php, you only have to make one request before the page loads, and once the request has been fulfilled the php (as you know) will vanish.

So the connect.php is an important part to any php file that makes requests, be sure to include it in the top of every file.

$serverName = ""; Or whatever server you're using
$username = "JohnSmith"; The username for your database profile.
$password = "•••••"; The password for the databse profile.
$database = "myDatabase"; The database name for your database profile.
$conn = new mysqli($serverName, $username, $password);
The actual connection request, it contains all of the information needed to connect to your database.

if ($conn -> connect_error) {
die("Connection failed: " . $conn -> connect_error);
This is incase the connection to the database fails.


Using The Database and The Data

Let's GET this bread!🍞

To actually use the database connection and to make requests, you need to use the connection variable we created in the connect.php to query the database.

Be sure to actually have created some data in your database at least to test before making more complex requests.

You can get creative with how php returns and what it returns or sends. You can use forms with a POST or GET method to retrieve and send data. For more info check this W3Schools explanation of Form Handling

PHP print, echo, and die are very important and powerful tools, you can not only return regular texts but whole tags and even js in a script tag (and it will work!).

<p> My Name is:
echo ($conn -> query("SELECT username FROM User WHERE userId = '".$userId."';")); ?>


Help & Troubleshooting


Be sure that you have the right kind of data in your tables! And that your column data types are correct with what you are providing.

Be sure to use consistent naming structures when creating both tables, variables and more!

Always test data using smaller requests to be sure that your queries and requests are working properly.

Check the syntax of your php, like the . or using that you're properly using the -> top reference functions inside of objects.

Always test queries in your database before making larger scale or even code test requests, just to be sure that your table is structured properly.

Check with a die or an exit to be sure that your code is properly exiting at the right location.

The occasional var_dump helps to check what kind of data you are working with and if it is the right kind of data.

If you are using the foreach function, make sure you are using it correctly and are receiving the correct data result from it.


For all you cool kids 😎

Draw and plan out larger scale databases to ensure that everything is properly flowing BEFORE coding.

Use features like auto incrementation, column and table relationships, keys, and sorting in your database!

PHP can return a script tag with JS in it so you can essentially create a dataflow into your JS to make information dynamic if you need it

Be sure to take advantage of more advanced database systems like MongoDB, that relies on JS API requests which are easier to manage!

Keep any variables that you're going to be constantly using in a separate PHP file that you "include" in each file you need the variables.

This is less of a tip and more of a suggestion, STYLE THE DATA YOU GET!! DON'T JUST MAKE AN UGLY TABLE!

That's all for now!

Be sure to contact me if you have any questions or concerns