updating records using php and mysql

•

What is DaniWeb IT Discussion Community?

You're currently browsing the PHP section within the Web Development category of DaniWeb, a massive community of 427,420 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,483 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our PHP advertiser: Lunarpages PHP Web Hosting

Thread Tools Display Modes

Views: 3464 | Replies: 6

•

Join Date: Nov 2004

Posts: 229

Reputation: sam1 is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

sam1

Offline

Posting Whiz in Training

updating records using php and mysql

Mar 23rd, 2007

hi,

I am creating a form for user to update their details which is saved in the database. So far i can show their details in the form where i have a submit button but the updating part doesnt work(it doesnt update at all). here is the code:

Help with Code Tags

(Toggle Plain Text)

<html>
<?php

session_start();

$host="localhost"; // Host name
$username="user"; // Mysql username
$password="123456"; // Mysql password
$db_name="db"; // Database name
$tbl_name="member"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");  
    
    
$username = $_SESSION['myusername'];

if(!isset($_SESSION['myusername']))
	{
   header("location: main_login.php");
}
  else
{
	$query=" SELECT * FROM $tbl_name WHERE LoginName='$username'";
	$result=mysql_query($query);
	$num=mysql_numrows($result);
	mysql_close();

$i=0;
while ($i < $num) {
$phone=mysql_result($result,$i,"telephone");
$add=mysql_result($result,$i,"address");
$town=mysql_result($result,$i,"town");
$email=mysql_result($result,$i,"email");
$city=mysql_result($result,$i,"city");
$postcode=mysql_result($result,$i,"postcode");
++$i;
}
$u_phone=$_POST['phone'];
$u_add=$_POST['add'];
$u_town=$_POST['town'];
$u_city=$_POST['city'];
$u_postcode=$_POST['postcode'];
$u_email=$_POST['email'];


$query1="UPDATE $tbl_name SET memberNo = NULL, LoginName=NULL, title=NULL, firstName=NULL, lastName=NULL, gender= NULL, address='$u_add', town='$u_town', city='$u_city', postcode='$u_postcode', telephone='$u_phone',  email='$u_email', mshipType = NULL";
mysql_query($query1);
echo "Record Updated";
mysql_close();
}
?>
<form action="changedetails.php" method="post">
<table>
<tr>
	<td>Phone Number :</td>
	<td><input type="text" name="phone" size="30" value="<?php echo $phone; ?>"></td>
</tr>
<tr>
	<td>Address :</td>
	<td><input type="text" name="add" size="30" value="<?php echo $add; ?>"></td>
</tr>
<tr>
	<td>Town :</td>
	<td><input type="text" name="town" size="30" value="<?php echo $town; ?>"></td>
</tr>
<tr>
	<td>City :</td>
	<td><input type="text" name="city" size="30" value="<?php echo $city; ?>"></td>
</tr>
<tr>
	<td>Post Code :</td>
	<td><input type="text" name="postcode" size="30" value="<?php echo $postcode; ?>"></td>
</tr>
<tr>
	<td>E-mail Address :</td>
	<td><input type="text" name="email" size="30" value="<?php echo $email; ?>" ></td>
</tr>
</table>

 <p>

<input type="Submit" value="Update">
<input type="Submit" value="Cancel">
</form>
</html>

<html>
<?php

session_start();

$host="localhost"; // Host name
$username="user"; // Mysql username
$password="123456"; // Mysql password
$db_name="db"; // Database name
$tbl_name="member"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");  
    
    
$username = $_SESSION['myusername'];

if(!isset($_SESSION['myusername']))
	{
   header("location: main_login.php");
}
  else
{
	$query=" SELECT * FROM $tbl_name WHERE LoginName='$username'";
	$result=mysql_query($query);
	$num=mysql_numrows($result);
	mysql_close();

$i=0;
while ($i < $num) {
$phone=mysql_result($result,$i,"telephone");
$add=mysql_result($result,$i,"address");
$town=mysql_result($result,$i,"town");
$email=mysql_result($result,$i,"email");
$city=mysql_result($result,$i,"city");
$postcode=mysql_result($result,$i,"postcode");
++$i;
}
$u_phone=$_POST['phone'];
$u_add=$_POST['add'];
$u_town=$_POST['town'];
$u_city=$_POST['city'];
$u_postcode=$_POST['postcode'];
$u_email=$_POST['email'];


$query1="UPDATE $tbl_name SET memberNo = NULL, LoginName=NULL, title=NULL, firstName=NULL, lastName=NULL, gender= NULL, address='$u_add', town='$u_town', city='$u_city', postcode='$u_postcode', telephone='$u_phone',  email='$u_email', mshipType = NULL";
mysql_query($query1);
echo "Record Updated";
mysql_close();
}
?>
<form action="changedetails.php" method="post">
<table>
<tr>
	<td>Phone Number :</td>
	<td><input type="text" name="phone" size="30" value="<?php echo $phone; ?>"></td>
