Belajar Membuat CRUD( Create, Read, Update, Delete ) di PHP - MySql dengan jQuery (basic)

Contoh Membuat CRUD di PHP -MySql

Create Read Update Delete

Untuk membuat sebuah CRUD seperti diatas  pertama yang kita butuhkan adalah database dan tabelnya. Berikut contoh tabel berserta isinya.

-- ----------------------------

-- Table structure for `biodata`

-- ----------------------------

DROP TABLE IF EXISTS `biodata`;

CREATE TABLE `biodata` (

  `id` int(10) NOT NULL AUTO_INCREMENT,

  `nama` varchar(50) DEFAULT NULL,

  `alamat` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;



-- ----------------------------

-- Records of biodata

-- ----------------------------

INSERT INTO `biodata` VALUES ('1', 'Yanuar', 'Blitar');

INSERT INTO `biodata` VALUES ('2', 'Nopi', 'Surabaya');

Dari tabel diatas kita dapat membuat koneksinya setelah itu baru membuat form inputanya.

<script>

$(document).ready(function(){

    $("#batal").click(function(){

        $("#form_data").fadeOut("slow");

    })

    $("#save_data").submit(function(){

        var data = $("#save_data").serialize();

        $.ajax({

            type:"POST",

            data:"aksi=Simpan&"+data,

            url:"SimpanData.php",

            success:function(data){

                //alert(data);

                $("#refresh").click();

                $("#batal").click();

                return true;

            }

        })

        return false;

    })

})

</script>

<form id="save_data" method="post">

<table>

<tr>

    <td>Nama</td>

    <td><input  type="text" name="nama" id="nama" required="required" /></td>

</tr>

<tr>

    <td>Alamat</td>

    <td><input  type="text" name="alamat" id="nama" required="required"/></td>

</tr>

<tr>

    <td colspan="2"><input type="submit" value="Simpan" id="simpan" /><input type="button" id="batal" value="Batal" /></td>

</tr>



</table>

</form>

Setelah form input kemudian proses menyimpan dan akan langsung menuju ke tampilan datanya berikut script untuk view datanya.

<script>

$(document).ready(function(){

    $(".edit").click(function(){

        var id = $(this).closest("tr").attr("id");

        $.ajax({

            type:"POST",

            data:"id="+id,

            url:"EditData.php",

            success:function(data){

            $("#form_data").fadeIn("10");

            $("#form_data").html(data);

            }

        })

    })

    $(".delete").click(function(){

        var id = $(this).closest("tr").attr("id");

        var nama = $(this).closest("tr").attr("title");

        var x = confirm("Menghapus User " + nama)

        if(x){

         $.ajax({

            type:"POST",

            data:"aksi=Delete&id="+id,

            url:"SimpanData.php",

            success:function(data){

              alert("Data Berhasil Dihapus");  

              $("#refresh").click();  

            }

         })

        }

    })

})

</script>

<?php

include "koneksi.php";

echo "<table width='750'>

<tr>

    <td>No</td>

    <td>Nama</td>

    <td>Alamat</td>

    <td>Aksi</td>

</tr>";

$sql = mysql_query("select * from biodata order by id asc");

while ($h=mysql_fetch_array($sql))

{

    ++$no;

 echo"<tr id='$h[id]' title='$h[nama]'>

    <td>$no</td>

    <td>$h[nama]</td>

    <td>$h[alamat]</td>

    <td><input type='button' value='Edit' class='edit'/>

<input  type='button' value='Delete' class='delete'/></td>

    </tr>";

}

echo "</table>";

?>

Untuk proses Delete sudah termasuk pada script view. Sekarang tinggal proses Edit, Update. Untuk script Edit sebagai berikut.

<script>

$(document).ready(function(){

    $("#batal").click(function(){

        $("#form_data").fadeOut("slow");

    })

    $("#save_data").submit(function(){

        var data = $("#save_data").serialize();

        $.ajax({

            type:"POST",

            data:"aksi=Update&"+data,

            url:"SimpanData.php",

            success:function(data){

                //alert(data);

                $("#refresh").click();

                $("#batal").click();

                return true;

            }

        })

        return false;

    })

})

</script>

<?php

include "koneksi.php";

$sql = "select * from biodata where id = $_POST[id]";

$sql = mysql_query($sql);

$h = mysql_fetch_array($sql);

?>

<form id="save_data" method="post">

<input type="hidden" name="id" id="id" value="<?php echo $_POST["id"];?>" />

<table>

<tr>

    <td>Nama</td>

    <td><input  type="text" name="nama" id="nama" required="required" value="<?php echo $h[nama];?>" /></td>

</tr>

<tr>

    <td>Alamat</td>

    <td><input  type="text" name="alamat" id="nama" required="required" value="<?php echo $h[alamat];?>"/></td>

</tr>

<tr>

    <td colspan="2"><input type="submit" value="Update" id="update" /><input type="button" id="batal" value="Batal" /></td>

</tr>



</table>

</form>

Bagian terpenting dari semua script diatas adalah Proses Simpan, Update dan Deletenya seperti dibawah ini.

<?php

include "koneksi.php";

if($_POST["aksi"]=="Simpan"){

$nama = $_POST["nama"];

$alamat = $_POST["alamat"];

$sql = mysql_query("insert into biodata(nama,alamat) values('$nama','$alamat')");

echo "insert into biodata(nama,alamat) values('$nama','$alamat')";

}

else if($_POST["aksi"]=="Update"){

$nama = $_POST["nama"];

$alamat = $_POST["alamat"];

$sql = mysql_query("update biodata set nama = '$nama',alamat='$alamat' where id= $_POST[id]");

echo "update biodata set nama = '$nama',alamat='$alamat' where id= $_POST[id]";

}

else if($_POST["aksi"]=="Delete"){

$sql = mysql_query("delete from biodata where id= $_POST[id]");

echo "delete from biodata where id= $_POST[id]";

}

?>

Secara lengkap script diatas dapat di download pada link berikut Belajar Membuat CRUD
Sekian dulu Terima kasih atas kunjunganya, selamat mencoba dan semoga bermanfaat.

Read More

How To Create MySql Database and Table Using PHP File

Creating a Database:

To create and delete a database you should have admin priviledge. Its very easy to create a new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_query( sql, connection );

Parameter	Description
sql	Required - SQL query to create a database
connection	Optional - if not specified then last opend connection by mysql_connect will be used.

Example:

Try out following example to create a database:

<?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'rootpassword'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) {   die('Could not connect: ' . mysql_error()); } echo 'Connected successfully'; $sql = 'CREATE Database test_db'; $retval = mysql_query( $sql, $conn ); if(! $retval ) {   die('Could not create database: ' . mysql_error()); } echo "Database test_db created successfully\n"; mysql_close($conn); ?>

