Here’s my table “section” :
CREATE TABLE `sections` (
`section_id` int(10) unsigned NOT NULL auto_increment,
`section_name` varchar(50) NOT NULL default '',
PRIMARY KEY (`section_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
--
-- Dumping data for table `sections`
--
INSERT INTO `sections` (`section_id`, `section_name`) VALUES
(1, 'HOME'),
(2, 'SOLUTIONS'),
(6, 'TESTING');
And Here’s my table “category”
CREATE TABLE `category` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`section_id` int(10) unsigned NOT NULL default '0',
`cat_name` varchar(30) NOT NULL default '',
`url_name` varchar(30) NOT NULL default '',
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
--
-- Dumping data for table `category`
--
INSERT INTO `category` (`cat_id`, `section_id`, `cat_name`, `url_name`) VALUES
(1, 1, 'News', 'www.news.com'),
(2, 2, 'Private', 'www.private.com'),
(6, 1, 'Blog', 'www.blog.com');
Each category will be assigned to section. And I have successfully done it.
Now here’s the problem :
I want to be able to edit the category by re-assigning to different section. The section will appear as PHP list/menu box form. So that the re-assigning just as simple as changing the name in the list box.
However It does not work.
Here’s the complete code of edit.php
<?php
require_once('something.php');
// include Database classes
require_once('something_else.php');
require_once('something_else_too.php');
$db;
if ($_GET && !$_POST) {
$cat_id = $_GET['cat_id'];
// get details of record to be edited
$getDets = "SELECT * FROM category WHERE cat_id = $cat_id";
$catDets = $db->query($getDets);
//
while ($row = $catDets->fetch_assoc()) {
$cat_id = $row['cat_id'];
$section_id = $row['section_id'];
}
$result = $db->query($getDets);
$row = $result->fetch_assoc();
//get Section
$getSections = 'SELECT * FROM sections ORDER BY section';
$sections = $db->query($getSections);
}
elseif ($_POST) {
// escape quotes and apostrophes if magic_quotes_gpc off
if (!get_magic_quotes_gpc()) {
foreach($_POST as $key=>$value) {
$temp = addslashes($value);
$_POST[$key] = $temp;
}
}
// create Database instance
$db;
//
if (array_key_exists('updateCategory', $_POST)) {
// code for updating category
$cat_id = $_POST['cat_id'];
$section_id = $_POST['section_id'];
$cat_name = $_POST['cat_name'];
$url_name = $_POST['url_name'];
$checkName = "SELECT * FROM category
WHERE cat_name = '$cat_name'
AND url_name = '$url_name'
AND cat_id != $cat_id";
$result = $db->query($checkName);
if ($result->num_rows > 0) {
$categoryAlert = "$cat_name $url_name is already registered";
}
if (!isset($categoryAlert)) {
$updateCategory = "UPDATE category SET cat_name = '$cat_name',
url_name = '$url_name', section_id = '$section_id'
WHERE cat_id = $cat_id";
$result = $db->query($updateCategory);
if ($result) {
$db->close();
$category = urlencode("$cat_name $url_name");
header('Location: 04_list_category.php?cat_id='.$cat_id);
}
}
}
// close database connection
$db->close();
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>EDIT HELP</title>
<link href="admin.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="maincontent">
<h1>Edit Categories</h1>
<?php
if (isset($categoryAlert))
echo '<p id="alert">'.$categoryAlert.'</p>';
?>
<form name="catDets" id="catDets" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
<table>
<tr>
<th scope="row" class="leftLabel">Category name: </th>
<td><input name="cat_name" type="text" id="cat_name" class="mediumbox"
value="<?php if (isset($row['cat_name'])) echo $row['cat_name']; ?>"
/></td>
</tr>
<tr>
<th scope="row" class="leftLabel">URL name (leave it blank): </th>
<td><input name="url_name" type="text" id="url_name" class="mediumbox"
value="<?php if (isset($row['url_name'])) echo $row['url_name']; ?>"
/></td>
</tr>
<tr>
<th scope="row" class="leftLabel">SECTION ID - Just to check if the record is successfully pull out from DB: </th>
<td><input name="section_id" type="text" id="section_id" class="mediumbox"
value="<?php if (isset($row['section_id'])) echo $row['section_id']; ?>"
/></td>
</tr>
<tr>
<th scope="row" class="leftLabel">SECTION ID: </th>
<td><select name="section" id="section">
<option value="0">Select Section</option>
<option value="other"
<?php
if (isset($section_id) && $section_id == 0 || (isset($_POST['section']) && $_POST['section'] == 'other')) echo 'selected="selected"'; ?>
>Not listed</option>
<?php
while ($row = $sections->fetch_assoc()) {
echo '<option value="'.$row['section_id'].'"';
if ((isset($section_id) && $section_id == $row['section_id']) || (isset($_POST['section']) && $_POST['section'] == $row['section_id']))
echo 'selected="selected"';
echo '>'.$row['section'].'</option>';
}
// close database connection
$db->close();
?>
</select></td>
</tr>
<th><input name="cat_id" type="hidden" id="cat_id"
value="<?php if (isset($row['cat_id'])) echo $row['cat_id']; ?>" />
</th>
<td><input name="updateCategory" type="submit" id="updateCategory" value="Update category" /></td>
</tr>
</table>
</form>
</div>
</body>
</html>
My effort generate this error message :
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
Where did I go wrong? Can someone have a look at the code above and help me? Thanks a lot.
