Mysqlexport – Exporting the Database with PHP

Whether you are a programmer or a website owner, it’s up to everyone; you want to back up your databases but you use shared hosting and it becomes impossible to export and back up your databases because of problems from the hosting company. Even if you do not have such a problem, you can manage your own server like me, and it’s hard for you to back up many databases manually. You would like to have a system (PHP script) that automatically backs up all your databases. Anyway, the programmer is lazy, the programmers are people who automate or facilitate everything to stay lazy.

I also created a PHP class to automatically back up the databases on my server. I will get rid of this problem by creating a cronjob. You can either use it for automatic backup like me or you can create a custom page to backup with a password you will enter via the web.

You can find my “mysqlexport” class on github. You can back up your databases as you like with the following sample usage. You should only do this with a user who has access to all databases. Otherwise, it will only back up the database if it has permission.

Usage Steps;

  1. First, we include our class.
  2. We create a new “mysqlexport” class instance. In the first parameter, we write the path to be backed up. We then write the database connection address (usual localhost), the username, and the password as parameters.
  3. After that, we have two choices; we define the databases we want to back up in an array with setDatabases(array $databases), or we get the names of the databases with the getDatabases() function.
  4. Finally, we end the backup with the export() function.
<?php
include 'Export.php';
$export = new MysqlExport\Export('backup', 'localhost', 'root', 'dev1');
$export->getDatabases();
$export->export();

How To Import SQL File With PHP

Sometimes there are times when you can not use PhpMyAdmin or any database program, and you have to use other options to import the Sql file into your database. Here, I’ll tell you how you can import with both the old mysql_query and the new mysqli_query in Php. You can also access the codes from the my Github project.

You need to do followings on both methods; upload Sql file and php file at the same place and run the php file through the site address. You can also run console commands through the server.

Update $filename with the name of sql. Update the $mysql_host variable with the database server. If the database server is not different, you can leave it as it is. $mysql_username and $mysql_password are the username and password for your database. $mysql_database is the database name, update it with your database name.

With mysql_query Function

<?php

// Name of the file
$filename = 'sql.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'username';
// MySQL password
$mysql_password = 'password';
// Database name
$mysql_database = 'database';

// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line) {
// Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;

// Add this line to the current segment
    $templine .= $line;
// If it has a semicolon at the end, it's the end of the query
    if (substr(trim($line), -1, 1) == ';') {
        // Perform the query
        mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
echo "Tables imported successfully";

With mysqli_query class

<?php

// Name of the file
$filename = 'sql.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'username';
// MySQL password
$mysql_password = 'password';
// Database name
$mysql_database = 'database';

// Connect to MySQL server
$con = @new mysqli($mysql_host,$mysql_username,$mysql_password,$mysql_database);

// Check connection
if ($con->connect_errno) {
    echo "Failed to connect to MySQL: " . $con->connect_errno;
    echo "<br/>Error: " . $con->connect_error;
}

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line) {
// Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;

// Add this line to the current segment
    $templine .= $line;
// If it has a semicolon at the end, it's the end of the query
    if (substr(trim($line), -1, 1) == ';') {
        // Perform the query
        $con->query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . $con->error() . '<br /><br />');
        // Reset temp variable to empty
        $templine = '';
    }
}
echo "Tables imported successfully";
$con->close($con);

Finding all the Weekdays in the month with Php

Hello friends. Today, I had to find every weekdays in the month, including the week we were there. Using the codes you write as you wish, you can get the days you want. It’s simple. You can only know the DateTime class.

I only collect weekdays on a array. I do not include weekends. After I set this $lastDay variable to Friday, I update the $lastDay variable to be 3 days later at the beginning of each loop. So I find the monday and push each day into the array separately.

<?php

