[SOLVED] Edit data in database:

roldahayes · July 15, 2008

hello, I have my database set up as

CREATE TABLE `quotes` (

`pageid` VARCHAR( 50 ) NOT NULL ,

`quote` VARCHAR( 300 ) NOT NULL,

`author` VARCHAR( 100 ) NOT NULL

);

the field "pageid" determines which page the "quote" appears on within my site,

using

$result = mysql_query( "SELECT quote,author FROM quotes WHERE pageid = 1 ORDER BY quote DESC" )

The problem I have is when I edit the quotes using my edit page, it changes every quote that has that particular pageid and changes them all the same...??

My page to edit this information is pasted below:

<?php session_start();

  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($filter) && isset($_SESSION["filter"])) $filter = $_SESSION["filter"];
  if (!isset($filterfield) && isset($_SESSION["filter_field"])) $filterfield = $_SESSION["filter_field"];

?>

<html>
<head>
<title>admin:</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
  body {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
  .bd {
    background-color: #FFFFFF;
    color: #000000;
    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: #068FE6;
    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: #FFFCD9;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
</style>
</head>
<body>
<table class="bd" width="100%"><tr><td class="hr"><h2>Admin:</h2></td></tr></table>
<?php
  if (!login()) exit;
?>
<div style="float: right"><a href="quotes.php?a=logout">[ Logout ]</a></div>
<br>
<?php
  $conn = connect();
  $showrecs = 20;
  $pagerange = 10;

  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  $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;
  }

  switch ($a) {
    case "add":
      addrec();
      break;
    case "view":
      viewrec($recid);
      break;
    case "edit":
      editrec($recid);
      break;
    case "del":
      deleterec($recid);
      break;
    default:
      select();
      break;
  }

  if (isset($filter)) $_SESSION["filter"] = $filter;
  if (isset($filterfield)) $_SESSION["filter_field"] = $filterfield;
  if (isset($wholeonly)) $_SESSION["wholeonly"] = $wholeonly;

  mysql_close($conn);
?>

</body>
</html>

<?php function select()
  {
  global $a;
  global $showrecs;
  global $page;
  global $filter;
  global $filterfield;
  global $wholeonly;


  if ($a == "reset") {
    $filter = "";
    $filterfield = "";
    $wholeonly = "";
  }

  $checkstr = "";
  if ($wholeonly) $checkstr = " checked";
  $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) {mysql_data_seek($res, $startrec);}
  $reccount = min($showrecs * $page, $count);
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr><td>Table: quotes</td></tr>
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<hr size="1" noshade>
<form action="quotes.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><b>Custom Filter</b> </td>
<td><input type="text" name="filter" value="<?php echo $filter ?>"></td>
<td><select name="filter_field">
<option value="">All Fields</option>
<option value="<?php echo "pageid" ?>"<?php if ($filterfield == "pageid") { echo "selected"; } ?>><?php echo htmlspecialchars("pageid") ?></option>
<option value="<?php echo "quote" ?>"<?php if ($filterfield == "quote") { echo "selected"; } ?>><?php echo htmlspecialchars("quote") ?></option>
<option value="<?php echo "author" ?>"<?php if ($filterfield == "author") { echo "selected"; } ?>><?php echo htmlspecialchars("author") ?></option>
</select></td>
<td><input type="checkbox" name="wholeonly"<?php echo $checkstr ?>>Whole words only</td>
</td></tr>
<tr>
<td> </td>
<td><input type="submit" name="action" value="Apply Filter"></td>
<td><a href="quotes.php?a=reset">Reset Filter</a></td>
</tr>
</table>
</form>
<hr size="1" noshade>
<?php showpagenav($page, $pagecount); ?>
<br>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="100%">
<tr>
<td class="hr"> </td>
<td class="hr"> </td>
<td class="hr"> </td>
<td class="hr"><?php echo "pageid" ?></td>
<td class="hr"><?php echo "quote" ?></td>
<td class="hr"><?php echo "author" ?></td>
</tr>
<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
    $style = "dr";
    if ($i % 2 != 0) {
      $style = "sr";
    }
?>
<tr>
<td class="<?php echo $style ?>"><a href="quotes.php?a=view&recid=<?php echo $i ?>">View</a></td>
<td class="<?php echo $style ?>"><a href="quotes.php?a=edit&recid=<?php echo $i ?>">Edit</a></td>
<td class="<?php echo $style ?>"><a href="quotes.php?a=del&recid=<?php echo $i ?>">Delete</a></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["pageid"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["quote"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["author"]) ?></td>
</tr>
<?php
  }
  mysql_free_result($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 == "gkamperis") && ($password == "greatone1961")) {
        $_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="quotes.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, $recid)
  {
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("pageid")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["pageid"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("quote")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["quote"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("author")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["author"]) ?></td>
</tr>
</table>
<?php } ?>

<?php function showroweditor($row, $iseditmode)
  {
  global $conn;
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("pageid")." " ?></td>
<td class="dr"><input type="text" name="pageid" maxlength="50" value="<?php echo str_replace('"', '"', trim($row["pageid"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("quote")." " ?></td>
<td class="dr"><textarea cols="35" rows="4" name="quote" maxlength="300"><?php echo str_replace('"', '"', trim($row["quote"])) ?></textarea></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("author")." " ?></td>
<td class="dr"><textarea cols="35" rows="4" name="author" maxlength="300"><?php echo str_replace('"', '"', trim($row["author"])) ?></textarea></td>
</tr>
</table>
<?php } ?>

<?php function showpagenav($page, $pagecount)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="quotes.php?a=add">Add Record</a> </td>
<?php if ($page > 1) { ?>
<td><a href="quotes.php?page=<?php echo $page - 1 ?>"><< Prev</a> </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="quotes.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php } } } else { ?>
<td><a href="quotes.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
<?php } } } ?>
<?php if ($page < $pagecount) { ?>
<td> <a href="quotes.php?page=<?php echo $page + 1 ?>">Next >></a> </td>
<?php } ?>
</tr>
</table>
<?php } ?>

