MySQL – PHP Syntax Table of content PHP MySQLi Library How to Install MySQLi PHP Functions to Access MySQL Basic Example ”; Previous Next Various programming languages like PERL, C, C++, JAVA, PHP, etc. are used as client programs to request query executions on a MySQL Server. Out of these languages, PHP is the most popular one because of its web application development capabilities. A PHP library is like a toolbox for web developers, providing pre-built functions and code snippets to simplify common tasks. It saves time and effort by offering ready-made solutions for tasks such as handling databases, processing forms, and managing files. Developers can easily include these libraries in their PHP projects to boost efficiency and create robust web applications. PHP MySQLi Library The MySQL PHP connector, often referred to as MySQLi, enables communication between PHP scripts and MySQL databases. It provides a set of functions and methods that allow PHP applications to connect, query, and manipulate data in MySQL databases, providing efficient and secure database interactions in PHP web development. This tutorial focuses on using MySQL in a various environments. If you are interested in MySQL with PERL, then you can consider reading the PERL Tutorial. How to Install MySQLi To install MySQLi on Windows, you can follow these general steps − Download PHP: Download the latest version of PHP from the official PHP website (https://www.php.net/downloads.php). Choose the Windows version that matches your system architecture (e.g., 32-bit or 64-bit). Download the ZIP file for the Thread Safe or Non-Thread Safe version, depending on your needs. Extract the ZIP File: Extract the contents of the downloaded ZIP file to a location on your computer (e.g., C:php). Configure PHP: In the extracted PHP folder, find the “php.ini-development” file. Copy and rename it to “php.ini”. Open “php.ini” in a text editor (e.g., Notepad) and find the line: “;extension=mysqli”. Remove the semicolon (;) at the beginning of the line to uncomment it: “extension=mysqli”. Save the php.ini file. Set Environment Variables: Add the PHP installation directory to the system”s PATH environment variable. This allows you to run PHP from any command prompt. To do this, right-click on “This PC” or “Computer” on your desktop or in File Explorer, select “Properties,” and click on “Advanced system settings.” Then, click on the “Environment Variables” button. In the “System variables” section, select the “Path” variable and click “Edit.” Add the path to your PHP installation directory (e.g., C:php). Restart your Web Server: If you are using a web server like Apache or Nginx, restart it to apply the changes. Verify Installation: Create a PHP file with the following content and save it in your web server”s document root (e.g., C:Apache24htdocs for Apache): <?php phpinfo(); ?> Open the file in your web browser and search for “mysqli” to verify that the MySQLi extension is now enabled. PHP Functions to Access MySQL PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database − S.No Function & Description 1 mysqli_connect() Establishes a connection to the MySQL server. 2 mysqli_query() Performs a query on the database. 3 mysqli_fetch_assoc() Fetches a result row as an associative array. 4 mysqli_fetch_array() Fetches a result row as an associative array, a numeric array, or both. 5 mysqli_close() Closes a previously opened database connection. 6 mysqli_num_rows() Gets the number of rows in a result. 7 mysqli_error() Returns a string description of the last error. 8 mysqli_prepare() Used for prepared statements to help prevent SQL injection. 9 mysqli_fetch_row() Fetches a result row as an enumerated array. 10 mysqli_insert_id() Gets the ID generated in the last query. Basic Example Following are the steps to connect to a MySQL database, execute a query, process the results, and close the connection using PHP and MySQLi − Define the parameters needed to connect to your MySQL database, such as ”$dbhost” (host name), ”$dbuser” (username), ”$dbpass” (password), and ”$dbname” (database name). Create a new instance of the ”mysqli” class to establish a connection to the MySQL database. Use the ”query” method of the ”mysqli” object to execute a MySQL query. Fetch and process the results returned by the query. Close the connection to the MySQL database when you are done. The following example shows a generic syntax of PHP to call any MySQL query. <html> <head> <title>PHP with MySQL</title> </head> <body> <?php // Include database connection parameters $dbhost = “localhost”; $dbuser = “your_username”; $dbpass = “your_password”; $dbname = “your_database”; // Establish a connection to MySQL $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_error) { die(“Connection failed: ” . $mysqli->connect_error); } // Execute a MySQL query $sql = “SELECT * FROM your_table”; $result = $mysqli->query($sql); if (!$result) { die(“Error: ” . $mysqli->error); } // Process the query results while ($row = $result->fetch_assoc()) { // Process each row of data echo “ID: ” . $row[“id”] . ” Name: ” . $row[“name”] . “<br>”; } // Close the database connection $mysqli->close(); ?> </body> </html> Print Page Previous Next Advertisements ”;
Category: mysql
MySQL – Introduction
MySQL – Introduction Table of content What is a Database? RDBMS Terminology MySQL Database History of MySQL Before You Begin ”; Previous Next What is a Database? A database is used to store a collection of data (which can either be structured or unstructured). Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds. Other kinds of data storages can also be used to manage data, 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 type of systems. Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. In such a database, the data is stored in a structured way with the help of different tables. Relations are established among these tables using primary keys or other keys known as Foreign Keys. A Relational DataBase Management System (RDBMS) is a software that − Enables you to implement a database with tables, columns and indexes. Guarantees the Referential Integrity between rows of various tables. Updates the indexes automatically. Interprets an SQL query and combines information from various tables. RDBMS Terminology Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database. Database − A database is a collection of tables, with related data. Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet. Column − One column (data element) contains data of one and the same kind, for example the column postcode. Row − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription. Redundancy − Storing data twice, redundantly to make the system faster. Primary Key − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row. Foreign Key − A foreign key is the linking pin between two tables. Compound Key − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique. Index − An index in a database resembles an index at the back of a book. Referential Integrity − Referential Integrity makes sure that a foreign key value always points to an existing row. MySQL Database MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons − MySQL is released under an open-source license. So you have nothing to pay to use it. MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages. MySQL uses a standard form of the well-known SQL data language. MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc. MySQL works very quickly and works well even with large data sets. MySQL is very friendly to PHP, the most appreciated language for web development. MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB). MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments. History of MySQL Development of MySQL by Michael Widenius & David Axmark beginning in 1994. First internal release on 23rd May 1995. Windows Version was released on the 8th January 1998 for Windows 95 and NT. Version 3.23: beta from June 2000, production release January 2001. Version 4.0: beta from August 2002, production release March 2003 (unions). Version 4.1: beta from June 2004, production release October 2004. Version 5.0: beta from March 2005, production release October 2005. Sun Microsystems acquired MySQL AB on the 26th February 2008. Version 5.1: production release 27th November 2008. Oracle acquired Sun Microsystems on 27th January 2010. Version 5.5: general availability on 3rd December 2010 Version 5.6: general availability on 5th February 2013 Version 5.7: general availability on 21st October 2015 Version 8.0: general availability on 19th April 2018 Before You Begin Before you begin this tutorial, you should have a basic knowledge of the information covered in our PHP and HTML tutorials. This tutorial focuses heavily on using MySQL in a PHP environment. Many examples given in this tutorial will be useful for PHP Programmers. We recommend you check our PHP Tutorial for your reference. Print Page Previous Next Advertisements ”;
MySQL – Node.js Syntax
MySQL – Node.js Syntax Table of content Installation “mysql” package NodeJS Functions to Access MySQL Basic Example ”; Previous Next Node.js is a JavaScript runtime environment that allows developers to run JavaScript code outside of a web browser, enabling server-side scripting. When we talk about a Node.js MySQL connector, we are referring to a specific library that facilitates communication between a Node.js application and a MySQL database. This connector enables developers to interact with a MySQL database by providing methods and functionalities that simplify tasks like querying, updating, and managing data within the database using JavaScript code. Essentially, it acts as a bridge, allowing Node.js applications to seamlessly connect with and manipulate data stored in a MySQL database. Installation “mysql” package To use MySQL with Node.js, you can use the “mysql” package, which is a popular MySQL driver for Node.js. Here are the steps to install Node.js and the MySQL package − Step 1: Install Node.js Visit the official Node.js website (https://nodejs.org/) and download the latest version of Node.js for your operating system. Follow the installation instructions provided on the website. Step 2: Create a Node.js Project Create a new directory for your Node.js project and navigate to it using your terminal or command prompt. mkdir mynodeproject cd mynodeproject Step 3: Initialize a Node.js Project Run the following command to initialize a new Node.js project. This will create a ”package.json” file. npm init -y Step 4: Install the MySQL Package Install the “mysql” package using the following command: npm install mysql Step 5: Create a JavaScript File Create a JavaScript file (e.g., app.js) in your project directory. Step 6: Run the Node.js Script Run your Node.js script using the following command: node app.js Now, you have successfully installed the MySQL Node.js connector (mysql package) for your Node.js project. NodeJS Functions to Access MySQL In Node.js, the “mysql” package provides a set of functions to interact with MySQL databases. Here are some of the major functions you can use− S.No Function & Description 1 createConnection(config) Creates a new MySQL connection. 2 connect(callback) Establishes a connection to the MySQL server. 3 query(sql, values, callback) Executes a SQL query on the connected MySQL database. You can provide placeholders in the SQL query and pass values as an array to replace the placeholders. 4 execute(sql, values, callback) Similar to the query function, but specifically designed for executing non-select queries (e.g., INSERT, UPDATE, DELETE). 5 beginTransaction(callback) Starts a new transaction. 6 commit(callback) Commits the current transaction. 7 rollback(callback) Rolls back the current transaction. 8 end() Closes the MySQL connection. Basic Example Following are the steps to connect and communicate with a MySQL database using Node.js − Download and install Node.js Create a new directory, navigate to it, and run ”npm init -y”. Run ”npm install mysql”. Create a JavaScript file (e.g., app.js) and use the “mysql” package to connect to the MySQL database. Use the query or execute functions to perform SQL queries on the database. Implement error handling for database operations. Close the database connection when finished. Execute your Node.js script with node app.js. The following example shows a generic syntax of NodeJS to call any MySQL query. const mysql = require(“mysql2″); // Create a connection to the MySQL database const connection = mysql.createConnection({ host: ”your-mysql-hostname”, user: ”your-mysql-username”, password: ”your-mysql-password”, database: ”your-mysql-database”, }); // Connect to the database connection.connect((err) => { if (err) { console.error(”Error connecting to MySQL:”, err); return; } console.log(”Connected to MySQL database”); // Perform MySQL operations here connection.query(“SELECT * FROM your_table”, (err, results) => { if (err) throw err; console.log(”Query result:”, results); }); // Close the connection when done connection.end((err) => { if (err) console.error(”Error closing MySQL connection:”, err); else console.log(”Connection closed”); }); }); Print Page Previous Next Advertisements ”;
MySQL – Versions
MySQL – Versions Table of content MySQL Versions Features Added in MySQL 8.0 Features Deprecated in MySQL 8.0 ”; Previous Next Versions are introduced in any product to upgrade by adding extra features and removing unnecessary ones, fixing the bugs etc. The process of versioning is actually important to make the product more efficient with growing technology. A product is generally released after performing phases of testing: alpha testing, beta testing, gamma testing, and then it is produced once all these tests are passed. Whenever MySQL is installed, we must choose the version to install along with its distribution format. The latest version of MySQL is 8.0 with its minor version being 8.0.34. To install the MySQL server, there are two methods: Development release and General Availability. The Development Release contains all the latest features but it is not recommended to be used in production. The General Availability Release is more of a production release which can be stably used in production. MySQL Versions MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL was first internally released on 23rd May, 1995, and until Oracle acquired Sun Microsystems, version 3.19 to version 5.1 were released. Version 5.1 The version 5.1 of MySQL had its production release on 27th November, 2008 by adding extra features like event scheduler, partitioning, plugin API, row-based replication, server log tables. But, version 5.1 contained 20 known bugs that gave out wrong results, along with the 35 bugs from version 5.0. However, almost all of them are fixed as of release 5.1.51. Also, MySQL 5.1 and 6.0 (in alpha test phase) showed poor performance in data warehousing, which could partially be due to its inability to utilize multiple CPU cores for processing a single query. Version 5.5 MySQL Server 5.5 was a General Availability Release as of December 2010. The improved features in this version include − The default storage engine is InnoDB with improved I/O subsystem, which supports transactions and referential integrity constraints. Improved SMP support Semi-synchronous replication. SIGNAL and RESIGNAL statement was added in compliance with the SQL standard. Support for supplementary Unicode character sets utf16, utf32, and utf8mb4. New options for user-defined partitioning. Version 5.6 The General Availability of MySQL 5.6 was released in February 2013. New features of this version included: Query optimizer has more efficient performance. Higher transactional throughput in InnoDB. New NoSQL-style memcached APIs. Improvements to partitioning for querying large tables. Better management of very large tables. TIMESTAMP column type that correctly stores milliseconds. Improvements to replication. Better performance monitoring by expanding the data available through the PERFORMANCE_SCHEMA. The InnoDB storage engine also provides support for full-text search and improved group commit performance. Version 5.7 MySQL 5.7 was made generally available in October 2015. For the minor versions of MySQL 5.7, MySQL 5.7.8 and later, a support for a native JSON data type defined by RFC 7159 by August 2015. Version 8.0 MySQL Server 8.0 was announced in April 2018, with new improved features. Currently, the minor versions in MySQL 8.0 start from 8.0.0 to 8.0.34. Previous MySQL Server 8.0.0-dmr (as a Development Milestone Release) was announced on 12th September, 2016. Features Added in MySQL 8.0 The latest version of MySQL is 8.0. The following features are some of the newly added features to it: Data dictionary − In previous MySQL releases, dictionary data was stored in metadata files and non-transactional tables. MySQL now incorporates a transactional data dictionary that stores information about database objects. Atomic Data Definition Language(Atomic DDL) statements − An Atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. Upgrade procedure − Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the ”sys” schema and user schemas. As of MySQL 8.0.16, the server also performs the tasks previously handled by mysql_upgrade. In addition, the server updates the contents of the help tables as well. A new –upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations. Session Reuse − MySQL Server now supports SSL session reuse by default with a timeout setting to control how long the server maintains a session cache that establishes the period during which a client is permitted to request session reuse for new connections. All MySQL client programs support session reuse. In addition, C applications now can use the C API capabilities to enable session reuse for encrypted connections. Security and account management − The security is improved greatly and greater DBA flexibility in account management is also enabled. Resource management − MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group. Table encryption management − Table encryption can now be managed globally by defining and enforcing encryption defaults. InnoDB enhancements − Several InnoDB enhancements were added, like, auto-increment counter value, index tree corruption, mem-cached plug-in, InnoDB_deadlock_detect, tablespace encryption feature, storage engine, InnoDB_dedicated_server, creating temporary tables in temporary tablespace, zlib library etc. Character set support − The default character set has changed from latin1 to utf8mb4. The utf8mb4 character set has several new collations, including utf8mb4_ja_0900_as_cs, the first Japanese language-specific
MySQL – Variables
MySQL – Variables Table of content Variables in MySQL User-Defined Variables Local Variables System Variables ”; Previous Next In general, variables are the containers that store some information in a program. The value of a variable can be changed as many times as required. Each variable has a datatype specifying the type of data we can store in it such as integer, string, float etc. In some programming languages such as Java, C, C++ etc., we need to declare the datatype of a variable before assigning values to it. In languages like python the datatype of a variable is presumed based on the values assigned to it. There is no need of declaring the datatype separately. In MySQL there is no need to declare the datatype we can simply define a variable with a value using the SET statement. Variables in MySQL The main purpose of a variable is to label a memory location(s) and store data in it so that it can be used throughout the program. The characters we use to declare and define a variables are called literals and a literal can be anything other than special characters, numbers and, reserved keywords. In MySQL, there are three types of variables. The same is described below − User-Defined Variable Local Variable System Variable User-Defined Variables The User-Defined variable allows us to store a value in one statement and subsequently refer to it in another. To do so, MySQL provides SET and SELECT commands to declare a variable. These variable names will have the symbol “@” as a prefix. We can use either = or := symbols depending on the situation. The user-defined data type can be any of the following: integer, decimal, Boolean, etc. Syntax Following is the syntax to declare a user-defined variable in MySQL using the SET statement − SELECT @variable_name = value Example In the following query, we are assigning a value to a variable using the SET statement as follows − SET @Name = ”Michael”; Using the SELECT statement, we can display the value of @name variable − SELECT @Name; Output The output for the query above is produced as given below − @Name Michael Example Here, we are assigning a value to a variable using the SELECT statement − SELECT @test := 10; Output On executing the given query, the output is displayed as follows − @test := 10 10 Example Let us create table with the name CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Now, let us insert values into the above-created table using the INSERT INTO statement − INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES (”Ramesh”, 32, ”Ahmedabad”, 2000.00), (”Khilan”, 25, ”Delhi”, 1500.00), (”Kaushik”, 23, ”Kota”, 2000.00), (”Chaitali”, 25, ”Mumbai”, 6500.00), (”Hardik”, 27, ”Bhopal”, 8500.00), (”Komal”, 22, ”Hyderabad”, 4500.00), (”Muffy”, 24, ”Indore”, 10000.00); The CUSTOMERS table is created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, let us declare a variable with the name @max_salary using the SELECT statement to display the maximum salary value from the CUSTOMERS table − SELECT @max_salary := MAX(salary) FROM CUSTOMERS; Then, we will select records from the table where the salary is equal to @max_salary variable − SELECT * FROM CUSTOMERS WHERE SALARY = @max_salary; Output The output for the query above is produced as given below − ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 10000.00 Local Variables The MySQL local variable can be declared using the DECLARE keyword. When we declare the local variable, the @ symbol is not used as prefix. This variable is a strongly typed variable, which means that we definitely need to declare a data type. The MySQL DEFAULT keyword can be used while declaring a variable to set the default value of the variable. This is an optional parameter, if we do not define this, the initial value will be NULL. Syntax Following is the syntax to declare a local variable in MySQL − DECLARE variable_name1, variabale_name2, … data_type [DEFAULT default_value]; Example In the following example, we are using the DECLARE statement in a stored procedure. DELIMITER // CREATE PROCEDURE salaries() BEGIN DECLARE Ramesh INT; DECLARE Khilan INT DEFAULT 30000; DECLARE Kaushik INT; DECLARE Chaitali INT; DECLARE Total INT; SET Ramesh = 20000; SET Kaushik = 25000; SET Chaitali = 29000; SET Total = Ramesh+Khilan+Kaushik+Chaitali; SELECT Total,Ramesh,Khilan,Kaushik,Chaitali; END // Now, let us call the stored procedure using the following query − CALL salaries() //; Output Following is the output − Total Ramesh Khilan Kaushik Chaitali 104000 20000 30000 25000 29000 System
MySQL – Python Syntax
MySQL – Python Syntax Table of content Installing “python-mysql” connector Python Functions to Access MySQL Basic Example ”; Previous Next The MySQL-Python connector specifically refers to a library in Python that enables communication between a Python program and a MySQL database. It acts as a bridge, allowing Python programs to interact with and manipulate data stored in a MySQL database. Essentially, the MySQL-Python connector simplifies the process of connecting, querying, and managing databases, enabling developers to seamlessly integrate their Python applications with MySQL databases. Installing “python-mysql” connector To use MySQL with Python, you typically need to install a MySQL connector or library. Here are the general steps to install it − Step 1: Install MySQL Server Make sure you have MySQL Server installed on your machine or have access to a remote MySQL server. Step 2: Install MySQL Connector for Python Open a command prompt or terminal and use the following command to install the MySQL Connector for Python using pip, which is the package installer for Python: pip install mysql-connector-python If you are using Python 3, you might need to use ”pip3” instead of ”pip”. Step 3: Verify Installation After the installation is complete, you can verify that the library is installed by opening a Python interactive shell and trying to import the connector: import mysql.connector Python Functions to Access MySQL When working with MySQL in Python, the ”mysql-connector-python” library provides various functions to interact with a MySQL database. Here are some important functions commonly used − S.No Function & Description 1 connect() Establishes a connection to the MySQL server. 2 cursor() Creates a cursor object to execute SQL queries. 3 execute(query, params=None) Executes a SQL query. ”params” is an optional parameter for query parameters. 4 fetchone() Fetches the next row from the result set. 5 fetchall() Fetches all rows from the result set. 6 commit() Commits the current transaction to the database. 7 rollback() Rolls back the current transaction, undoing any changes since the last commit. 8 close() Closes the cursor and the connection to the database. 9 executemany() Executes a SQL command against all parameter sequences in the provided list. Basic Example To connect and communicate with a MySQL database using Python, you can follow these steps − Use ”pip install mysql-connector-python” to install the MySQL Connector for Python. Import the MySQL Connector module in your Python script: “import mysql.connector”. Create a connection using “mysql.connector.connect()” with your database details. Create a cursor using “connection.cursor()”. Use the cursor”s “execute()” method to run SQL queries. If applicable, use “fetchone()” or “fetchall()” to retrieve query results. If you modify data, commit changes using “connection.commit()”. Close the cursor and connection with “cursor.close()” and “connection.close()”. The following example shows a generic syntax of a Python program to call any MySQL query − import mysql.connector # Establish connection connection = mysql.connector.connect(host=”localhost”, user=”user”, password=”pass”, database=”db”) # Create cursor cursor = connection.cursor() # Execute query cursor.execute(“SELECT * FROM table”) # Fetch and print results rows = cursor.fetchall() print(rows) # Close cursor and connection cursor.close() connection.close() Print Page Previous Next Advertisements ”;