Saturday, 11 February 2017

MYSQL Extension Database Tutorial

09:51

Lets  Start with  first  MYSQL Extension:
Connectivity    Code   for MYSQL  DATABASE.

here in given below code  localhost is server name and root is username, password is ""(blank).
note: when we upload it on server then we require  following:

(1)databse server name.
(2)username. 
(3)password.
(4)database name.

<?php

$con=mysql_connect("localhost","root","");

mysql_select_db("needa",$con);

if($con)
{
echo  "connection is successful";
}
else
{
     echo "not connected";
}
?>

Insert   , update , delete ,search in   PHP :

First   OF ALL   create table   coursedetails  :
SQL>
create table coursedetails
(
id  int,
coursename  varchar(200),
coursedetails   varchar(200),
universityname  varchar(200)
);

(   (1)    first      write code   for   insert.html :

<form action="insert.php" method="post">
id<input type="text" name="id">
course name<input type="text" name="coursename">
course details<input type="text" name="coursedetails">
university name<input type="text" name="universityname">
<input name="submit" type="submit" value="insert">
</form>


 (2) write     code    for     insert.php  :

<?php

$con=mysql_connect("localhost","root","");
mysql_select_db("mandar",$con);
$id=$_POST["id"];
$coursename=$_POST["coursename"];
$coursedetails=$_POST["coursedetails"];
$universityname=$_POST["universityname"];

$sql="insert into coursedetails(id,coursename,coursedetails,universityname)values('$id','$coursename','$coursedetails','$universityname')";

$result=mysql_query($sql);

if($result)
{
echo "inserted";
}
else
{
echo "not inserted";
}
?>



Now write code for update in php:

(1)write     code     for      update.html  :
<form action="update.php" method="post">
id<input type="text" name="id">
course name<input type="text" name="coursename">
course details<input type="text" name="coursedetails">
university name<input type="text" name="universityname">
<input name="submit" type="submit" value="insert">
</form>

(2)write    code     for     update.php       file:
<?php
$con=mysql_connect("localhost","root","");
mysql_select_db("mandar",$con);

$id=$_POST["id"];
$coursename=$_POST["coursename"];
$coursedetails=$_POST["coursedetails"];
$universityname=$_POST["universityname"];

$sql="update coursedetails  set coursename='$coursename',coursedetails='$coursedetails' where id='$id'";

$result=mysql_query($sql) or die (mysql_error());
if($result)
{
echo "record update successfully";
}
else
{
echo "record not updated";
}

?>



Now write code   delete in php :

(1)write  code for delete.html:

<form action="delete.php" method="post">
id<input type="text" name="id">

<input name="submit" type="submit" value="submit">
</form>

( (2) write     code    for     “delete.php”     file :

<?php

$con=mysql_connect("localhost","root","");
mysql_select_db("mandar",$con);

$id=$_POST["id"];

$sql="delete from coursedetails where id='$id'";

$result=mysql_query($sql);

if($result)
{
echo "record delete successfully";
}
else
{
echo "record not deleted";
}
?>


now      write     code    for    search      example    :

( (1)   write    code   for   search.html  :
<form action="search.php" method="post">

course name<input type="text" name="coursename">

<input name="submit" type="submit" value="search">
</form>


(2)write   code for    search.php   :
<?php
$coursename=$_POST["coursename"];

$con=mysql_connect('localhost','root',"");
mysql_select_db("mandar",$con);
$sql="select * from coursedetails where coursename='$coursename'";
$result=mysql_query($sql);


while($row=mysql_fetch_array($result))
{
echo $row["id"];
echo $row["coursename"];
echo $row["coursedetails"];
echo $row["universityname"];
echo '<br>';
}
?>



========================================================================
with Debugging techniques   to check whether  query is executed properly or not.
========================================================================
first of all create table   record :
create table  record
(id  int primary key auto_increment,
 name varchar(20),
lastname varchar(20),
city     varchar(20)
);
(     (1)   write   code  insert.html file :

<form action=insert.php method=post>
id<input type=text name=id>
name<input type=text name=name>
lastname<input type=text name=lastname>
insert city name<select name=cityname>
<option value=0>selct city</option>
<option value=Mumbai>Mumbai</option>
<option value=Chennai>Chennai<option>
</select>
<input type=submit name=insert value=insert>
</form>

(2)write    code  insert.php   file:
<?php
$con=mysql_connect("localhost","root","");
mysql_select_db("practice",$con);
$id=$_POST['id'];
$name=$_POST['name'];
$lastname=$_POST['lastname'];
$city=$_POST['cityname'];
$sql="insert into record(id,name,lastname,city)values('$id','$name','$lastname','$city')";
$res=mysql_query($sql);
if($res)
{
echo "recod inserted";
}
else
{
echo "record not inserted";
}
?>

(3)write code for update.html file:

<form action=update.php method=post>
id<input type=text name=id>
name<input type=text name=name>
lasname<input type=text name=lastname>
insertt city name<select name=cityname>
<option value=0>selct city</option>
<option value=Mumbai>Mumbai</option>
<option value=Chennai>Chennai<option>
</select>
<input type=submit name=insert value=insert>
</form>
(4)write   code for update.php file :
<?php
$con=mysql_connect("localhost","root","");
mysql_select_db("practice",$con);
$id=$_POST['id'];
$name=$_POST['name'];
$lastname=$_POST['lastname'];
$city=$_POST['cityname'];
$sql="update record set name='$name',lastname='$lastname',city='$city' where id='$id'";
$result=mysql_query($sql);
if($result)
{
echo "record updated";
}
else
{
echo "record not updated";
}
?>

Write code for display  data from table:

(1)write code for display.php file:
<?php
$con=mysql_connect("localhost","root","");
mysql_select_db("practice",$con);
$sql="select * from record";
$result=mysql_query($sql);
echo '<table border=1>';
echo '<tr><td>ID</td><td>Name</td></tr>';

while($row=mysql_fetch_array($result))
{
echo '<tr>';
echo '<td>';
echo $row['id'];
echo '</td>';


echo '<td>';
echo $row['name'];
echo '</td>';
echo '</tr>';

}echo '</table>';
?>


Search example code :

(1)write code for search.html file:
<form action=search.php method=post>
<input type=text name=name>
<input type=submit name=search value=submit data>
</form>
(2)write  code for search.php  file :
<?php
$con=mysql_connect("localhost","root","");
mysql_select_db("practice",$con);
$name=$_POST['name'];
$sql="select * from record where name= '$name' ";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo $row['name'];
echo $row['lastname'];
echo $row['id'];
echo $row['city'];
       
}

?>



==========================================================================================================================================

another search example if record not found or not matched :


(1)write  code for search.html  :

<form action="search.php" method="post">
name<input type="text" name="name">
<input name="search" type="submit" value="search">
</form>

(2)write code for search.php   file:

<?php
$nam=$_POST['name'];
$con=mysql_connect('localhost','root','');
mysql_select_db('tajal', $con);
$sql="select * from myform where name='$nam'";
$result=mysql_query($sql);
$num=mysql_num_rows($result);
if($num>0)
{
 while($rows=mysql_fetch_array($result))
{
echo $rows['id'];
echo $rows['name'];
echo $rows['address'];
echo $rows['contact'];
echo "<br>";
}
}
else
{
echo "No record matched";
}


?>

=============================mysqli======================================

(1)create table   myrecord :
create table   myrecord
( id int  primary key  auto_increment,
  name varchar(200),
 city varchar(200)
);

(2)write code for insert.html  :
<form action=insert.php   method=post>
name <input type=text name=name>
city <input type=text name=city>
<input type="submit"  name="submit"  value="submit">
</form>

(3)now write code for  insert.php  file:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$name=$_POST['name'];
$city=$_POST['city'];

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO myrecord (name,city)
VALUES ('$name','$city')";

$result=mysqli_query($conn, $sql) or die(mysqli_error());

if($result)
{
echo "record  inserted";
}
else
{
echo "record not inserted";
}
mysqli_close($conn);

?>

Object  Oriented  way of  mysqli    code will be  just use given below code as "insert.php" file :

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$name=$_POST['name'];
$city=$_POST['city'];

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) 
{
    die("Connection failed: " . $conn->connect_error);


$sql = "INSERT INTO myrecord (name,city) VALUES ('$name','$city')";

if ($conn->query($sql) === TRUE) 
{
    echo "New record created successfully";


else

 {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();


?>



Now  example  for  update .html:

(1)write code for update.html:
<form action=update.php   method=post>
id <input type=text name=id>
name <input type=text name=name>
city <input type=text name=city>
<input type="submit"  name="submit"  value="submit">
</form>  

(2)write code for update.php :

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
 $id=$_POST['id'];
$name=$_POST['name'];
$city=$_POST['city'];

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql ="update myrecord set name='$name',city='$city' where id='$id'";


$result=mysqli_query($conn, $sql) or die(mysqli_error());
if($result)
{
echo "record  updated";
}
else
{
echo "record not updated";
}


mysqli_close($conn);

?>



Object  Oriented  way of  mysqli    code will be  just use given below code as update.php file :


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$id=$_POST['id'];
$name=$_POST['name'];
$city=$_POST['city'];

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) 
{
    die("Connection failed: " . $conn->connect_error);

$sql = "update  myrecord set name='$name',city='$city'  where   id='$id'";

if ($conn->query($sql) === TRUE) 
{
    echo "New record updated successfully";
else

 {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();


?>



now  write code  for   search  :

(1)write code for  search.html :

<form action=search.php   method=post>

name <input type=text name=name>

<input type="submit"  name="submit"  value="submit">

</form> 
(2)write code for  search.php :

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$name=$_POST['name'];

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql ="select  * from myrecord  where name='$name'";


$result=mysqli_query($conn, $sql) or die(mysqli_error());

if($result)
{
while($row=mysqli_fetch_array($result))
{

 echo $row['name'];
 echo $row['city'];
 }
}
else
{
 echo "record not found";
}


mysqli_close($conn);

?>


Object   Oriented    mysqli code for search   for search.php  file:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$name=$_POST['name'];
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);


$sql = "SELECT * from myrecord where name='$name'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"];

     echo "-Name: " . $row["name"];

echo "-city: " . $row["city"]. "<br>";
    }
} else {
    echo " No results found";
}
$conn->close();
?>



Now  write code for  delete record :


(1)write code for delete.html  file:

<form action=delete.php   method=post>
id <input type=text name=id>

<input type="submit"  name="submit"  value="submit">
</form>  


(2)write  for delete.php file:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
 $id=$_POST['id'];


// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql ="delete from myrecord where id='$id'";


$result=mysqli_query($conn, $sql) or die(mysqli_error());
if($result)
{
 echo "record  deleted";
}
else
{
 echo "record not deleted";
}


mysqli_close($conn);

?>


Object   Oriented   mysqli    code  for   delete.php  file:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$id=$_POST['id'];


// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) 
{
    die("Connection failed: " . $conn->connect_error);


$sql = "delete from myrecord  where  id='$id' ";

if ($conn->query($sql) === TRUE) 
{
    echo "record deleted successfully";


else

 {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();


?>


Written by

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.

1 comments:

PHP Training Mumbai, PHP MySQL Training Mumbai, PHP Course Mumbai, PHP Institute Mumbai

 

© 2013 PHP TRAINING IN MUMBAI BY OM SIR . All rights resevered. Designed by OM SIR

Back To Top