1

Is it possible in MySQL to update a single field and select multiple different fields all where a single condition is true, in a single query? I'm not exactly sure of what the proper SQL would be, but I want to merge these two example queries, if it is possible, of course.

Example

An update query:

UPDATE `users` SET `activation`='$activationCode' WHERE `email`='$anEmail'

and a select query:

SELECT `password`,`salt`,`fname`,`email` FROM `users` WHERE `email`='$anEmail'

See the duplicate condition here? Surely it's unnecessary to query a MySQL database twice with duplicate conditions, when I could do it all at once under a single condition?

It's worth noting that $anEmail and $activationCode would be previously defined variables in PHP (with the former also being exactly the same through both queries). Cheers.

marked-down
  • 9,958
  • 22
  • 87
  • 150
  • The PHP variable names suggest you're interpolating values directly into the statement. Don't do that. Use a prepared statement (which are supported by [PDO](http://php.net/PDO) and [mysqli](http://php.net/mysqli)) to keep the data separate from the statement. Among the many benefits, this prevents [SQL injection](http://unixwiz.net/techtips/sql-injection.html) via the values. – outis Dec 26 '11 at 12:01
  • @outis okay, can you give me any links to tutorials of using prepared statements with mysqli? I've tried looking but most of them are useless at explaining how they work and run. – marked-down Dec 26 '11 at 21:56
  • 1
    I know of a tutorial for [PDO](http://www.kitebird.com/articles/php-pdo.html), but none for mysqli. A web search will give you better results than I. For a conceptual overview, read [Parallels Between Prepared Statements and Functions](http://blog.oudamou.co.cc/2011/07/parallels-between-prepared-statements.html). There's not very much to prepared statements. You don't need to know how they work, you just need to know how to use them. – outis Dec 26 '11 at 21:59
  • @outis, thank you - I was going to use MySQLi, but I've since been convinced otherwise and will now try PDO. Thanks again. – marked-down Dec 26 '11 at 23:44
  • 1
    PDO is nicer to use, I find. For one thing, `PDOStatement` implements [`Traversable`](http://php.net/Traversable), meaning you can loop over results with `foreach`. – outis Dec 26 '11 at 23:50

2 Answers2

3

You have to write a stored procedure.

delimiter ;;
CREATE PROCEDURE UpdateAndSelect (mEmail Varchar(50), mactivationCode Varchar(20))
 BEGIN
   START TRANSACTION;
   UPDATE `users` SET `activation`=mactivationCode WHERE `email`=mEmail;
   SELECT `password`,`salt`,`fname`,`email` FROM `users` WHERE `email`=mEmail;
   COMMIT;
 END;;
delimiter ;

Call the procedure with:

CALL UpdateAndSelect(:email, :activation)
outis
  • 75,655
  • 22
  • 151
  • 221
KV Prajapati
  • 93,659
  • 19
  • 148
  • 186
1

If you just want to skip the repeated where clause then check this post (first answer)

SQL Update Multiple Fields FROM via a SELECT Statement

Community
  • 1
  • 1
Uday Sawant
  • 5,748
  • 3
  • 32
  • 45