[Resolved] Updating fields

[Calahan]

Thought I'd almost finished when I ran into this little problem.
I want to update data in a database. I know all the code I need to use, this a more a question of design I guess. The Admins for this database can set up users which get added to the users table. Once a user is added, the Admins can then modify the user. I have a set of pages/scripts that display the select user's details in text boxes. The Admin then updates the information as required. The problem comes when an Admin edits a user and changes the name of that user to one that already exists in the user table. The information gets added so I end up with 2 users with the same name. I thought of writing in code to check the database for an existing user before updating but this will cause problems if the Admin doesn't do anything with the fields and just clicks next. The code will run and the Admin will be told that the user already exists. I get a feeling I've no doubt that the answer will be simple but I'm missing it so can anyone think of a way around this?

[travelbuff]

Compare the user name with the user ID - you do have a unique user ID for each user, right? If not, I strongly recommend doing so. Then, your code that checks for the existance of the username could just compare uid's first.

[Barb54]

I'm a newby but I know you can set other fields to Unique in the table. I have username and email set as Unique in my members table so that I don't get duplicates. I still have my Primary key field as well.

Hope that helps.........

Barb

[laserlight]

Do what travelbuff said.

In fact, go on to read this article

[Calahan]

Either I'm not understanding what you're saying or I've not explained it properly. In case it's the latter, I'll show you the code...

Page 1

$get_job_categories = "SELECT job_cat FROM " .$Table. "";

$job_cat_list = mysql_query($get_job_categories) OR die(mysql_error());
echo "<form action='edit-categories2.php' method='post' name='job_cat'>
<center><select name='job_cat'>";
while( $row = mysql_fetch_array($job_cat_list) )
{
echo "<option value='".$row['job_cat']."'>" .$row['job_cat']."</option>";
}
echo "</select>
&nbsp; <input value='Next' type='submit' name='submit2'>
</form>

Page 2

$get_catagory_details = "SELECT * FROM " .$Table. " WHERE job_cat='".$_POST['job_cat']."'";

$catagory_details = mysql_query($get_catagory_details) OR die(mysql_error());

while( $row = mysql_fetch_array($catagory_details) )
{
echo "<form method='POST' name='job_cat' action='edit-categories-confirmed.php'>
<center>
<table width='10%'>
    <tbody>
      <tr>
        <td><input name='job_cat' type='text' value='" .$row['job_cat'] . "'></td>
      </tr>
     </tbody>
  </table>
<p>
<input name='submit' type='submit' value='Update'></p>
</form>
$_SESSION['edit_job_cat_no']=$row['job_cat_no'];
}

Page 3

$job_cat_update="UPDATE " . $Table . " SET job_cat='" . $_POST['job_cat'] . "', datetime='" . date('Y-m-d H:i:s') . "', last_modified_by='" . $_SESSION['logged_on_db_admin'] . "'" . " WHERE job_cat_no='" . $_SESSION['edit_job_cat_no'] . "'";

mysql_query($job_cat_update) or die(mysql_error());

I can't see a way to stop the user changing a category to one that already exists.

[travelbuff]

I think I get it. you don't want an admin to be able to change a job_cat description to be the same as (or too similar to) another.

That is where having a unique job cat id (which it appears you may have in job_cat_no) comes in.

At the beginning of page 3, select all job_cats where the descriptions match $_POST[job_cat], but the job_cat_no does not. If there are no matching rows, you are good to go. If there is a match, through up a flag:

$existing_check = 'SELECT * FROM $Table WHERE job_cat = $POST["job_cat"] AND job_cat_no != $POST["job_cat_no"]';
$existing_check _result = @($existing_check) OR DIE (MYSQL_ERROR());
$num_matches = MYSQL_NUM_ROWS($existing_check_result);
if ($num_matches ==0) { // good to go
} else { //send up a flag

Excuse the not-enough-coffee syntax, but hopefully you get the idea.

That being said, the problem with what you are trying to do is that the cat descriptions are going to have to match pretty close to exactly for you to be able to halt the update process. You could try using a LIKE select statement (http://www.mysql.com/doc/en/String_comparison_functions.html) or preg_match (http://php.benscom.com/manual/en/function.preg-match.php) and try to get close.

[Calahan]

Good Job!
Why I couldn't see that myself ticks me off a bit. :rolleyes:
Never mind. Still learning.

Thanks again.

[travelbuff]

🆒 No sweat. That is what the forums are for. I have posted many problems and then when someone gave me the SIMPLE answer the only thing I could say was DOH!:rolleyes: Sometimes you can't see the forest for all of the trees.....