PHP MySQLi Functions: mysqli_query | mysqli_connect | mysqli_fetch_array

A wide range of PHP MySQL database manipulation functions is already included in PHP. We’ll talk about PHP MySQLi Functions today. Prior to these, we also covered PHP Exception And Error Handling Tutorial , Forms in PHPPHP SessionPHP Operators, and Mail Function In PHP. If you don’t know about these, then act quickly!

Access MySQL database servers using the MySQLi functions.

The final optional parameter link_identifier is accepted by the majority of MySQL functions. If it is omitted, the most recent connection established is used. If it is absent, connection attempts are made using the php.ini default parameters. Functions return FALSE if the operation is unsuccessful.

Installation / Runtime Configuration

It is necessary to compile PHP with support for the MySQLi extension if you want the MySQLi functions to be available.

With PHP 5.0.0, the MySQLi extension was introduced. In PHP 5.3.0, the MySQL Native Driver was added.

For installation, go to:  http://php.net/manual/en/mysqli.installation.php

For runtime configuration, go to:  http://php.net/manual/en/mysqli.configuration.php

PHP MySQLi Functions

PHP mysqli_connect function

To establish a connection with a MySQL database server, use the PHP MySQL connect function.

Syntax:

<?php;
$db_handle = mysqli_connect($db_server_name, $db_user_name, $db_password);
?>
PHP mysqli_select_db function

The function mysqli_select_db is used to choose a database.

Syntax:

<?php
mysqli_select_db($db_handle,$database_name);
?>
PHP mysqli_query function

To run SQL queries, use the mysqli_query function. The following query types can be run using the function.

  • Insert
  • Select
  • Update
  • delete

Syntax:

<?php
mysqli_query($db_handle,$query) ;
?>
PHP mysqli_num_rows function

The number of rows returned from a select query can be determined using the mysqli_num_rows function.

Syntax:

<?php
mysqli_num_rows($result);
?>
PHP mysqli_fetch_array function

Row arrays are fetched from a query result set using the mysqli_fetch_array function.

Syntax:

<?php
mysqli_fetch_array($result);
?>
PHP mysqli_close function

An open database connection can be closed using the mysqli_close function.

Syntax:

<?php
mysqli_close($db_handle);
?>

Let’s examine some real-world examples of how these features are used.

The MySQL database creation This tutorial assumes that the reader has some familiarity with MySQL and SQL; if not, please consult the tutorials on those topics.

The steps to create the database and table are listed below. Here we have created a form that has connected to the database and you can insert the data and you can delete the data.

Filename: fileinsert.php

