Update database

fuyuchi · January 27, 2010

Hi, I'm creating a webpage for the purpose of editing the database. I have been looking through forums and websites, trying to find a tutorial for it but in vain.

What I want is for the user to click on the edit button of that particular record and it will link them to the edit page. Moreover, all the data of that particular record has to be pulled and displayed on the edit page for the user to edit. This is where I got stuck at. The codes I've tried so far does not give me the result I wanted.

These are the codes I had tried:

View List:

<html>
<head>
<title>View List</title>

<SCRIPT LANGUAGE="JavaScript">
function goToURL1() { window.location = "index.php"; }
function goToURL2() { window.location = "update.php";}
</SCRIPT>

<script>
function checkAll()
{
var boxes = cBoxes.getElementsByTagName("input");
for (var i = 0; i < boxes.length; i++) {
myType = boxes[i].getAttribute("type");
if ( myType == "checkbox") {
boxes[i].checked=1;
	}
}
}
function checkNone()
{
var boxes = cBoxes.getElementsByTagName("input");
for (var i = 0; i < boxes.length; i++) {
myType = boxes[i].getAttribute("type");
if ( myType == "checkbox") {
boxes[i].checked=0;
	}
}
}
</script>
</script>
</head>
<body>


<h1>View List</h1>

<b>Search:</b>
<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

<?php
$con = mysql_connect("localhost","root","12345");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("ModuleDatabase", $con);

$result = mysql_query("SELECT * FROM modules");
?>

<div id="cBoxes">
<table width="800" border="1" cellspacing="0" cellpadding="3">

<tr>
<td align="center"><strong>Select</strong></td>
<td align="center"><strong>Edit</strong></td>
<td align="center"><strong>ID</strong></td>
<td align="center"><strong>Module Code</strong></td>
<td align="center"><strong>Module Name</strong></td>
<td align="center"><strong>Status</strong></td>
<td align="center"><strong>Syllabus</strong></td>
</tr>

<?php
if(!isset($cmd))
{
while($row = mysql_fetch_array($result))
  {
  if($row['deleted']=='0')
  {
echo "<tr>";
echo "<td><input type=checkbox name=cboxes[] value=" . $row['edit'] . ">"; 
echo "<td><a href='editagain2.php?cmd=edit&id=".$row['ID']."'>Edit</a></td>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['code'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "<td>" . $row['syllabus'] . "</td>";

   }
}
echo "</table>";
mysql_close($con);
}
?>

</div>
<br />

<input type=button value="Check All" onClick=" return checkAll();">
<input type=button value="Clear" onClick=" return checkNone();">
<input type=button value=" Edit Selected " onClick="goToURL2()">
<hr>
<center>
<input type=button value=" HOME " onClick="goToURL1()">
</center>

</form>
</body>
</html>

Edit Page:

<html>
<head>
<title>Edit Modules</title>
</head>
<body>

