Laravel Query Builder - Migrating From Traditional SQL

- 1 answer

So I'm slowly starting to migrate my website to Laravel and I', finding it very challenging to say the least. I now have to rewrite all my traditional sql statements and the documentation doesn't give simple snippets of common sql queries. Below is my login script that I've spent hours trying to figure out how to convert..

$query = "SELECT * FROM users WHERE email='$email' AND password='$password'";
$result = mysqli_query($connection, $query);
$count = mysqli_num_rows($result);

if ($count > 0) {
    $row = mysqli_fetch_array($result);

    if ($row['verified'] == 1) {
        echo "Valid";
    } elseif ($row['verified'] == 0) {
        echo "not aValid";
} elseif ($count == 0) {
    echo "not Valid";

Any help or guidance will greatly be appreciated. I need to be pointed in the right direction as to how to do simple queries like this one as my site is riddled with a lot of them. I'm starting to think I may have written my logic in a bad way to begin with.



Converting your query can be done pretty easily thanks to Laravel's Query Builder:

$user = DB::table('users')->where(['email' => $email, 'password' => $password])->first();

if (!is_null($user)) {
    if ($user->verified) {
        echo 'Valid';
    } else {
        echo 'Not Valid';
} else {
    echo 'Not Valid';

Building queries using the query builder is actually a pretty simple process if you're queries are not extremely complex. You should read the documentation in depth and you should be fine. Also, when your queries don't seem to return the results you're expecting you can always use the toSql method that returns a string containing the SQL query that was being generated, so this:

dump(DB::table('users')->where(['email' => $email, 'password' => $password])->toSql());

Will print this:

select * from `users` where (`email` = ? and `password` = ?)

The question marks are placeholders for the actual values being used in conditions, because Laravel uses PDO with bindings to build the queries, meaning you don't need to escape your values when passing them to the builder, so no worries about SQL injection.

That being said, it looks to me like you're trying to authenticate a user with that query. If that's the case, I strongly suggest you use the Authentication System that is part of Laravel.