Administrators updating users details (PHP form/script, MySQL database)

[User_90]

Hi All,

 

I've searched long and hard accross the web for an answer to this and finnally given in and requesting help. Here's what i have, i have a database setup and working fine. What i would like to do is for an administrator to be able to update my users details. It may sound odd, why don't you let your users update their own details? Well the administrators are dispatchers if you like, and my users are the 'dispatchees', for want of a better word. So i would like my administrators to be able to dispatch my users with routes and my users be able to see the routes that have been dispatched to them. I've setup a login area and a page that pulls there routes off the database, depending on their login details, i.e. jack will see his routes and jill will see her's independantly. This works by me editing the appropriate columns/rows of my database using phpmyadmin. What i'd like now is for administrators (who are directed to a seperate page, with more controls) to be able to do the same as me (updating the database) but by using a php form/script.

 

I'd like to be able to select the routes from a second table on the same database if possible, to try and keep everything tidy. So my dispatcher would select Route001 from a drop down list, this would fill in the text fields next to the route field with From To, so my dispatcher would know what route001 actually is from/ too, choose a username (now being driven from my other table) and hit dispatch. My user would login to their area, hit view dispatched routes and it would display Route 001 with the correct information.

 

The login area was a downloaded script i modified to suit and is called Login-Redirect_v1.31_FULL

 

Many thanks in advance, hope you can sort of understand what i want 

 

Josh

 

PHP/MySQL ability:Novice

 

[haku]

What exactly are you having troubles with?

[User_90]

I don't know what code to write that will allow my administrators to edit certain columns on my database,for specific users using a php form. So my administrators will want to change for example first and last name of a specific user (crude example). Can a code be wrote so that they can choose a user, from a Select (List/Menu) write in a text field for first and last name and it update that specific users first and last name on my database?

 

Thanks

 

Josh

[Gotharious]

not sure about the select user code, but the query to update record would be something like

 

<?php

