Database Update script problems

as22607 · #1 May 12th, 2007, 21:31

Hi there again,

ok i am having a problem with my update script, it is meant to update a record after first you enter the record you are looking for it pulls it up on php which then display it in form so that it can tehn be sent via get to the script tha tupdates it. i rather suspect its my sql command that wrong

here is the 3 piece of script

Quote:

<html>
<title>Update Student</title>
Enter Student ID to update results
<form action="update.php" method="post">
Student ID: <input type="text" name="studentid"><br>
<input type="submit" value="OK">
</form>
</html>

Quote:

<html>
<title>Updating Student Record</title>
<?
$studentid=$_POST['studentid'];
$user="root";
$password="no";
$database="college";
mysql_connect("localhost",$user);
@mysql_select_db($database) or die( "Unable to select database");
$query="select * from student where id = '$studentid'";

mysql_query($query);
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();
$i =0;
$studentid=mysql_result($result,$i,"id");
$studentname=mysql_result($result,$i,"name");
$class=mysql_result($result,$i,"class");
$maths=mysql_result($result,$i,"math");
$english=mysql_result($result,$i,"english");
$socsci=mysql_result($result,$i,"socsci");
echo "<b> Record for $studentname to be updated</b>";
?>
<form action="apply.php" method="GET">

Student ID : <input type="text" name="id" value ="<? echo $studentid; ?>"><br>
Name : <input type="text" name="name" value ="<? echo $studentname;?>"><br>
Class : <input type="text" name="class" value ="<? echo $class; ?>"><br>
Maths : <input type="text" name="maths" value ="<? echo $maths;?>"><br>
English : <input type="text" name="english" value ="<? echo $english;?>"><br>
Social Sci : <input type="text" name="socsci" value ="<? echo $socsci;?>"><br>
<input type="submit" value="Update">
</form>
</html>

Quote:

<html>
<title>Updating student record</title>
<?
$user="root";
$password="no";
$database="college";
mysql_connect("localhost",$user);
@mysql_select_db($database) or die( "Unable to select database");

$studentid=$_GET['id'];
$studentname=$_GET['name'];
$class=$_GET['class'];
$maths=$_GET['maths'];
$english=$_GET['english'];
$socsci=$_GET['socsci'];
$query="UPDATE student SET
id = '$studentid',
name = '$studentname',
class = '$class',
maths = '$maths',
english = '$english',
socsci = '$socsci',
WHERE id = '$studentid'";

mysql_query($query);
mysql_query('COMMIT');
printf ("Updated records: %d\n", mysql_affected_rows());
echo "Record Updated, Results below". '<br><br>';
mysql_close();

echo $studentid . '<br>';
echo $studentname . '<br>';
echo $class . '<br>';
echo $maths . '<br>';
echo $english . '<br>';
echo $socsci . '<br>';

//header("Location: staff.html");
?>
</form>
</html>

any help will be appericated

thanks

CarolineBogart · #2 May 13th, 2007, 06:20

Method is post but you're using Get. Use $_POST to get user's input.

Is the password really "no"?

echo $query to the browser, then paste it in the sql tool on phpmyadmin, it might give a syntax error that'd be a pain to parse by eye.

Accurax · #3 May 13th, 2007, 09:27

ignore this... i should really actually read code before making comments :P

as22607 · #4 May 13th, 2007, 10:51

Quote:

Originally Posted by CarolineBogart

Method is post but you're using Get. Use $_POST to get user's input.

Is the password really "no"?

echo $query to the browser, then paste it in the sql tool on phpmyadmin, it might give a syntax error that'd be a pain to parse by eye.

i have tried with post and get nither work

the password is only set to no on the localhost on my machine the live version would not be set to no

as22607 · #5 May 13th, 2007, 10:53

Quote:

Originally Posted by Accurax

Code: Select all

      $query="UPDATE student SET

your missing some punctuation

i assume it should be UPDATE 'student' SET? if so i have already tried that to im really confussed as to why just abotu everything i have tried seems to fail but ill give both your and the above poster methods a try see if i can get it to debug

as22607 · #6 May 13th, 2007, 11:00

this is the sql error i am getting

ERROR: Unclosed quote @ 121STR: 'SQL: UPDATE 'student' SET studentname = 'test', class = 'test', math = '30', english = '40', socsci = '50', WHERE studentid = '1000SQL-query :
UPDATE 'student' SET studentname = 'test', class = 'test', math = '30', english = '40', socsci = '50', WHERE studentid = '1000
MySQL said:

