-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.

Improve this question
13
  • 1
    Look into joins. 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. php.net/manual/en/mysqli.quickstart.prepared-statements.php Commented Dec 26, 2015 at 17:26
  • 5
    But you don't have multiple tables. That is one table - users. Is your your script not working? Commented Dec 26, 2015 at 17:31
  • 1
    Please check Google for "SQL Injection" since your script is not safe (when using a form) Commented Dec 26, 2015 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? Commented Dec 26, 2015 at 17:33
  • 1
    That line could be correct if all the values are correct/present. Take a look at this answer stackoverflow.com/questions/32685881/…, the OP there never confirmed it worked so I'm not sure it is all correct but should give you a starting point. Commented Dec 26, 2015 at 17:40

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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).

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.