MYSQL comparison of values in same column

Question

I'm trying to add an advanced search feature on my website.

For the moment, everything works decently, except the moment when I have to compare multiple values in the same column

For example, I wish to get back all the candidacies which are at the same time type of mystery customer and investigators.

So I'm executing at the first time in mysql interface this query to check if everything is collected but only the first condition is considerated, I get back only the candidacies of type mystery customer :

SELECT * FROM candidacies WHERE candidacyType LIKE "%mysteryCustomer%" AND candidacyType LIKE "%investigators%";

So I did some research and I came across IN operator. I modify my query with this operator. The problem is that this error is send back to me :

PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE zipcode IN ("East") LIMIT ?, ?' at line 1 in /xxx/xxx/xxx/search.php:106

$('.advancedSearchingContent').on('click', function()
  {
      var data = [];
      var index = "";
      $('input:checked').each(function(element)
      {
          //index += $(this).closest('div').attr('id');
          data.push($(this).val());
      });

      var nb_per_page = $('.entriesData').val();
      var search = $('#searchContent').val();
      var page = 1;
      var sort = $('.sortList').val();
      var zipcode = data;

      loadContent(nb_per_page, search, page, sort, zipcode);
  });
  
function loadContent(nb_per_page, search, page, sort, zipcode)
{
    var p = {};
    p['nb_per_page'] = nb_per_page;
    p['search'] = search;
    p['page'] = page;
    p['sort'] = sort;
    p['zipcode'] = zipcode;

    $.post("search.php", p, function(data)
    {
        $("#result").html(data);
    });
}

.sidenav 
{
    height: 100%;
    width: 0;
    position: fixed;
    z-index: 1;
    top: 0;
    left: 0;
    background-color: #778899;
    overflow-x: hidden;
    transition: 0.5s;
    padding-top: 60px;
}

.sidenav a 
{
    padding: 8px 8px 8px 32px;
    text-decoration: none;
    font-size: 25px;
    color: #818181;
    display: block;
    transition: 0.3s;
}

.sidenav a:hover 
{
    color: #f1f1f1;
}

.sidenav .closebtn 
{
	position: absolute;
    top: 0;
    right: 25px;
    font-size: 36px;
    margin-left: 50px;
}

@media screen and (max-height: 450px)
{
	.sidenav 
  	{
  		padding-top: 15px;
  	}
  	.sidenav a 
  	{
		font-size: 18px;
	}
}

.sidenav > div > label
{
	font-size: 20px;
	width: 100%;
}

.sidenav > div > input
{
	width: 100%;
}

.advancedSearchingContent:hover
{
	background-color: white;
}

.sidenav > div 
{
	width: 300px;
	margin-left: 10px;
}

