PHP MYSQL update table

Question

I'm using MYSQL and php to insert data into my sql.

I have two tables users and userrights.

I'm using checkbox to display rights on user edit page. when rights are de-selected or select i wont to update my userrights table.

CREATE TABLE userrights (
  userid int(11) NOT NULL default '0',
  right_name varchar(50) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (userid,right_name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


INSERT INTO userrights (userid, right_name) VALUES
(1, 'add'),
(1, 'delete'),
(1, 'edit'),
(1, 'view'),
(2, 'add'),
(2, 'delete'),
(2, 'edit'),
(2, 'view');

CREATE TABLE users (
  id int(11) NOT NULL,
  `name` varchar(100) collate latin1_general_ci default NULL,
  email varchar(100) collate latin1_general_ci default NULL,
  pass varchar(42) collate latin1_general_ci default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO users (id, name, email, pass) VALUES
(1, 'name', '[email protected]', '5f4dcc3b5aa765d61d8327deb882cf99'),
(2, 'name name', '[email protected]', '1a1dc91c907325c69271ddf0c944bc72');

how do I do this?

Please clarify... are you asking how to write your queries to update permissions?... Or how to tie that functionality to a checkbox on your site (either using form submission, or Ajax)? — Brian Adkins
– Brian Adkins, Commented Jul 29, 2012 at 16:29
how add this functionality to site just by submitting the form — user1561124
– user1561124, Commented Jul 29, 2012 at 16:32

Madara's Ghost · Accepted Answer · 2012-07-29 16:24:42Z

1

I've had a similar problem before.

The solution I came up with is truncate the table and reinsert all of the selected entries.

answered Jul 29, 2012 at 16:24

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1561124 Over a year ago

I did think of deleting all records and then re-enter records again $sql = “INSERT INTO userrights ( 1, ‘add’ );”; $sql .= “INSERT INTO userrights ( 1, ‘edit’ );”; $sql .= “INSERT INTO userrights ( 1, ‘delete’ );”; $result2 = mysql_query($sql);

Madara's Ghost Over a year ago

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information.

Madara's Ghost Over a year ago

@user1561124: Also, the use of PDO transaction in conjunction with prepared statements can give you a huge efficiency boost.

user1561124 Over a year ago

thank you for this information I have chnage my database to mysqli

score 0 · Accepted Answer · 2012-08-03 19:45:56Z

0

You get the data of the checkbox(true or false)

$rows = mysql_num_rows(mysql_query("SELECT * FROM userrights WHERE id = 'x' and right_name = 'y'"); // Please use insted of mysql_* PDO or MySQLi 
if ($rows > 0)
{
        if ($checkbox == false)
        {
        mysql_query("DELETE FROM userrights WHERE id = 'x' AND right_name = 'y'");
        }
}
else
{
// Recht nicht vorhanden --> if checkbox == true
      if ($checkbox == true)
      {
      mysql_query("INSERT INTO... VALUES ($id, $right_name)
      }
}

First you check if the user had the right. If yes you need to check if the right (data from checkbox) is set to false --> delete that row from the table. If the user hadn't that right and the checkbox is true, you must insert that in your mysql_table. The comments must be changed to and if-clause in your code.

edited Aug 3, 2012 at 19:45

answered Jul 29, 2012 at 17:08

user1552265

2 Comments

user1561124 Over a year ago

if permission have already have been added and if i try select again they will be deleted and how do I add the permission and update user same time

user1552265 Over a year ago

the code above wasn't complete. oh fuuuuuuu! I wrote something in German.... SOrry my fault. I changed the code abvoe!

Collectives™ on Stack Overflow

PHP MYSQL update table

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related