CODEWITHSUNDEEP
phpmysqlsql
ZeroSuf3r
ZeroSuf3r

Reputation: 2001

php mysql update two tables/columns

my tables: blocked_peoples and members. In table blocked_peoples column ips = members column signup_ip.

Let's say i wanna block person from accessing my site. I block user by his IP and it too update members table and column banned with 1.

In short, if i update table blocked_peoples (column ips) and it's result found/same as members (column signup_ip) in members table update column banned with 1.

It's possible ? If yes, how sql will look like ?

Upvotes: 1

Views: 661

Answers (3)

Matt Beckman
Matt Beckman

Reputation: 5012

I would recommend getting rid of the blocked_peoples table completely if the relationship to members is 1-to-1. Otherwise, you should remove the banned column from members and rely specifically on the blocked_peoples to check for banned IPs.

Example Update:

UPDATE `members` SET `banned` = 1 WHERE `signup_Ip` = '123.123.123.123';

Example Select:

SELECT * FROM `members` WHERE `banned` = 1

Upvotes: 0

xkeshav
xkeshav

Reputation: 54022

STEPS:

  • first make both table engine to INNODB
  • then create a foreign key reference with constraint ON UPDATE CASCADE ON DELETE CASCADE
  • parent_table(blocked_people).ips will be referenced to child table(members).signup_ip

for more information read about foreign key reference .

Happy To HELP :)

Upvotes: 0

David Fells
David Fells

Reputation: 6798

UPDATE blocked_peoples, members
   SET members.banned = 1 
 WHERE members.signup_ip = blocked_peoples.ip 
   AND blocked_peoples.ip = 'ip.address.goes.here';

That's the best I can come up with based on your question. I'm not sure though. It doesn't make sense to use two tables in the update since members table has both the ip and the "blocked" flag.

Upvotes: 1

Related Questions