1

I've been looking around and have found many similar questions, but the answers are seemingly not quite what I'm looking for.

The sign up process on my website is like follows:

  1. User provides email and password
  2. Email and password are inserted into _unverified table
  3. Verification link is sent to email address
  4. Once user clicks link, email and password are transferred from _unverified to _verified

Thus, when a user tries to create an account, I have to query both tables to make sure the email isn't already in use. I have always done this separately, as can be seen below, which obviously bloats the script.

$statement = $connect->prepare("SELECT account_email FROM users_unverified WHERE account_email = :account_email");
$statement->bindParam(":account_email", $_POST["email"]);
$statement->execute();
$result = $statement->fetch(PDO::FETCH_ASSOC);

if ($result["account_email"]) // verification process is already active or has expired

$statement = $connect->prepare("SELECT account_email FROM users_verified WHERE account_email = :account_email");
$statement->bindParam(":account_email", $_POST["email"]);
$statement->execute();
$result = $statement->fetch(PDO::FETCH_ASSOC);

if ($result["account_email"]) // account already exists

Notably, $_POST["email"] will be in either one table or the other or neither.

In the similar questions that I found, people have suggested using JOINS and UNIONS. However, after reading about each method, I don't believe JOINS and UNIONS are what I'm looking for since I don't want to combine rows or result sets (unless of course I would still be able to identify in which table the value is located?).

Simply put, I want to be able to query both tables with a single prepared statement, yet still be able to determine in which table the value is located if it is indeed located in one of them. I'm imagining something like following, which I came across in this question, but, again, I don't believe you would be able to identify in which specific table the value was.

$statement = $connect->prepare("SELECT account_email FROM users_unverified, users_verified WHERE account_email = :account_email");

Is there any way to combine those queries while still being able to identify in which specific table the value exists?

oldboy
  • 5,729
  • 6
  • 38
  • 86
  • 7
    Why not have a verified column rather than a seperate table? – chris85 Apr 01 '18 at 23:05
  • 1
    @chris85 i could definitely do that, but i'd prefer to keep the tables separate so that `_verified` would remain as lean as possible in order to keep the queries as quick as possible. i also wouldn't want to have to manually go through the table and clean out all of the expired, non-verified accounts all the time since i'm on shared hosting – oldboy Apr 01 '18 at 23:08
  • I'm with @chris85 on this. Maybe you feel that by using a form of relational tables works here. Not when it comes to verifying users. Oh it works, sure, but relational tables work best with existing data and for data that is for the long stay. I'd just set a boolean flag on verified. – Funk Forty Niner Apr 01 '18 at 23:12
  • *"i also wouldn't want to have to manually go through the table and clean out all of the expired, non-verified accounts"* - Why not just run a cron job to do that for you and use a WHERE clause on unverifiied accounts over a certain period of time? Or an EVENT handler, TRIGGER. – Funk Forty Niner Apr 01 '18 at 23:14
  • @FunkFortyNiner can you run cron jobs on shared hosting?! i'll have to contact my provider. the answer to the question would still be nice to know for other use cases in the future – oldboy Apr 01 '18 at 23:16
  • @Scuzzy i'd also have to add other columns like `expiration_time` to every single row in the `_verified` table which adds complexity and starts to make it messy – oldboy Apr 01 '18 at 23:19
  • *"can you run cron jobs on shared hosting?"* - Some do and some don't. However if you can't run a cron, you can probably do it with mysql using a TRIGGER http://www.mysqltutorial.org/sql-triggers.aspx or an EVENT, as seen in this Q&A on Stack https://stackoverflow.com/q/9472167/1415724 - Because querying 2 tables each time with 2 different queries, is too much I feel. But that is just an opinion. – Funk Forty Niner Apr 01 '18 at 23:19
  • N.B.: There is something about your using the same name placeholders and suggest you read this Q&A about that https://stackoverflow.com/q/42244086/1415724 should you not be able to use the same name for both queries if emulation isn't set. What you have now might fail. – Funk Forty Niner Apr 01 '18 at 23:27
  • @FunkFortyNiner i don't think it matters in my context because i'm using two separate prepared statements – oldboy Apr 01 '18 at 23:34

2 Answers2

1

You can try this.

    SELECT account_email, 'users_unverified' AS tablename FROM users_unverified WHERE account_email  = :account_email
