We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

php code modification for mysql from oracle

karunamoorthy
on
Medium Priority
809 Views
Last Modified: 2012-05-06
I have a sample php page which is working fine with PHP+Oracle environment. Now I have to modify this code to work in mysql environment. I am new(novice) to mysql and php. Anyone help me to change this code to work in mysql.

Thanks in advance.
<? session_start(); ?>
 
<html>
<head>
<title>MY CONTACTS</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
  body {
    background-color: #FFFFFF;
    color: #004080;
    font-family: Arial;
    font-size: 12px;
  }
  .bd {
    background-color: #FFFFFF;
    color: #004080;
    font-family: Arial;
    font-size: 12px;
  }
  .tbl {
    background-color: #FFFFFF;
  }
  a:link { 
    color: #FF0000;
    font-family: Arial;
    font-size: 12px;
  }
  a:active { 
    color: #0000FF;
    font-family: Arial;
    font-size: 12px;
  }
  a:visited { 
    color: #800080;
    font-family: Arial;
    font-size: 12px;
  }
  .hr {
    background-color: #336699;
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:link {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:active {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:visited {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  .dr {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
  .sr {
    background-color: #EFEFEF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
</style>
</head>
<body>
<table class="bd" width="100%"><tr><td class="hr"><h2>My Contacts</h2></td></tr></table>
<?php
  if (!login()) exit;
?>
<?php
  $conn = connect();
  $showrecs = 50;
  $pagerange = 10;
 
  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  if (isset($_GET["order"])) $order = @$_GET["order"];
  if (isset($_GET["type"])) $ordtype = @$_GET["type"];
 
  if (isset($_POST["filter"])) $filter = @$_POST["filter"];
  if (isset($_POST["filter_field"])) $filterfield = @$_POST["filter_field"];
  $wholeonly = false;
  if (isset($_POST["wholeonly"])) $wholeonly = @$_POST["wholeonly"];
 
  if (!isset($order) && isset($_SESSION["order"])) $order = $_SESSION["order"];
  if (!isset($ordtype) && isset($_SESSION["type"])) $ordtype = $_SESSION["type"];
  if (!isset($filter) && isset($_SESSION["filter"])) $filter = $_SESSION["filter"];
  if (!isset($filterfield) && isset($_SESSION["filter_field"])) $filterfield = $_SESSION["filter_field"];
 
  $page = @$_GET["page"];
  if (!isset($page)) $page = 1;
 
  $sql = @$_POST["sql"];
 
  switch ($sql) {
    case "insert":
      sql_insert();
      break;
    case "update":
      sql_update();
      break;
    case "delete":
      sql_delete();
      break;
    case "copy":
      sql_copy();
      break;
 
  }
 
  switch ($a) {
    case "add":
      addrec();
      break;
    case "copy":
      copyrec($recid);
      break;
    case "view":
      viewrec($recid);
      break;
    case "edit":
      editrec($recid);
      break;
    case "del":
      deleterec($recid);
      break;
    default:
      select();
      break;
  }
 
  if (isset($order)) $_SESSION["order"] = $order;
  if (isset($ordtype)) $_SESSION["type"] = $ordtype;
  if (isset($filter)) $_SESSION["filter"] = $filter;
  if (isset($filterfield)) $_SESSION["filter_field"] = $filterfield;
  if (isset($wholeonly)) $_SESSION["wholeonly"] = $wholeonly;
 
  ocilogoff($conn);
?>
<table class="bd" width="100%"><tr><td class="hr">My Contacts</td></tr></table>
</body>
</html>
 
<?php function select()
  {
  global $a;
  global $showrecs;
  global $page;
  global $filter;
  global $filterfield;
  global $wholeonly;
  global $order;
  global $ordtype;
 
 
  if ($a == "reset") {
    $filter = "";
    $filterfield = "";
    $wholeonly = "";
    $order = "";
    $ordtype = "";
  }
 
  $checkstr = "";
  if ($wholeonly) $checkstr = " checked";
  if ($ordtype == "asc") { $ordtypestr = "desc"; } else { $ordtypestr = "asc"; }
  $res = sql_select();
  $count = sql_getrecordcount();
  if ($count % $showrecs != 0) {
    $pagecount = intval($count / $showrecs) + 1;
  }
  else {
    $pagecount = intval($count / $showrecs);
  }
  $startrec = $showrecs * ($page - 1);
  if ($startrec < $count) {for ($i = 1; $i <= $startrec; ocifetchassoc($res), $i++);}
  $reccount = min($showrecs * $page, $count);
  $fields = array(
    "NAME" => "NAME",
    "CELLNO" => "CELLNO",
    "DESIGNATION" => "DESIGNATION",
    "OFFICE" => "OFFICE",
    "EMAILID" => "EMAILID",
    "ADDRESS" => "ADDRESS",
    "REMARKS" => "REMARKS");
?>
<div style="float: right"><a href="contacts.php?a=logout">[ Logout ]</a></div>
 
<table class="bd" border="0" cellspacing="1" cellpadding="4">
 
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<form action="contacts.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><b>Custom Filter</b>&nbsp;</td>
<td><input type="text" name="filter" value="<?php echo $filter ?>"></td>
<td><select name="filter_field">
<option value="">All Fields</option>
<?php
  reset($fields);
  foreach($fields as $val => $caption) {
    if ($val == $filterfield) {$selstr = " selected"; } else {$selstr = ""; }
?>
<option value="<?php echo $val ?>"<?php echo $selstr ?>><?php echo htmlspecialchars($caption) ?></option>
<?php } ?>
</select></td>
<td><input type="checkbox" name="wholeonly"<?php echo $checkstr ?>>Whole words only</td>
</td></tr>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="action" value="Apply Filter"></td>
<td><a href="contacts.php?a=reset">Reset Filter</a></td>
</tr>
</table>
</form>
 
<?php showpagenav($page, $pagecount); ?>
<br>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="100%">
<tr>
<?php
  reset($fields);
  foreach($fields as $val => $caption) {
?>
<td class="hr"><a class="hr" href="contacts.php?order=<?php echo $val ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars($caption) ?></a></td>
<?php } ?>
<td class="hr">Copy</td>
<td class="hr">View</td>
<td class="hr">Edit</td>
<td class="hr">Delete</td>
</tr>
<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = ocifetchassoc($res);
    $style = "dr";
    if ($i % 2 != 0) {
      $style = "sr";
    }
?>
<tr>
<?php
  reset($fields);
  foreach($fields as $val => $caption) {
?>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row[$val]) ?></td>
<?php } ?>
<td class="<?php echo $style ?>"><a href="contacts.php?a=copy&recid=<?php echo $i ?>">Copy</a></td>
<td class="<?php echo $style ?>"><a href="contacts.php?a=view&recid=<?php echo $i ?>">View</a></td>
<td class="<?php echo $style ?>"><a href="contacts.php?a=edit&recid=<?php echo $i ?>">Edit</a></td>
<td class="<?php echo $style ?>"><a href="contacts.php?a=del&recid=<?php echo $i ?>">Delete</a></td>
</tr>
<?php
  }
  ocifreestatement($res);
?>
</table>
<br>
<?php showpagenav($page, $pagecount); ?>
<?php } ?>
 
<?php function login()
{
  global $_POST;
  global $_SESSION;
 
  global $_GET;
  if (isset($_GET["a"]) && ($_GET["a"] == 'logout')) $_SESSION["logged_in"] = false;
  if (!isset($_SESSION["logged_in"])) $_SESSION["logged_in"] = false;
  if (!$_SESSION["logged_in"]) {
    $login = "";
    $password = "";
    if (isset($_POST["login"])) $login = @$_POST["login"];
    if (isset($_POST["password"])) $password = @$_POST["password"];
 
    if (($login != "") && ($password != "")) {
      if (($login == "ppc") && ($password == "ppc")) {
        $_SESSION["logged_in"] = true;
    }
    else {
?>
<p><b><font color="-1">Sorry, the login/password combination you've entered is invalid</font></b></p>
<?php } } }if (isset($_SESSION["logged_in"]) && (!$_SESSION["logged_in"])) { ?>
<form action="contacts.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td>Login</td>
<td><input type="text" name="login" value="<?php echo $login ?>"></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password" value="<?php echo $password ?>"></td>
</tr>
<tr>
<td><input type="submit" name="action" value="Login"></td>
</tr>
</table>
</form>
<?php
  }
  if (!isset($_SESSION["logged_in"])) $_SESSION["logged_in"] = false;
  return $_SESSION["logged_in"];
} ?>
 
<?php function showrow($row)
  {
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("NAME")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["NAME"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("CELLNO")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["CELLNO"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("DESIGNATION")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["DESIGNATION"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("OFFICE")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["OFFICE"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("EMAILID")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["EMAILID"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("ADDRESS")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["ADDRESS"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("REMARKS")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["REMARKS"]) ?></td>
</tr>
</table>
<?php } ?>
 
<?php function showroweditor($row)
  {
  global $conn;
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="60%">
<tr>
<td class="hr"><?php echo htmlspecialchars("NAME")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="NAME" maxlength="25" value="<?php echo str_replace('"', '&quot;', trim($row["NAME"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("CELLNO")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="CELLNO" maxlength="25" value="<?php echo str_replace('"', '&quot;', trim($row["CELLNO"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("DESIGNATION")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="DESIGNATION" maxlength="25" value="<?php echo str_replace('"', '&quot;', trim($row["DESIGNATION"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("OFFICE")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="OFFICE" maxlength="25" value="<?php echo str_replace('"', '&quot;', trim($row["OFFICE"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("EMAILID")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="EMAILID" maxlength="30" value="<?php echo str_replace('"', '&quot;', trim($row["EMAILID"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("ADDRESS")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="ADDRESS" maxlength="50" value="<?php echo str_replace('"', '&quot;', trim($row["ADDRESS"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("REMARKS")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="REMARKS" maxlength="10" value="<?php echo str_replace('"', '&quot;', trim($row["REMARKS"])) ?>"></td>
</tr>
</table>
<?php } ?>
 
<?php function showpagenav($page, $pagecount)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="contacts.php?a=add">Add Record</a>&nbsp;</td>
<?php if ($page > 1) { ?>
<td><a href="contacts.php?page=<?php echo $page - 1 ?>">&lt;&lt;&nbsp;Prev</a>&nbsp;</td>
<?php } ?>
<?php
  global $pagerange;
 
  if ($pagecount > 1) {
 
  if ($pagecount % $pagerange != 0) {
    $rangecount = intval($pagecount / $pagerange) + 1;
  }
  else {
    $rangecount = intval($pagecount / $pagerange);
  }
  for ($i = 1; $i < $rangecount + 1; $i++) {
    $startpage = (($i - 1) * $pagerange) + 1;
    $count = min($i * $pagerange, $pagecount);
 
    if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
      for ($j = $startpage; $j < $count + 1; $j++) {
        if ($j == $page) {
?>
<td><b><?php echo $j ?></b></td>
<?php } else { ?>
<td><a href="contacts.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php } } } else { ?>
<td><a href="contacts.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
<?php } } } ?>
<?php if ($page < $pagecount) { ?>
<td>&nbsp;<a href="contacts.php?page=<?php echo $page + 1 ?>">Next&nbsp;&gt;&gt;</a>&nbsp;</td>
<?php } ?>
</tr>
</table>
<?php } ?>
 
<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="contacts.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="contacts.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
<td><a href="contacts.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
<?php } ?>
</tr>
</table>
<hr size="1" noshade>
<?php } ?>
 
<?php function addrec()
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="contacts.php">Index Page</a></td>
</tr>
</table>
<hr size="1" noshade>
<form action="contacts.php" method="post">
<p><input type="hidden" name="sql" value="insert"></p>
<?php
$row = array(
  "NAME" => "",
  "CELLNO" => "",
  "DESIGNATION" => "",
  "OFFICE" => "",
  "EMAILID" => "",
  "ADDRESS" => "",
  "REMARKS" => "");
showroweditor($row)
?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php } ?>
 
<?php function copyrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  for ($i = 1; $i <= $recid; ocifetchassoc($res), $i++);
  $row = ocifetchassoc($res);
  showrecnav("copy", $recid, $count);
?>
 
 
<br>
<form action="contacts.php" method="post">
<input type="hidden" name="sql" value="insert">
<input type="hidden" name="xNAME" value="<?php echo $row["NAME"] ?>">
<input type="hidden" name="xCELLNO" value="<?php echo $row["CELLNO"] ?>">
<input type="hidden" name="xDESIGNATION" value="<?php echo $row["DESIGNATION"] ?>">
<input type="hidden" name="xOFFICE" value="<?php echo $row["OFFICE"] ?>">
<input type="hidden" name="xEMAILID" value="<?php echo $row["EMAILID"] ?>">
<input type="hidden" name="xADDRESS" value="<?php echo $row["ADDRESS"] ?>">
<input type="hidden" name="xREMARKS" value="<?php echo $row["REMARKS"] ?>">
<?php showroweditor($row) ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
  ocifreestatement($res);
} ?>
 
<?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  for ($i = 1; $i <= $recid; ocifetchassoc($res), $i++);
  $row = ocifetchassoc($res);
  showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row) ?>
<br>
<hr size="1" noshade>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="contacts.php?a=add">Add Record</a></td>
<td><a href="contacts.php?a=copy&recid=<?php echo $recid ?>">Copy Record</a></td>
<td><a href="contacts.php?a=edit&recid=<?php echo $recid ?>">Edit Record</a></td>
<td><a href="contacts.php?a=del&recid=<?php echo $recid ?>">Delete Record</a></td>
</tr>
</table>
<?php
  ocifreestatement($res);
} ?>
 
<?php function editrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  for ($i = 1; $i <= $recid; ocifetchassoc($res), $i++);
  $row = ocifetchassoc($res);
  showrecnav("edit", $recid, $count);
?>
 
 
<br>
<form action="contacts.php" method="post">
<input type="hidden" name="sql" value="update">
<input type="hidden" name="xNAME" value="<?php echo $row["NAME"] ?>">
<input type="hidden" name="xCELLNO" value="<?php echo $row["CELLNO"] ?>">
<input type="hidden" name="xDESIGNATION" value="<?php echo $row["DESIGNATION"] ?>">
<input type="hidden" name="xOFFICE" value="<?php echo $row["OFFICE"] ?>">
<input type="hidden" name="xEMAILID" value="<?php echo $row["EMAILID"] ?>">
<input type="hidden" name="xADDRESS" value="<?php echo $row["ADDRESS"] ?>">
<input type="hidden" name="xREMARKS" value="<?php echo $row["REMARKS"] ?>">
<?php showroweditor($row) ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
  ocifreestatement($res);
} ?>
 
<?php function deleterec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  for ($i = 1; $i <= $recid; ocifetchassoc($res), $i++);
  $row = ocifetchassoc($res);
  showrecnav("del", $recid, $count);
?>
<br>
<form action="contacts.php" method="post">
<input type="hidden" name="sql" value="delete">
<input type="hidden" name="xNAME" value="<?php echo $row["NAME"] ?>">
<input type="hidden" name="xCELLNO" value="<?php echo $row["CELLNO"] ?>">
<input type="hidden" name="xDESIGNATION" value="<?php echo $row["DESIGNATION"] ?>">
<input type="hidden" name="xOFFICE" value="<?php echo $row["OFFICE"] ?>">
<input type="hidden" name="xEMAILID" value="<?php echo $row["EMAILID"] ?>">
<input type="hidden" name="xADDRESS" value="<?php echo $row["ADDRESS"] ?>">
<input type="hidden" name="xREMARKS" value="<?php echo $row["REMARKS"] ?>">
<?php showrow($row) ?>
<p><input type="submit" name="action" value="Confirm"></p>
</form>
<?php
  ocifreestatement($res);
} ?>
 
<?php function connect()
{
  $conn = ocilogon("PPC", "ppc123", "payroll");
  return $conn;
}
 
function sqlvalue($val, $quote)
{
  if ($quote)
    $tmp = sqlstr($val);
  else
    $tmp = $val;
  if ($tmp == "")
    $tmp = "NULL";
  elseif ($quote)
    $tmp = "'".$tmp."'";
  return $tmp;
}
 
function sqlstr($val)
{
  return str_replace("'", "''", $val);
}
 
function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;
 
  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT \"NAME\", CELLNO, DESIGNATION, OFFICE, EMAILID, ADDRESS, REMARKS FROM PPC.TBL_CONTACTS";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (\"NAME\" like '" .$filterstr ."') or (CELLNO like '" .$filterstr ."') or (DESIGNATION like '" .$filterstr ."') or (OFFICE like '" .$filterstr ."') or (EMAILID like '" .$filterstr ."') or (ADDRESS like '" .$filterstr ."') or (REMARKS like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by \"" .sqlstr($order) ."\"";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = ociquery($sql);
  return $res;
}
 
function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;
 
  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM PPC.TBL_CONTACTS";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (\"NAME\" like '" .$filterstr ."') or (CELLNO like '" .$filterstr ."') or (DESIGNATION like '" .$filterstr ."') or (OFFICE like '" .$filterstr ."') or (EMAILID like '" .$filterstr ."') or (ADDRESS like '" .$filterstr ."') or (REMARKS like '" .$filterstr ."')";
  }
  $res = ociquery($sql);
  $row = ocifetchassoc($res);
  reset($row);
  return current($row);
}
 
function sql_insert()
{
  global $conn;
  global $_POST;
 
  $sql = "insert into PPC.TBL_CONTACTS (\"NAME\", CELLNO, DESIGNATION, OFFICE, EMAILID, ADDRESS, REMARKS) values (" .sqlvalue(@$_POST["NAME"], true) .", " .sqlvalue(@$_POST["CELLNO"], true) .", " .sqlvalue(@$_POST["DESIGNATION"], true) .", " .sqlvalue(@$_POST["OFFICE"], true) .", " .sqlvalue(@$_POST["EMAILID"], true) .", " .sqlvalue(@$_POST["ADDRESS"], true) .", " .sqlvalue(@$_POST["REMARKS"], true) .")";
  ociquery($sql);
}
 
function sql_update()
{
  global $conn;
  global $_POST;
 
  $sql = "update PPC.TBL_CONTACTS set \"NAME\"=" .sqlvalue(@$_POST["NAME"], true) .", CELLNO=" .sqlvalue(@$_POST["CELLNO"], true) .", DESIGNATION=" .sqlvalue(@$_POST["DESIGNATION"], true) .", OFFICE=" .sqlvalue(@$_POST["OFFICE"], true) .", EMAILID=" .sqlvalue(@$_POST["EMAILID"], true) .", ADDRESS=" .sqlvalue(@$_POST["ADDRESS"], true) .", REMARKS=" .sqlvalue(@$_POST["REMARKS"], true) ." where " .primarykeycondition();
  ociquery($sql);
}
 
function sql_delete()
{
  global $conn;
 
  $sql = "delete from PPC.TBL_CONTACTS where " .primarykeycondition();
  ociquery($sql);
}
function primarykeycondition()
{
  global $_POST;
  $pk = "";
  $pk .= "(\"NAME\"";
  if (@$_POST["xNAME"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xNAME"], true);
  };
  $pk .= ") and ";
  $pk .= "(CELLNO";
  if (@$_POST["xCELLNO"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCELLNO"], true);
  };
  $pk .= ") and ";
  $pk .= "(DESIGNATION";
  if (@$_POST["xDESIGNATION"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xDESIGNATION"], true);
  };
  $pk .= ") and ";
  $pk .= "(OFFICE";
  if (@$_POST["xOFFICE"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xOFFICE"], true);
  };
  $pk .= ") and ";
  $pk .= "(EMAILID";
  if (@$_POST["xEMAILID"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xEMAILID"], true);
  };
  $pk .= ") and ";
  $pk .= "(ADDRESS";
  if (@$_POST["xADDRESS"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xADDRESS"], true);
  };
  $pk .= ") and ";
  $pk .= "(REMARKS";
  if (@$_POST["xREMARKS"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xREMARKS"], true);
  };
  $pk .= ")";
  return $pk;
}
 
 
function ociquery($sql)
{
  global $conn;
 
  $res = ociparse($conn, $sql);
  ociexecute($res, OCI_DEFAULT) or die(ocierror());
  ocicommit($conn);
  return $res;
}
 
function ocifetchassoc($res)
{
  $ret_array = array();
  ocifetchinto($res, $ret_array, OCI_ASSOC);
  return $ret_array;
} ?>

Open in new window

Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Wow - you posted 738 lines of code.  That's not a question, that's an application development requirement!

I will try to give you some examples.  In the mean time, buy this book.  It has excellent information that will be very helpful for you.  Most of the SQL part will be similar, but there are a few MySQL specifics.

http://www.sitepoint.com/books/phpmysql1/

I'll post some examples in a moment, ~Ray
Most Valuable Expert 2011
Author of the Year 2014
Commented:
Here is the general way to connect to MySQL and select a data base.  In my code, I have a "config.php" file that I call at the top of each script.  That, in turn, calls the db_link script.  Once you get these four variables set, you probably will not need to change them.
<?php // ../db_link.php IN THE ROOT DIRECTORY (ABOVE WWW)
 
$db_host	= "localhost";
$db_name	= "name_of_db";
$db_user	= "user_id";
$db_word	= "password";
 
// CONNECT TO THE DATA BASE
if (!$db_connection = @mysql_connect("$db_host", "$db_user", "$db_word")) {
	$errmsg	= mysql_errno() . ' ' . mysql_error();
	echo "\n\n\n\n<!-- ! db_connection -->";
	echo "\n<!-- $errmsg -->\n\n\n\n";
	warning_RAY($errmsg);
}
 
// SELECT THE DATA BASE
if (!$db_sel = @mysql_select_db($db_name, $db_connection)) {
	$errmsg	= mysql_errno() . ' ' . mysql_error();
	echo "\n\n\n\n<!-- ! db_sel -->";
	echo "\n<!-- $errmsg -->\n\n\n\n";
	warning_RAY($errmsg);
}
 
?>

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Most Valuable Expert 2011
Author of the Year 2014
Commented:
When MySQL has a problem, its functions return FALSE so you must always test for FALSE from any MySQL query.  If MySQL failed, you can find two useful pieces of information in these functions.
<?php // SQL QUERY AND ERROR EXAMPLE
 
$sql = 'SELECT * FROM nonExistentTable';
if (!$s = mysql_query($sql))
{
   $errno = mysql_errno(); // INTEGER VALUE OF THE ERROR NUMBER
   $error = mysql_error(); // TEXT EXPLANATION OF THE ERROR
   echo "<br/>QUERY FAIL: $sql \n";
   echo "<br/> $errno $error \n";
}

Open in new window

Most Valuable Expert 2011
Author of the Year 2014
Commented:
The number of results from a query appears in a function called mysql_num_rows()
<?php // EXAMPLE OF DEALING WITH AN EMPTY QUERY RESULT SET
 
$sql = "SELECT name, address, phone FROM friendsTable WHERE name like \"%foo\" LIMIT 3";
if (!$s = mysql_query($sql))
{
   $errno = mysql_errno(); // INTEGER VALUE OF THE ERROR NUMBER
   $error = mysql_error(); // TEXT EXPLANATION OF THE ERROR
   echo "<br/>QUERY FAIL: $sql \n";
   echo "<br/> $errno $error \n";
}
 
// HOW MANY ROWS IN THE RESULT SET POINTED TO BY $s ?
$n = mysql_num_rows($s);
if ($n == 0) echo "NOTHING FOUND";

Open in new window

Most Valuable Expert 2011
Author of the Year 2014
Commented:
This shows how to iterate over a results set
<?php // EXAMPLE OF ITERATING OVER QUERY RESULT SET
 
$sql = "SELECT name, address, phone FROM friendsTable WHERE name like \"%foo\" LIMIT 3";
if (!$s = mysql_query($sql))
{
   $errno = mysql_errno(); // INTEGER VALUE OF THE ERROR NUMBER
   $error = mysql_error(); // TEXT EXPLANATION OF THE ERROR
   echo "<br/>QUERY FAIL: $sql \n";
   echo "<br/> $errno $error \n";
}
 
// HOW MANY ROWS IN THE RESULT SET POINTED TO BY $s ?
$n = mysql_num_rows($s);
echo "<br/>$n RESULTS FOR $sql \n";
 
// ITERATE OVER THE RESULTS SET
while ($row = mysql_fetch_assoc($s))
{
 
// INJECT VARIABLES INTO OUR NAMESPACE
   extract($row); // GETS $name $address AND $phone FROM THE ASSOCIATIVE ARRAY $row
 
// PRINT INFORMATION FROM EACH ROW   
   echo "<br/> $name ADDRESS $address PHONE $phone \n";
}

Open in new window

Most Valuable Expert 2011
Author of the Year 2014
Commented:
INSERT and retrieve auto_increment key.
<?php // EXAMPLE OF INSERTING DATA
 
// ESCAPE MAKING THE INSERTED FIELDS SAFE
$name_e    = mysql_real_escape_string($name);
$address_e = mysql_real_escape_string($address);
 
$sql = "INSERT INTO friendsTable (name, address) VALUES (\"$name_e\", \"$address_e\")";
if (!$s = mysql_query($sql))
{
   $errno = mysql_errno(); // INTEGER VALUE OF THE ERROR NUMBER
   $error = mysql_error(); // TEXT EXPLANATION OF THE ERROR
   echo "<br/>QUERY FAIL: $sql \n";
   echo "<br/> $errno $error \n";
}
 
// RETRIEVE AUTO_INCREMENT KEY
$key = mysql_insert_id();
 
// REPORT THE RESULT
echo "<br/>$name $address INSERTED AT KEY = $key \n";

Open in new window

Most Valuable Expert 2011
Author of the Year 2014

Commented:
And finally, another really useful book  get a copy of MySQL The Complete Reference by Vikram Vaswani.  The latest edition, whatever that is.

PHP MySQL functions, with code examples and user-contributed notes are available in the online PHP man pages here: http://us2.php.net/manual/en/ref.mysql.php

Best regards, ~Ray

Author

Commented:
Thans to Ray_Paseur,
I will try your code examples and follow what you said.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
You're welcome, of course.  Please feel free to post any specific questions here at EE.  There is a robust MySQL community.  You will get good answers quickly. ~Ray
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.