MySQL中的OR逻辑

I have a simple table

Via $Poles OR $Power (or combination of both) I am selecting a list of rows

if($_GET["Power"]!="*" and $_GET["Poles"]!="*") $query = "SELECT * FROM `TABLE 2` WHERE Power=".$_GET["Power"]." AND Poles=".$_GET["Poles"]."";
elseif($_GET["Poles"]!="*") $query.= "SELECT * FROM `TABLE 2` WHERE Poles=".$_GET["Poles"]."";
elseif($_GET["Power"]!="*") $query.= "SELECT * FROM `TABLE 2` WHERE Power=".$_GET["Power"]."";
else $query= "SELECT * FROM `TABLE 2` ORDER BY Power";

I am absolute beginner but I feel that this is a wrong way to do it.

Please advise me, how should it be done?

1. If Power is set and Poles is * - I want all rows with this power.
2. If Poles is set and Power is * - I want all rows with this poles.
3. If both are set to any value - I want to see this exact row;
4. If both are set to * - I want all rows from the table

$query = "SELECT * FROM `TABLE 2` WHERE Power LIKE ".$_GET["Power"]." AND Poles LIKE ".$_GET["Poles"]."";

Using this query will be able to get you the result you want. Notice that I have change the = operator to LIKE.

Then in your userform or the way you pass values to $_GET you need to change * to %. % is MySQL's wilcard symbol.

Additionally

you are using input from the user in database queries and as such you should ALWAYS use a prepared statement to avoid SQL injection.

Please refer to This SO Q&A which two very easy to follow samples of how to perform a proper prepared statement

With just the information provided I think your logic generally accomplishes what you're going for. You have several scenarios that to me are distinctly different queries, so I would handle the logic for building the queries in PHP.

I'm not sure if you are using any kind of framework or not, but I'll assume you are not, though I would suggest that you do if it's practical to do so. A good framework will provide input scrubbing and database management to make life easier, and also more security options.

For security purposes, since you are constructing a query with input directly from the user, I would suggest you use the PDO class for interacting with your database (if you aren't). The PDO class will allow you to bind the user's input to the query, and then it will escape the input automatically for you. This greatly reduces your risk of SQL injection attacks (users doing malicious things to your database using what they submit to you) and is considered standard practice in PHP now.

More information on the PDO class can be found here: http://php.net/manual/en/book.pdo.php

More information on SQL injection attacks can be found here: http://www.w3schools.com/sql/sql_injection.asp

So here would be my untested code putting it all together:

$dsn = "localhost"; //Where is your DB located?
$username = "username";
$password = "password";

//Create new DB connection (http://php.net/manual/en/pdo.construct.php)
$dbh = new PDO($dsn, $username, $password);

//Craft and execute the appropriate query
//Each query will include placeholders for data (like :power)
//Placeholders will be replaced with the data passed to execute()
//The PDO class will escape the user input to prevent SQL injection
if ($_GET["Power"] != "*" && $_GET["Poles"] != "*") {
    $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` WHERE `Power` = :power AND `Poles` = :poles;");
    $stmt->execute([
        ':power' => $_GET["Power"],
        ':poles' => $_GET["Poles"]
    ]);
} elseif ($_GET["Power"] != "*") {
    $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` WHERE `Power` = :power");
    $stmt->execute([':power' => $_GET["Power"]]);
} elseif ($_GET["Poles"]) {
    $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` WHERE `Poles` = :poles;");
    $stmt->execute([':power' => $_GET["Poles"]]);
} else {
    $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` ORDER BY `Power` ASC");
    $stmt->execute();
}

if ($stmt->rowCount()) {
    //The query returned results, which we can fetch in a variety of ways
    //http://php.net/manual/en/pdostatement.fetch.php
    //http://php.net/manual/en/pdostatement.fetchall.php
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    //No results, handle appropriately
}

If that if/elseif/else block looks gnarly, you can actually accomplish the same thing with a switch statement. Pass true as the value to match, and make your case statements the logic that defines each of your cases. The first case that matches will run, while default will run if no cases match.

Here's what that looks like:

switch (true) {
    case $_GET["Power"] != "*" && $_GET["Poles"] != "*":
        $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` WHERE `Power` = :power AND `Poles` = :poles;");
        $stmt->execute([
            ':power' => $_GET["Power"],
            ':poles' => $_GET["Poles"]
        ]);
        break;
    case $_GET["Power"] != "*":
        $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` WHERE `Power` = :power");
        $stmt->execute([':power' => $_GET["Power"]]);
        break;
    case $_GET["Poles"] != "*":
        $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` WHERE `Poles` = :poles;");
        $stmt->execute([':power' => $_GET["Poles"]]);
        break;
    default:
        $stmt = $dbh->prepare("SELECT * FROM `TABLE 2` ORDER BY `Power` ASC");
        $stmt->execute();
        break;

Edit 1:

For what it's worth, I tend to avoid exposing any of my underlying technologies as much as possible, so using default SQL keywords/operators that a user can see and potentially manipulate is problematic (to me). As a result, I don't use * or % as values that a user can pass to me. Instead I go with some other placeholder I can use in my logic, like an empty string, 0, or -1, depending on what the field accepts.

The use of PDO and bound parameters negates the risk of users doing malicious things with their input, so just doing that alone takes away a lot of the risk of using * or %. In that case it just comes down to whether or not you want the user to be able to guess your underlying storage engine.

From a performance standpoint, I believe the equality operator (=) will be faster than the LIKE operator in a WHERE clause, but you would likely only notice the performance hit in a large production environment, so no real worries there.