<html>
  <head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">
    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.2.0/css/all.css" integrity="sha384-hWVjflwFxL6sNzntih27bfxkr27PmbbK/iSvJ+a4+0owXq79v+lsFkW54bOGbiDQ" crossorigin="anonymous">
    </head>
    <body>
      <div class="form-group">
        <div class="input-group">
            <input type="text" name="searchContent" id="searchContent" placeholder="Search a candidacy" class="form-control" />
            <button class="btn btn-outline-success my-2 my-sm-0 searchContent" title="Search"><i class="fa fa-search"></i></button>
            <button class="btn btn-outline-info resetContent" title="Reset content"><i class="fas fa-sync-alt" id="iconReset"></i>
            </button>
            <button class="btn btn-outline-secondary advancedSearching" title="advanced Search"><i class="fas fa-search-plus" id="iconAdvancedSearching"></i></button>
        </div>
    </div>
    <section class="dropdownList">
        <label>Display</label>
        <div class="dropdownEntries">
            <select name="entriesData" class="entriesData">
                <option value="5" selected>5</option>
                <option value="10">10</option>
                <option value="20">20</option>
                <option value="50">50</option>
                <option value="100">100</option>
            </select>
        </div>
        <label>results</label>
    </section>
    <section class="dropdownSortList">
        <label>Sort by</label>
        <div class="sortEntries">
            <select name="sortList" class="sortList">
                <option value="lastName" selected>lastName</option>
                <option value="firstName">firstName</option>
                <option value="birthday">Âge</option>
                <option value="zipcode">zipcode</option>
                <option value="scheduleRange">scheduleRange</option>
                <option value="phoneNumber">phoneNumber</option>
                <option value="email">Email</option>
                <option value="candidacyType">candidacyType</option>
            </select>
        </div>
    </section>
    <div id="result">
    <?php include('search.php');?>
    </div>
    <div id="zipContent"></div>
    <div id="mySidenav" class="sidenav">
        <a href="javascript:void(0)" class="closebtn">&times;</a>
        <div class="row" id="zipcode">
            <label for="advancedSearchingZipcode">zipcode : </label>
            <input type="checkbox" name="advancedSearchingZipcode[]" value="North">North
            <input type="checkbox" name="advancedSearchingZipcode[]" value="South">South
            <input type="checkbox" name="advancedSearchingZipcode[]" value="West">West
            <input type="checkbox" name="advancedSearchingZipcode[]" value="East">East
        </div>
        <div class="row" id="age">
            <label for="advancedSearchingAge">Âge between :</label>
            <input type="text" name="advancedSearchingAge[]">
            <label for="advancedSearchingAge">And : </label>
            <input type="text" name="advancedSearchingAge[]">
        </div>
        <div class="row" id="scheduleRange">
            <label for="advancedSearchingScheduleRange">Schedule Range between: </label>
            <input type="text" name="advancedSearchingScheduleRange[]" id="advancedSearchingScheduleRange">
            <label for="advancedSearchingScheduleRange">And :</label>
            <input type="text" name="advancedSearchingScheduleRange[]" id="advancedSearchingScheduleRange2">
        </div>
        <div class="row" id="candidacyType">
            <label for="candidacyType">candidacyType :</label>
            <input type="checkbox" name="advancedSearchingCandidacyType[]" value="mysteryCustomer">mysteryCustomer
            <input type="checkbox" name="advancedSearchingCandidacyType[]" value="investigationUnit">investigationUnit
            <input type="checkbox" name="advancedSearchingCandidacyType[]" value="investigators">investigators
        </div>
        <div class="row">
            <button class="btn btn-outline-success my-2 my-sm-0 advancedSearchingContent" title="Search"><i class="fa fa-search"></i></button>
        </div>
    </div>
