blogblogblogblogblogblog SyKoHPaTh

Web Development From Scratch, Day 6

The typical approach to handling databases is called, "CRUD". This stands for Create, Read, Update, and Delete. While not all of these will be used for every project, "Read" is mainly used (speaking from experience, so milage may vary).

Creating the database and table has already been covered. In the MVC tutorial, there was a quick bit about connecting to the database, but I'll go into further detail here.

1) FIRST. Security.

Do "NOT" have your connection settings in a web-accessible directory (such as http:///db_connect.php). Even though everything is "hidden" in hardcoded php ($username="Database";), there are ways these files can be read. Even something as simple as accidentally setting PHP files to be "off" will cause .php files to display as HTML instead of being compiled first.

So, on your website directory, you have the following setup:
|- /www
|- /includes

/www is the base folder that is loaded when http:/// is accessed. We want to put our db_connect.php file into /includes, and then call it from our web folder.

There's a couple options here - in "php.ini" (if you can access it), you can specify include directories:
* Adjust "include_path" to include that directory. include_path = "/includes" (don't erase what's already in there! Also, restart the webserver after changing).

If you're unable to edit the php.ini, hope isn't lost:
* In the php script you can use: set_include_path() - although if anyone is able to see this, they now know the direct route to your included file (not that they'd stumble across it anyway if they are able to poke around the files/directories at this point)
* You can alternatively use __DIR__ as include(__DIR__ . '/includes/file.php');
* Alternative to __DIR__: set_include_path(get_include_path() . '/includes');

On your local computer, instead of putting this into "~/Projects/", instead, create this file in a new folder in your Projects directory: "~/Projects/includes"


So, with that setup, make "db_connect.php" in your /includes directory:

Code Sample:
  1. <?php $mysql_db = mysqli_connect('', '', '', '');
  2. if(!$mysql_db){
  3. die('Unable to connect to Server: ' . mysqli_connect_error() . ' (#' . mysqli_connect_errno() . ')');
  4. }
  5. ?>

* Use "mysqli" instead of "mysql". This is anotehr security issue, plus, future versions (heck, it's already deprecated in current version) will likely remove "mysql" commands.
* This is using procedural over Object oriented style (OOP). You'll find out the difference, and use OOP in the future, but for now, procedural is fine as you start out.
* mysqli_connect: Use the details from your webhost. The last option, , refers to the database name, which if you followed these tutorials so far, should be "test". EASY!
* The only variable carried over from our include is "$mysql_db". This is a link to the database that will be passed into other functions.
* Connection information (server, username, password) is sometimes stored in variables, and then called after the include/require. This is fine (and used commonly in frameworks). However, "a hacker" could put "echo $server,$user,$password;" at the top of a file, and then BOOM info is exposed, even though they can't access your /includes directory. It happens, but meh, keep it in mind - no need to be too paranoid about it.
* "die" causes the script to terminate immediately - here, it will show the connection error and error number. This is fine for testing, but for a "live" environment, it's better to redirect to a "Server is down" page, and not show any error information.

Hey, guess what? If you FTP to your site, chances are, it'll plop you in your web root directory. So how the heck do we create the includes directory (and our file!) outside of webroot?

1) Git Bash, and ssh to your site ("ssh @")
2) You should be in your home directory, if not: cd ~
3) "ls" and you should see your /www, as well as other miscellaneous folders.
4) Create the /includes folder: mkdir includes
5) cd includes
6) Funky part here. Leave this window open, and open a *new* instance of Git Bash. Yes, you will have two windows open now - one for your local connection (LOCAL:), one for your remote connection (REMOTE:).
7) LOCAL: You should be in your home directory, "cd ~" if not
8) LOCAL: cd Projects/includes
9) LOCAL: "ls" and make sure you're in the right spot - you should see your "db_connect.php" file in this directory (and nothing else!)
10) LOCAL: Copy the file using "scp", which syntax is "scp @:/":
11) LOCAL: scp db_connect.php @:~/includes/db_connect.php
12) REMOTE: "ls" and you should now see your file!


Yesterday, we made the "User" table, and went ahead and made a test record: Bob Bobkins. So, let's see if we can "Read" first, since it's the primary thing that will be used more often than not.

In our web root directory, have the index.php:

Code Sample:
  1. <?php
  2. require "db_connect.php"; //load config from /includes if pathed in php.ini
  3. //OR
  4. //require "../../includes/db_connect.php"; ///
  5. //OR
  6. //require __DIR__ . "/includes/db_connect.php";
  7. require "controller/users.php"; //pass to controller
  8. ?>

* Pick an option for calling db_connect.php. Go with whatever one works, it honestly doesn't matter, and varies greatly by setup. This may take trial and error ("~/includes/db_connect.php" worked for me). After each change, "git push" to your site, and see if the error message pops up ( replace the controller require with an "echo 'success!' to check script progress). If no error, you're good!

Onto our reading test. For purposes of just trying to read, we'll test straight from the controller. Once we can read and understand how that works, we'll move the database information into the model.

Code Sample:
  1. <?php
  2. $sql = "SELECT * FROM users";
  3. $result = mysqli_query($mysql_db, $sql);
  4. if(!$result){
  5. die('SQL Error: ' . mysqli_error($result));
  6. }
  7. while($row = mysqli_fetch_assoc($result)){
  8. echo $row['name']; //list all names
  9. }
  10. mysqli_free_result($result);
  11. ?>

* All this will do is display "Bob Bobkins", when successful.
* $sql contains the...sql...statement, pure and unaltered:
- SELECT = read from table
- * = all fields, we could name individual fields if we wanted.
- FROM = designates what table(s) to read "from"
- users = the name of our table.
- Simple syntax form: SELECT FROM
* mysqli_query(connection, sql): is what sends the command to SQL to process, and returns a link to the result. It doesn't necessarily contain what we need directly.
* mysqli_fetch_assoc(result-link): THIS is how we get the information from the database. There's a few ways we can get data:
-mysql_fetch_array() = get results as an array, defaults to both "numeric" and "associative"
- Numeric array: elements are represented as numbers. "0" refers to the first element, which depends on the SQL query and/or the order of the fields in our table. In this case, likely to refer to "id" field in our table, since it is "first".
- Associative array: elements are represented by labels. "name" refers to the "name" field.
- mysql_fetch_array(result, MYSQLI_ASSOC) is EXACTLY the same as mysql_fetch_assoc(result).
- This function is put into a loop. Every time this function is called, it retrieves one "row" from the database. If we had more test records in our database, this loop would retrieve all rows and display the names.
* $row[field] = the associative array, referring to the field. Try changing this to other field names and get them to display. For a "fun" way to display all fields and their contents, replace the line with this monster:
foreach($row as $key=>$value){
echo $key, "=>", $value, "

- this will display each field and their contents.
- note that the "password" is showing 1234. Yes, it's that easy to display a plaintext password. We'll deal with this a tad later.
* mysqli_free_result(result-link) = ok, technically we don't need this. It's a good practice to get into, however (so is mysqli_close(database-link)), especially for large scripts and multiple calls. All it does, is free up memory that is allocated to the result. Imagine cases with multiple databases with multiple table calls using multipule variables - ugly, right? It's a common occurrence, however. It's important to remember memory management - while PHP is a language that handles garbage collection automatically, there's some other languages (C ) that require manual calls, or things can get "rather messy".

Ok, we have "Read" covered. Tomorrow we'll cram our database stuff into the model, and then start working on an input form, which will reside in our view.

php, tutorial, security, database, include, path