Sanjib Sinha Posted February 6, 2009 Share Posted February 6, 2009 I have two tables in my database: friends and pages. Now managing that friend part is going very smoothly. I can add and edit. I have a relation between 'friends' and 'pages' through 'subject_id'. Now I can not update 'pages'. Here goes the code of edit_subject.php: <?php require_once("includes/session.php"); ?> <?php require_once("includes/connection.php"); ?> <?php require_once("includes/functions.php"); ?> <?php // make sure subject id sent is an integer if (intval($_GET['page']) == 0) { redirect_to('content.php'); } include_once("includes/form_functions.php"); // Start form procesing // only execute the form processing if the form being submitted if (isset($_POST['submit'])) { // initialize an array to hold our errors $errors = array(); // perform validations on the form data $required_fields = array('menu_name','position', 'visible', 'content'); $errors = array_merge($errors, check_required_fields($required_fields)); $fields_with_lengths = array('menu_name' => 30); $errors = array_merge($errors, check_max_field_lengths($fields_with_lengths)); // clean up the form data before putting it in the database $id = mysql_prep($_GET['page']); $menu_name = trim(mysql_prep($_POST['menu_name'])); $position = mysql_prep($_POST['position']); $visible = mysql_prep($_POST['visible']); $content = mysql_prep($_POST['content']); if(empty($errors)) { $query = "UPDATE pages SET menu_name = '{$menu_name}', position = {$position}, visible = {$visible}, content = '{$content}', WHERE id = {$id}"; $result = mysql_query($query); if (mysql_affected_rows() == 1) { // Success $message = "The page was successfully updated. "; } else { $message = "The page could not be updated. "; $message .= "<br />" . mysql_error(); } } else { if (count($errors) == 1){ $message = "There was one error in the form."; } else { $message = "There were " . count($errors) . " errors in the form."; } } // END FORM PROCESSING } ?> <?php find_selected_page(); ?> <?php include("includes/header.php"); ?> <table id="structure"> <tr> <td id="navigation"> <?php echo navigation($sel_subject, $sel_page); ?><br /> <a href="new_subject.php">+Add a new subject</a> </td> <td id="page"> <h2>Edit Page: <?php echo $sel_page['menu_name']; ?></h2> <?php if (!empty($message)) { echo "<p class=\"message\">" . $message . "</p>";} ?> <?php if (!empty($errors)) { display_errors($errors); } ?> <form action="edit_page.php?page=<?php echo $sel_page['id']; ?>" <method="post"> <?php include ("page_form.php"); ?> <input type="submit" name="submit" value="Update Page" /> <a href="delete_page2.php?page=<?php echo $sel_page['id']; ?>" onclick="return confirm('Are You sure you want to delete this page?');">Delete Page</a> </form> <hr> <a href="content.php?page=<?php echo $sel_page['id']; ?>">Cancel</a><hr></td></tr></table> <?php include("includes/footer.php"); ?> To understand the inside mechanism I give the codes of my page_form.php that I have included in edit_subject.php here is the code: <?php require_once("includes/session.php"); ?> <?php confirm_logged_in(); ?> <?php if (!isset($new_page)) {$new_page = false;} ?> <p>Page name: <input type="text" name="menu_name" value"<?php echo $sel_page['menu_name']; ?>" id="menu_name" /></p> <p>Position: <selecct name="position"> <?php if ($new_page) { $page_set = get_pages_for_subject($sel_page['subject_id']); $page_count = mysql_num_rows($page_set); } else { $page_set = get_pages_for_subject($sel_subject['id']); $page_count = mysql_num_rows($page_set) + 1; } for ($count=1; $count <= $page_count; $count++) { echo "<option value=\"{$count}\""; if ($sel_page['position'] == $count) { echo " selected"; } echo ">{$count}</option>"; } ?> </select></p> <p>Visible: <input type="radio" name="visible" value="0"<?php if($sel_page['visible'] == 0){ echo " checked"; } ?> /> NO <input type="radio" name="visible" value="1"<?php if($sel_page['visible'] == 1){ echo " checked"; } ?> /> Yes </p> <p>Content: <br /> <textarea name="content" rows="20" cols="80"><?php echo $sel_page['content']; ?></textarea> </p> Next I give the code of functions.php which I keep in includes folder. here is the code: <?php function mysql_prep( $value ){ $magic_quotes_active = get_magic_quotes_gpc(); $new_enough_php = function_exists( "mysql_real_escape_string" ); // i.e. PHP >= v4.3.0 if( $new_enough_php ){ // PHP v4.3.0 or higher /*undo any magic quote efects so mysql_real_escape_string can do the work */ if( $magic_quotes_active ){ $value = stripslashes( $value ); } $value = mysql_real_escape_string( $value ); } else { // before PHP v4.3.0 // if magic quotes aren't already on them add slashes manually if( !$magic_quotes_active ){ $value = addslashes( $value ); } // if magic quotes are active, then the slashes already exist } return $value; } function redirect_to( $location = NULL ){ if ($location != NULL){ header("Location: {$location}"); exit; } } function confirm_query($result_set){ if(!$result_set){ die("Database query failed: " . mysql_error()); } } function get_all_subjects($public = true){ global $connection; $query = "SELECT * FROM friends "; if ($public) { $query .= "WHERE visible = 1 "; } $query .= "ORDER by position ASC"; $subject_set = mysql_query($query, $connection); confirm_query($subject_set); return $subject_set; } function get_pages_for_subject($subject_id, $public = true){ global $connection; $query = "SELECT * FROM pages "; $query .= "WHERE subject_id = {$subject_id} "; if ($public) { $query .= "AND visible = 1 "; } $query .= "ORDER by position ASC"; $page_set = mysql_query($query, $connection); confirm_query($page_set); return $page_set; } function get_subject_by_id($subject_id){ global $connection; $query = "SELECT * "; $query .= "FROM friends "; $query .= "WHERE id=" . $subject_id ." "; $query .= "LIMIT 1"; $result_set = mysql_query($query, $connection); confirm_query($result_set); // if no rows are returned, fetch_array will rwturn false if($subject = mysql_fetch_array($result_set)){ return $subject; } else { return NULL; } } function get_page_by_id($page_id){ global $connection; $query = "SELECT * "; $query .= "FROM pages "; $query .= "WHERE id=" . $page_id ." "; $query .= "LIMIT 1"; $result_set = mysql_query($query, $connection); confirm_query($result_set); if($page = mysql_fetch_array($result_set)){ return $page; } else { return NULL; } } /* function get_page_by_id($page_id){ global $connection; $query = "SELECT * "; $query .= "FROM pages "; $query .= "WHERE id=" . $page_id ." "; $query .= "LIMIT 1"; $result_set = mysql_query($query, $connection); confirm_query($result_set); // if no rows are returned, fetch_array will rwturn false if($page = mysql_fetch_array($result_set)){ return $page; } else { return NULL; } } */ function get_default_page($subject_id){ // Get all visible pages $page_set = get_pages_for_subject($subject_id, true); if ($first_page = mysql_fetch_array($page_set)) { return $first_page; } else { return NULL; } } function find_selected_page(){ global $sel_subject; global $sel_page; if (isset($_GET['subj'])){ $sel_subject = get_subject_by_id($_GET['subj']); $sel_page = get_default_page($sel_subject['id']); }elseif (isset($_GET['page'])){ $sel_subject = NULL; $sel_page = get_page_by_id($_GET['page']); } else { $sel_subject = NULL; $sel_page = NULL; } } function navigation($sel_subject, $sel_page, $public = false){ $output = "<ul class=\"subjects\">"; $subject_set = get_all_subjects($public); while($subject = mysql_fetch_array($subject_set)){ $output .= "<li"; if ($subject["id"] == $sel_subject['id']){ $output.= "class=\"selected\""; } $output .= "><a href=\"edit_subject.php?subj=" . urlencode($subject["id"]) . "\">{$subject["menu_name"]}</a></li>"; $page_set = get_pages_for_subject($subject["id"]); $output .= "<ul class=\"pages\">"; while ($page = mysql_fetch_array($page_set)){ $output.= "<li>"; $output .= "<a href=\"edit_page.php?page=" . urlencode($page["id"]) . "\">{$page["menu_name"]}</a>"; $output .= "<hr>"; $output .= "</li>"; } $output .= "</ul>"; } $output .= "</ul>"; return $output; } function public_navigation($sel_subject, $sel_page, $public = true){ $output = "<ul class=\"subjects\">"; $subject_set = get_all_subjects($public); while($subject = mysql_fetch_array($subject_set)){ $output .= "<li"; if ($subject["id"] == $sel_subject['id']){ $output.= "class=\"selected\""; } $output .= "><a href=\"index2.php?subj=" . urlencode($subject["id"]) . "\">{$subject["menu_name"]}</a></li>"; if ($subject["id"] == $sel_subject["id"]){ $page_set = get_pages_for_subject($subject["id"]); $output .= "<ul class=\"pages\">"; while ($page = mysql_fetch_array($page_set)){ $output.= "<li"; if ($page["id"] == $sel_page['id']) { $output .= " class\"selected\""; } $output .= "><a href=\"index2.php?page=" . urlencode($page["id"]) . "\">{$page["menu_name"]}</a></li>"; } $output .= "</ul>"; } } $output .= "</ul>"; return $output; } ?> Now whenever I want to edit 'pages' in my navigation link I found message like this: Position: Database query failed: 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 'AND visible = 1 ORDER by position ASC' at line 2 Can anyone figure out where is the problem? If I make 'position' invalid in my page_form.php the update occurs. And 'pages' has been updated. But there is no 'subject_id' or 'position' obviously and it resides only in Database not showing up in my output. Quote Link to comment Share on other sites More sharing options...
anthylon Posted February 6, 2009 Share Posted February 6, 2009 Your query looks ok. You should check your get_pages_for_subject() function. I am pretty sure that parameter $subject_id from this function is empty after it execute. So trace code and test if $subject_id has any value. :-\ I assume it comes null and you get query: SELECT * FROM pages WHERE subject_id = ORDER by position ASC Of course, this would generate an error. Good luck Quote Link to comment Share on other sites More sharing options...
Sanjib Sinha Posted February 6, 2009 Author Share Posted February 6, 2009 Thanks for your response. I think the main problem field is 'subject_id'. As it is the link that maintained the relationship between two tables 'friends' and 'pages'. And like every relationship it seems very very complex(joking!). Anyway, it seems that the main funda is the relational database which I should study a lot. Again thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.