<?
if (isset($_GET["cmd"]) =="edit" || (isset($_POST["cmd"]) =="edit"))
{
   if (!isset($_POST["submit"]))
   {
      $code = $_GET["ID"];
      $sql = "SELECT * FROM modules WHERE id=$ID";
      $result = mysql_query($sql);        
      $myrow = mysql_fetch_array($result);
      ?>
  
      <form action="updateModules.php" method="post">
      

ID:
<input type="hidden" value="<? echo $myrow['ID'] ?>" name="ID"> 
<br>

Module Name: 
<input type="text" value="<?php echo $myrow['name'] ?>" name="name" size="100"/>
<br>

Module Code: 
<input type="text" name="code" value="<?php echo $myrow['code'] ?>">
<br>

Status: 
<select name = "status">
<option>Select one</option>
<option>ACTIVE</option value="$status">
<option>INACTIVE</option value="$status">
</select>
<br>

<tr>
<td>
Syllabus: <br>
<textarea rows = "8" cols = "50" name = "syllabus"><?php echo $myrow['syllabus'] ?></textarea>
</td>
</tr>
</br>
   
      <input type="hidden" name="cmd" value="edit">
   
      <input type="submit" name="submit" value="submit">
   
      </form>
   
   <? } ?>

</body>
</html>

Module Updated:

<html>
<head>
<title>Module Updated</title>

<SCRIPT LANGUAGE="JavaScript">
function goToURL1() { window.location = "index.php"; }
function goToURL2() { window.location = "listModules.php"; }
</SCRIPT>

</head>
<body>

<?php
$con = mysql_connect("localhost","root","12345");

if (!$con){
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("ModuleDatabase", $con);

$ID = filter_input(INPUT_POST, "ID");
$name = filter_input(INPUT_POST, "name");
$code = filter_input(INPUT_POST, "code");
$status = filter_input(INPUT_POST, "status");
$syllabus = filter_input(INPUT_POST, "syllabus");
//clean up all data

$ID = mysql_real_escape_string($ID);
$name = mysql_real_escape_string($name);
$code = mysql_real_escape_string($code);
$status = mysql_real_escape_string($status);
$syllabus = mysql_real_escape_string($syllabus);

$sql = <<< END
UPDATE modules
SET
ID = '$ID',
name = '$name',
status = '$status',
syllabus = '$syllabus'
WHERE
code = '$code';
END;

print "<pre>$sql</pre> \n";
$result = mysql_query($sql) or die(mysql_error());
if ($result){
print "<h3>$name has been successfully updated.</h3>\n";
} 
else {
print "<h3>There was a problem with the database.</h3>\n";
} // end if

mysql_close($con)
?>

<hr>
<center>
<input type=button value=" View Modules " onClick="goToURL2()">
<br />
<input type=button value=" HOME " onClick="goToURL1()">
</center>
</body>
</html>

jl5501 · January 27, 2010

There are several potential issues with your code, but in the first instance, try changing your mysql_fetch_array() to mysql_fetch_assoc() as that is how you are then address the resultant array

fuyuchi · January 27, 2010

Thanks for replying. ^^

I have changed the mysql_fetch_array() to mysql_fetch_assoc(), but there is no change in the results. And what is a resultant array? Sorry but I'm still new in programming.

oni-kun · January 27, 2010

Don't use short tags.

jl5501 · January 27, 2010

Ok, so now time to be more specific

Sorry about the term resultant array - that just means the results array you are looping through to get your result rows.

So the first question is: do you get your rows from the database in your table as you expect?

Also, I would like to point out you are missing a </tr> at the end of your rows but that will not actually break it as it will be assumed when the next <tr> is output

Also in your second script you have this

$code = $_GET["ID"];

$sql = "SELECT * FROM modules WHERE id=$ID";

perhaps it should be

$code = $_GET["ID"];

$sql = "SELECT * FROM modules WHERE id=$code";

jl5501 · January 27, 2010

I agree on the short opening tag thing - always use <?php

fuyuchi · January 27, 2010

Yes, I do get the table for the View List. But when I click on the edit button and linked to the Edit Page, the data won't appear in their respective text box for edits.

I've edited those errors you said earlier. Also after doing what you've said about the short tag, changing all the <? to <?php, the edit page would not load. Instead, it gave me an error "Parse error: syntax error, unexpected $end in C:\Program Files\xampplite\htdocs\modules\editagain2.php on line 60".

I don't understand what's wrong.

jl5501 · January 27, 2010

Ok so fisrtly you need to get rid of youe parse error which will be caused by a <?php not having a ?> close tag somewhere in your code.

As far as the data itself is concerned on that page lets look at the query

you have

$code = $_GET["ID"];

$sql = "SELECT * FROM modules WHERE id=$code";

$result = mysql_query($sql);

$myrow = mysql_fetch_assoc($result);

so is the id field called id or ID?

then you can see if you get anything by doing print_r($myrow) after the fetch

fuyuchi · January 27, 2010

Okay, I've managed to add that missing tag in. The id field is being named as ID in my database.

I don't see anything after putting in the print_r($myrow). :-\

jl5501 · January 27, 2010

Ok so you are getting no results

so check that $code has a value by echo $code

and

change the query to

$sql = "SELECT * FROM modules WHERE ID=$code";

fuyuchi · January 27, 2010

Sorry I was away.

Um there's no results either. I got a feeling I'm doing it wrong. I'm suppose to put the "echo $code" just under the "$myrow = mysql_fetch_assoc($result);" am I right?

Somehow, I think the problem lays within my linking to the Edit Page. But I can't figure out what is the problem.

oni-kun · January 27, 2010

Sorry I was away.

Um there's no results either. I got a feeling I'm doing it wrong. I'm suppose to put the "echo $code" just under the "$myrow = mysql_fetch_assoc($result);" am I right?

Somehow, I think the problem lays within my linking to the Edit Page. But I can't figure out what is the problem.

Why would you echo a local variable? Do you mean to echo $myrow['code'] ?

[ot]Using short tags <? ?> will cause PHP to not assume the boundries, thus supressing errors.[/ot]

You should place the following code at the top of any PHP page to display errors, As PHP does not display them by default:

ini_set('display_errors',1);
error_reporting(E_ALL);

This could have provided the clue in the first place, and may have more.

jl5501 · January 27, 2010

I have just seen something in your code that would mean the query would never get run

you have

if (isset($_GET["cmd"]) =="edit" || (isset($_POST["cmd"]) =="edit"))

you should have

if(isset($_GET['cmd']) && $_POST['cmd'] == 'edit')

manwhoeatsrats · January 27, 2010

if (isset($_GET["cmd"]) =="edit" || (isset($_POST["cmd"]) =="edit"))

wait a sec. reading this I am assuming that you are wanting to:

check and see if $_GET['cmd'] is set, then you are wanting to see if it set to edit. then you are going or

if the $_POST['cmd'] is set, then you are wanting to see if it is set to edit.

this should not work, as written, and I am surprised if you are not getting a parsing error.

This seems a but over kill to be honest, but the way you would write that statement is

if ((isset($_GET['cmd']) && $_GET['cmd'] == 'edit')  || (isset($_POST['cmd'])  && $_POST['cmd'] == 'edit'))

now does it really matter if the value is set? It seems to me all you want to do is see if it is set to "edit" in which case this code would work.

if ($_GET['cmd'] == 'edit' || $_POST['cmd'] == 'edit')

fuyuchi · January 27, 2010

Hmm after trying all those being said, I'm still getting errors.

I tried adding the "if(isset($_GET['cmd']) && $_POST['cmd'] == 'edit')" and the text boxes were filled with error messages.

Notice: Undefined index: cmd in C:\Program Files\xampplite\htdocs\modules\editagain2.php on line 22

 Notice: Undefined variable: myrow in C:\Program Files\xampplite\htdocs\modules\editagain2.php on line 62

And after that, I tried the "if ($_GET['cmd'] == 'edit' || $_POST['cmd'] == 'edit')" and it gave me these error messages.

Notice: Undefined index: ID in C:\Program Files\xampplite\htdocs\modules\editagain2.php on line 25

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\Program Files\xampplite\htdocs\modules\editagain2.php on line 27

This is the new codes for the Edit Page:

<html>
<head>
<title>Edit Modules</title>
</head>
<body>

<?php
$con = mysql_connect("localhost","root","12345");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("ModuleDatabase", $con);

ini_set('display_errors',1);
error_reporting(E_ALL);

if ($_GET['cmd'] == 'edit' || $_POST['cmd'] == 'edit')
{
   if (!isset($_POST["submit"]))
   {
      $ID = $_GET["ID"];
      $result = mysql_query("SELECT * FROM modules WHERE ID=$ID");
      $myrow = mysql_fetch_assoc($result);

}
}


?>
  
<form action="updateModules.php" method="post">

ID:
<input type="hidden" value="<?php echo $myrow['ID'] ?>" name="ID"> 
<br>

Module Name: 
<input type="text" value="<?php echo $myrow['name'] ?>" name="name" size="100"/>
<br>

Module Code: 
<input type="text" name="code" value="<?php echo $myrow['code'] ?>">
<br>

Status: 
<select name = "status">
<option>Select one</option>
<option>ACTIVE</option value="$status">
<option>INACTIVE</option value="$status">
</select>
<br>

<tr>
<td>
Syllabus: <br>
<textarea rows = "8" cols = "50" name = "syllabus"><?php echo $myrow['syllabus'] ?></textarea>
</td>
</tr>
</br>

<input type="hidden" name="cmd" value="edit">
<input type="submit" name="submit" value="submit">
   
</form>
</body>
</html>

jl5501 · January 27, 2010

Ok good

All your errors are showing that you do not have a value for $_GET['ID']

Your list page has this

echo "<td><a href='editagain2.php?cmd=edit&id=".$row['ID']."'>Edit</a></td>";

So to keep everything consistant you need to be looking for $_GET['id']

Also, for debug purposes put

print_r($_GET); in there so we get to see what is set

fuyuchi · January 27, 2010

It worked!

Thanks for pointing out that mistake! I'll compile all my codes, hope there won't be any more errors. :happy-04:

Sign In

Update database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information