MySQL database not updating with PHP

Question

Quite simply, my MySQL database isn't updating when the form is submitted. The entire file's code can be seen below, thanks: Please note that the checkuserlog.php file does include a file that connects to the MySQL database...

<?php
include_once("../scripts/checkuserlog.php");
?>
<?php 
if (!isset($_SESSION['id']) || !isset($_SESSION['username'])){ 
   echo 'Please <a href="../login.php">log in</a> to view';
   exit(); 
}

// set variables
$id = $logOptions_id;
$school = "";
$form_teacher = "";
$room = "";
$success_msg = "";
$error_msg = "";
$errorMsg = "";
$sch_website = "";
$sch_phone = "";
$sch_email = "";
$notes = ""; 
// end variables and begin parsing
if (isset($_POST['parse_var'])){

if ($_POST['parse_var'] == "allstuff"){
    $form_teacher = strip_tags($_POST['form_teacher']);
    $form_teacher = str_replace("'", "&#39;", $form_teacher);
    $form_teacher = str_replace("`", "&#39;", $form_teacher);
    $form_teacher = mysql_real_escape_string($form_teacher);
    $school = strip_tags($_POST['school']);
    $school = str_replace("'", "&#39;", $school);
    $school = str_replace("`", "&#39;", $school);
    $school = mysql_real_escape_string($school);
    $room = strip_tags($_POST['room']);
    $room = str_replace("'", "&#39;", $room);
    $room = str_replace("`", "&#39;", $room);
    $room = mysql_real_escape_string($room);
    $sch_website = strip_tags($_POST['sch_website']);
    $sch_website = str_replace("'", "&#39;", $sch_website);
    $sch_website = str_replace("`", "&#39;", $sch_website);
    $sch_website = mysql_real_escape_string($sch_website); 
    $sch_phone = strip_tags($_POST['sch_phone']);
    $sch_phone = str_replace("'", "&#39;", $sch_phone);
    $sch_phone = str_replace("`", "&#39;", $sch_phone);
    $sch_phone = mysql_real_escape_string($sch_phone);
    $sch_email = strip_tags($_POST['sch_email']);
    $sch_email = str_replace("'", "&#39;", $sch_email);
    $sch_email = str_replace("`", "&#39;", $sch_email);
    $sch_email = mysql_real_escape_string($sch_email); 
    $notes = strip_tags($_POST['pln_notes']);
    $notes = str_replace("'", "&#39;", $notes);
    $notes = str_replace("`", "&#39;", $notes);
    $notes = mysql_real_escape_string($notes);

    $sqlUpdate = mysql_query("UPDATE table SET form_teacher='$form_teacher', school='$school', form_room='$room', sch_website='$sch_website', sch_phone='$sch_phone', sch_email='$sch_email' pln_notes='$notes' WHERE mem_id='$id' LIMIT 1");
    if ($sqlUpdate){
        $success_msg = '<img src="images/round_success.png" width="20" height="20" alt="Success" />We successfully updated your personal information.';
    } else {
        $error_msg = '<img src="images/round_error.png" width="20" height="20" alt="Failure" /> ERROR: Problems arose during the information exchange, please try again later.</font>';
    }
}
}
// end parsing
// get existing data
$sql_default = mysql_query("SELECT * FROM table WHERE mem_id='$id'");