$query = mysql_query("UPDATE tablename SET firstname = '$firstname'
AND LastName = '$lastname" WHERE id = '$id');


?>

[User_90]

Hi,

 

Thanks for the reply! I've been looking around and found a basic script (split over 3 files), that i think i could modify to suit. However... i've used this script, it reports that its made changes to my DB successfully, but it doesn't actually update my DB, could you look over the codes for me please, i'm struggling to find any flaw in it:

 

list_records.php

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

$sql="SELECT * FROM $tbl_name";

$result=mysql_query($sql);

?>

<table width="400" border="0" cellspacing="1" cellpadding="0">

<tr>

<td>

<table width="400" border="1" cellspacing="0" cellpadding="3">

<tr>

<td colspan="4"><strong>List data from mysql </strong> </td>

</tr>

 

<tr>

<td align="center"><strong>Name</strong></td>

<td align="center"><strong>Lastname</strong></td>

<td align="center"><strong>Email</strong></td>

<td align="center"><strong>Update</strong></td>

</tr>

<?php

while($rows=mysql_fetch_array($result)){

?>

<tr>

<td><? echo $rows['name']; ?></td>

<td><? echo $rows['lastname']; ?></td>

<td><? echo $rows['email']; ?></td>

 

// link to update.php and send value of id

<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>

</tr>

<?php

}

?>

</table>

</td>

</tr>

</table>

<?php

mysql_close();

?>

 

update.php

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

// get value of id that sent from address bar

$id=$_GET['id'];

 

 

// Retrieve data from database

$sql="SELECT * FROM $tbl_name WHERE id='$id'";

$result=mysql_query($sql);

 

$rows=mysql_fetch_array($result);

?>

<table width="400" border="0" cellspacing="1" cellpadding="0">

<tr>

<form name="form1" method="post" action="update_ac.php">

<td>

<table width="100%" border="0" cellspacing="1" cellpadding="0">

<tr>

<td> </td>

<td colspan="3"><strong>Update data in mysql</strong> </td>

</tr>

<tr>

<td align="center"> </td>

<td align="center"> </td>

<td align="center"> </td>

<td align="center"> </td>

</tr>

<tr>

<td align="center"> </td>

<td align="center"><strong>Name</strong></td>

<td align="center"><strong>Lastname</strong></td>

<td align="center"><strong>Email</strong></td>

</tr>

<tr>

<td> </td>

<td align="center"><input name="name" type="text" id="name" value="<? echo $rows['name']; ?>"></td>

<td align="center"><input name="lastname" type="text" id="lastname" value="<? echo $rows['lastname']; ?>" size="15"></td>

<td><input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15"></td>

</tr>

<tr>

<td> </td>

<td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td>

<td align="center"><input type="submit" name="Submit" value="Submit"></td>

<td> </td>

</tr>

</table>

</td>

</form>

</tr>

</table>

 

<?

 

// close connection

mysql_close();

 

?>

 

update_ac.php

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

// update data in mysql database

$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";

$result=mysql_query($sql);

 

// if successfully updated.

if($result){

echo "Successful";

echo "<BR>";

echo "<a href='list_records.php'>View result</a>";

}

 

else {

echo "ERROR";

}

 

?>

 

Note, i have modified the top 5 lines to match my DB details.

 

Thanks

 

Josh

[litebearer]

in update_ac.php, where are you gathering the form data?

[User_90]

Hi,

 

Thanks for replying. Trying to answer your question, in update.php the values are taken from the existing records of the MySQL database, the appropriate fields are taken from the DB and presented in the textbox. By editing the text boxes and hitting submit actions this part of the script:

 

<td> </td>

<td align="center"><input name="name" type="text" id="name" value="<? echo $rows['name']; ?>"></td>

<td align="center"><input name="lastname" type="text" id="lastname" value="<? echo $rows['lastname']; ?>" size="15"></td>

<td><input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15"></td>

</tr>

<tr>

<td> </td>

<td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td>

<td align="center"><input type="submit" name="Submit" value="Submit"></td>

<td> </td>

 

So i think it works by whatever text is in the fields at the time of hitting submit, will be whatever text it puts into the corresponding colums of the table. As Submit is hit,it checks against update_ac.php, to check the DB connection and post the changes. I could be wrong?

 

Thanks

[litebearer]

In the update_ac.php file You need to gather the '$_POST[]' values for each field.

As a simple test simply echo the query, as it now stands, no values should show.

[User_90]

Could you give me a bit more direction please? Not really sure what you mean, or how to do what your asking,sorry.

[litebearer]

Rough idea (in the update_ac.php file) (note: be sure to cleanse/validate the data)...

$lastname = $_POST['lastname'];
$email = $_POST['email'];

do that for each of the form fields.

http://www.tizag.com/phpT/forms.php

 

 

[User_90]

Hi,

 

I've made the following change to my update_ac.php:

 

// if successfully updated.

if($result){

echo $firstname = $_POST['firstname'];

echo $lastname = $_POST['lastname'];

echo $email = $_POST['email'];

echo "<BR>";

echo "<a href='list_records.php'>View result</a>";

}

 

else {

echo "ERROR";

}

 

?>

 

When it submits the form and brings me to the update_ac.php page the only post it shows is the change to the lastname field. For example if i change the last name to unknown and hit submit,it shows the page with:

unknown

View result (html code to return to the list_records.php)

 

I've tried just only posting one at a time, so the code looks like this:

 

// if successfully updated.

if($result){

echo $firstname = $_POST['firstname'];

echo "<BR>";

echo "<a href='list_records.php'>View result</a>";

}

 

else {

echo "ERROR";

}

 

?>

 

Is this what was expected? It seems the lastname will post the change, but not update it. The other two fields don't seem to post at all.

 

Thanks

 

[litebearer]

1. in your form you are calling it name; whereas, in the processing you are calling it firstname. (suggest calling it firstname in the form).

 

2. you NEED to get ALL the form values, ie

$firstname = $_POST['firstname']
$lastname = $_POST['lastname'];
$email = $_POST[email'];
$id = $_POST['id'];
echo $firstname; /* etc etc */

 

Do NOT do

echo $lastname = $_POST['lastname'];

 

[User_90]

Hello,

 

Do you mean like this?I've stuck with using 'name'.

 

<?php

$host="localhost"; // Host name

$username=""; // Mysql username

$password=""; // Mysql password

$db_name="test"; // Database name

$tbl_name="test_mysql"; // Table name

 

// Connect to server and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

 

// update data in mysql database

$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";

$result=mysql_query($sql);

 

// if successfully updated.

if($result){

$id = $_POST['id'];

$name = $_POST['name'];

$lastname = $_POST['lastname'];

$email = $_POST['email'];

echo "Success ". $id . "". $name . "". $lastname . " " . $email . "<br />";

echo "<a href='list_records.php'>View result</a>";

}

 

else {

echo "ERROR";

}

 

?>

 

Regards

 

[litebearer]

like this...

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="test_mysql"; // Table name

/* obtain the form data */

$id = $_POST['id'];
$name = $_POST['name'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database
$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if(mysql_affected_rows>0){
echo "Success ". $id . "". $name . "". $lastname . " " . $email . "<br />";
echo "<a href='list_records.php'>View result</a>";
}else{
echo "ERROR";
}

?>

 

You also should always 'cleanse' your data

[User_90]

Hi,

 

Sorry now i understand what it was you were trying to get me to do. For some reason i thought you mean't something different. Well that seems to be working now, updating my database as it should etc. What do you mean cleanse your data? Is it breaking up the code (the bits in orange)? Like:

 

/* obtain the form data */

 

Thanks for your help so far! Just learn't how to post code up here too with the code tags! Learning alot today!

 

 

[litebearer]

an overview

http://element-80.com/2010/11/php-tutorial-how-to-sanitize-form-data/

[User_90]

Hi,

 

I have an auto incrememnt column on my table for id (each user having a seperate id, 1,2,3, etc). How could i capture the id of the user that log's in, so that i could use this to update just their own details. I'm guessing id would be a better value to use than say user_name? I guess i could use the previous script to post the updates to the database,just unsure how to capture it:

 

To Post

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="test_mysql"; // Table name

/* obtain the form data */

$id = $_POST['id'];
$name = $_POST['name'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database
$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if(mysql_affected_rows>0){
echo "Success ". $id . "". $name . "". $lastname . " " . $email . "<br />";
echo "<a href='list_records.php'>View result</a>";
}else{
echo "ERROR";
}

?>

 

I've tried using the echo function, but again it says its posted, but doesn't change anything:

<?php
// update data in mysql database
$sql="UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$_SESSION[id]'";
$result=mysql_query($sql);

?>

 

Thanks alot

 

Josh