#1064 - 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 ''student' SET studentname = 'test', class = 'test', math = '30'

CarolineBogart · #7 May 13th, 2007, 11:04

put a closing tick after '1000

Accurax · #8 May 13th, 2007, 11:22

ignore this... not reading properly

as22607 · #9 May 13th, 2007, 12:15

all it says now is

WHERE studentid = '1000 ' that where there error is although i am stumped because the query seems fine to me and when i do it via myphpadmin for updating its the exact same query so i dnt understadn why it wont work

i guess i am missing something trival

CarolineBogart · #10 May 14th, 2007, 07:05

post the entire query as printed out through an echo statement after building it in PHP?

as22607 · #11 May 14th, 2007, 18:08

Quote:

Originally Posted by CarolineBogart

post the entire query as printed out through an echo statement after building it in PHP?

ok i have edited the code so ill repost it all again

Quote:

<html>
<title>Update Student</title>
Enter Student ID to update results
<form action="update.php" method="post">
Student ID: <input type="text" name="studentid"><br>
<input type="submit" value="OK">
</form>
</html>

Quote:

<?
echo "<title>Updating employee table</title>";
$studentid=$_POST['studentid'];
$user="root";
$password="no";
$database="college";
mysql_connect("localhost",$user);
@mysql_select_db($database) or die( "Unable to select database");
$query="select * from student where id = '$studentid'";

mysql_query($query);
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();
$i =0;
$studentid=mysql_result($result,$i,"id");
$studentname=mysql_result($result,$i,"name");
$class=mysql_result($result,$i,"class");
$maths=mysql_result($result,$i,"math");
$english=mysql_result($result,$i,"english");
$socksi=mysql_result($result,$i,"socsci");
echo "<b> Record for $studentname to be updated</b>";
?>
<form action="apply.php" method="POST">

Student Id : <input type="text" name="studentid" value ="<? echo $studentid; ?>"><br>
Student Name : <input type="text" name="studentname" value ="<? echo $studentname;?>"><br>
Class : <input type="text" name="class" value ="<? echo $class; ?>"><br>
Maths : <input type="text" name="maths" value ="<? echo $maths;?>"><br>
English : <input type="text" name="english" value ="<? echo $english;?>"><br>
Socksi : <input type="text" name="socksi" value ="<? echo $socksi;?>"><br>
<input type="Submit">
</form>
</html>

Quote:

<html>
<title>Updating student record</title>
<?
$user="root";
$password="no";
$database="college";

mysql_connect("localhost",$user);
@mysql_select_db($database) or die( "Unable to select database");

$studentid=$_POST['studentid'];
$studentname=$_POST['studentname'];
$class=$_POST['class'];
$maths=$_POST['maths'];
$english=$_POST['english'];
$socksi=$_POST['socksi'];
$query="UPDATE student SET
name = '$studentname',
class = '$class',
math = '$maths',
english = '$english',
socsci = '$socksi',
WHERE id = '$studentid'";
echo $query;
mysql_query($query);
mysql_query("COMMIT");
echo "Record Updated, Results below". '<br><br>';
mysql_close();

echo $studentid . '<br>';
echo $studentname . '<br>';
echo $class . '<br>';
echo $maths . '<br>';
echo $english . '<br>';
echo $socksi . '<br>';
?>
</html>

ok here is the query outputted

UPDATE student SET name = 'test', class = 'test', math = '30', english = '40', socsci = '50', WHERE id = '1000'

here is what db admin says when i use the command

Error
SQL-query :
UPDATE student SET name = 'test',
class = 'test',
math = '30',
english = '40',
socsci = '50',
WHERE id = '1000'
MySQL said:

#1064 - 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 'WHERE id = '1000'' at line 6

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
[SOLVED] Use info from Database in a script ???	WebNinja	PHP Forum	2	May 3rd, 2008 00:59
More script problems yet again	a.jenery	Classic ASP	12	May 9th, 2006 09:24
problems connecting to database	benbacardi	Classic ASP	8	Apr 15th, 2005 16:16
still having problems writing to database...	benbacardi	Classic ASP	9	Aug 26th, 2004 16:20
Update autonumber field in Access Database	redkyna	Databases	3	Aug 20th, 2004 09:18

		Webforumz.com > Main Forums > Program Your Website > PHP Forum
Database Update script problems