Styjun

extracting sublists

Is "ln" (natural log) and "log" the same thing if used in this answer?

Organisational search option

Find equation of the circle whose diameter is the common chord of two other circles?

Would Taiwan and China's dispute be solved if Taiwan gave up being the Republic of China?

Can Northern Ireland's border issue be solved by repartition?

Line segments inside a square

Two trains move towards each other, a bird moves between them. How many trips can the bird make?

How do you use the interjection for snorting?

Strange Sticky Substance on Digital Camera

Going to France with limited French for a day

Magneto 2 How to call Helper function in observer file

Why does this image of Jupiter look so strange?

Why does (inf + 0j)*1 evaluate to inf + nanj?

Can an integer optimization problem be convex?

How do I set a custom order for folders on Windows 7 and 10?

A simple game that keeps track of the number of questions asked

Does "as soon as" imply simultaneity?

Why are there two fundamental laws of logic?

What is the need of methods like GET and POST in the HTTP protocol?

When is it acceptable to write a bad letter of recommendation?

A food item only made possible by time-freezing storage?

Extruding snaps back

To change trains = cambiare treno?

How to check if a user is in a role?

How can I prevent SQL injection in PHP?How to get a list of user accounts using the command line in MySQL?mysql stored proceduremySql errno: 150 Create table statement insidehow to call multiple columns in a stored procedureASP.NET Role and Member ManagementHow do I import an SQL file using the command line in MySQL?Stored procedure for adding an application user MS SQL 2012get app users role, name for the valid users login using stored procedurePHP Role System - Logic Problems

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I'm doing a work for college and I'm finding some trouble creating a Stored Procedure to delete a user. I need to check if the user has a specific role in order to decide what else to do after the deletion.

The main idea is that you enter a nickname as a parameter and the user with that email will be deleted. All this is fine and works with a simple "DROP USER IF EXISTS username" query. I get that.

But I have 3 roles, and if a user is created with one of those roles, it is also inserted onto that table.

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser`(
 IN Nickname varchar(45))
BEGIN

DECLARE `Host` CHAR(14) DEFAULT '@'localhost'';
DECLARE `Gmail` CHAR(14) DEFAULT '@'gmail.com'';

SET `Username`:= CONCAT(`Nickname`,`Host`);
SET `Email`:= CONCAT(`Nickname`,`Gmail`);

IF ("`Username` is investigador")
 Delete user where Gmail = Email from table Investigadores
 DROP USER IF EXISTS `Username`;
END

The behaviour I'm looking for is the following:
- call deleteUser Stored Procedure
- delete user from mySQL
- if the user has the "investigador" role delete the row where that user is

But how do I make that verification inside an IF statement?

mysql stored-procedures roles

share|improve this question

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge

add a comment
 | 

0

I'm doing a work for college and I'm finding some trouble creating a Stored Procedure to delete a user. I need to check if the user has a specific role in order to decide what else to do after the deletion.

The main idea is that you enter a nickname as a parameter and the user with that email will be deleted. All this is fine and works with a simple "DROP USER IF EXISTS username" query. I get that.

But I have 3 roles, and if a user is created with one of those roles, it is also inserted onto that table.

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser`(
 IN Nickname varchar(45))
BEGIN

DECLARE `Host` CHAR(14) DEFAULT '@'localhost'';
DECLARE `Gmail` CHAR(14) DEFAULT '@'gmail.com'';

SET `Username`:= CONCAT(`Nickname`,`Host`);
SET `Email`:= CONCAT(`Nickname`,`Gmail`);

IF ("`Username` is investigador")
 Delete user where Gmail = Email from table Investigadores
 DROP USER IF EXISTS `Username`;
END

The behaviour I'm looking for is the following:
- call deleteUser Stored Procedure
- delete user from mySQL
- if the user has the "investigador" role delete the row where that user is

But how do I make that verification inside an IF statement?

mysql stored-procedures roles

share|improve this question

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge

add a comment
 | 

I'm doing a work for college and I'm finding some trouble creating a Stored Procedure to delete a user. I need to check if the user has a specific role in order to decide what else to do after the deletion.

The main idea is that you enter a nickname as a parameter and the user with that email will be deleted. All this is fine and works with a simple "DROP USER IF EXISTS username" query. I get that.

But I have 3 roles, and if a user is created with one of those roles, it is also inserted onto that table.

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser`(
 IN Nickname varchar(45))
BEGIN

DECLARE `Host` CHAR(14) DEFAULT '@'localhost'';
DECLARE `Gmail` CHAR(14) DEFAULT '@'gmail.com'';

SET `Username`:= CONCAT(`Nickname`,`Host`);
SET `Email`:= CONCAT(`Nickname`,`Gmail`);

IF ("`Username` is investigador")
 Delete user where Gmail = Email from table Investigadores
 DROP USER IF EXISTS `Username`;
END

The behaviour I'm looking for is the following:
- call deleteUser Stored Procedure
- delete user from mySQL
- if the user has the "investigador" role delete the row where that user is

But how do I make that verification inside an IF statement?

mysql stored-procedures roles

share|improve this question

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser`(
 IN Nickname varchar(45))
BEGIN

DECLARE `Host` CHAR(14) DEFAULT '@'localhost'';
DECLARE `Gmail` CHAR(14) DEFAULT '@'gmail.com'';

SET `Username`:= CONCAT(`Nickname`,`Host`);
SET `Email`:= CONCAT(`Nickname`,`Gmail`);

IF ("`Username` is investigador")
 Delete user where Gmail = Email from table Investigadores
 DROP USER IF EXISTS `Username`;
END

share|improve this question

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge

share|improve this question

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge

asked Mar 28 at 16:52

Bruno ColaçoBruno Colaço

111 bronze badge