<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="quotes.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="quotes.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
<td><a href="quotes.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="quotes.php">Index Page</a></td>
</tr>
</table>
<hr size="1" noshade>
<form enctype="multipart/form-data" action="quotes.php" method="post">
<p><input type="hidden" name="sql" value="insert"></p>
<?php
$row = array(
  "pageid" => "",
  "quote" => "",
  "author" => "");
showroweditor($row, false);
?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php } ?>

<?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row, $recid) ?>
<br>
<hr size="1" noshade>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="quotes.php?a=add">Add Record</a></td>
<td><a href="quotes.php?a=edit&recid=<?php echo $recid ?>">Edit Record</a></td>
<td><a href="quotes.php?a=del&recid=<?php echo $recid ?>">Delete Record</a></td>
</tr>
</table>
<?php
  mysql_free_result($res);
} ?>

<?php function editrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("edit", $recid, $count);
?>
<br>
<form enctype="multipart/form-data" action="quotes.php" method="post">
<input type="hidden" name="sql" value="update">
<input type="hidden" name="xpageid" value="<?php echo $row["pageid"] ?>">
<?php showroweditor($row, true); ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
  mysql_free_result($res);
} ?>

<?php function deleterec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("del", $recid, $count);
?>
<br>
<form action="quotes.php" method="post">
<input type="hidden" name="sql" value="delete">
<input type="hidden" name="xpageid" value="<?php echo $row["pageid"] ?>">
<?php showrow($row, $recid) ?>
<p><input type="submit" name="action" value="Confirm"></p>
</form>
<?php
  mysql_free_result($res);
} ?>

<?php function connect()
{
  $conn = mysql_connect("localhost", "******", "******");
  mysql_select_db("******");
  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 $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT `pageid`, `quote`, `author` FROM `quotes`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`pageid` like '" .$filterstr ."') or (`quote` like '" .$filterstr ."') or (`author` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM `quotes`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`pageid` like '" .$filterstr ."') or (`quote` like '" .$filterstr ."') or (`author` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}

function sql_insert()
{
  global $conn;
  global $_POST;

  $sql = "insert into `quotes` (`pageid`, `quote`, `author`) values (" .sqlvalue(@$_POST["pageid"], true).", " .sqlvalue(@$_POST["quote"], true).", " .sqlvalue(@$_POST["author"], true).")";
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
  global $conn;
  global $_POST;

  $sql = "update `quotes` set `pageid`=" .sqlvalue(@$_POST["pageid"], true).", `quote`=" .sqlvalue(@$_POST["quote"], true).", `author`=" .sqlvalue(@$_POST["author"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_delete()
{
  global $conn;

  $sql = "delete from `quotes` where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}
function primarykeycondition()
{
  global $_POST;
  $pk = "";
  $pk .= "(`pageid`";
  if (@$_POST["xpageid"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xpageid"], true);
  };
  $pk .= ")";
  return $pk;
}
?>

cooldude832 · July 15, 2008

your primary key function thing looks like the issue (if its returning an empty key then its updating all rows)

your php looks very bloated for what u are doing.

roldahayes · July 15, 2008

I thought it was something to do with the key, but what can I do to fix it?

dannyb785 · July 15, 2008

Ok, here's the thing with setting up tables... you NEED to give every row a primary key id. That is, a key to identify each row specifically in the table. This is so you can uniquely identify, delete, edit, etc any specific row if needed. what you need to do is create a new column and make it 'quote_id' or something similar. Then when you are editing the info, you need a hidden input field with the row's quote_id as its value. Then when you are editing the quote, you say "UPDATE quotes SET quote_body='$whatever', etc etc WHERE quote_id='$id' " and $id will be the value in the hidden input field.

dannyb785 · July 15, 2008

also, make sure you set the quote_id as the primary key, and make it auto_increment that way when you create a new quote, it will increment the quote_id automatically for you.

roldahayes · July 15, 2008

ok, but how do I make quote_id auto increment?

i get this...

SQL query: 

ALTER TABLE `quotes` CHANGE `quote_id` `quote_id` VARCHAR( 300 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL AUTO_INCREMENT 

MySQL said:  

#1063 - Incorrect column specifier for column 'quote_id'

dannyb785 · July 15, 2008

did you type that query yourself or did you use phpmyadmin?

edit: remember, you need to just ADD a column quote_id, not modify a current column. And you have to make it a data type of int, not varcharr

roldahayes · July 16, 2008

I used Phpmyadmin, all sorted now.

Now how do I adjust my form to work?

roldahayes · July 16, 2008

Great it works now!

Thanks for the help Danny, I learnt a lot from this!

Cheers

Sign In

[SOLVED] Edit data in database:

Recommended Posts

roldahayes

Link to comment

Share on other sites

cooldude832

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

dannyb785

Link to comment

Share on other sites

dannyb785

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

dannyb785

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information