Selecting a Database:

Once you estblish a connection with a database server then it is required to select a particular database where your all the tables are associated.

This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.

PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

Syntax:

bool mysql_select_db( db_name, connection );

Parameter	Description
db_name	Required - Database name to be selected
connection	Optional - if not specified then last opend connection by mysql_connect will be used.

Example:

Here is the example showing you how to select a database.

<?php $dbhost = 'localhost:3036'; $dbuser = 'guest'; $dbpass = 'guest123'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) {   die('Could not connect: ' . mysql_error()); } echo 'Connected successfully'; mysql_select_db( 'test_db' ); mysql_close($conn); ?>

Creating Database Tables:

To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

Example:

Try out following example to create a table:

<?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'rootpassword'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) {   die('Could not connect: ' . mysql_error()); } echo 'Connected successfully'; $sql = 'CREATE TABLE employee( '.        'emp_id INT NOT NULL AUTO_INCREMENT, '.        'emp_name VARCHAR(20) NOT NULL, '.        'emp_address  VARCHAR(20) NOT NULL, '.        'emp_salary   INT NOT NULL, '.        'join_date    timestamp(14) NOT NULL, '.        'primary key ( emp_id ))'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) {   die('Could not create table: ' . mysql_error()); } echo "Table employee created successfully\n"; mysql_close($conn); ?>

In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and excute those commands.

Consider the following content in sql_query.txt file

CREATE TABLE employee(      emp_id INT NOT NULL AUTO_INCREMENT,      emp_name VARCHAR(20) NOT NULL,      emp_address  VARCHAR(20) NOT NULL,      emp_salary   INT NOT NULL,      join_date    timestamp(14) NOT NULL,      primary key ( emp_id ));

<?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'rootpassword'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) {   die('Could not connect: ' . mysql_error()); } $query_file = 'sql_query.txt'; $fp    = fopen($query_file, 'r'); $sql = fread($fp, filesize($query_file)); fclose($fp); mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) {   die('Could not create table: ' . mysql_error()); } echo "Table employee created successfully\n"; mysql_close($conn); ?>

Read More