Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      PHP Syntax

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      PHP Syntax

      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:\Apache24\htdocs 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>