</body>

    <?php

    require('config/config.php');

    if(!isset($_SESSION))
    {
        session_start();
    }

    if(isset($_SESSION['flash']))
    {
        foreach($_SESSION['flash'] as $type => $message)
        {
            echo $message;
        }
        unset($_SESSION['flash']);
    }

    $nb_per_page = !empty($_POST['nb_per_page']) ? $_POST['nb_per_page'] : 5;
    $search = !empty($_POST['search']) ? $_POST['search'] : "";
    $p = !empty($_POST['page']) ? $_POST['page'] : 1;
    $sort = !empty($_POST['sort']) ? $_POST['sort'] : "lastName";
    $zipcode = !empty($_POST['zipcode']) ? $_POST['zipcode'] : "";
    $output = "";

    $result = getList($nb_per_page, $search, $p, $sort, $zipcode, $cnx);

    showList($result['list']);
    pagination($result['nb_pages'], $p);

    function getList($nb_per_page, $search, $p, $sort, $zipcode, $cnx)
    {
        $nb_per_page = intval($nb_per_page);
        $start = intval(($p-1) * $nb_per_page);
        $where = "";

        if($search != "")
        {
            $where .= "WHERE lastName LIKE '%".$search."%' OR firstName LIKE '%".$search."%' OR birthday LIKE '%".$search."%' OR zipcode LIKE '%".$search."%' OR scheduleRange LIKE '%".$search."%' OR phoneNumber LIKE '%".$search."%' OR email LIKE '%".$search."%' OR candidacyType LIKE '%".$search."%'";
        }
        if($sort != "")
        {
            $order .= "ORDER BY ".$sort." ";
        }
        if($zipcode != "")
        {
            if($where != "")
            {
                $checkbox .= "AND zipcode IN (\"".$zipcode."\")";
            }
            else
            {
                $checkbox .= "WHERE zipcode IN (\"".$zipcode[0]."\")";
            }
        }

        $query = $cnx->prepare("SELECT * FROM candidacies ".$where.$order.$checkbox." LIMIT :start, :nb_per_page");
        $query->bindValue(':start', $start, PDO::PARAM_INT);
        $query->bindValue(':nb_per_page', $nb_per_page, PDO::PARAM_INT);
        $query->execute();

        $list = $query->fetchAll(PDO::FETCH_ASSOC);

        $query = $cnx->prepare('SELECT COUNT(id) AS totalResults FROM candidacies '.$where.' ');
        $query->execute();

        $result = $query->fetch(PDO::FETCH_OBJ);
        $nb = $result->totalResults;

        $nb_pages = ceil($nb / $nb_per_page);
        if($nb_pages < 1)
        {
            $nb_pages = 1;
        }

        return array(
            "list" => $list,
            "nb" => $nb,
            "nb_pages" => $nb_pages
            );
    }

    function showList($list)
    {
        $output = "";

        if(sizeof($list) > 0)
        {
            $output .= '
                <table class="table table-striped" id="candidaciesTable">
                    <thead class="thead-light">
                        <tr>
                            <th scope="col">lastName</th>
                            <th scope="col">firstName</th>
                            <th scope="col">Âge</th>
                            <th scope="col">Zipcode</th>
                            <th scope="col">Schedule Range</th>
                            <th scope="col">phoneNumber</th>
                            <th scope="col">Email</th>
                            <th scope="col">TcandidacyType</th>
                            <th scope="col">Action</th>
                        </tr>
                    </thead>
                    <tbody>
            ';

            foreach($list as $key => $value)
            {
                $currentDate = date("Y");
                $currentBirthday = substr($value['birthday'], 0, 4);

                $birthday = $currentDate - $currentBirthday;

                $output .= "
                    <tr>
                        <td>".htmlspecialchars($value["lastName"])."</td>
                        <td>".htmlspecialchars($value["firstName"])."</td>
                        <td>".htmlspecialchars($birthday)."</td>
                        <td>".htmlspecialchars($sort["zipcode"])."</td>
                        <td>".htmlspecialchars($value["scheduleRange"])."</td>
                        <td>".htmlspecialchars($value["phoneNumber"])."</td>
                        <td>".htmlspecialchars($value["email"])."</td>
                        <td>".htmlspecialchars($search["candidacyType"])."</td>
                        <td>
                            <div class='btn-group'>
                                <button class='btn btn-light btnViewCandidacy' id=".$value['id'].">
                                    <i class='far fa-eye'></i>
                                </button>
                                <button class='btn btn-info' id='btnEditCandidacy' onclick='editCandidacy(".$value['id'].")'>
                                    <i class='far fa-edit'></i>
                                </button>
                                <button class='btn btn-danger' id='btnDeleteCandidacy' onclick='deleteCandidacy(".$value['id'].")'>
                                    <i class='far fa-trash-alt'></i>
                                </button>
                                <button class='btn btn-secondary' id='btnZipContent' onclick='zipExtract(".$value['id'].")'>
                                    <i class='fas fa-file-archive'></i>
                                </button>
                            </div>
                        </td>
                    </tr>
                ";
            }

            $output .= "
                </tbody>
            </table>";

            $output .= '
                <div class="modal" tabindex="-1" role="dialog" id="candidacyModal">
                    <div class="modal-dialog" role="document">
                        <div class="modal-content">
                            <div class="modal-header">
                                <h5 class="modal-title">Candidature</h5>
                                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                                    <span aria-hidden="true">&times;</span>
                                </button>
                            </div>
                            <div class="modal-body" id="modal-body">
                            </div>
                            <div class="modal-footer">
                                <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                            </div>
                        </div>
                    </div>
                </div>';

            $output .= '
                <div class="modal" tabindex="-1" role="dialog" id="deleteCandidacyModal">
                    <div class="modal-dialog" role="document">
                        <div class="modal-content">
                            <div class="modal-header">
                                <h5 class="modal-title">Candidature</h5>
                                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                                    <span aria-hidden="true">&times;</span>
                                </button>
                            </div>
                            <div class="modal-bodyCandidacy">
                                <p></p>
                            </div>
                            <div class="modal-footer">
                                <button type="button" class="btn btn-primary" id="confirmDeletion">Supprimer</button>
                                <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                            </div>
                        </div>
                    </div>
                </div>';

            /*$output .= "
                <div class='modal' tabindex='-1' role='dialog' id='advancedSearchingModal'>
                    <div class='modal-dialog' role='document'>
                        <div class='modal-content'>
                            <div class='modal-header'>
                                <h5 class='modal-titleadvancedSearching'>Recherche avancée</h5>
                                <button type='button' class='close' data-dismiss='modal' aria-label='Close'>
                                    <span aria-hidden='true'>&times;</span>
                                </button>
                            </div>
                            <div class='modal-body' id='modal-bodyadvancedSearching'>
                            </div>
                            <div class='modal-footer'>
                                <button type='button' class='btn btn-secondary' data-dismiss='modal'>Close</button>
                            </div>
                        </div>
                    </div>
                </div>";*/
        }
        else
        {
            $output .= "<div class='alert alert-info' style='margin-top: 20px'>No results found !</div>";
        }

        echo $output;
    }

    function pagination($nb_pages, $page)
    {
        $output = "";
        $output .= "<div class='pagination-centered'>";
        for($i = 1; $i <= $nb_pages; $i++)
        {
            $class = "";
            if($page == $i){
                $class = "active";
            }
            $output .=  '<span class="paginationLink '.$class.'" id="'.$i.'">'.$i.'</span>';
        }
        $output .=  "</div>";

        echo $output;
    }
