What is the correct MySQL database statement where the variable defined as all the rows

Question

I try to explain this well folks,

I have a database with 20 questions with two principles : 1) Cardiology and 2) Endocrine. And you can select using HTML selection menu either of the concepts or you can select 3) All.

And on my html page I have a selection menu with 3 options and they each have a value:

<div id="selectContainer1">
<select id="selectedPrinciple" name="selectedPrinciple">
        <option value="" disabled="disabled" selected="selected">Select a System</option>

        <option value="">All</option>
        <option value="VASCULAR">Cardiology, Vascular System</option>
        <option value="ENDOCRINE">Endocrine</option>

    </select>
    </div>
    <input type="submit" value="Start">

I have this code on php:

$selectedPrinciple = $_POST['selectedPrinciple']; 

$sql = ("SELECT * FROM qbanktable WHERE Principle = '$selectedPrinciple'"

Now when I select option "Cardiology" or "Endocrine", all the rows that are related to those are picked from my database and showed on the next page. But when I select "All" I get a syntax error because of course as it does have no value the row cannot be found on my database. Is there anything I can put for the option value for "All" that the mysql returns all the rows?

There's several issues at play here. The most important is that your query is vulnerable to [SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Next is that your code will throw a notice / warning if `$_POST['selectedPrinciple']` is not set. Third, while you've sliced off a nice minimal amount of code, you haven't provided enough context to know how to give you the best method for tackling this issue. — random_user_name, Jan 13 '17 at 21:53

score 1 · Accepted Answer · 2017-01-13T22:04:22.937

You could check if $selectedPrinciple is empty() and modify the query accordingly.

$selectedPrinciple = $_POST['selectedPrinciple']; 
if(!empty($selectedPrinciple)) {
// this line indicates that you don't use prepared statements
$sql = "SELECT * FROM `qbanktable` WHERE `Principle` = '$selectedPrinciple'";
} else {
$sql = "SELECT * FROM `qbanktable`";
}

full example using mysqli prepared statement

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";    
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);    
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$selectedPrinciple = $_POST['selectedPrinciple']; 
if(!empty($selectedPrinciple)) {
// prepare and bind
    $stmt = $conn->prepare("SELECT * FROM `qbanktable` WHERE `Principle` = ?");
    $stmt->bind_param("s", $selectedPrinciple);
} else {
// prepare
    $stmt = $conn->prepare("SELECT * FROM `qbanktable`");
}    
// execute
$stmt->execute();
// fetch data
if (!($res = $stmt->get_result())) {
   echo "Failed to fetch the result set: (" . $stmt->errno . ") " . $stmt->error;
}
// print data
print_r($res->fetch_all());
// close prepared statement
$stmt->close();
// close connection
$conn->close();

You should at least mention the fact that using string interpolation in SQL is dangerous and opens the DB up to SQL injection attacks. — random_user_name, Jan 13 '17 at 21:50
SQL injection is true, but it is for a group of 10 people so hopefully non is that skilled — oliverfoxx, Jan 13 '17 at 22:00
If it's on the internet, you have to think about *bad visitors*, NOT about your intended audience. — random_user_name, Jan 13 '17 at 22:03

score 1 · Answer 2 · answered Jan 13 '17 at 22:02

Personally, I like to use a few techniques to "build" my query, like so:

NOTE:
I'm demonstrating how to do this with PDO and parameter binding, because your query is open to SQL injection attacks.

$sql = "SELECT * FROM `qbanktable`";

$where = [];
$params = [];

if ( ! empty( $_POST['selectedPrinciple'] ) ) {
    $where[] = '`Principle` = ?';
    $params[] = $_POST['selectedPrinciple'];
}

if ( /* some other condition */ ) {
    // add to the $where / $params as appropriate
}

// Glue the $where into a string
$where = implode( ' AND ', $where );

// Append where to the $sql statement
$sql .= ( $where ) ? ' WHERE ' . $where : '';

// assumes $conn is already a set-up PDO connection
$stmt = $conn->prepare( $sql );
$stmt->execute( $params );
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

score 0 · Answer 3 · answered Jan 13 '17 at 21:55

I think the best way is to check in PHP if the value of selectedPrinciple is something like ALL or is empty, and then don´t add the WHERE part of the query.

If really want to use some value for All option, you can try to use one or two percents signs '%' or '%%', but I don´t remember if it works. However, I don´t recommend this approach. Take care also about SQL Injection.

What is the correct MySQL database statement where the variable defined as all the rows

3 Answers3