SQL Archives - Carlo Fontanos

How to install and use Neor Profiler SQL

The Neor Profiler SQL help developers profile their app connections to MySQL database. A lot of programmers face a situations where a large project requires optimization. This process is much more complicated if the code is confused and does not contain a single entry point for accessing the database. The tool resolves that problems.

Installation:

Install the software then open it.
By default, the software listens to port 4040, you can leave this as is.
- IMPORTANT: In your PHP database connection file, you need to change the host from “localhost” to “localhost:4040”
  - this way all query requests will pass by “localhost:4040” first, then will be routed to “localhost:3306”

Usage:

Go to “Sessions” tab
Then open a page in your localhost website that you want to track queries (just load the page once)
Go back to Neor Profiler SQL, you will notice that new sessions were added.
- you can double click on a session to see the queries that executed in a new tab.
  - in the NEW TAB, you will see a graph at the top, and bellow it are the queries that executed
    - the graph shows the query execution. The higher the point, the slower the execution time. So you need to focus on optimizing these high points
    - you can also click on the high point of the graph and to show the query responsible.
    - in the table of queries you can sort the results by clicking the column title.
    - there is also an “execute” button if you want to run a specific query.
  - if you have a page that is ajax loaded, the ajax call will be recorded as another session.
Before capturing another page, use the “Clear All” button on the top right to remove all sessions.

CRUD Query using JSON Column

Requirements: MySQL 5.7.9

SELECT examples:

mysql> SELECT *
FROM users
WHERE meta_data->"$.first_name" = 'bob'

You might notice your json data results are “quoted”. You could use JSON_UNQUOTE,
or you could use this “>>”, which is a shortcut of JSON_EXTRACT & JSON_UNQUOTE:

mysql> SELECT post_data_json->>"$._price" AS price
FROM wp_posts
WHERE post_data_json->>"$.post_title" LIKE '%Ninja%'

Select data from within sub objects:3

mysql> SELECT meta_data->>"$.address.tel"
FROM users
WHERE meta_data->>"$.address.street" = "123 Main St"

Search for strings like with the use of lower() function:

mysql> SELECT post_data_json->>"$._price" AS price
FROM wp_posts
WHERE lower(post_data_json->>"$.post_title") LIKE '%ninja%'

UPDATE and DELETE examples:

Update a specific key:

mysql> UPDATE wp_posts
SET post_data_json = JSON_SET(post_data_json, "$.post_title", "I am ID1")
WHERE ID = 1;

Update multiple keys:

mysql> UPDATE wp_posts
SET post_data_json = JSON_SET(post_data_json, "$.post_title", "I am ID 01", "$.post_name", "I-am-ID-01")
WHERE ID = 1;

Append a new key-value pair to existing JSON:

mysql> UPDATE wp_posts
SET post_data_json = JSON_SET(post_data_json, "$.something", "I am something")
WHERE ID = 1;

Delete key-value pair from existing JSON:

mysql> UPDATE wp_posts
SET post_data_json = JSON_REMOVE(post_data_json, "$.something")
WHERE ID = 1;

INSERT examples:

There’s no special function to use for creating a json object, just do the traditional SQL style:

mysql> INSERT INTO person
VALUES ('{"pid": 101, "name": "name1"}');

Using REPLACE in an UPDATE statement

REPLACE takes in three strings as arguments and returns a string as a result.

1	REPLACE(item1, item2, item3)

item1 is our original string. item2 is the subtring we want replaced. item2 is the string that will replace item2 is the resultant string. This resultant string is represented by the REPLACE function itself. The following example shows how the REPLACE function works.

Let’s say you have thousands of posts that contain your site’s link, ex: http://example.com and you want to change it to a sub-domain link, ex: http://sub.example.com, the SQL command bellow will do exactly what we want to achieve:

1	UPDATE posts_table SET content = replace(content, 'http://example.com', 'http://sub.example.com') WHERE content LIKE '%http://sub.example.com%';

posts_table – Name of the table that contains all your posts
content – Column name that contains the contents of your posts

Simple Database Connection using MySQLi OOP and Procedural

OOP Approach

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

<?php
$server_name = 'localhost';
$username = 'root';
$password = '';
$db_name = 'carlofontanos';

$conn = new mysqli($server_name, $username, $password, $db_name);

if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}

$sql = 'SELECT * FROM wp_posts WHERE post_type = "post"';
$result = $conn->query($sql);

if ($result->num_rows > 0) {

while($row = $result->fetch_object()) {
echo 'Post ID: ' . $row->ID. ' - Title: ' . $row->post_title. ' Date Uploaded: ' . $row->post_date. '<br>';
}

} else {
echo 'No Posts Found';
}
$conn->close();

Procedural Approach

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php
$server_name = 'localhost';
$username = 'root';
$password = '';
$db_name = 'carlofontanos';

$conn = mysqli_connect($server_name, $username, $password, $db_name);

if (!$conn) {
die('Connection failed: ' . mysqli_connect_error());
}

$sql = 'SELECT * FROM wp_posts WHERE post_type = "post"';
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

while($row = mysqli_fetch_object($result)) {
echo 'Post ID: ' . $row->ID. ' - Title: ' . $row->post_title. ' Date Uploaded: ' . $row->post_date. '<br>';
}

} else {
echo 'No Posts Found';
}

mysqli_close($conn);

Category

Category: SQL