// Set Today
$today = new DateTime();
// Find beginning of the week
$monday = clone $today;
$monday = $monday->modify(('Sunday' == $monday->format('l')) ? 'Monday last week' : 'Monday this week');
// I will set the variable $lastDay in the loop to the previous week's date. So I can update it on a Monday.
$lastDay = clone $monday;
$lastDay = $lastDay ->modify('-3 day');
// We create an array that specifies 5 weeks.
$dates = array(
    "week1" => array(),
    "week2" => array(),
    "week3" => array(),
    "week4" => array(),
    "week5" => array()
);
// We are starting loop. Here we put our $dates array into foreach.
foreach ($dates as $key => $date) {
    // We use the $lastDay variable to find the first day of the week.
    $firstDay = clone $lastDay;
    $firstDay = $firstDay->modify('+3 day');
    // After that, we increase the days to finding the remaining 4 days.
    $secondDay = clone $firstDay;
    $secondDay = $secondDay->modify('+1 day');
    $thirdDay = clone $secondDay;
    $thirdDay = $thirdDay->modify('+1 day');
    $forthDay = clone $thirdDay;
    $forthDay = $forthDay->modify('+1 day');
    $fifthDay = clone $forthDay;
    $fifthDay = $fifthDay->modify('+1 day');
    // We set the $lastDay variable to the 5th day, ie Friday. In this way, we will be able to find Monday at beginning of the loop.
    $lastDay = clone $fifthDay;
    // I am adding dates to the corresponding week in the $date array. I used different indexes here. You can edit it as you like.
    $dates[$key] = array(
        $firstDay->format('M d D') => $firstDay->format('Y-m-d'),
        $secondDay->format('M d D') => $secondDay->format('Y-m-d'),
        $thirdDay->format('M d D') => $thirdDay->format('Y-m-d'),
        $forthDay->format('M d D') => $forthDay->format('Y-m-d'),
        $fifthDay->format('M d D') => $fifthDay->format('Y-m-d'),
    );
}

PHP And JSON

To explain the relationship between PHP and Json and how to use Json in PHP, let’s start with “What’s Json” on top of it.

In programming, there is a need for data exchange between different platforms. For example, you might want to pull out the latest posts from any social platform and use them in your own project. There is a need to share between platforms for such transactions, and some technologies have been developed for this. For example, one of them is XML technology, but XML is not well used with Javascript. Json is preferred because it is compatible with Javascript.

The data in Json is in the form of array and object.

Object

It starts with “{“ and ends with “}”. The data is written in “Key” and “Data”. Sample;

{
    "name": "Erhan",
    "surname": "Kılıç"
}

Array

It starts with “[“ and ends with “]”. The data in it is written only as “Data”. Keys are automatically identified. Sample;

[
    "Erhan", "Kılıç"
]

Both can be used together in Json as desired. Sample;

{
    "type": "book",
    "genre": "novel",
    "products": [
        {"name": "Ihtiyar Kemanci", "author": "Nihat Genc"},
        {"name": "Su Cilgin Turkler", "author": "Turgut Ozakman"},
        {"name": "Kar", "author": "Orhan Pamuk"}
    ]
}

The most important reason why Json is preferred is that the object and array structure in Javascript are exactly the same. As an example, you will also notice when we try to do it in Javascript.

Json Functions in PHP

In PHP, Json-related operations can be used for the following reason; to use it in Javascript or libraries like jQuery, AngularJs on our own site, to share data with other platforms and to create api. We can use the data that we extracted with Json from anywhere in our php application.

If we pull the json data from elsewhere, we need to be careful; if we do not use the following functions, php will treat the json data as a string value. In fact, everywhere perceives json data as a string, unless the necessary markers are used.

json_encode();

This function allows you to print the data as Json. Sample Usage;

<?php
$array = [
    "type"=> "book",
    "genre"=> "novel",
    "products"=> [
        ["name"=> "Ihtiyar Kemanci", "author"=> "Nihat Genc"],
        ["name"=> "Su Cilgin Turkler", "author"=> "Turgut Ozakman"],
        ["name"=> "Kar", "author"=> "Orhan Pamuk"]
    ]
];
$json = json_encode($array);
echo $json;

json_decode();

This function allows us to decode the Json data we extract from a place and use it in our php software. We can access the data in Json as accessing the data on object. In the following example, I’ll create json data as string. Because when we extract Json data from outside, we will get string data in the same way. Sample;

<?php
$json = '{
    "type": "book",
    "genre": "novel",
    "products": [
        {"name": "Ihtiyar Kemanci", "author": "Nihat Genc"},
        {"name": "Su Cilgin Turkler", "author": "Turgut Ozakman"},
        {"name": "Kar", "author": "Orhan Pamuk"}
    ]
}';
$data = json_decode($json);
echo $data->type;
echo $data->genre;
foreach($data->products as $product){
    var_dump($product);
    echo "<br/>";
}

If you add true as a second parameter in the function, the data will be stored as an array rather than as an object, and our access will change accordingly. Sample;

<?php
$json = '{
    "type": "book",
    "genre": "novel",
    "products": [
        {"name": "Ihtiyar Kemanci", "author": "Nihat Genc"},
        {"name": "Su Cilgin Turkler", "author": "Turgut Ozakman"},
        {"name": "Kar", "author": "Orhan Pamuk"}
    ]
}';
$data = json_decode($json, true);
echo $data["type"];
echo $data["genre"];
foreach($data["products"] as $product){
    var_dump($product);
    echo "<br/>";
}

Creating a CSV File Using PHP

In PHP, there are functions defined to create a CSV file. There are several advantages to using these functions instead of libraries like PhpExcel. It consumes less resources and is faster.