</tr>
<tr>
	<td>Address :</td>
	<td><input type="text" name="add" size="30" value="<?php echo $add; ?>"></td>
</tr>
<tr>
	<td>Town :</td>
	<td><input type="text" name="town" size="30" value="<?php echo $town; ?>"></td>
</tr>
<tr>
	<td>City :</td>
	<td><input type="text" name="city" size="30" value="<?php echo $city; ?>"></td>
</tr>
<tr>
	<td>Post Code :</td>
	<td><input type="text" name="postcode" size="30" value="<?php echo $postcode; ?>"></td>
</tr>
<tr>
	<td>E-mail Address :</td>
	<td><input type="text" name="email" size="30" value="<?php echo $email; ?>" ></td>
</tr>
</table>

 <p>

<input type="Submit" value="Update">
<input type="Submit" value="Cancel">
</form>
</html>

here is the table:

Help with Code Tags

(Toggle Plain Text)

CREATE TABLE Member(
 memberNo       INT(8) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
 LoginName 	VARCHAR(6)  NOT NULL UNIQUE,
 title          VARCHAR(5),
 firstName      VARCHAR(20) NOT NULL,
 lastName       VARCHAR(20) NOT NULL,
 gender         VARCHAR(6)  NOT NULL
                             CHECK(gender IN('MALE','FEMALE')),
 address        VARCHAR(30), 
 town           VARCHAR(20),
 city           VARCHAR(20),
 postcode       VARCHAR(8)  NOT NULL,
 telephone      INT(11),
 email          VARCHAR(50) NOT NULL,
 mshipType      VARCHAR(15) NOT NULL 
                             CHECK(mshipType IN('STUDENT','STAFF','ALUMNI')),
 password       VARCHAR(20) NOT NULL,
 PRIMARY KEY(memberNo)
);

Last edited by sam1 : Mar 23rd, 2007 at 2:40 pm.

•

Join Date: Mar 2007

Location: georgia

Posts: 55

Reputation: hunkychop is an unknown quantity at this point

Rep Power: 2

Solved Threads: 4

hunkychop

Offline

Junior Poster in Training

Re: updating records using php and mysql

Mar 23rd, 2007

i ran a search in the php documentation.

i dont know if this will help, but its worth checking out.

http://us2.php.net/manual/en/function.newt-refresh.php

•

Join Date: Nov 2005

Posts: 2

Reputation: jgutierrez@mail is an unknown quantity at this point

Rep Power: 0

Solved Threads: 1

jgutierrez@mail jgutierrez@mail is offline

Offline

Newbie Poster

Re: updating records using php and mysql

Mar 23rd, 2007

According to the UPDATE syntax, I observe that your $query1 is missing the WHERE clause to select the record or records to be updated.

The specification says that if the WHERE is missing, every row in the table will be updated!.

I do not thik that that is what you want.

i i am correct, you should add to $query1 the following text:
WHERE LoginName='$username'';

•

Join Date: Nov 2004

Posts: 229

Reputation: sam1 is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

sam1

Offline

Posting Whiz in Training

Re: updating records using php and mysql

Mar 24th, 2007

no worries guys it is solved:cheesy:

•

Join Date: Mar 2008

Posts: 1

Reputation: jicoro is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

jicoro

Offline

Newbie Poster

Re: updating records using php and mysql

Mar 1st, 2008

how did you solve it? I really need to know...our project is due next week,,,please...

•

Join Date: Apr 2006

Posts: 66

Reputation: silviuks is an unknown quantity at this point

Rep Power: 3

Solved Threads: 11

silviuks

Offline

Junior Poster in Training

Re: updating records using php and mysql

Mar 2nd, 2008

Hi,

if you don't have the WHERE clause in your sql script, your entire table will be update, but if you don't see this you probably have some other errors. try use:
mysql_query($sql) or die(mysql_error());

also try to use addslashes() function to escape some chars.

www.aiss-softwork.com