Deleting and adding database entries with PHP - code problem
Posted: Sun Mar 14, 2010 5:49 am
I need to be able to add registration details to a MySQL table and be able to delete entries using a checkbox and submit button. Something is wrong with my scripts but I cannot find a solution, can anyone help me?
My scripts are attached:
<?php
// User details can be amended through this script
session_start();
include("dbconnect.inc");
// If the form has been submitted
if ($_POST[submit]){
// Build an sql statment to update the student details
$sql = "INSERT INTO set firstname ='" . $_POST[txtfirstname] . "',";
$sql = $sql . "lastname ='" . $_POST[txtlastname] . "',";
$sql = $sql . "house ='" . $_POST[txthouse] . "',";
$sql = $sql . "town ='" . $_POST[txttown] . "',";
$sql = $sql . "county ='" . $_POST[txtcounty] . "',";
$sql = $sql . "country ='" . $_POST[txtcountry] . "',";
$sql = $sql . "postcode ='" . $_POST[txtpostcode] . "' ";
$sql = $sql . "where studentid = '" . $_SESSION[id] . "';";
$result = mysql_query($sql,$conn);
?>
<html>
<head>
<link href="css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
<h2>Your details have been added to our database</h2>
<a href="authenticate.php">Authentication Page</a>
</body>
</html>
<?php
}
else {
// Build an sql statment to return the student record whoes id
// matches that of the session variable.
$sql = "select * from student where studentid='". $_SESSION[id] . "';";
$result = mysql_query($sql,$conn);
$row = mysql_fetch_array($result);
?>
<html>
<head>
</head>
<body>
<h2>Please enter your details below:</h2>
<form name="frmdetails" action="<?php echo $PHP_SELF ?>" method="post">
First Name :
<input name="txtfirstname" type="text" value="" />
<br/>
Surname :
<input name="txtlastname" type="text" value="" />
<br/>
Number and Street :
<input name="txthouse" type="text" value="" />
<br/>
Town :
<input name="txttown" type="text" value="" />
<br/>
County :
<input name="txtcounty" type="text" value="" />
<br/>
Country :
<input name="txtcountry" type="text" value="" />
<br/>
Postcode :
<input name="txtpostcode" type="text" value="" />
<br/>
<input type="submit" value="Save" name="submit"/>
</form>
</body>
</html>
<?php
session_start();
// Connect to the database
include("dbconnect.inc");
// Build SQL statment that selects a student's modules
$sql = "select * from student";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<table width="600" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="600" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<th colspan="7" bgcolor="#FFFFCC"><strong>Student Registration Details</strong> </th>
</tr>
<tr>
<th align="center" bgcolor="#FFFFFF">#</th>
<th align="center" bgcolor="#FFFFFF"><strong>Student Id</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Forename</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Surname</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Street</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Town</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Postcode</strong></th>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['studentid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['firstname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['house']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['town']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['postcode']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_multiple.php\">";
}
}
mysql_close();
?>
</table>
</form>
My scripts are attached:
<?php
// User details can be amended through this script
session_start();
include("dbconnect.inc");
// If the form has been submitted
if ($_POST[submit]){
// Build an sql statment to update the student details
$sql = "INSERT INTO set firstname ='" . $_POST[txtfirstname] . "',";
$sql = $sql . "lastname ='" . $_POST[txtlastname] . "',";
$sql = $sql . "house ='" . $_POST[txthouse] . "',";
$sql = $sql . "town ='" . $_POST[txttown] . "',";
$sql = $sql . "county ='" . $_POST[txtcounty] . "',";
$sql = $sql . "country ='" . $_POST[txtcountry] . "',";
$sql = $sql . "postcode ='" . $_POST[txtpostcode] . "' ";
$sql = $sql . "where studentid = '" . $_SESSION[id] . "';";
$result = mysql_query($sql,$conn);
?>
<html>
<head>
<link href="css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
<h2>Your details have been added to our database</h2>
<a href="authenticate.php">Authentication Page</a>
</body>
</html>
<?php
}
else {
// Build an sql statment to return the student record whoes id
// matches that of the session variable.
$sql = "select * from student where studentid='". $_SESSION[id] . "';";
$result = mysql_query($sql,$conn);
$row = mysql_fetch_array($result);
?>
<html>
<head>
</head>
<body>
<h2>Please enter your details below:</h2>
<form name="frmdetails" action="<?php echo $PHP_SELF ?>" method="post">
First Name :
<input name="txtfirstname" type="text" value="" />
<br/>
Surname :
<input name="txtlastname" type="text" value="" />
<br/>
Number and Street :
<input name="txthouse" type="text" value="" />
<br/>
Town :
<input name="txttown" type="text" value="" />
<br/>
County :
<input name="txtcounty" type="text" value="" />
<br/>
Country :
<input name="txtcountry" type="text" value="" />
<br/>
Postcode :
<input name="txtpostcode" type="text" value="" />
<br/>
<input type="submit" value="Save" name="submit"/>
</form>
</body>
</html>
<?php
session_start();
// Connect to the database
include("dbconnect.inc");
// Build SQL statment that selects a student's modules
$sql = "select * from student";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<table width="600" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="600" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<th colspan="7" bgcolor="#FFFFCC"><strong>Student Registration Details</strong> </th>
</tr>
<tr>
<th align="center" bgcolor="#FFFFFF">#</th>
<th align="center" bgcolor="#FFFFFF"><strong>Student Id</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Forename</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Surname</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Street</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Town</strong></th>
<th align="center" bgcolor="#FFFFFF"><strong>Postcode</strong></th>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['studentid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['firstname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['house']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['town']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['postcode']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
// Check if delete button active, start this
if($delete){
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_multiple.php\">";
}
}
mysql_close();
?>
</table>
</form>