SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
Thread: Modifying Entries
-
Feb 18, 2001, 21:45 #1
- Join Date
- Jan 2001
- Location
- California
- Posts
- 342
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Feb 18, 2001, 22:44 #2
- Join Date
- Dec 2000
- Location
- Idaho, USA
- Posts
- 452
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
yes there is... and it's VERY easy! And at the same time it will speed up your scripts...
use this query:
Code:$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");
God BlessBlamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.
Exbabylon- Professional Internet Services
-
Feb 19, 2001, 15:53 #3
- Join Date
- Oct 2000
- Location
- Location:
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Feb 19, 2001, 16:47 #4
- Join Date
- Aug 2000
- Location
- Silicon Valley
- Posts
- 2,241
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Put a form for them to modify, then have a script to update those fields.
- Son Nguyen
AdSpeed.com - Ad Serving and Ad Management Made Easy
-
Feb 19, 2001, 17:55 #5
- Join Date
- Dec 2000
- Location
- Idaho, USA
- Posts
- 452
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ok I am going to make this fast, and am assuming you already have a working knowledge of basic PHP
Code:<?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"); }
Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.
Exbabylon- Professional Internet Services
-
Feb 19, 2001, 19:03 #6
- Join Date
- Oct 2000
- Location
- Location:
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you very much.....!
I'll see what I can do with the script.
Koncise!
-
Feb 20, 2001, 15:07 #7
- Join Date
- Oct 2000
- Location
- Location:
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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]Last edited by Kevin Yank; Feb 20, 2001 at 15:18.
-
Feb 20, 2001, 15:14 #8
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
First, this query has a spurious 'where' in it:
Code: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
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Feb 20, 2001, 15:16 #9
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
This is probably the cause. It's mysql_num_rows, not mysql_numrows:
Code:$num = mysql_numrows($result);
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Feb 20, 2001, 15:19 #10
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
The hilighted code seems like it is performing an additional, and unneccessary database connection and query:
Code: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."); ?>
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Feb 20, 2001, 16:57 #11
- Join Date
- Oct 2000
- Location
- Location:
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Feb 20, 2001, 17:18 #12
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Where is your <form> tag?
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Feb 20, 2001, 21:09 #13
- Join Date
- Oct 2000
- Location
- Location:
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Feb 20, 2001, 21:56 #14
- Join Date
- Dec 2000
- Location
- Idaho, USA
- Posts
- 452
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
simple fix... you have your control structures goofed!
here's the proper way:
PHP Code:$query = "UPDATE Groups SET Category='$category', Group_name='$group_name', Contact_name='$contact_name', Region='$region' where ID='$rec_id'";
try the code I gave above.Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.
Exbabylon- Professional Internet Services
-
Feb 21, 2001, 08:06 #15
- Join Date
- Oct 2000
- Location
- Location:
- Posts
- 94
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you very much...
I'll try that code out when I get home from Uni!
K.
Bookmarks