Database query failed

[Sanjib Sinha]

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.

 

 

 

 

 

 

[anthylon]

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

[Sanjib Sinha]

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.