CODEWITHSUNDEEP
phpmysqlsql-update
john smith
john smith

Reputation: 759

updating on multiple tables

I have two tables in mysql that are "users" and "users_info" - the first contains the login data, such as email and password, and name.

and the second table that contains more information, like address, dob, bio, etc.

Now, I am working on an admin panel in which I can modify any information regarding those users. To get the data, I am using a join, but what about saving?

How can I save data regardless of its table? if it was just one table I would have done (in PHP):

$values = Array(
  "email" => "[email protected]", //this goes into "users"
  "name" => "john", //this one, too
  "bio" => "i is cool" //this one should to go users_info!!
);

foreach($values as $k=>$v) {
  $query = " UPDATE users SET '$k'='$v' WHERE id=7653 ";
}

this will give an error because "bio" is not a column inside users_info. Any ideas?

Thanks in advance.

Upvotes: 0

Views: 81

Answers (4)

Kerem
Kerem

Reputation: 11586

I never recommend queries that inside a loop. I think you need to seperate your post values first;

<form method="post">
    <input type="text" name="user[name]">
    <input type="text" name="user[email]">
    <textarea name="user_info[bio]"></textarea>
</form>

And PHP part;

$user_post      = (array) $_POST['user'];
$user_info_post = (array) $_POST['user_info'];
$user_query = $user_info_query = array();
foreach ($user_post as $k => $v) {
    // db_escape is your data security
    $user_query[] = sprintf("`%s` = '%s'", $k, db_escape($v));
}
if (!empty($user_query)) {
    $user_query = join(', ', $user_query);
    // db_query("UPDATE users SET ...");
}
if (!empty($user_info_query)) {
    $user_info_query = join(', ', $user_info_query);
    // db_query("UPDATE users_info SET ...");
}

Upvotes: 0

M.Kreusburg
M.Kreusburg

Reputation: 19

$table = "users";
foreach($values as $k=>$v) { 
if($values = "bio"{ 
$table = "users_info"; 
} 
$query = "UPDATE '$table' set '$k'='$v' WHERE id = 7653";
$table = "users"; 
}

Upvotes: 0

Rikesh
Rikesh

Reputation: 26451

You way of coding is bit vague though you can try doing like below for now,

$usersColumns = array('email','password',...);
$usersInfoColumns = array('address','dob',...);
foreach($values as $k=>$v) {
   if(in_array($k,$usersColumns)){
     $tableName = 'users';
   }
   else if(in_array($k,$usersInfoColumns)){
     $tableName = 'users_info';       
   }
   $query = "UPDATE $tableName SET '$k'='$v' WHERE id=7653";
 }

Upvotes: 1

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146660

You can of course use the multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

However, if I was you, I wouldn't care and I'd simply run two regular single-table updates. It'd be easy to make an error (and update unintended records) and you don't get any appreciable benefit.

Upvotes: 0

Related Questions