Can not Delete and edit values table database

Discussion in 'PHP' started by irfansyah, Jun 22, 2017.

0
  1. #1
    I try make Show, Update, insert, and delete data table with Dreamweaver... but Edit and Delete option can not work..

    This is code for all:
    Show Data table
    <?php require_once('library/koneksi.php'); ?>
<?php
$maxRows_data = 10;
$pageNum_data = 0;
if (isset($_GET['pageNum_data'])) {
  $pageNum_data = $_GET['pageNum_data'];
}
$startRow_data = $pageNum_data * $maxRows_data;

mysql_select_db($database_koneksi, $koneksi);
$query_data = "SELECT * FROM `data` ORDER BY NIP ASC";
$query_limit_data = sprintf("%s LIMIT %d, %d", $query_data, $startRow_data, $maxRows_data);
$data = mysql_query($query_limit_data, $koneksi) or die(mysql_error());
$row_data = mysql_fetch_assoc($data);

if (isset($_GET['totalRows_data'])) {
  $totalRows_data = $_GET['totalRows_data'];
} else {
  $all_data = mysql_query($query_data);
  $totalRows_data = mysql_num_rows($all_data);
}
$totalPages_data = ceil($totalRows_data/$maxRows_data)-1;
?>
<table border="0">
<caption><h1>Data Pribadi</h1></caption>
  <thead>
  <tr id="keptabel">
    <td>NIP</td>
    <td>Nama Lengkap</td>
    <td>Tempa tLahir</td>
    <td>Tanggal Lahir</td>
    <td>Alamat</td>
    <td>Keahlian</td>
    <td>Tamatan</td>
    <td>Hobi</td>
    <td>Aksi</td>
  </tr>
  </thead>
  <?php do { ?>
  <tbody>
  <tr>
    <td><?php echo $row_data['NIP']; ?></td>
    <td><?php echo $row_data['NamaLengkap']; ?></td>
    <td><?php echo $row_data['TempatLahir']; ?></td>
    <td><?php echo $row_data['TanggalLahir']; ?></td>
    <td><?php echo $row_data['Alamat']; ?></td>
    <td><?php echo $row_data['Keahlian']; ?></td>
    <td><?php echo $row_data['Tamatan']; ?></td>
    <td><?php echo $row_data['Hobi']; ?></td>
    <td>Edit || <a href="admin.php?p=data/hapusdata">Delete</a></td>
  </tr>
  </tbody>
  <?php } while ($row_data = mysql_fetch_assoc($data)); ?>
</table>
<tr>
      <th colspan='8'><a href='admin.php?p=data/tambahdata'>
      <input name='input' type="button" value='Tambah'>
</a></th>
     </tr>
<?php require_once('library/koneksi.php'); ?>

<?php
mysql_free_result($data);
?>
    PHP:

    Add Table Data

    <?php require_once('library/koneksi.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;   
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO data (NIP, NamaLengkap, TempatLahir, TanggalLahir, Alamat, Keahlian, Tamatan, Hobi) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['NIP'], "int"),
                       GetSQLValueString($_POST['NamaLengkap'], "text"),
                       GetSQLValueString($_POST['TempatLahir'], "text"),
                       GetSQLValueString($_POST['TanggalLahir'], "date"),
                       GetSQLValueString($_POST['Alamat'], "text"),
                       GetSQLValueString($_POST['Keahlian'], "text"),
                       GetSQLValueString($_POST['Tamatan'], "text"),
                       GetSQLValueString($_POST['Hobi'], "text"));

  mysql_select_db($database_koneksi, $koneksi);
  $Result1 = mysql_query($insertSQL, $koneksi) or die(mysql_error());

  $insertGoTo = "admin.php?p=data/data";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

$colname_data = "1";
if (isset($_GET['NIP'])) {
  $colname_data = (get_magic_quotes_gpc()) ? $_GET['NIP'] : addslashes($_GET['NIP']);
}
mysql_select_db($database_koneksi, $koneksi);
$query_data = sprintf("SELECT * FROM `data` WHERE NIP = %s", $colname_data);
$data = mysql_query($query_data, $koneksi) or die(mysql_error());
$row_data = mysql_fetch_assoc($data);
$totalRows_data = mysql_num_rows($data);

mysql_free_result($data);
?>

<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">NIP:</td>
      <td><input type="text" name="NIP" value="<?php echo $row_data['NIP']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">NamaLengkap:</td>
      <td><input type="text" name="NamaLengkap" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TempatLahir:</td>
      <td><input type="text" name="TempatLahir" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TanggalLahir:</td>
      <td><input type="text" name="TanggalLahir" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Alamat:</td>
      <td><input type="text" name="Alamat" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Keahlian:</td>
      <td><input type="text" name="Keahlian" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Tamatan:</td>
      <td><input type="text" name="Tamatan" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Hobi:</td>
      <td><input type="text" name="Hobi" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_insert" value="form1">
