How to select from multiple tables?

Question

I'm making a website where and I need a login file to redirect administrators and clients to their respective account.

It is supposed to verify both tables and see if email corresponds to password.

Here's what I already have

<?php
include "data-base.php";
if($_SESSION){
header("location: index.php?site=perfil");
    echo "<script>window.location.href = \"index.php?site=login&erro=3\";</script>";
}
$result = mysql_query("select * from admin, clients where email='".$_POST['email']."' and password='".$_POST['password']."'");
if (mysql_num_rows($result)>0)
{
$linha = mysql_fetch_array($result);
$_SESSION['id']=$linha['id'];
if($linha['email'] = "admin") {
    header("location: admin.php?id=".$linha['id']."");
} else {
    header("location: client.php?id=".$linha['id']."");
}
} else {
echo "<script type=\"text/javascript\">alert(\"Incorrect email or password\"); window.location = 'index.php';</script>";
}
?>

`if($linha["email"] = "admin") {` you're assigning instead of comparing. — Funk Forty Niner, May 27 '14 at 00:01
Change your database structure? Maybe add a boolean that represents that a user is an administrator. — Pakspul, May 27 '14 at 12:21
Please be aware that the mysql extension (supplying the `mysql_` functions) has been deprecated since 2012, in favor of the mysqli and PDO extensions. It's use is highly discouraged. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Oldskool, May 27 '14 at 13:06

score 1 · Answer 1 · edited Oct 24 '15 at 12:35

1

I think it's better to slice one query on two parts. First, try to get rows from "admin"table. If mysql_now_rows == 0, then get rows from users. If empty, show the message about wrong login&password

$q = "SELECT * FROM admin WHERE email = '".$_POST["email"]."' AND password ='".$_POST["password"]."'";
$result = mysql_query($q);
if(sizeof($result) !== 0){
    header("put admin URL here");
} else {
    $q = "SELECT * FROM clients WHERE email = '".$_POST["email"]."' AND password ='".$_POST["password"]."'";
    $result = mysql_query($q);
    if(sizeof($result) !== 0){
        header("put clients URL here");
    } else {
        print_r("wrong email&pass");
    }
}

Sorry about formatting, I'm from phone

edited Oct 24 '15 at 12:35

Funk Forty Niner

74,450
15
68
141

answered May 27 '14 at 00:15

Zhooravlik

11
2

Using `$POST`'s will fail, on top of being open to SQL injection. – Funk Forty Niner May 27 '14 at 00:32
I just gave right way, everything other on topicstarter's hands. I think he'll use htmlspecialchars – Zhooravlik May 27 '14 at 00:42
That's not what I mean. I mean that using `$POST` will fail. You're missing something very important. Look at your code, then look at the other answers including the OP's code and compare it with what you have. – Funk Forty Niner May 27 '14 at 00:43
I guess I have to spell it out for you then. This `$POST`, is missing the underscore, as in `$_POST`. – Funk Forty Niner May 27 '14 at 01:32
Edit all of those, and I'll remove my -1 because I won't do it for you. – Funk Forty Niner May 27 '14 at 01:33

score 1 · Answer 2 · answered May 27 '14 at 00:44

"select * from admin, clients where (admin.email='".$_POST['email']."' OR
clients.email='".$_POST['email']."') AND (admin.password='".$_POST['password']."' OR
clients.password='".$_POST['password']."')";

in this question, you also can use a column to enter user type rather than using two tables. if then can run below statement.

"select * from admin_clients_table where email='".$_POST['email']."' AND
password='".$_POST['password']."'";

Since question was how to select from multiple tables, you can do as follows:

"select table1.column1, table1.column2, table2.column1, table2.column3 from table1,
 table2 where (table1.column4 = table3.column1 and table2.column2 = 'value1') or
 table3.column1 = 'value2' order by table1.id desc limit 0,100";

score 0 · Answer 3 · answered May 27 '14 at 00:07

There is a small error in your script.

if($linha["email"] = "admin")

should be

if($linha["email"] == "admin")

With 1 = all you do is saying $linha["email"] should become "admin" With 2 = you are comparing $linha["email"] and $linha["email"]

So in other words if $linha["email"] is the same as $linha["email"]

Hope this helps you on your way.

Also you need to change your query and join the to tables.

$result = mysql_query("SELECT * from admin, clients 
                       WHERE email='".$_POST['email']."' 
                       AND password='".$_POST['password']."'");

How to select from multiple tables?

3 Answers3