Modifying Entries

[Milamber]

This is probably a very basic question, but I haven't learned it yet so I need to ask.


Up until now I have been "modifying" my database entries (in the scripts I make) by deleting the old one and then putting in the new one.

What is the actual SQL statement to replace/modify an entry?

Thanks Much!
-Milamber

[exbabylon]

yes there is... and it's VERY easy! And at the same time it will speed up your scripts...

use this query:

$table = "your_table_name";

$sql = "UPDATE $table SET $city = '$city', state = '$state', zip = '$zip' WHERE ID = '$id';
if(!mysql_query($sql)){
     echo("An error has occured with the database");
} else {
echo("Thank you for updating your contact information");

that should do it!

God Bless

[Koncise]

Hey I'm looking for a similar script which will only allow authorized users to updtae their own records!!!

I have created a form which will ask for users for their username and password. If the username and password matches any of those within the database, it populates a table showing the user the details of the record based on the username entered

However where I am stuck is, how could I allow these authorized users to UPDATE/modify their records????

Please help!

Thanx!!


Koncise

[Son Nguyen]

Put a form for them to modify, then have a script to update those fields.

[exbabylon]

ok I am going to make this fast, and am assuming you already have a working knowledge of basic PHP

<?php

if(!$edit){
/* your password verification */
$sql = "SELECT * FROM user WHERE username = '$username' and password = '$password'";
$result = mysql_query($sql);

if(!$result){
   echo("an error has occured");
   exit();
} if(mysql_num_rows($result) != "1"){
   echo("Wrong username or password entered");
} else {
while($row = mysql_fetch_array($result)){
$city = $row['city'];
$state = $row['state'];
$zip = $row['zip'];
$id = $row['id'];
?>
<form action="<?php echo $PHP_SELF ?" method="post">
<div align="center"><?php echo $username; ?>
</div>
<p><input type="text" name="city" value="<?php echo $city; ?><br>
<input type="text" name="state" value="<?php echo $state; ?><br>
<input type="text" name="zip" value="<?php echo $zip; ?><br>
<input type="submit" value="Update My Profile" name="edit">
<input type=hidden" value="<?php echo $id; ?>" name="id">
</form>
<?php
}
}
}

//If they submit to change.
if(isset($edit){

$sql = "UPDATE user SET $city = '$city', state = '$state', zip = '$zip' WHERE ID = '$id';
if(!mysql_query($sql)){
     echo("An error has occured with the database");
} else {
echo("Thank you for updating your profile information.");
}
} else {

echo("No action specified");
}

freddy and Commish, go ahead and correct me where I'm wrong... but I think this should at least get you somewhere where you can start from.

[Koncise]

Thank you very much.....!

I'll see what I can do with the script.


Koncise!

[Koncise]

Greetings All!!!

I have devised a user update form, which is designed to provide a update form based upon the Username and password entered in the initial authentication form.

However when I attempt to submit the changes by pressing 'submit', the following error message appears:

"You are not authorized! Please try again."

This of course is the else if statement which is echoed if the submition fails, at the end of the script.:

<?php
endwhile;
else :

echo "You are not authorized! Please try again.</p>";
endif;



?>



Below is the script I am using for this:


<?php

/* This is a script which will perform a select query on the 'Love_to_sing database based upon the User_name and Password entered. The results are neatly presented in a table according to column and field names! */


$connection = mysql_connect("localhost", "root", "******") or die ("Couldn't connect to server.");

mysql_select_db("Love_to_sing", $connection) or die ("Couldn't select databagjse");

//Check if form has been submitted to update record.
//If so, perform database update

if (isset($update)) {
$query = "UPDATE Groups set where User_name='$User_name', Password='$Password',
Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region'

where ID='$rec_id' ";

//Perform Update
mysql_query($query);
}

$sql = "SELECT ID, User_name, Password, Category, Group_name, Contact_name,
Region FROM Groups
WHERE User_name='$User_name' and Password='$Password'";
$result = mysql_query($sql,$connection)
or die("Couldn't execute query.");
$num = mysql_numrows($result);

if ($num):
echo "<P>Thank you!<br><p>";
echo "Welcome back $User_name.<br>";
echo "Please feel free to update your group details below, hit 'submit'
when your done!</p>";

mysql_connect("localhost", "root", "******")
or die ("Couldn't connect to server.");
mysql_select_db("Love_to_sing", $connection)
or die ("Couldn't select database.");
$result = mysql_query($sql,$connection)
or die("Couldn't execute query.");
?>

<TABLE BORDER=8>
<tr><th>User_name</th><th>Password</th><th>Category</th><th>Group_name</th>
<th>Contact_name</th><th>Region</th></tr>
<form method=post action="<?php echo $PHP_SELF; ?>">

<?php

//PUT THE ROWS OF THE RESULT INTO AN ARRAY, THEN GO THROUGH ROW BY ROW,
//ECHOING WHAT IS IN EACH ONE

while ($row = mysql_fetch_array($result) ):

$ID = $row["ID"];
$Username = $row["User_name"];
$Password = $row["Password"];
$Category = $row["Category"];
$Group_name = $row["Group_name"];
$Contact_name = $row["Contact_name"];
$Region = $row["Region"];
?>

<tr>
<td><input type=text name="username" value="<?php echo $Username; ?>"></td>
<td><input type=text name="password" value="<?php echo $Password; ?>"></td>
<td><input type=text name="category" value="<?php echo $Category; ?>"></td>
<td><input type=text name="group_name" value="<?php echo $Group_name; ?>"></td>
<td><input type=text name="contact_name" value="<?php echo $Contact_name; ?>"></td>
<td><input type=text name="region" value="<?php echo $Region; ?>"></th>
</tr>

</table>
<br>
<input type=hidden name="rec_id" value="<?php echo $ID; ?>">
<input type=submit value="Submit" name="update">
</form>

<?php
endwhile;
else :

echo "You are not authorized! Please try again.</p>";
endif;



?>

I'm trying to figure out why the error message appears, I'll keep trying to sort it out. If there is a mistake within the script, I certainly cannot locate it!!

I would be extremely greatful if anybody could glance over the script to see if there are any mistakes!!

Thank you very, very much!!

Koncise.


[Edit: masked your password for you --Kevin Yank]

[Kevin Yank]

First, this query has a spurious 'where' in it:

if (isset($update)) { 
$query = "UPDATE Groups set where User_name='$User_name', Password='$Password', 
Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region' 

where ID='$rec_id' "; 

//Perform Update 
mysql_query($query); 
}

[Kevin Yank]

This is probably the cause. It's mysql_num_rows, not mysql_numrows:

$num = mysql_numrows($result);

[Kevin Yank]

The hilighted code seems like it is performing an additional, and unneccessary database connection and query:

if ($num): 
echo "<P>Thank you!<br><p>"; 
echo "Welcome back $User_name.<br>"; 
echo "Please feel free to update your group details below, hit 'submit' 
when your done!</p>"; 

mysql_connect("localhost", "root", "******") 
or die ("Couldn't connect to server."); 
mysql_select_db("Love_to_sing", $connection) 
or die ("Couldn't select database."); 
$result = mysql_query($sql,$connection) 
or die("Couldn't execute query.");
?>

[Koncise]

Thank you very much Kevin, that sorted out the problems, however there is still one more:

When I press 'submit' nothin happens; the screen does'nt change. The submit button does'nt work for some reason.

Here is the final script I can't see anything wrong with the submit command, have I missed something again??

<?php


$connection = mysql_connect("localhost", "root", "******") or die ("Couldn't connect to server.");

mysql_select_db("Love_to_sing", $connection) or die ("Couldn't select databagjse");

//Check if form has been submitted to update record.
//If so, perform database update

if (isset($update)) {
$query = "UPDATE Groups set User_name='$User_name', Password='$Password',
Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region'

where ID='$rec_id' ";

//Perform Update
mysql_query($query);
}

$sql = "SELECT ID, User_name, Password, Category, Group_name, Contact_name,
Region FROM Groups
WHERE User_name='$User_name' and Password='$Password'";
$result = mysql_query($sql,$connection)
or die("Couldn't execute query.");
$num = mysql_num_rows($result);

if ($num):
echo "<P>Thank you!<br><p>";
echo "Welcome back $User_name.<br>";
echo "Please feel free to update your group details below, hit 'submit'
when your done!</p>";

//PUT THE ROWS OF THE RESULT INTO AN ARRAY, THEN GO THROUGH ROW BY ROW,
//ECHOING WHAT IS IN EACH ONE

while ($row = mysql_fetch_array($result) ):

$ID = $row["ID"];
$Username = $row["User_name"];
$Password = $row["Password"];
$Category = $row["Category"];
$Group_name = $row["Group_name"];
$Contact_name = $row["Contact_name"];
$Region = $row["Region"];
?>

<tr>
<td><input type=text name="username" value="<?php echo $Username; ?>"></td>
<td><input type=text name="password" value="<?php echo $Password; ?>"></td>
<td><input type=text name="category" value="<?php echo $Category; ?>"></td>
<td><input type=text name="group_name" value="<?php echo $Group_name; ?>"></td>
<td><input type=text name="contact_name" value="<?php echo $Contact_name; ?>"></td>
<td><input type=text name="region" value="<?php echo $Region; ?>"></th>
</tr>

</table>
<br>
<input type=hidden name="rec_id" value="<?php echo $ID; ?>">
<input type=submit value="Submit" name="update">
</form>

<?php
endwhile;
else :

echo "You are not authorized! Please try again.</p>";
endif;



?>


Thank you for your help and patience!!!

Koncise.

[Kevin Yank]

Where is your <form> tag?

[Koncise]

Ahh yes this was something I noticed also Kevein, thanx.

However when I did enter the <form> tag, a strange thing happened:

When I pressed 'submit' on the form, the changes were saved to the database, great! But it clears what ever values are in the 'Username' and 'Password fields' - even though I made no change to them!

I am trully puzzled as to why this is happening!

Without a doubt, updating records has been the most difficult operation I've come across yet.....


Koncise.

[exbabylon]

simple fix... you have your control structures goofed!

here's the proper way:

$query = "UPDATE Groups SET Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region' where ID='$rec_id'"; 

before you had not declared $User_name and $Password as anything, so it just inserted those as blank.

try the code I gave above.

[Koncise]

Thank you very much...

I'll try that code out when I get home from Uni!

K.