MySQL query select row with specific item

Question

I have list of categories in my database that I populate in dropdown menu. The website should be able to display items depending on which category selected by user.

My current coding :

if ($selected_cat == "All Item" || !isset($selected_cat)) {
    $sql_select = "SELECT * FROM tblproduct";
}
else {
    $sql_select = "SELECT * FROM tblproduct WHERE prodCat = '$selected_cat'";
}
$result = $conn->query($sql_select);

if ($result->num_rows > 0){
    while($row = mysqli_fetch_assoc($result))
    {
        extract($row);
        // some statement to display item
    }
}

So the problem here is, some of my category's value have apostrophic (') such as Woman's Fashion. If I run my query, mysql wouldn't be able to find the specific category as it read Woman's Fashion as Woman\'s Fashion, but my query $sql_select = "SELECT * FROM tblproduct WHERE prodCat = '$selected_cat'"; is reading it as Woman's Fashion which is wrong format in mysql.

Learn to use parameterized queries. Avoiding unnecessary syntax errors is the best reason. Avoiding SQL injection is a really close second. Re-using query plans is a really close third. And, there are no doubt other really good reasons too. — Gordon Linoff, Apr 22 '17 at 13:52

score 0 · Answer 1 · answered Apr 22 '17 at 13:55

You have to prepare the queries then bind values to them in order to avoid this kind of issues. I have encountered this too many times because of the French speaking applications I have developed.

My best advise to you is merging toward PDO and using prepare + execute see the example below:

function getDocument($conn, $ref)
{
    $req = $conn->prepare('SELECT * FROM document where reference= :reference');
    $req->execute(array('reference' => $ref));
    return $req->fetchAll();
}

Believe me once you get used to PDO you'll save yourself from a lot of headaches of this kind. Furthermore, it will secure your app against SQL injections.

score 0 · Answer 2 · answered Apr 22 '17 at 14:00

Since you're already using mysqli, learn to use bind parameterized prepared statements like:

$query = "SELECT * FROM tblproduct WHERE prodCat = ?";
$stmt = $conn->prepare($query);

$stmt->bind_param("s", $selected_cat);
$stmt->execute();
$stmt->close();

You could do it the Object oriented way or your current procedural way Have a look at this: http://php.net/manual/en/mysqli-stmt.execute.php

score -1 · Accepted Answer · answered Apr 22 '17 at 14:02

I hope below code will help you use addslashes() function

if ($selected_cat == "All Item" || !isset($selected_cat)) {
    $sql_select = "SELECT * FROM tblproduct";
}
else {
    $sql_select = "SELECT * FROM tblproduct WHERE prodCat ='".addslashes($selected_cat)."'";
}
$result = $conn->query($sql_select);

if ($result->num_rows > 0){
    while($row = mysqli_fetch_assoc($result))
    {
        extract($row);
        // some statement to display item
    }
}

MySQL query select row with specific item

3 Answers3