Tag Archives: sql

MySQL 5.7 Reference Manual

My SQL

Nowadays, MySQL is the most popular database system used with PHP among so many developers. PHP combined with MySQL are cross-platform which you can develop in Windows and serve on a Unix platform. Basically, tables stores the data in a MySQL database. A table consists columns and rows which is a collection of related data. Databases are useful for storing information categorically. My SQL is an open source relational database management system (RDBMS).  So, whether you are a mobile or web developer, it is never too late to learn more about MySQL 5.7. Here are the details and don’t hesitate to check them out!

MySQL has supported for full-text indexing and searching, such as follows:

  • A full-text index in MySQL is an index of type FULLTEXT.
  • Only with InnoDB or MyISAM tables, you can create Full-text indexes, and can be created only for CHAR, VARCHAR, or TEXT
  • As of MySQL 5.7.6, My SQL provides a built-in full-text ngram parser. It supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese. Parsing differences are outlined in Section 13.9.8, “ngram Full-Text parser”. Then, Section 13.9.9, “MeCab Full-Text Parser Plugin”.
  • CREATE TABLE provides A FULLTEXT index definition or adds later using ALTER TABLE orCREATE INDEX.
  • To load large data sets faster, you can use a table that has no FULLTEXT index. Then, create the index to load data into a table that has an existing FULLTEXT index.

By using  MATCH() … AGAINST syntax, you can perform full-text searching. MATCH() takes a comma-separated list that names the columns to be searched. You can use AGAINST to take a string and an optional modifier that indicates what type of search to perform. Create search string that is constant during query evaluation through string value. This rules out, for example, a table column because that can differ for each now.

There are three types of full-text searches:

  • A natural language search interprets search string as a phrase in natural human language. There are no special operators. The stop word list applies if the modifier gives or not give the IN NATURAL LANGUAGE MODE. Full-text searches are natural language searches.
  • Using the rules of a special query language, a Boolean search interprets the search string. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows. It also should be weighted higher or lower than usual. In Boolean search interprets, search index will omit certain common words. Besides, some words do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a Boolean search.
  • A modification of a natural language search is a query expansion. To perform a natural language search, you can use search string. After adding the words to the search string, then words from the most relevant rows returned by the search. The search is done again. From the second search, the query returns the rows.

PHP Prepared Statements

Print

Somehow for web developers, doing a query can be more complicated if it should face a large amount of data since you may have to repeat the same query for several times. Therefore, if you would like to input a large amount of database into your query, you can try using prepared statements to optimize your query process and prevent hacker from corrupting your database through SQL injection method. Besides, in order to execute the same (or similar) SQL statement repeatedly with high efficiency, a prepared statement is the best feature for it which it basically works like this:

  1. Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled “?”). Example: INSERT INTO MyGuests VALUES(?,?,?)
  2. The query optimization on the SQL statement template are parsed, compiled, and performed by the database which in the end the result will be stored without the need to execute it.
  3. Execute: In the future, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values.

There are two main advantages that prepared statements can offer when it is compared to executing SQL statements directly:

  • This technique will be suitable if you wish to reduce parsing time since you only need to make the preparation on the query once.
  • As you need send only the parameters each time and not the whole query, bound parameters will minimize bandwidth to the server.
  • Because parameter values, which are transmitted later using a different protocol, need not be correctly escaped, prepared statements are very useful against SQL injections. Besides, SQL injection cannot occur if the original statements template is not derived from external input.

Prepared Statements in MySQLi

The following example uses prepared statements and bound parameters in MySQLi:

Example (MySQLi with Prepared Statements)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDB”;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare(“INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)”);
$stmt->bind_param(“sss”, $firstname, $lastname, $email);

// set parameters and execute
$firstname = “John”

$lastname = “Doe”;
$email = “john@example.com”;
$stmt->execute();

$firstname = “Mary”;
$lastname = “Moe”;
$email = “mary@example.com”;
$stmt->execute();

$firstname = “Julie”;
$lastname = “Dooley”;
$email = “julie@example.com”;
$stmt->execute();

echo “New records created successfully”;

$stmt->close();
$conn->close();
?>

Code lines to explain from the example above:

“INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)”

We insert a question mark (?) in our SQL where we want to substitute in an integer, string, double or blob value.

Then, have a look at the bind_param() function:

$stmt->bind_param(“sss”, $firstname, $lastname, $email);

This function binds the parameters to the SQL query and tells the database what the parameters are. The “sss” argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

  • i-integer
  • d-double
  • s-string
  • b-BLOB

We must have one of these for each parameter. By telling mysql what type of data to expect, we minimize the risk of SQL injections.

Prepared Statements in PDO

The following example uses prepared statements and bound parameters in PDO:

Example (PDO with Prepared Statements)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDBPDO”;

try {
    $conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 // prepare sql and bind parameters
    $stmt = $conn->prepare(“INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES (:firstname, :lastname, :email)”);
    $stmt->bindParam(‘:firstname’, $firstname);
    $stmt->bindParam(‘:lastname’, $lastname);
    $stmt->bindParam(‘:email’, $email);

    // insert a row
    $firstname = “John”;
    $lastname = “Doe”;
    $email = “john@example.com”;
    $stmt->execute();

    // insert another row
    $firstname = “Mary”;
    $lastname = “Moe”;
    $email = “mary@example.com”;
    $stmt->execute();

// insert another row
    $firstname = “Julie”;
    $lastname = “Dooley”;
    $email = “julie@example.com”;
    $stmt->execute();

    echo “New records created successfully”;
    }
catch(PDOException $e)
    {
    echo “Error: ” . $e->getMessage();
    }
$conn = null;
?>

The Easy Way to Mass Find & Replace WordPress Databases

The Easy Way to Mass Find & Replace WordPress Databases

Web developers who have an experience with changing WordPress url address would know that after migrating the database, URLs inside of it will still refer to the old site. However, WordPress is widely known as a web platform that has a ton of plugins and therefore there are many tools that can assist you to import database easily. But, a tricky method is necessary for requiring the shortcut of changing every URL instance inside the database.

As it is described in the picture below that the old URL in the wp_options table, set as the value of the siteurl and home options, and it’s probably also embedded throughout several other rows and tables in the database. If you find this kind of old URLs may eventually prevent your site from running properly, then you need to change to the new URL, such as below:

yourweb1

In the image above, you will find that the site acme.dev will simply lead to a blank page. Below is given a technique to change all these URLs in the database.

Leveraging WP-CLI

Leveraging WP-CLI is not the only way to change the URLs in the database, you can run an SQL query to replace it, but I find that leveraging WP-CLI is a handier alternative compare to SQL query which is a workable solution, yet at the same time, is not convenient to do. Assuming you have installed WP-CLI and have the wp command accessible as the alias, navigate to the directory where your WordPress site files reside.

Then, run the following command:

wp search-replace ‘http://acme.com’ ‘http://acme.dev

The first parameter, ‘http://acme.com’, is the old entry to be replaced with the second one, ‘http://acme.dev’.

yourweb2

Not only the post_content column and wp_options table but also all the tables within the database will be searched by the command line. Then, it will replace every instance of the entry passed through the parameters of the command, like it is explained by the above screenshot that a total of 225 have been made through a simple line of command.

Moreover, we can use the wp search-replace command, not only for replacing URLs, but also any piece of value stored in the database. The operation can also be limited into a certain table by passing the table name as the fourth parameter, as follows:

wp search-replace ‘.jpg’ ‘.webp’ wp_posts

Bear in mind that taking this action means you have to install the WP-CLI first and it will replace the image extension from .jpg to .webp.