Php mysql multiple where statements

Question

How can I have multiple AND conditions in a clause? Like SELECT * FROM database WHERE x=x,y=y AND z=z I tried that, and it didn't work. Thanks.

Nội dung chính Show

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.
How to add multiple WHERE conditions in MySQL?
How run multiple MySQL queries in PHP?
How to select 2 data in MySQL?
How write SQL query in multiple lines in PHP?

asked Jul 18, 2012 at 16:18

0

SELECT * FROM table WHERE x=x AND y=y AND z=z

answered Jul 18, 2012 at 16:18

Yan BerkYan Berk

14.3k9 gold badges54 silver badges52 bronze badges

0

SELECT * FROM database WHERE `x`=x AND `y`=y AND `z`=z

answered Jul 18, 2012 at 16:19

PetePete

1,26910 silver badges18 bronze badges

0

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

MySQL allows you to perform more complicated queries by using AND and OR in your WHERE clause to tie conditions together. You can also use brackets to form groups of equations through two main processes - using AND/OR (plus brackets) to make your queries more specific, and using the JOIN keyword to merge tables together.

Using AND and OR as well as brackets, you can form complex queries with little fuss. Here is a basic example to find someone with FirstName "John" and LastName "Smith".

SELECT * FROM usertable WHERE FirstName = 'John' AND LastName = 'Smith';

Here MySQL will only return rows that meet both requirements. If we wanted MySQL to return any row that had "John" as FirstName or "Smith" as LastName, it is as simple as changing the AND to an OR:

SELECT * FROM usertable WHERE FirstName = 'John' OR LastName = 'Smith';

That would return records such as "John Jones", "Karen Smith". If we wanted only records that definitely had John as FirstName, but could be either "Jones" or "Smith" as LastName, we would need to use brackets, like this:

SELECT * FROM Users WHERE FirstName = 'John' AND (LastName = 'Smith' OR LastName = 'Jones');

This time the FirstName condition must match as well as either LastName "Smith" or LastName "Jones" - "John Connor" will not match. For the final example of AND and OR, here's a snippet of code that will match one of four possibilities: John Smith, John Jones, Jennifer Smith, or Jennifer Jones:

SELECT * FROM Users WHERE (FirstName = 'John' OR FirstName = 'Jennifer') AND (LastName = 'Smith' OR LastName = 'Jones');

AND and OR really aren't difficult at all, particularly seeing as MySQL will sort out bracket problems quite easily- you can use more brackets than is required, but it will still work out OK. When using complex queries with AND or OR, consider trying them out in the MySQL monitor first to make sure you have got the query right before trying them in your PHP scripts.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Grouping rows together with GROUP BY >>

Previous chapter: A working example

Jump to:

Home: Table of Contents

MySQL optionally allows having multiple statements in one statement string, but it requires special handling.

Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.

Example #1 Multiple Statements

<?php

mysqli_report

(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");$sql = "SELECT COUNT(*) AS _num FROM test;
        INSERT INTO test(id) VALUES (1); 
        SELECT COUNT(*) AS _num FROM test; ";$mysqli->multi_query($sql);do {
    if ($result = $mysqli->store_result()) {
        var_dump($result->fetch_all(MYSQLI_ASSOC));
        $result->free();
    }
} while ($mysqli->next_result());

The above example will output:

array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "1"
  }
}

Security considerations

The API functions mysqli::query() and mysqli::real_query() do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the damage of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli::multi_query is not used, the server will not execute the injected and malicious SQL statement.

Example #2 SQL Injection

<?php $mysqli = new mysqli("example.com", "user", "password", "database"); $result = $mysqli->query("SELECT 1; DROP TABLE mysql.user"); if (!$result) { echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error; } ?>

The above example will output:

Error executing query: (1064) You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax 
to use near 'DROP TABLE mysql.user' at line 1

Prepared statements

Use of the multiple statement with prepared statements is not supported.

See also

mysqli::query()
mysqli::multi_query()
mysqli::next_result()
mysqli::more_results()

velthuijsen ¶

4 years ago

Suggested improvement(s) to example 1.

reasons: Multi_query only returns a non false response if a data/result set is returned and only checks for the first query entered. Switching the first SELECT query with the INSERT query will result in a premature exit of the example with the message "Multi query failed: (0)". The example assumes that once the first query doesn't fail that the other queries have succeeded as well. Or rather it just exits without reporting that one of the queries after the first query failed seeing that if a query fails next_result returns false.

The changes in the example comes after the creation of the string $sql.

<?php $mysqli = new mysqli("example.com", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; }

if (!

$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";// changes to example 1 start here// don't bother checking the result from multi_query since it will return false
// if the first query does not return data even if the query itself succeeds.$mysqli->multi_query($sql);do // while (true); // exit only on error or when there are no more queries to process
{
    // check if query currently being processed hasn't failed
    if (0 !== $mysqli->errno)
    {
        echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
        break;
    }// store and possibly process result of the query,
    // both store_result & use_result will return false 
    // for queries that do not return results (INSERT for example)
    if(false !== ($res = $mysqli->store_result() )
    {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
            $res->free();
    }// exit loop if there ar no more queries to process
    if (false === ($mysqli->more_results() )
    {
        break;
    }// get result of the next query to process
    // don't bother to check for success/failure of the result
    // since at the start of the loop there is an error check &
    // report block.
    $mysqli->next_result()} while (true); // exit only on error or when there are no more queries to process
?>

Note that the normal while ($mysqli->more_results() && $mysqli->next_result() has been replaced by two checks and  while (true);
This is due to the 'problem' that next_result will return false if the query in question failed.
So one either needs to do one last check after the while loop to check if there was an error or one has to split up the different actions.
The changes in the example do the splitting.

How to add multiple WHERE conditions in MySQL?

MySQL - WHERE Clause.

You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command..

You can specify any condition using the WHERE clause..

You can specify more than one condition using the AND or the OR operators..

How run multiple MySQL queries in PHP?

Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

How to select 2 data in MySQL?

To select multiple values, you can use where clause with OR and IN operator.

How write SQL query in multiple lines in PHP?

Multi-Line Strings can be written in PHP using the following ways. Using escape sequences: We can use the \n escape sequences to declare multiple lines in a string. PHP Code: PHP.

programming php PHP WHERE Multiple in PHP MySQL multiple OR