DaniWeb Forum Index > Web Development > PHP

updating records using php and mysql

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Last »

•

Join Date: Nov 2004

Posts: 257

Reputation: sam1 is an unknown quantity at this point

Solved Threads: 1

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 
 PHP Syntax (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 
 PHP Syntax (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)
);
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 3:40 pm.

•

Join Date: Mar 2007

Posts: 55

Reputation: hunkychop is an unknown quantity at this point

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

Solved Threads: 1

jgutierrez@mail jgutierrez@mail is offline

Offline

Newbie Poster

Re: updating records using php and mysql

Mar 24th, 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: 257

Reputation: sam1 is an unknown quantity at this point

Solved Threads: 1

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

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

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

•

Join Date: Nov 2004

Posts: 257

Reputation: sam1 is an unknown quantity at this point

Solved Threads: 1

sam1

Offline

Posting Whiz in Training

Re: updating records using php and mysql

Mar 5th, 2008

as said above just use the where clause and u should be fine mate

•

Join Date: Nov 2009

Posts: 11

Reputation: takeshi is an unknown quantity at this point

Solved Threads: 0

takeshi

Offline

Newbie Poster

15 Days Ago

is there any code of adding record using drop down menu? or should i say,the <select><option> function?all i know is input text area..tnx..

•

Join Date: Oct 2009

Posts: 142

Reputation: venkat0904 is an unknown quantity at this point

Solved Threads: 14

venkat0904 venkat0904 is online now

Online

Junior Poster

15 Days Ago

its the same as u would do for input text...
just access the data from ur post variable as $_POST["yourSelectName"] and u can very well add it to ur record as u do for text. the post variable will contain the value of option which user selected...

•

Originally Posted by takeshi

is there any code of adding record using drop down menu? or should i say,the <select><option> function?all i know is input text area..tnx..

Gimme reputation points if u find my post helpful.
use [code] tags wherever applicable
dont start a new thread unless u cant find the topic already on forum.
mark a thread "solved" as soon as u get a solution

•

Join Date: Nov 2009

Posts: 11

Reputation: takeshi is an unknown quantity at this point

Solved Threads: 0

takeshi

Offline

Newbie Poster

yap..

#10

15 Days Ago

•

Originally Posted by venkat0904

its the same as u would do for input text...
just access the data from ur post variable as $_POST["yourSelectName"] and u can very well add it to ur record as u do for text. the post variable will contain the value of option which user selected...

>> so the code will be <select name="sample here">
<option value="<?php echo $row_sample here['sample here']?>" ?
i used the this array > while ($row= mysql_fetch_array($result)); before declaring the <select name =" "> and it's value..there's an error with that. the error was "< and >" but i can't find which "< and >" is wrong.