Form won't insert value into DB?

monkeymaker · January 7, 2011

Hi again all, I have made an update form where the user selects an ID from a dropdown, and then enters the new infomation into fields and saves it. Unfortunately I just cant quite seem to get it to actually pass the data into the database and save it, please see below.

This is studentfunc.php

<?php

require("db_config.php");

class dbstudent {
    /* DB connection handle */

    private $conn;

    function insert_student($sid, $name, $address, $postcode, $photo) {
        $esc_name = mysql_real_escape_string($name, $this->conn);
        $esc_address = mysql_real_escape_string($address, $this->conn);
        $esc_postcode = mysql_real_escape_string($postcode, $this->conn);
        $esc_photo = mysql_real_escape_string($photo, $this->conn);
        $sql = "insert into student (sid , name, address, postcode, photo)
values ('{$sid}', '{$esc_name}', '{$esc_address}', '{$esc_postcode}', '{$esc_photo}')";

        $result = mysql_query($sql, $this->conn);

        if (!$result) {
            die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }

     function student_update($sid, $name, $address, $postcode, $photo)
{
$esc_sid = mysql_real_escape_string($sid , $this->conn);
$esc_name = mysql_real_escape_string($name, $this->conn);
$esc_address = mysql_real_escape_string($address, $this->conn);
$esc_postcode = mysql_real_escape_string($postcode, $this->conn);
$esc_photo = mysql_real_escape_string($photo, $this->conn);



$sql = "
   UPDATE
      student
   SET
      name='" . $esc_name . "' where sid='" . $esc_sid . "'";




$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

    function openDB() {
        $this->conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        if (!$this->conn) {
            die("SQL Connection error: " . mysql_error());
        }
        $db_selected = mysql_select_db(DB_NAME, $this->conn);
        if (!$db_selected) {
            die("SQL Selection error: " . mysql_error());
        }

this is updatestudent.php

<?php include 'studentfunc.php'; //import the class in this web page ?>
<!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=utf-8" />
<title>Update Student</title>
</head>
<body>
<?php

$db1 = new dbstudent();
$db1->openDB();
$sql="select sid from student";
$result=$db1->getResult($sql);

if (!$_POST) //page loads for the first time
{
?>

    <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Select Student ID to update: <select name="sid">
<?php
while($row = mysql_fetch_assoc($result))
echo "<option value='{$row['sid']}'>{$row['sid']} </option>";
?>
</select>
    </form>

<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Update new student name:<input type="text" name="name" /><br />
Update new student address:<input type="text" name="address" /><br />
Update new student postcode:<input type="text" name="postcode" /><br />
Update new student picture:<input type="text" name="photo" /><br />
<br />
<input type="submit" value="Save" />
</form>
<?php
} //end if
else
{
$sid = $_POST['sid'];
$name = $_POST['name'];
$address = $_POST['address'];
$postcode = $_POST['postcode'];
$photo = $_POST['photo'];
$db1 = new dbstudent();
$db1->openDB();
$numofrows = $db1->student_update($name, $address, $postcode, $photo, $sid);
echo "Success. Number of rows affected:
<strong>{$numofrows}<strong>";
$db1->closeDB();
}
?>
</body>
</html>

dragon_sa · January 7, 2011

$sql = "insert into student (sid , name, address, postcode, photo)
values ('{$sid}', '{$esc_name}', '{$esc_address}', '{$esc_postcode}', '{$esc_photo}')";

should be

$sql = "UPDATE student (sid , name, address, postcode, photo)
values ('$sid', '$esc_name', '$esc_address', '$esc_postcode', '$esc_photo') WHERE sid='$sid'";

edit:

just looking again you dont need to update the student id so can remove sid and $sid from the update

$sql = "UPDATE student (name, address, postcode, photo)
values ('$esc_name', '$esc_address', '$esc_postcode', '$esc_photo') WHERE sid='$sid'";

monkeymaker · January 7, 2011

Hey thanks!! I don't want to sound stupid or anything though but when I added your updated section into studentfunc.php I re-ran updatestudent.php and the server is returning an error :

SQL 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 '(name, address, postcode, photo) values ('', '', '', '') WHERE sid=''' at line 1 :shrug:

dragon_sa · January 7, 2011

I think you will find that its is because $sid is not defined, define it with the other variables and it should work and i forgot to type in your curly brackets

monkeymaker · January 7, 2011

I think you will find that its is because $sid is not defined, define it with the other variables and it should work and i forgot to type in your curly brackets

Awesome thanks.. heres the current code

function student_update($name, $address, $postcode, $photo)
{
$esc_sid = mysql_real_escape_string($sid , $this->conn);
$esc_name = mysql_real_escape_string($name, $this->conn);
$esc_address = mysql_real_escape_string($address, $this->conn);
$esc_postcode = mysql_real_escape_string($postcode, $this->conn);
$esc_photo = mysql_real_escape_string($photo, $this->conn);
$sql = "UPDATE student (name, address, postcode, photo)
values ('{$esc_name}', '${esc_address}', '{$esc_postcode}', '${esc_photo}') WHERE sid='${sid}'";


$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

I'm not sure what you mean about defining $sid, it is a field in the DB and on the function ?

dragon_sa · January 7, 2011

I must apologise i misread the first part of your code as the update section and have given you some misguidence

it should be

$sql = "UPDATE student SET name='$esc_name' where sid='$esc_sid '";
$result = mysql_query($sql);

if that does not work echo the value 0f $esc_sid and $esc_name to make sure they are being set

monkeymaker · January 7, 2011

Ok thanks but sorry Im still a bit confused, the whole SQL section of the function should be this?

$sql = "UPDATE student SET name='$esc_name' where sid='$esc_sid '
values ('{$esc_name}', '${esc_address}', '{$esc_postcode}', '${esc_photo}' WHERE sid='${sid}')";

Myoga- · January 7, 2011

It looks like you're trying to update a row by using the INSERT commands syntax

$sql = "UPDATE student (name, address, postcode, photo)
values ('{$esc_name}', '${esc_address}', '{$esc_postcode}', '${esc_photo}') WHERE sid='${sid}'";

Now I'm not sure if it works using INSERT's syntax but try this:

$sql = "UPDATE student SET name='$esc_name', address='$esc_address', 
postcode='$esc_postcode', photo='$esc_photo' WHERE sid='$sid'";

Just try it for lols, if it works great if not then do what dragon_sa said and echo the values to make sure they're being set.

$sql = "UPDATE student SET name='$esc_name' where sid='$esc_sid '
values ('{$esc_name}', '${esc_address}', '{$esc_postcode}', '${esc_photo}' WHERE sid='${sid}')";

The above won't work since the fields are no longer declared for the values to inserted into.

UPDATE has 3 things that it requires to work;

UPDATE

SET

WHERE Can be left out unless you want to limit the rows affected

You are using INSERTS syntax in an UPDATE command which won't work properly (or at least shouldn't).

monkeymaker · January 7, 2011

It ran but now rows were effected :confused:

Can you give an example on where I should put the echo statements for the variables. Should that be in the function or the php page? Also what about vardump() ?

Thanks for having the patience to help me out!

Myoga- · January 7, 2011

It ran but now rows were effected

Can you give an example on where I should put the echo statements for the variables. Should that be in the function or the php page? Also what about vardump() ?

Thanks for having the patience to help me out!

Add it above the return in the function. Since you say its querying correctly now I assume we can just add it to the success part of the if statement.

$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

->

$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
print("$sid"); /*You can add other values as well to see them all*/
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

If sid isn't being set properly it won't be able to find the row to edit.

monkeymaker · January 7, 2011

Hey thanks for getting back to me again!

I tried some other changes now I'm getting the following

SQL 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 'values ('test', 'test', 'test', '' WHERE sid='test')' at line 2

This is student_update()

 function student_update($sid, $name, $address, $postcode, $photo)
{
$esc_sid = mysql_real_escape_string($sid , $this->conn);
$esc_name = mysql_real_escape_string($name, $this->conn);
$esc_address = mysql_real_escape_string($address, $this->conn);
$esc_postcode = mysql_real_escape_string($postcode, $this->conn);
$esc_photo = mysql_real_escape_string($photo, $this->conn);
$sql = "UPDATE student SET name='{$esc_name}' where sid='${esc_sid} '
values ('{$esc_name}', '${esc_address}', '{$esc_postcode}', '${esc_photo}' WHERE sid='${sid}')";

$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
print("$sid"); /*see sid*/
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

Myoga- · January 7, 2011

Hey thanks for getting back to me again!

I tried some other changes now I'm getting the following

SQL 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 'values ('test', 'test', 'test', '' WHERE sid='test')' at line 2

This is student_update()
 function student_update($sid, $name, $address, $postcode, $photo)
{
$esc_sid = mysql_real_escape_string($sid , $this->conn);
$esc_name = mysql_real_escape_string($name, $this->conn);
$esc_address = mysql_real_escape_string($address, $this->conn);
$esc_postcode = mysql_real_escape_string($postcode, $this->conn);
$esc_photo = mysql_real_escape_string($photo, $this->conn);
$sql = "UPDATE student SET name='{$esc_name}' where sid='${esc_sid} '
values ('{$esc_name}', '${esc_address}', '{$esc_postcode}', '${esc_photo}' WHERE sid='${sid}')";

$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
print("$sid"); /*see sid*/
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

You're using values in update again.

$sql = "UPDATE student SET name='{$esc_name}', address='{$esc_address}', 
postcode='{$esc_postcode}', photo='{$esc_photo}' WHERE sid='{$sid}'";

Should work fine.

Pikachu2000 · January 7, 2011

INSERT syntax:

INSERT INTO `table` (`field1`, `field2`) VALUES ( 'string_value', numeric_value )

UPDATE syntax:

UPDATE `table` SET `field1` = 'string_value', `field2` = numeric_value WHERE `some_field` = 'some_value'

The difference between string values and numeric values is that strings get quoted in the query string, whereas numbers shouldn't be.

monkeymaker · January 7, 2011

Hey, thanks again. The SQL statement now runs but actually I think there might be something a bit wierd going on with my input forms, print 'sid', as used on the function, is actually outputting the input from 'name'? Something must be missing or I am trying something totally wrong?

See

<?php include 'studentfunc.php'; //import the class in this web page ?>
<!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=utf-8" />
<title>Update Student</title>
</head>
<body>
<?php

$db1 = new dbstudent();
$db1->openDB();
$sql="select sid from student";
$result=$db1->getResult($sql);

if (!$_POST) //page loads for the first time
{
?>

    <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Select Student ID to update: <select name="sid">
<?php
while($row = mysql_fetch_assoc($result))
echo "<option value='{$row['sid']}'>{$row['sid']} </option>";
?>
</select>
    </form>

<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Update new student name:<input type="text" name="name" /><br />
Update new student address:<input type="text" name="address" /><br />
Update new student postcode:<input type="text" name="postcode" /><br />
Update new student picture:<input type="text" name="photo" /><br />
<br />
<input type="submit" value="Save" />
</form>
<?php
} //end if
else
{
$sid = $_POST['sid'];
$name = $_POST['name'];
$address = $_POST['address'];
$postcode = $_POST['postcode'];
$photo = $_POST['photo'];
$db1 = new dbstudent();
$db1->openDB();
$numofrows = $db1->student_update($name, $address, $postcode, $photo, $sid);
echo "Success. Number of rows affected:
<strong>{$numofrows}<strong>";
$db1->closeDB();
}
?>
</body>
</html>

Myoga- · January 7, 2011

I just checked updatestudent.php and you have this line:

$numofrows = $db1->student_update($name, $address, $postcode, $photo, $sid);

But the function is looking for the values in this order:

$sid, $name, $address, $postcode, $photo

So...

Change

$numofrows = $db1->student_update($name, $address, $postcode, $photo, $sid);

->

$numofrows = $db1->student_update($sid, $name, $address, $postcode, $photo);

monkeymaker · January 7, 2011

**updated**

Thank you, I sincerely appreciate your efforts. I have made some ammendments as suggested, see the function below.

The user selects the ID they want to change in the database, then the user fills in the input boxes and submit button should update or change the exisiting fields on the database to the new ones entered in by the user using the input boxes.

Right now the newly entered inputs from the form fields just dont over write the existing db fields :shrug:

Please advise

  function student_update($sid, $name, $address, $postcode, $photo)
{
$esc_sid = mysql_real_escape_string($sid , $this->conn);
$esc_name = mysql_real_escape_string($name, $this->conn);
$esc_address = mysql_real_escape_string($address, $this->conn);
$esc_postcode = mysql_real_escape_string($postcode, $this->conn);
$esc_photo = mysql_real_escape_string($photo, $this->conn);

$sql = "UPDATE student SET
name='{$esc_name}',
address='{$esc_address}',
postcode='{$esc_postcode}',
photo='{$esc_photo}'

WHERE sid='{$esc_sid}'";

$result = mysql_query($sql, $this->conn);
if(!$result)
die("SQL Error: " . mysql_error());
else
{
print("$esc_sid"); /*see sid*/
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

<?php include 'studentfunc.php'; //import the class in this web page ?>
<!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=utf-8" />
<title>Update Student</title>
</head>
<body>
<?php

$db1 = new dbstudent();
$db1->openDB();
$sql="select sid from student";
$result=$db1->getResult($sql);

if (!$_POST) //page loads for the first time
{
?>

    <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Select Student ID to update: <select name="sid">
<?php
while($row = mysql_fetch_assoc($result))
echo "<option value='{$row['sid']}'>{$row['sid']} </option>";
?>
</select>
    </form>

<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
Update new student name:<input type="text" name="name" /><br />
Update new student address:<input type="text" name="address" /><br />
Update new student postcode:<input type="text" name="postcode" /><br />
Update new student picture:<input type="text" name="photo" /><br />
<br />
<input type="submit" value="Save" />
</form>
<?php
} //end if
else
{
$sid = $_POST['sid'];
$name = $_POST['name'];
$address = $_POST['address'];
$postcode = $_POST['postcode'];
$photo = $_POST['photo'];
$db1 = new dbstudent();
$db1->openDB();
$numofrows = $db1->student_update($sid, $name, $address, $postcode, $photo);
echo "Success. Number of rows affected:
<strong>{$numofrows}<strong>";
$db1->closeDB();
}
?>
</body>
</html>

Myoga- · January 7, 2011

edit: Just a second...

Myoga- · January 7, 2011

Try echoing all the values that are being submitted,

change

print("$esc_sid"); /*see sid*/

->

print("$esc_sid, {$esc_name}, $esc_address, {$esc_postcode}, $esc_photo"); /*see sid*/

Just to see if it all matches up.

monkeymaker · January 7, 2011

Yes thanks all matches up using print() now it just needs to save into the student table on the db :shrug:

Myoga- · January 7, 2011

Try this...

  function student_update($sid, $name, $address, $postcode, $photo)
{
$esc_sid = mysql_real_escape_string($sid , $this->conn);
$esc_name = mysql_real_escape_string($name, $this->conn);
$esc_address = mysql_real_escape_string($address, $this->conn);
$esc_postcode = mysql_real_escape_string($postcode, $this->conn);
$esc_photo = mysql_real_escape_string($photo, $this->conn);

$sql = "UPDATE student SET 
name='$esc_name', 
address='$esc_address', 
postcode='$esc_postcode', 
photo='$esc_photo' 

WHERE sid='$sid'";

$result = mysql_query($sql, $this->conn);
if(!$result) {
die("SQL Error: " . mysql_error());
} else {
print("$esc_sid"); /*see sid*/
$numofrows = mysql_affected_rows($this->conn);
return $numofrows;
}
}

Sign In

Form won't insert value into DB?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information