UNION 
SELECT account_email, 'users_verified' AS tablename FROM users_verified WHERE account_email = :account_email;
chris85
  • 23,846
  • 7
  • 34
  • 51
sweting
  • 390
  • 1
  • 8
  • i'm fairly new to PHP and MySQL, so, since the tables' aliases only exist for the duration of the query, how would i be able to access those names once the query has been executed? – oldboy Apr 01 '18 at 23:12
  • The tablename is in the static column tablename you can acess it in these query with $result["tablename"] – sweting Apr 01 '18 at 23:17
  • `users_unverified AS unveri` and `users_verified AS veri` are the names of the tables, though, not what is being selected? or can the names of tables be selected like you've done in your code (`SELECT account_email, users_verified AS veri ...`??) – oldboy Apr 01 '18 at 23:25
  • No you cant select the name of the table in this context. I have selected the name of the table as static string in the column 'tablename'. So if there is a result from users_unverified the column tablename contains users_unverified for each result row from this table. Similiary to the other table. – sweting Apr 01 '18 at 23:30
  • @Anthony I'm wondering why you didn't tell them what you wrote in your question: *"I don't believe JOINS and UNIONS are what I'm looking for since I don't want to combine rows"*. – Funk Forty Niner Apr 01 '18 at 23:30
  • @FunkFortyNiner i said " ... (unless of course i can still identify in which table the value is located?)" – oldboy Apr 01 '18 at 23:32
  • @sweting ohhh, so then does that temporarily create an additional column, the `tablename` column? (can you delete my edit if it isn't correct, so i can see the original answer again?) – oldboy Apr 01 '18 at 23:33
  • @Anthony yes there is temporarily an additional column tablename. – sweting Apr 01 '18 at 23:40
  • @sweting so you can create temporary columns by simply SELECTing them in queries?! – oldboy Apr 02 '18 at 00:03
  • 1
    @Anthony It is not a column, it is just a string you are **select**ing – chris85 Apr 02 '18 at 00:38
  • @chris85 hm... i'm going to have to read more about this. i can't even see the original answer anymore because i ignorantly edited it and buddy hasn't declined the edit – oldboy Apr 02 '18 at 01:09
0
SELECT account_email, true AS verified  FROM users_unverified WHERE account_email = :account_email
UNION 
SELECT account_email, false AS verified FROM users_verified WHERE account_email = :account_email;
Roemer
  • 1,124
  • 8
  • 23
  • so then after executing that statement, (`$result = statement->fetch(PDO::FETCH_ASSOC);`) `$result["verifiied"]` will return `true` or `false`???? – oldboy Apr 04 '18 at 00:11
  • That's the idea :D – Roemer Apr 04 '18 at 22:00
  • okay, the only thing is that it won't work in this case because you also need to account for the event that neither database may contain an entry, in which case false would also be returned? it still might be doable, but i'm kinda new to PHP/MySQL, so i'm not exactly sure what is returned when there are no entries detected (an empty array?), but i know it evaluates to false – oldboy Apr 04 '18 at 23:42
  • $result["verifiied"] will return true or false,and the query will return no results if it is in neither table. Thus you can easily distinguish the three different scenarios. – Roemer Apr 05 '18 at 11:41
  • what is this "no result" that it returns? how do you check for that? `empty($result["verified"])`? – oldboy Apr 06 '18 at 05:48
  • Dear Anthony, as much as I like to help you, this is not the forum on "how-to" of the very basic capabilities. This kind of info is EVERYWHERE on the Internet, Google is your friend. This forum is for very specific problems you cannot solve with Google. You need to invest a little time to master the basics of PHP and MySQL, and since there is tons of free material over this, I am not going to replicate that here. Good luck! You'll find it! – Roemer Apr 06 '18 at 09:10
  • i'm simply inquiring about YOUR comment. YOU said it returns "no result", whatever that means. i've never heard of that or read about that anywhere online lol – oldboy Apr 06 '18 at 19:42
  • Exactly. And that is exactly why you should do a crash course on PHP and MySQL. I am not explaining you the alphabet either, even if I am using letters to answer your question. I really don't mean this rude. I actually mean it, anyone should first learn the very very very basics before you start actually programming. And this is not the place for those basics. – Roemer Apr 07 '18 at 17:14