</form>
    PHP:
    UPDATE TABLE DATA
    <?php require_once('library/koneksi.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;   
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE data SET NamaLengkap=%s, TempatLahir=%s, TanggalLahir=%s, Alamat=%s, Keahlian=%s, Tamatan=%s, Hobi=%s WHERE NIP=%s",
                       GetSQLValueString($_POST['NamaLengkap'], "text"),
                       GetSQLValueString($_POST['TempatLahir'], "text"),
                       GetSQLValueString($_POST['TanggalLahir'], "date"),
                       GetSQLValueString($_POST['Alamat'], "text"),
                       GetSQLValueString($_POST['Keahlian'], "text"),
                       GetSQLValueString($_POST['Tamatan'], "text"),
                       GetSQLValueString($_POST['Hobi'], "text"),
                       GetSQLValueString($_POST['NIP'], "int"));

  mysql_select_db($database_koneksi, $koneksi);
  $Result1 = mysql_query($updateSQL, $koneksi) or die(mysql_error());

  $updateGoTo = "admin.php?p=data/data";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}

mysql_select_db($database_koneksi, $koneksi);
$query_data = "SELECT * FROM `data`";
$data = mysql_query($query_data, $koneksi) or die(mysql_error());
$row_data = mysql_fetch_assoc($data);
$totalRows_data = mysql_num_rows($data);

mysql_free_result($data);
?>

<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">NIP:</td>
      <td><input type="text" name="NIP" value="<?php echo $row_data['NIP']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">NamaLengkap:</td>
      <td><input type="text" name="NamaLengkap" value="<?php echo $row_data['NamaLengkap']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TempatLahir:</td>
      <td><input type="text" name="TempatLahir" value="<?php echo $row_data['TempatLahir']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TanggalLahir:</td>
      <td><input type="text" name="TanggalLahir" value="<?php echo $row_data['TanggalLahir']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Alamat:</td>
      <td><input type="text" name="Alamat" value="<?php echo $row_data['Alamat']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Keahlian:</td>
      <td><input type="text" name="Keahlian" value="<?php echo $row_data['Keahlian']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Tamatan:</td>
      <td><input type="text" name="Tamatan" value="<?php echo $row_data['Tamatan']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Hobi:</td>
      <td><input type="text" name="Hobi" value="<?php echo $row_data['Hobi']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Update record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="NIP" value="<?php echo $row_data['NIP']; ?>">
</form>
    PHP:

    Delete Table Data
    <?php require_once('library/koneksi.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;   
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

if (isset($_GET['NIP'])){
  $deleteSQL = sprintf("DELETE FROM data WHERE NIP=%s",
                       GetSQLValueString($_GET['NIP'], "int"));

  mysql_select_db($database_koneksi, $koneksi);
  $Result1 = mysql_query($deleteSQL, $koneksi) or die(mysql_error());

  $deleteGoTo = "admin.php?p=data/data";
  if (isset($_SERVER['QUERY_STRING'])) {
    $deleteGoTo .= (strpos($deleteGoTo, '?')) ? "&" : "?";
    $deleteGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $deleteGoTo));
}
?>
    PHP:
    where is that problem with above code php?
    After i run update and Delete command. It show just blank display.. and next check the table data still view.
     
    irfansyah, Jun 22, 2017 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,875
    Likes Received:
    4,547
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Let me get in first to say you shouldn't be using mysql_ commands these days - look up the uptodate methods of connecting to your database.

    that aside...

    the first thing you need to do is turn on show_errors so you are seeing anything and everything that fails.
    put an echo at the top of your delete put an echo so you know it's being accessed.

    but I suspect your problem is here:
    $deleteSQL = sprintf("DELETE FROM data WHERE NIP=%s",
GetSQLValueString($_GET['NIP'], "int"));
    PHP:
    echo out what $deleteSQL turns into because I suspect you really need that statement to be:
    $deleteSQL = sprintf("DELETE FROM `data` WHERE `NIP`='%s'",
GetSQLValueString($_GET['NIP'], "int"));
    PHP:
    The key difference is the single quotes around %s

    Extra stuff
    • you have absolutely no sanitising of your data before you send it to the database. You need to get that sorted.
    • people use database libraries (or frameworks) so that the heavy lifting of safe data is done for them - look for one that looks easy to use.
    • your function GetSQLValueString should be in a functions file so that if you make changes to it you only have to do it once.
     
    sarahk, Jun 24, 2017 IP
    ThePHPMaster likes this.