-3

I am absolute beginner in creating database and I only know that we can use,

"SELECT * FROM users WHERE username = '$username' and password = '$password'";

but, what if there is multiple table in my SQL and I want to select them all?

can I just do,

"Select * from users where username = '$username' and password = '$password' and email = '$email' and address = '$address' and phone = '$phone'";

Here are my PHP script:

public function does_user_exist($username,$password,$email,$address,$phone){
    $query = "Select * from users where username = '$username' and password = '$password' and email = '$email' and address = '$address' and phone = '$phone'";
        $result = mysqli_query($this->connection, $query);
        if(mysqli_num_rows($result) > 0){
            $json['success'] = 'Welcome '.$email;
            echo json_encode($json);    
            mysqli_close($this->connection);
            } else {
                $query = "Insert into users(username, password, email, address, phone) values ('$username','$password','$email', '$address', '$phone')";
                $is_inserted = mysqli_query($this->connection, $query);
                if ($is_inserted == 1){
                    $json['success'] = 'Account created, welcome '.$email;
                    } else {
                        $json['error'] = 'Wrong password ';
                        }
                        echo json_encode($json);
                        mysqli_close($this->connetion);
                }
        }

UPDATE

<?php

require_once 'connection.php';
header('Content-Type: application/json');

class User {
    private $db;
    private $connection;
    
    function __construct() {
    $this->db = new DB_Connection();
    $this->connection = $this->db->get_connection();
    }
    
    public function does_user_exist($username,$password,$email,$address,$phone){
        $query = ("Select * from users where username = '$username' and password = '$password' and email = '$email' and address = '$address' and phone = '$phone'");
        $result = mysqli_query($this->connection, $query);
        if(mysqli_num_rows($result) > 0){
            $json['success'] = 'Welcome '.$email;
            echo json_encode($json);    
            mysqli_close($this->connection);
            } else {
                $query = "Insert into users(username, password, email, address, phone) values ('$username','$password','$email', '$address', '$phone')";
                $is_inserted = mysqli_query($this->connection, $query);
                if ($is_inserted == 1){
                    $json['success'] = 'Account created, welcome '.$email;
                    } else {
                        $json['error'] = 'Wrong password ';
                        }
                        echo json_encode($json);
                        mysqli_close($this->connetion);
                }
        }
}

$user = new User();
if (isset($_POST['username'], $_POST['password'], $_POST['email'], $_POST['address'], $_POSt['phone'])){
    
    $username = $POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];
    $address = $_POST['address'];
    $phone = $_POST['phone'];
    

    if(!empty($username) && !empty($password) && !empty($email) && !empty($address) && !empty($phone)){
        $encrypted_password = md5($password);
        $user -> does_user_exist($username,$encrypted_password,$email,$address,$phone);
    } else {
        echo json_encode("You must fill all fields!")
        }
}

?>

Hopefully you guys can help, I really appreciate the answers.

Community
  • 1
  • 1
Zubli Quzaini
  • 352
  • 1
  • 3
  • 17
  • 1
    Look into `joins`. http://dev.mysql.com/doc/refman/5.7/en/join.html Also look into using parameterized prepared statements. You don't want to really get into the habit of having variables in your queries. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – chris85 Dec 26 '15 at 17:26
  • 5
    But you don't have multiple tables. That is one table - users. Is your your script not working? – paparazzo Dec 26 '15 at 17:31
  • 1
    Please check Google for "SQL Injection" since your script is not safe (when using a form) – barfoos Dec 26 '15 at 17:32
  • 1
    Okay, you updated the title... Is the question how to build a query dynamically when only some of the fields are present? – chris85 Dec 26 '15 at 17:33
  • Yes, Chris my bad.. That is exactly what I am talking about.. – Zubli Quzaini Dec 26 '15 at 17:35
  • @chris85 I know this line "username = '$username' and password = '$password' and email = '$email' and address = '$address' and phone = '$phone'" are wrong.. I just don't know the correct solution.. – Zubli Quzaini Dec 26 '15 at 17:37
  • 1
    That line could be correct if all the values are correct/present. Take a look at this answer http://stackoverflow.com/questions/32685881/filter-mysql-query-with-form-options/32686418#32686418, the OP there never confirmed it worked so I'm not sure it is all correct but should give you a starting point. – chris85 Dec 26 '15 at 17:40
  • 1
    If at least one of the columns supports unique values then you don't need so many conditions in `WHERE` clause, just one condition would be enough. For example, email-id would be unique for each user. – Rajdeep Paul Dec 26 '15 at 17:42
  • Already updated the question.. – Zubli Quzaini Dec 26 '15 at 17:45
  • 1
    @ZubliQuzaini Are you sure you mean *multiple table*? I think you're trying to say, *but, what if there are multiple columns in my SQL...*. Is it? – Rajdeep Paul Dec 26 '15 at 18:04
  • @RajdeepPaul OMG, I feel so stupid.. Sorry, this is my first time doing SQL please forgive me.. – Zubli Quzaini Dec 26 '15 at 18:08
  • @ZubliQuzaini If that's the case, then all these answers don't fit in your question. Next time onwards try to put your question correctly. – Rajdeep Paul Dec 26 '15 at 18:31
  • Thank you, Rajdeep.. By the way, you know the solution? – Zubli Quzaini Dec 26 '15 at 18:52

2 Answers2

1

You have to use JOIN Queries. Try the below SQL Statement

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Refer this JOIN SQL SO Answers

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Difference in MySQL JOIN vs LEFT JOIN

Mysql join query

Community
  • 1
  • 1
Venkat.R
  • 7,420
  • 5
  • 42
  • 63
0

Generally speaking you will want to use JOIN to do this, and you will need to have values in common between your tables, which act as foreign keys, referencing corresponding values in other tables.

EDIT

Updates to the question and comments indicate that the original question was slightly misphrased. Yes, you can absolutely reference/use/compare/evaluate numerous columns in a single query. The example you posted is a good example:

SELECT (column1, column2) FROM users WHERE username = $username AND email = $email

And so on, for as many columns as the table has. You can also use the OR operator, which has the effect of including any row that matches on username OR on email (or whatever other columns you like).

Redbeard011010
  • 954
  • 6
  • 20