affluentnk-blog

[affluentnk-blog]

MySQLi - Using Joins

MySQLi – Using Joins

Thus far, we have only been getting data from one table at a time. This is fine for simple takes, but in most real world MySQL usage, you will often need to get data from multiple tables in a single query.

You can use multiple tables in your single SQL query. The act of joining in MySQLi refers to smashing two or more tables into a single table.

You can use JOINS in SELECT, UPDATE and DELETE…

[affluentnk-blog]

MySQLi - Transactions

MySQLi – Transactions

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Practically, you will club many SQL queries into a group and you will…

[affluentnk-blog]

MySQLi - INDEXES

MySQLi – INDEXES

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

While creating index, it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.

Practic…

[affluentnk-blog]

MySQLi - Sorting Results

MySQLi – Sorting Results

We have seen SQL SELECT command to fetch data from MySQLi table. When you select rows, the MySQLi server is free to return them in any order, unless you instruct it otherwise by saying how to sort the result. But you sort a result set by adding an ORDER BY clause that names the column or columns you want to sort by.

Syntax

Here is generic SQL syntax of SELECT command along with ORDER BY clause to…

[affluentnk-blog]

MySQLi - Clone Tables

MySQLi – Clone Tables

There may be a situation when you need an exact copy of a table and CREATE TABLE … SELECT doesn’t suit your purposes because the copy must include the same indexes, default values, and so forth.

You can handle this situation by following steps −

Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table’s structure, indexes and all.

Modify the statement to change the…

[affluentnk-blog]

MySQLi - ALTER Command

MySQLi – ALTER Command

MySQLi ALTER command is very useful when you want to change a name of your table, any table field or if you want to add or delete an existing column in a table.

Let’s begin with creation of a table called tutorials_alter.

root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tutorials_alter -> ( -> i INT, -> c CHAR(1) -> ); Query…

[affluentnk-blog]

MySQLi - Regexps

MySQLi – Regexps

You have seen MySQLi pattern matching with LIKE …%. MySQLi supports another type of pattern matching operation based on regular expressions and the REGEXP operator. If you are aware of PHP or PERL, then it’s very simple for you to understand because this matching is very similar to those scripting regular expressions.

Following is the table of pattern, which can be used along with REGEXP operator.

[affluentnk-blog]

MySQLi - Temporary Tables

MySQLi – Temporary Tables

The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.

As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when…

[affluentnk-blog]

MySQLi - Handling NULL Values

MySQLi – Handling NULL Values

We have seen SQL SELECT command along with WHERE clause to fetch data from MySQLi table, but when we try to give a condition, which compare field or column value to NULL, it does not work properly.

To handle such situation MySQLi provides three operators

IS NULL − operator returns true if column value is NULL.

IS NOT NULL − operator returns true if column value is not NULL.

<=> − operator…

[affluentnk-blog]

MySQLi - DELETE Query

MySQLi – DELETE Query

If you want to delete a record from any MySQLi table, then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax

Here is generic SQL syntax of DELETE command to delete data from a MySQLi table −

DELETE FROM table_name [WHERE Clause]

If WHERE clause is not specified, then all the records will be deleted from the given MySQLi table.

[affluentnk-blog]

MySQLi - LIKE Clause

MySQLi – LIKE Clause

We have seen SQL SELECT command to fetch data from MySQLi table. We can also use a conditional clause called WHERE clause to select required records.

A WHERE clause with equals sign (=) works fine where we want to do an exact match. Like if “name = ‘sai'”. But there may be a requirement where we want to filter out all the results where name should contain “johar”. This can be handled using SQL LI…

[affluentnk-blog]

MySQLi Tutorial

MySQLi is a Relational SQL database management system. MySQLi is used inside the PHP programming language to give an interface with MySQL databases. This tutorial will give you quick start with MySQLi and make you comfortable with MySQLi programming.

Audience

This reference has been prepared for the beginners to help them understand the basics to advanced concepts related to MySQLi languages.

Pr…

[affluentnk-blog]

MySQLi - Introduction

MySQLi – Introduction

What is Database?

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

Other kinds of data stores can be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those types of…

[affluentnk-blog]

MySQLi - Installation

MySQLi – Installation

Downloading MySQL

The MySQLi extension is designed to work with MySQL version 4.1.13 or newer, So have to download MySQL. All downloads for MySQL are located at MySQL Downloads. Pick the latest version number for MySQL Community Server you want and, as exactly as possible, the platform you want.

Installing MySQL on Linux/UNIX

The recommended way to install MySQL on a Linux system is via RPM.…

[affluentnk-blog]

MySQLi - Administration

MySQLi – Administration

Running and Shutting down MySQLi Server

MySQLi is extended of MySQL so first check if your MySQL server is running or not. You can use the following command to check this −

ps -ef | grep mysqld

If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command−

root@host# cd /usr/bin ./safe_mysqld &

[affluentnk-blog]

MySQLi - PHP Syntax

MySQLi – PHP Syntax

MySQLi works very well in combination of various programming languages like PERL, C, C++, JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities.

This tutorial focuses heavily on using MySQLi in a PHP environment. PHP provides various functions to access MySQLi database and to manipulate data records inside MySQLi database. You…

[affluentnk-blog]

MySQLi - Connection

MySQLi – Connection

MySQLi Connection using mysql binary

You can establish MySQLi database using mysql binary at command prompt.

Example

Here is a simple example to connect to MySQL server to establish mysqli database from command prompt −

[root@host]# mysql -u root -p Enter password:******

This will give you mysql> command prompt where you will be able to execute any SQL command. Following is the result of above…