Share the Love! Free Programming eBooks

Leave a comment Standard

GoalKicker.com is giving away these free programming books and I thought I would share the love for anyone who hasn’t seen them yet. There are more available from their website but these are the ones I was most interested in. Enjoy!

Screen Shot 2018-01-31 at 4.26.46 PM

Screen Shot 2018-01-31 at 4.26.54 PM

Screen Shot 2018-01-31 at 4.27.01 PM

Screen Shot 2018-01-31 at 4.27.11 PM

Screen Shot 2018-01-31 at 4.27.18 PM

Screen Shot 2018-01-31 at 4.27.24 PM

Screen Shot 2018-01-31 at 4.27.37 PM

Screen Shot 2018-01-31 at 4.27.44 PM

MySQL Tutorial: Archiving and Loading Archived Table Data

Comments 2 Standard

Let’s say you have a really large table and you want to archive the data to a new table so that your main table is smaller again. This is useful with things like chat logs or game logs which grow large really quickly but you don’t want to lose the data or you want to keep a history of it. Making an archive table from the main table allows you to truncate the main table for faster queries.

Create the Archive Table

If you want to save the contents of the table into another archive table you’ll need to create a copy of the original table with all the same indexes and schema:

CREATE TABLE your_new_archive_table LIKE the_table_you_want_to_archive;

Dump the Data into the Archive Table

Now you have an archive table that matches your original table let’s dump the data into it:

INSERT your_new_archive_table * FROM the_table_you_want_to_archive;

Dump the Data into a SQL file

If you prefer to save your data to a file on the server rather than in another table you can archive it this way instead. Make sure your file has saved and has data in it before you truncate your original table.

SELECT * FROM the_table_you_want_to_archive INTO OUTFILE '/save_path/your_new_archive_table .sql';

Test the Archive Table

Make sure your table has data in it by looking at your new archive table:

SELECT * FROM your_new_archive_table LIMIT 100;

Test the Archive File

Go to the command line and check to make sure you file was created and has data in it by typing the following:

cat /save_path/your_new_archive_table.sql

Empty the Original Table

Make sure your archive table or archive file has data in it before you empty your original table.

TRUNCATE TABLE the_table_you_want_to_archive;

Load an Archive Table Back Into the Original Table

If you decide you want to load your archived data back into the original table you can do the following.

INSERT the_table_you_want_to_archive * FROM your_new_archive_table;

Load an Archive File Back Into the Original Table

Once you have an archive file you can load it back into your database by doing the following:

LOAD DATA INFILE '/save_path/your_new_archive_table.sql' INTO TABLE the_table_you_want_to_archive;

PHP Tutorial: Looping Through & Displaying A MySQLi Result Set

Comment 1 Standard

Recently I’ve seen a lot of people using while loops to retrieve a single record. You only need to loop through your result set when you expect to get more than one row of data returned. If you use a loop even when you only expect one value back from your query then you’re wasting your server’s resources because the PHP interpreter has to do more work to process a loop than it does to return a single result. This assumes you have already connected to the database and have a table to work with.

Let’s say we have a table called members with the following information:

-----------------------------------------------------
| ID  | NAME       | EMAIL              | LOGGED_ON |
-----------------------------------------------------
| 1   | Sam Jones  | sjones@gmail.com   |  False    |
-----------------------------------------------------
| 2   | Dee Walter | deeman@gmail.com   |  True     |
-----------------------------------------------------
| 3   | John Doe   | jdoe@gmail.com     |  False    |
-----------------------------------------------------
| 4   | Lisa Wells | lwells@gmail.com   |  True     |
-----------------------------------------------------

Now our ID field is an auto_increment and unique. That means no two members will have the same ID number. It’s guaranteed that looking up a member by their ID number will return 1 record or no results. 1 record if a member with that ID number is found and no records if a member with that ID doesn’t exist. Anytime you expect to get 1 or fewer records returned there’s no reason to use a loop, instead find the row directly:

<?php

//run the query
$result = mysqli_query($dbh, "SELECT * FROM members WHERE id='1'")
    or die (mysqli_error($dbh));

//fetch the results
$row = mysqli_fetch_array($result);

//display the results
echo $row['id'] . " " . $row['name'] . " " . $row['email'] . " "  . $row['logged_on'];

?>

Since we know there is only one member with the ID equal to 1 we can run our query, fetch the results and then display the data returned from the result.

Now let’s say we want to display ALL of our members. In this situation we know our members table will have zero or more records at any time, especially as new members are added in the future. In this case we expect our result will always, with the exception of when we add our first member, have one or more member in it. Here it’s appropriate to use a loop to display the data.

<?php

//run the query
$loop = mysqli_query($dbh, "SELECT * FROM members")
   or die (mysqli_error($dbh));

