PHP - MySQL - Connection

MySQL Connection Using PHP Script

PHP provides mysql_connect() function to open a database connection.

Syntax

connection mysql_connect(server,user,passwd,new_link,client_flag);

Returns a MySQL link identifier on success or FALSE on failure.

Example

<?php
         $dbhost = 'localhost';
         $dbuser = 'test';
         $dbpass = 'Ii@21!abg';

         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br>';

         $sql = 'SELECT id, name, course FROM std';

         mysql_select_db('ellorahs_test');
         $retval = mysql_query( $sql, $conn );
   
         if(! $retval ) {
              die('Could not get data: ' . mysql_error());
         }
   
         while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
            echo "ID :{$row['id']}  <br> ".
                 "Name : {$row['name']} <br> ".
                 "Course : {$row['course']} <br> ".
                 "--------------------------------<br>";
         } 

         echo "Fetched data successfully\n";

         mysql_close($conn);
?>

mysql_close()

Disconnect from the MySQL database anytime using another PHP function mysql_close().

SELECT command is used to fetch data from the MySQL database. 

Selecting a MySQL Database Using PHP



mysql_select_db to select a database. 

It returns TRUE on success or FALSE on failure.

Fetching Data Using a PHP

The SQL SELECT command is used to fetch data from the MySQL database.

SELECT command into a PHP function mysql_query().

mysql_fetch_array() can be used to fetch all the selected data. 

MySql Boolean Data type field create

MySql BOOLEAN or TINYINT

MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1).

Example 1
Create job table  with boolean data type.

After save job table structure.

Example 2

CREATE TABLE tasks (

    id INT PRIMARY KEY AUTO_INCREMENT,

    title VARCHAR(255) NOT NULL,

    completed BOOLEAN

)

MySQL Tutorials

mysql “Where not in” using two columns

Use this

SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
      ( SELECT userID, taskID
        FROM PlannedTasks
      ) ;

MySQL date format  in SELECT * query

Use DATE_FORMAT:

SELECT *, DATE_FORMAT(date, "%m-%d-%y") AS date FROM my_table;

DATE_FORMAT(created_date,'%d-%m-%y') AS created_date

MySQL ROUND() Function

Round the number to 2 decimal places:

SELECT ROUND(235.158, 2);

Result: 235.16