?>
<script>
    $(document).ready(function()
    {
        $('.btnViewCandidacy').on('click', function () 
        {
            var candidacyId = $(this).attr('id');
            $.ajax({  
                url:"viewCandidacy.php",  
                method:"get",  
                data:{candidacy:candidacyId},  
                success:function(data)
                {  
                    $('#modal-body').html(data);
                    $('#candidacyModal').modal("show");  
                }  
            });          
        });

        $('.paginationLink').on('click', function()
        {
            var page = $(this).attr('id');
            var nb_per_page = $('.entriesData').val();
            var search = $('#searchContent').val();
            var sort = $('.sortList').val();
            //loadPagination(page);

            loadContent(nb_per_page, search, page, sort);
        });

        $('#candidacyModal').on('shown.bs.modal', function()
        {
            var urlCoveringLetter = $('#handleCoveringLetter').attr('href');
            var urlCurriculumVitae = $('#handleCurriculumVitae').attr('href');

            if(urlCoveringLetter)
            {

            }
            else
            {
                $('#handleCoveringLetter').css('color', 'red');
            }

            if(urlCurriculumVitae)
            {

            }
            else
            {
                $('#handleCurriculumVitae').css('color', 'red');
            }
        });
    });
</script>

LIKE operator can mean: %keyword = any value that ends with the searched keyword, keyword% = any value that begins with the searched keyword, %keyword% any value that contains the searched keyword. If you are looking for a specific keyword exactly then it is useless — user2399035
– user2399035, Commented Oct 24, 2018 at 7:30
IN operator means: I have a set of possible values, comma separated, and I want to check if the searched keyword is inside these set of values. example: keyword IN("k1","k2","k3") will return true if keyword is matching k1 OR k2 OR k3 — user2399035
– user2399035, Commented Oct 24, 2018 at 7:32
I'm not using LIKE operator anymore, but IN operator right now ! And why downvote really??? — Maestro
– Maestro, Commented Oct 24, 2018 at 7:32
Yeah the extra code is very irrelevant. Just change the AND into OR in your original MySQL query and you'll get the results you want. GL :-) — Werner
– Werner, Commented Oct 24, 2018 at 7:32
coming to your issue: the first query can work if you just replace the AND condition between your like with an OR — user2399035
– user2399035, Commented Oct 24, 2018 at 7:33

Nick · Accepted Answer · 2018-10-24 07:53:39Z

1

Your problem is in this line:

$query = $cnx->prepare("SELECT * FROM candidacies ".$where.$order.$checkbox." LIMIT :start, :nb_per_page");

$order is an ORDER BY clause and $checkbox is a WHERE clause, which has to go before any ORDER BY clause. Change that line to this and it should work fine.

$query = $cnx->prepare("SELECT * FROM candidacies ".$where.$checkbox.$order." LIMIT :start, :nb_per_page");

answered Oct 24, 2018 at 7:53

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

MYSQL comparison of values in same column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related