while ($row = mysqli_fetch_array($loop))
{
     echo $row['id'] . " " . $row['name'] . " " . $row['email'] . " "  . $row['logged_on'] . "<br/>";
}
?>

PHP Tutorial: Connecting to a MySQLi Database

Leave a comment Standard

So, here’s the low down on how to setup a connection to a MySQL database using MySQLi and PHP. This will also make sure your login information is protected. First, you’ll need a database, PHP, and a place to host your files. Once you have those you’re ready to go. Don’t have a hosting provider? No problem, check out XAMPP.

1) You want your login for your database to be protected. That means you want the file to reside in a non-public part of your website. For instance, in most hosting places when you go to view all of your files you know to put them in the public_html folder or the www folder or the index folder. Your database login needs to be in the directory ABOVE your public folder. For most people this is called home, the name of your website, or even the root directory.

2) Create a file called dbh.php (or any other name that suits your fancy) in your home/root directory. That means if people navigate your website they’ll never be able to accidentally access this information because your home/root directory has different chmod permissions (711) then your public_html directory (750).

3) Now you need to write the code to make the actual connection to the database in dbh.php.

<?php
/**
* replace things with { } curly brackets with the appropriate information
* including the curly brackets! don't leave those in there...
* You'll use your database handler ($dbh) when you run your queries
**/

//connect to the server
$dbh = mysqli_connect("localhost", "{username}", "{password}", "{database}")
     or die ('cannot connect to database because ' . mysqli_connect_error());
?>

4) That’s it for the code to connect! Short and sweet. Now the question is how to get it in all your files.

5) Create a file in your public_html folder called index.php. You want this file to be publicly accessible to anyone. Put this inside of it:

<?php
/**
* include your database connection from a protected directory
* by include it from the directory above (../) where this file is
**/
include('../dbh.php');

//do all the rest of your usual php coding here
echo "connection successful!";

?>

If you don’t get an error message when you try to view this php script then you know your include was successful. Now there’s no chance someone can get the login and password for your database and you can access it from your php files like you normally would AND you don’t have to rewrite the connection statement at the top of EVERY php page you make.

Nifty huh?

PHP Tutorial: Create a nl2br function that doesn’t add tags inside of textarea contents

Comment 1 Standard

So this really bugs me and I thought I would share. Let’s say you want to allow someone to post their own custom html in a message or on a part of your website. If you’re using nl2br to format that message and they have <textarea> tags inside of their content then when you apply nl2br it adds <br> tags to the content inside of their textarea. For example:

$query = mysql_query("SELECT custom_html FROM users WHERE uid='$uid'") or die ('cannot load user custom html content');
$user_content = mysql_fetch_assoc($query);
echo nl2br($user_content['custom_html']);

If the user’s custom_html had:

Hi guys!
My name is Joe.
<textarea>this is
my html content</textarea>

Applying nl2br to that would result in the following:

Hi guys!<br />My name is Joe.<br /><textarea>this is<br />my html content</textarea>

So here you can see that we have <br /> tags inside of our textarea. When we display this on the page the textarea displays the <br> tags instead of indenting the content to a new line like we expect it to. This is where my code snippet comes into play.

//replace your nl2br function with our new nl2br_textarea function
function nl2br_textarea($message)
{
    $message = nl2br($message);

    function scrub_textarea($data) {
        return str_replace(array("<br>", "<br/>", "<br />"), "", $data[0]);
    }
    return preg_replace_callback('#<textarea[^>]*>(.*?)</textarea>#is',scrub_textarea,$message);
}

Using our nl2br_textarea function our original code would be updated to look like this:

$query = mysql_query("SELECT custom_html FROM users WHERE uid='$uid'") or die ('cannot load user custom html content');
$user_content = mysql_fetch_assoc($query);
echo nl2br_textarea($user_content['custom_html']);

Which means the output of user’s custom_html will now be this where we actually see a line break in the textarea without the additional <br> tags thrown into the mix:

Hi guys!<br />My name is Joe.<br /><textarea>this is/nmy html content</textarea>

ERROR FIX: json_encode returns null even if there is a value being passed to it

Comments 7 Standard

Loosing Data With json_encode?

So this took me several days of pulling out my hair so I’m hoping to save you some hair loss. I had a query pulling data from a MySQL database and then I was encoding the data and sending it back as a json_encode to an ajax file. The strangest thing was happening to my query — it would load a set number of records and then it just stopped, my json_encode would always return a null value even if there was data being passed to it.

Here’s The Solution

Apparently json_encode will only work properly if the data being passed to it is encoded as UTF-8. One of the posts I was pulling back had a Microsoft curly apostrophe in it (darn you user-entered data!).  So the quick solution is to use the utf8_encode() function around your data before you pass it to json_encode. Otherwise it’s time to go through all of your data and make sure it’s encoded UTF-8 first!