while($row = mysql_fetch_array($sql_default)){ 

    $school = $row["school"];
    $form_teacher = $row["form_teacher"];
    $room = $row["form_room"];  
    $sch_website = $row["sch_website"];
    $sch_phone = $row["sch_phone"];
    $sch_email = $row["sch_email"];
    $notes = $row["pln_notes"];
    $notes = str_replace("<br />", "", $notes);
    $notes = stripslashes($notes);


} 
// end get data
?>
<!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" />
<meta http-equiv="X-UA-Compatible" content="IE=edge;chrome=1">
<title>Edit Your Planner</title>
<link href="../style/main.css" rel="stylesheet" type="text/css" />
<link rel="icon" href="../favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
</head>
<body>
<?php include_once "../header_template.php"; ?>
<div id="maincont">
<h1>Edit your Planner <span style="float:right;margin-right:270px;"><a href="planner.php" class="medium awesome green">View Planner</a></span></h1>
<table border="0" width="950">
<tr><td width="680" valign="top">
<h2><?php echo $success_msg; ?><font color="#FF0000"><?php echo $errorMsg; ?></font></h2>
<table width="100%" border="0">
<tr><td width="50%" valign="top">
<form name="editplanner" method="post" action="planner_edit.php">
<p><strong>School:</strong><br /><input type="text" name="school" id="school" placeholder="What's the name of your school?" value="<?php echo $school; ?>" class="formfield" size="45"></p>
<p><strong>Form Teacher:</strong><br /><input type="text" name="form_teacher" id="form_teacher" value="<?php echo $form_teacher; ?>" placeholder="Who is you form/class teacher?" class="formfield" size="45"></p>
<p><strong>Form Room:</strong><br /><input type="text" name="room" id="room" class="formfield" value="<?php echo $room; ?>" placeholder="Room number or room name" size="45"></p>
<p><strong>School Website:</strong><br /><input type="text" name="sch_website" id="sch_website" class="formfield" value="<?php echo $sch_website; ?>" placeholder="School website URL here" size="45"></p>
<p><strong>Phone:</strong><br /><input type="text" name="sch_phone" id="sch_phone" class="formfield" value="<?php echo $sch_phone; ?>" placeholder="School phone number" size="45"></p>
<p><strong>Email:</strong><br /><input type="text" name="sch_email" id="sch_email" class="formfield" value="<?php echo $sch_email; ?>" placeholder="School email address" size="45"></p>
</td>
<td width="50%" valign="top">
<h2>Notes</h2>
<textarea name="pln_notes" id="pln_notes" placeholder="Add notes to your planner here..." style="width:340px;height:267px;"><?php echo $notes; ?></textarea>
</td></tr></table>
<input name="parse_var" type="hidden" value="allstuff" />
<input type="submit" name="updateBtn2" class="formfield" value="Update!" /></form>
<hr />
</td>
<td width="270" valign="top"><?php include_once "../temps/sidebar.php"; ?>
</td></tr></table>
</div>
<?php include_once "../footer_template.php"; ?>
</body>
</html>

What have you done to try and debug the code yourself? Any error messages? — Aaron W.
– Aaron W., Commented Apr 23, 2012 at 19:41
When you run the code, does your success message show? or does your error message? are there any warnings/errors in your PHP log? — Developer
– Developer, Commented Apr 23, 2012 at 19:42
You really should consider using parameterized queries or (even better) some framework for interacting with SQL. All it takes is forgetting to escape one variable by hand and you've got a potentially serious security problem. — Eli
– Eli, Commented Apr 23, 2012 at 19:43

Gustavo Vargas · Accepted Answer · 2012-04-23 19:42:46Z

2

You query looks wrong, you miss a coma before pln_notes=, and the limit 1 is unnecessary for the update query.

answered Apr 23, 2012 at 19:42

Gustavo Vargas

2,6272 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Developer · Accepted Answer · 2012-04-23 19:42:04Z

0

it doesn't look like $logOptions_id is being set anywhere, so $id is likely to be null

Also, your update query is missing a comma after "sch_email":

$sqlUpdate = mysql_query("UPDATE table SET form_teacher='$form_teacher', school='$school', form_room='$room', sch_website='$sch_website', sch_phone='$sch_phone', sch_email='$sch_email', pln_notes='$notes' WHERE mem_id='$id' LIMIT 1");

answered Apr 23, 2012 at 19:42

Developer

2,00612 silver badges11 bronze badges

1 Comment

Eli Over a year ago

You don't need the quotes around an integer... but they also don't hurt anything. That's not the problem.

Oscar Jara · Accepted Answer · 2012-04-23 19:45:49Z

0

Please, take a look at this if your Id's are integer / Your code in first SQL query:

$sqlUpdate = mysql_query("UPDATE table SET form_teacher='$form_teacher', school='$school', form_room='$room', sch_website='$sch_website', sch_phone='$sch_phone', sch_email='$sch_email' pln_notes='$notes' WHERE mem_id='$id' LIMIT 1");

Incorrect (for me):

mem_id='$id'

Because if mem_id is Integer please use:

mem_id=$id

The same happens for second SQL query:

$sql_default = mysql_query("SELECT * FROM table WHERE mem_id='$id'");

Correction if mem_id is Integer:

mem_id=$id

Hope this solve your error.

answered Apr 23, 2012 at 19:45

Oscar Jara

14.1k10 gold badges64 silver badges94 bronze badges

Comments

EstelS · Accepted Answer · 2012-04-23 19:48:54Z

0

Yes, the other answers here are correct. Your update statement is missing a comma for the last column that is being updated.

However, for future reference, you may be able track SQL query errors by using a utility function that actually logs errors in your queries. An example is below.

function db_query($query) {
    $result = mysql_query($query);

    $error_text = mysql_error();

    if ($error_text) {
        trigger_error('SQL ERROR: ' . $error_text, E_USER_WARNING);
    }

    return $result;
}

This should help you track down SQL related errors in the future.

answered Apr 23, 2012 at 19:48

EstelS

1631 silver badge6 bronze badges

Collectives™ on Stack Overflow

MySQL database not updating with PHP

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related