0

I was not sure how to title this.

I have inherited a database that has a user(id) from a third table lets call short_table that ties to a core data table (let's call core_table) through a second table called Leads_table.

I need to be able to edit and change the user ID from one employee to another when we hand-off clients from one to another. Here is what I have in the tables

core_table id /first_name /last_name

leads_table id /cand_id

short_table id / lead_id /timestamp /user_id

//Here is my code

$cand_sql = '
Select 
core_table.id,
core_table.first_name,
core_table.last_name,
short_table.user_id,
short_table.lead_id
From core_table
LEFT JOIN leads_table ON ( core_table.id = leads_table.cand_id) 
LEFT JOIN short_table ON (short_table.lead_id = leads_table.id)
WHERE
core_table.id = "' . $cand_id . '"';
$cand_res = mysql_query($cand_sql,$test_db);
$cand = mysql_fetch_array($cand_res);

//Here is a small piece of my edit form code to send the user_id info    
Staff:<input type="text" name="staff_id" id="staff_id" 
value="<?php echo $cand[staff_id]; ?>" />

// Here is the process form code that is messed up

if($_POST['staff_id'] != ''){
  $sql1 = 'UPDATE short_table
           SET user_id=  ("'.$_POST['staff_id'].'")
           WHERE short_table.lead_id = leads_table.id AND 
           leads_table.cand_id = . "' . $_POST['cand_id'] . '"';
           $res1 = mysql_query($sql1,$test_db);
}

//I am not getting any change in the user field of the short table when I execute this code and try to change the user id. Any ideas on where I am wrong?

I am just not sure how to code this to be able to change the user_id in the short_table and make sure it is linked to the same candidate that I have in the core_table. Does this make sense? Any help would be appreciated.

BlueJay
  • 13
  • 2
  • Are you checking for SQL error return from mysql_query? Because your update query as presented sure has them. Also mysql_query is deprecated and should be replaced with mysqli_query. – Jim Mc May 23 '15 at 21:31
  • Thanks for the advice. I am trying to learn, so I will take what you said do some research – BlueJay May 23 '15 at 23:03
  • One question about your comment about the mysql to mysqli_query. I saw from reading about it that it looks very similar. Do I need to change all those on my web pages by a certain time frame? Also, I know my coding is terrible, but do you have any insight for how I can change it to be able to update the user_id record? – BlueJay May 24 '15 at 00:00
  • Not sure when mysql_query will actually stop working. Check here for the syntax you need for your update: http://stackoverflow.com/questions/15209414/mysql-update-join – Jim Mc May 24 '15 at 20:15

0 Answers0