So I'm looking into create a MySQLi Transaction with prepared statements in PHP. I seem to have the idea all fine, however I have an issue. My statements rely on each other. For example:
Email address entered into email table. User entered into user table with Email Address ID as a foreign key
See code below, which shows two prepared statements, the second relying on the first for the e-mail ID:
//$email_sql
if(!($stmt = $mysqli->prepare($email_sql))) throw new \Exception("[mysqli] Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
if(!($stmt->bind_param('s', $email)) throw new \Exception("[mysqli] Bind Params failed: (" . $mysqli->errno . ") " . $mysqli->error);
//$user_sql
if(!($stmt = $mysqli->prepare($user_sql))) throw new \Exception("[mysqli] Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
if(!($stmt->bind_param('i', $SHOULD_BE_EMAIL_ID)) throw new \Exception("[mysqli] Bind Params failed: (" . $mysqli->errno . ") " . $mysqli->error);
You can see where I have put '$SHOULD_BE_EMAIL_ID', there is where the issue lies. As I can see, there's no way for me to get this value as the first query hasn't yet been executed at this point in the code. Is there a way for me to get round this? I.e. is there any functions that will allow me to get the email ID there. As far as I can see, I have not been able to find one.
The only way I can think of a solution at the moment, is changing the logic of my code, so it does utilise transactions, which if I can avoid, I will.
Many thanks for any help.