?
Solved

php code modification for mysql from oracle

Posted on 2009-02-16
9
Medium Priority
?
789 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

0
Comment
Question by:karunamoorthy
  • 8
9 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23650907
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
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 23650962
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

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 23651024
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 23651093
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

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 23651222
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

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 23651229
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

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23651255
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
0
 
LVL 7

Author Comment

by:karunamoorthy
ID: 23656484
Thans to Ray_Paseur,
I will try your code examples and follow what you said.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23659688
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question