First, let’s create a function that will create our CSV file.

<?php

function array_to_csv_function($array, $filename = "export.csv", $delimiter=";") {
    // Instead of opening a temp file, we use memory space.
    $f = fopen('php://memory', 'w');
    // We're making a loop with an array which contains our data
    foreach ($array as $line) {
        // Each array in our array is a line in our CSV file.
        fputcsv($f, $line, $delimiter);
    }
    // File initialization is reset
    fseek($f, 0);
    // Php tells the browser that it is a csv file
    header('Content-Type: application/csv');
    // Php tells the browser not to display, to save. 
    header('Content-Disposition: attachment; filename="'.$filename.'";');
    // The generated CSV is transferred to the browser.
    fpassthru($f);
}

With this function, we can create a CSV file and output it to the browser by sending a series of file names to the function. I have explained each line in the code with comments. I made this function use memory space instead of using a temporary temp file. If you want to use a temporary temp file, replace $f = fopen(‘php: // memory’, ‘w’); with $f = fopen(‘php: // output’, ‘w’); command.

We then create an array that will be an array of lines, either by pulling it from the database or from a json data, and send it to our function. That’s all. As an example, I wrote the code below;

<?php

$titles = array("Name", "Surname");
$row1 = array("Erhan", "Kılıç");
$row2 = array("Onur", "Bakır");
$content = array($titles, $row1, $row1);

array_to_csv_function($content, "export.csv");

How To Make Responsive Table?

Everyone uses tables in their sites, but the tables look very bad on mobile devices. Even if you set the width of the table to 100%, it will be reduced to a certain extent and the texts will be very small. It is not useful at all. In this article I will show you the responsive table.

HTML Codes

Simple sample table codes.

<table>
   <thead>
     <tr>
       <th>First Name</th>
       <th>Last Name</th>
       <th>Job Title</th>
     </tr>
   </thead>
   <tbody>
     <tr>
       <td>James</td>
       <td>Matman</td>
       <td>Chief Sandwich Eater</td>
     </tr>
     <tr>
       <td>The</td>
       <td>Tick</td>
       <td>Crimefighter Sorta</td>
     </tr>
   </tbody>
</table>

Css Codes

General css codes for tables. You can customize the style yourself.

/* 
General Table Styles
*/
table { 
  width: 100%; 
  border-collapse: collapse; 
}
tr:nth-of-type(odd) { 
  background: #eee; 
}
th { 
  background: #333; 
  color: white; 
  font-weight: bold; 
}
td, th { 
  padding: 6px; 
  border: 1px solid #ccc; 
  text-align: left; 
}

Css Codes For Responsive Design

With these CSS codes and the @media query, your table will become responsive. What you need to be aware of here is the css codes with the comment “Table Headers” at the end. These css codes add columns before each <td> element. You need to customize this for each table.

/* 
With this media query we make my table responsive under 760px width. 
At the same time, it will also work in 768px and 1024px resolutions, 
improving the look on devices such as iPads.
*/
@media 
only screen and (max-width: 760px),
(min-device-width: 768px) and (max-device-width: 1024px)  {

    /* Force table to not be like tables anymore */
    table, thead, tbody, th, td, tr { 
        display: block; 
    }
    
    /* Hide column headers (but not display: none;) */
    thead tr { 
        position: absolute;
        top: -9999px;
        left: -9999px;
    }
    
    tr { border: 1px solid #ccc; }
    
    td { 
        / * Make it behave like a line * /
        border: none;
        border-bottom: 1px solid #eee; 
        position: relative;
        padding-left: 50%; 
    }
    
    td:before { 
        / * Edit column as if header exists * /
        position: absolute;
        /* Make room for the title on the left */
        top: 6px;
        left: 6px;
        width: 45%; 
        padding-right: 10px; 
        white-space: nowrap;
    }
    
    /*
    Table Headers
    Add column headers for each <td> element
    You need to customize each table.
    */
    td:nth-of-type(1):before { content: "First Name"; }
    td:nth-of-type(2):before { content: "Last Name"; }
    td:nth-of-type(3):before { content: "Job Title"; }
    td:nth-of-type(4):before { content: "Favorite Color"; }
    td:nth-of-type(5):before { content: "Wars of Trek?"; }
    td:nth-of-type(6):before { content: "Porn Name"; }
    td:nth-of-type(7):before { content: "Date of Birth"; }
    td:nth-of-type(8):before { content: "Dream Vacation City"; }
    td:nth-of-type(9):before { content: "GPA"; }
    td:nth-of-type(10):before { content: "Arbitrary Data"; }
}

Result

It will look like this under 760px width;