<?php
if(isset($_POST['submit'])){
$server="localhost";
$username="root";
$password="";
$database='mpc_trip';  
$con = mysqli_connect($server,$username, $password, $database); 
if(!$con){
    die("Connection Failed".mysqli_connect_error());
}else{
//   echo "Successfully connected";  
$name= $_POST['name'];
$image= $_FILES['image']["name"];
$gender= $_POST['gender'];
$age= $_POST['age'];
$date= $_POST['date'];
$email= $_POST['email'];
$phone= $_POST['phone'];
$country= $_POST['country'];
$message= $_POST['message'];

if (isset($_POST['cbox'])) {
    $sms="YES";
}
elseif (!isset($_POST['cbox'])) {
    $sms="NO"; 
}
else {
    echo "Some error occurred";
}
$query2="select * from `formmpc` where name='$name'";
$result1=mysqli_query($con, $query2);
if(mysqli_num_rows($result1)>0){
    echo '<script>
        alert("Already exits");
    </script>';
}
    else{

$sql="INSERT INTO `mpc_trip`.`formmpc` (`id`, `name`, `image`,`gender`, `age`, `date`, `email`, `phone`, `country`, `message`, `emailverify`) VALUES ('', '$name', '$image','$gender', '$age', '$date', '$email', '$phone', '$country', '$message','$sms');";
$result = mysqli_query($con,$sql);
if($result){    
    // echo "Msg= $sms";
    move_uploaded_file($_FILES["image"]["tmp_name"], "image/".$_FILES['image']["name"]);

    echo '<script>alert("Inserted Successfully")</script>';
}
    // if($sql){
    //     echo "Data Inserted Successfully";
    // }
    // else{
    //     echo "Data not Inserted Successfully";
    // }

}
}
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Starting Form </title>
    <link href="https://fonts.googleapis.com/css2?family=Roboto+Serif:ital,wght@0,100;0,200;0,300;1,100;1,200&display=swap"
        rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Redressed&display=swap" rel="stylesheet">
    <link rel="Stylesheet" href="style.css">
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet"
        integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
</head>

<body>
    <!-- <img class="bg" src="" alt="" style="height: 57em;"> -->
    <div class="Container bg-success p-2 text-white bg-opacity-40" >
        <h1>Registration Form</h1>
        <p>Enter Your Details and submit this form to Confirm your participation in the Trip</p>
        <!-- <p class="submitmsg">Thanks for submitting your form. We are happy to see you joining with us</p> -->

        <form action="" method="post" enctype="multipart/form-data">
            <input type="text" name="name" id="Name" placeholder="Enter Your Name" style="margin:11px;">

            <div class=" images mb-2" style=" margin-right: 26em;margin-top: 1em;position: relative;">
            <label for="exampleFormControlInput2" class="form-label">Profile Photo</label>
            <input type="file" name="image" id="image" placeholder="" style="">
            </div>
            <div class="form-check" style="margin-right: 45em;margin-top: 1.5em;">
                Select your Genders <br>
                <input type="radio" name="gender" value="male" style="position: relative;top: 25px;left: 1em;">
                Male
                <input type="radio" name="gender" value="female" style="position: relative;top: 25px;left: 1em;">
                Female
            </div>
            <input type="text" name="age" id="age" placeholder="Enter Your Age" style="margin:11px;margin-top: 1em;">
            <div>
            <div class="mb-3" style=" margin-right: 45em;margin-top: 1em;position: relative;left: 13px;">
                <label for="exampleFormControlInput1" class="form-label">Date of Birth</label>
                <input type="date" class="form-control" name="date" id="exampleFormControlInput1" placeholder="date">
            </div>
            </div>

            <input type="email" name="email" id="Email" placeholder="Enter Your Email" style="margin:11px;">
            <input type="tel" name="phone" id="phone" placeholder="Enter Your Mobile Number" style="margin:11px;">

            <div style="margin-right:37em;margin-left: 1em;">
                <label for="country">Country: </label>
                <select name="country" id="country" required
                    style="margin: 22px 6px; border: 1px solid #030303c2; border-radius: 6px;height: 39px;">
                    <option value="please select a country">please select a country</option>
                    <option value="India">India</option>
                </select>
            </div>
            <textarea name="message" id="Description" cols="30" rows="10"
                placeholder="About Your Information" style="  height: 138px; position: relative;left: 8px;"></textarea>
            <div class="form-check" style="margin-right: 45em;margin-top: 1em;">
                <input class="form-check-input" name="cbox" type="checkbox" value="" style="    margin-left: 1em;">
                <label class="form-check-label" style="margin-left:10px;">
                    send sms in Email
                </label>
            </div>
            <div class="button" style="border: 1px solid rebeccapurple;background: antiquewhite;border: 1px solid rebeccapurple;background: antiquewhite;width: 95px;">
                <button class="btn" name="submit">Submit</button>
            </div>
            <div class="anchor" style="border: 1px solid black; margin-left: 18em;position: relative;bottom:40px;padding: 7px 6px;background: antiquewhite;width: 8em;">
                <a href="viewdetails.php" style="color: black; text-decoration: none;">View Details</a>
            </div>
        </form>
        <!-- <button class="btn">Reset</button> -->
    </div>
</body>
</html>

OUTPUT:

Starting Form
-->

Registration Form

Enter Your Details and submit this form to Confirm your participation in the Trip

Select your Genders
Male Female

For Image, You will create a image folder in the that parent Folder

Filename: style.css

For customized the form.

*{
    margin: 0px;
    padding: 0px;
    /* box-sizing: border-box; */
    
font-family: 'Roboto', sans-serif;
}

.container{
    max-width: 50%; 
    padding: 34px; 
    margin: auto;
}

h1 {
    text-align: center;
    font-family: 'Sriracha', cursive;
    font-size: 40px;
}

p{
    font-size: 17px;
    text-align: center;
    font-family: 'Sriracha', cursive;
}

input, textarea{
    
    border: 2px solid black;
    border-radius: 6px;
    outline: none;
    font-size: 16px;
    width: 80%;
    margin: 11px 0px;
    padding: 7px;
}

.btn{
    color: white;
    background: purple;
    padding: 8px 12px;
    font-size: 20px;
    border: 2px solid white;
    border-radius: 14px;
    cursor: pointer;
}

.bg{
    width: 100%;
    position: absolute;
    z-index: -1;
    opacity: 0.6;
}
.submitMsg{ 
    color: green;
}
form{
    display: flex;
    align-items: center;
    justify-content: center;
    flex-direction: column;
    position: relative;
    left: 12em;
    left: 12em;
    margin: -5px -108px;
    padding: 10px 0px;
    margin: -5px -108px;
    padding: 10px 0px;
    box-shadow: 0px -1px 7px 4px #fce541c4;
    height: 60em;
    width: 67em;
}
 

Filename: delete.php

In this file, It contains the queries which are written for the deletion of data.

<?php
$server="localhost";
$username="root";
$password="";
$database='mpc_trip';  
$con = mysqli_connect($server,$username, $password, $database); 
if(!$con){
    die("Connection Failed".mysqli_connect_error());
}
 if(isset($_GET['deleteid']))
 {
     $id=$_GET['deleteid'];
    $query = "DELETE FROM `formmpc` WHERE  `id`='$id'";
    $result = mysqli_query($con,$query);
    if($result){
        echo '<script>
        alert=("Deleted Successfully");
        </script>';
        header("location:viewdetails.php");
    }  
    }else{
        echo "not deleted";
    }
?>

Filename: Viewdetails.php

In this file, we have created a table and  we can able to see all the data which you have saved.

<?php
// session_start();
$server="localhost";
$username="root";
$password="";
$database='mpc_trip';  
$con = mysqli_connect($server,$username,$password,$database);
if(!$con){
    echo "Not Connected";
}
?>
<table width="109%" border="1" cellspacing="5" style="border-collapse: collapse;text-align: center;height: 8em;background: aliceblue;
">
    <thead colspan="2" align="center">
        <tr>
            <th>SL.No</th>
            <th>Name</th>
            <th>Profile Photo </td>
            <th>Gender</th>
            <th>Age</th>
            <th>DOB</th>
            <th>Email</th>
            <th>ContactNo</th>
            <th>Country</th>
            <th>Message</th>
            <th>Email Verify</th>
            <th>Operation</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <?php
                $sql="SELECT * FROM `formmpc` ORDER BY id";
                $result= mysqli_query($con,$sql);
                if($result){
                while($row = mysqli_fetch_array($result)){

                    $id=$row['id'];
                    $name=$row['name'];
                    $image=$row['image'];
                    $gender=$row['gender'];
                    $age=$row['age'];
                    $date=date("d M Y",strtotime($row['date']));
                    $email=$row['email'];
                    $phone=$row['phone'];
                    $country=$row['country'];
                    $message=$row['message'];
                    $sms=$row['emailverify'];

                    // $_SESSION['id'] = $id;
                    

                    echo'<tr>
                    <td>'.$id.'</td>
                    <td>'.$name.'</td>
                    <td><img src="image/'.$image.'" width="60px";height="50px"; alt="Empty"</td> 
                    <td>'.$gender.'</td>
                    <td>'.$age.'</td>
                    <td>'.$date.'</td>
                    <td>'.$email.'</td>
                    <td>'.$phone.'</td>
                    <td>'.$country.'</td>
                    <td>'.$message.'</td>
                    <td>'.$sms.'</td>
                    <td>
                    <button style="height: 2em;position: relative; right: 13px;width: 66px;border: none;background: darkgrey;" ><a href="update.php?updateid='.$id.'" style="text-decoration: none;color: azure;">Update</a></button>
                    <button style="height: 2em;position: relative; right: -2px;width: 66px;border: none;background: darkgrey;"><a href="delete.php?deleteid='.$id.'"  style="text-decoration: none;color: azure;">Delete</a></button>

                    </tr>'; 
                ?>
        </tr>

        <?php   
                }
            }
            ?>
    </tbody>
</table>
</body>
</html>

OUTPUT:

‘; ?>
SL.No Name Profile Photo Gender Age DOB Email ContactNo Country Message Email Verify Operation
‘.$id.’ ‘.$name.’ Empty ‘.$gender.’ ‘.$age.’ ‘.$date.’ ‘.$email.’ ‘.$phone.’ ‘.$country.’ ‘.$message.’ ‘.$sms.’
MySQLi FunctionsDescription
mysql_affected_rowsFind the number of rows from the last MySQL operation that were affected.
mysql_client_encodingreturns the character set’s name.
mysql_closeCut off your MySQL connection.
mysql_connectTo connect to a MySQL server, do so.
mysql_create_dbEstablish a MySQL database.
mysql_data_seekThe internal result pointer moved.
mysql_db_nameobtains the database name from the MySQL list DBS call.
mysql_db_querychooses a database and runs a query against it.
mysql_drop_dbDrop a MySQL database to delete it.
mysql_errnoreturns the error message’s numerical value from a previous MySQL operation.
mysql_errorreturns the text of the previous MySQL operation’s error message.
mysql_escape_stringTo utilise a string in a mysql query, it must be escaped.
mysql_fetch_arrayRetrieve a row of results as either an associative array, a numeric array, or both.
mysql_fetch_assocRetrieve a row of the result as an associative array.
mysql_fetch_fieldObtain column data from a result and provide it back as an object.
mysql_fetch_lengthsFind the length of each result’s output.
mysql_fetch_rowObtained an enumerated array of the result row.
mysql_field_flagsObtain the result’s flags for the field that was specified.
mysql_field_lenreturns the length of the field that was supplied.
mysql_field_nameObtain the result’s name for the given field.
mysql_field_seekSet the field offset for the result pointer.
mysql_field_tableObtain the table name where the requested field is located.
mysql_field_typeGet the field’s type as a result for the provided field.
mysql_free_resultClear the result memory.
mysql_get_client_infodiscover the MySQL client.
mysql_get_host_infoObtain MySQL host information.
mysql_get_proto_infolearn about the MySQL protocol.
mysql_get_server_infolearn about the MySQL server.
mysql_infoLearn more about the most recent search.
mysql_insert_idObtain the ID created by the most recent query.
mysql_list_dbsList the MySQL server’s accessible databases.
mysql_list_fieldsList the fields of a MySQL table.
mysql_list_processesList each instance of MySQL.
mysql_list_tablesthe MySQL database’s tables.
mysql_num_fieldsCount the number of fields in the result.
mysql_num_rowsCount the number of rows in the result.
mysql_pconnectEstablish an ongoing connection with a MySQL server.
mysql_pingIf a server connection is available, ping it or try again if none are.
mysql_querySend a query to MySQL.
mysql_real_escape_stringenables the usage of special characters in strings in SQL statements.
mysql_resultFind out the results.
mysql_select_dbchoosing a MySQL database.
mysql_set_charsetthe client character set is set.
mysql_statdiscover the present system status.
mysql_tablenameGet the field’s table name.
mysql_thread_idthe thread ID that is active right now.
mysql_unbuffered_queryDon’t fetch and buffer the rows of the response before sending a SQL query to MySQL.
mysql_fetch_objectGet a row of the results as an object.

ODBC The name “ODBC” stands for Open Database Connectivity. Its basic syntax is as follows.

<?php $conn = odbc_connect($dsn, $user_name, $password); ?>

The example makes you a given.

  • using Windows OS.
  • You have established an ODBC connection to the northwind Microsoft Access database.

The implementation code for ODBC data access is provided below.

<?php 
	$dbh = odbc_connect('northwind', '', ''); 
	if (!$dbh) {     
	exit("Connection Failed: " . $dbh); 
} 
	$sql_stmt = "SELECT * FROM form_mpc"; 
	$result = odbc_exec($dbh, $sql_stmt); 
	if (!$result) {     
	exit("Error access records"); 
} 
	while (odbc_fetch_row($result)) {     
	$company_name = odbc_result($result, "Name");    
	$contact_name = odbc_result($result, "Number");     
	echo "<b> Name (Contact Name):</b> $Number ($number) <br>";
} 
odbc_close($dbh); 
?>

You Might Like

Leave a Reply

Your email address will not be published. Required fields are marked *