CODEWITHSUNDEEP
phpsql
Muhammad Assane
Muhammad Assane

Reputation: 31

How to get data after @ in SQL

I have a table in the database that looks like this

id email
1 [email protected]
2 [email protected]
3 [email protected]

I have a form that looks like this

<form action="action.php" method="post" class="email-form" name="email-form">
  <label for="email">Enter Email</label>
  <input type="email" class="email" id="email" name="email">
  <input type="submit" class="submit-email" name="submit-email" value="submit Email">
</form>

In the backend I'm trying to match the input email domain with the ones in the database, after "@".

What I've tried (if user enters: [email protected])

$email = $_POST["email"];
$emailDomain = explode("@",$email);
$emailDomain = $emailDomain[1];
/*I get the value domain2.com*/

How should I add a Query that matches $emailDomain with the database's email domain.

I know that if I add

$sql = "SELECT * FROM table_name WHERE email = $emailDomain";

It will look for the exact email I want to look for whats after "@" in the email section

Upvotes: 0

Views: 48

Answers (2)

Obsidian Age
Obsidian Age

Reputation: 42354

You already have the part after the @ stored in $emailDomain. Simply check whether the email field contains this value with the LIKE operator:

SELECT * FROM table_name WHERE email LIKE '%value%'

However, note that you cannot simply substitute value with your $emailDomain in the above query, as this would make it vulnerable to SQL injection. Instead, make sure to paramaterise the query, and use a prepared statement to avoid SQL injection:

$conn = new mysqli($servername, $username, $password, $dbname);

$email = $_POST["email"];
$emailDomain = explode("@", $email);
$emailDomain = $emailDomain[1];
$param = "%{$emailDomain}%";
$stmt = $conn->prepare("SELECT * FROM table_name WHERE email LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($result); // Store the output in $result

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

One simple approach uses SUBSTRING_INDEX:

SELECT id, email, SUBSTRING_INDEX(email, '@', -1) AS domain
FROM yourTable;

Upvotes: 1

Related Questions