1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to bind select2 and mysql databse with php

Discussion in 'PHP' started by Hubert jiang, Jun 2, 2015.

0
  1. #1
    I am trying to bind my select2 input to get results from mysql database, using mysqli connection.I tried several solutions here but have yet to make it work, instead it keeps saying no results found.

    The latest I have tried was the javascript code from https://select2.github.io/examples.html.
    I am not sure if it is my javascript that is failing me or my php file.

    These are my codes, hope someone can point to me where needs to be change.

    HTML
    <div class="form-group">
   <div class="col-xs-12 col-sm-12 col-md-6 col-lg-6">
      <label class="control-label col-lg-3" for="Customer" id="Customer"><span style="color:red;">*</span>Customer:</label>
      <div class="col-lg-9">
         <input id="cCustomer" name="cCustomer" class="cCustomer form-control" type="hidden" value="" style="width: 100%" />  
      </div><!-- END col-lg-9  -->
   </div><!-- END col-xs-12 col-sm-12 col-md-6 col-lg-6  -->
</div><!-- END .form-group  -->
    Code (markup):
    I did include
    <link rel="stylesheet" href="//localhost/1System/select2/css/select2.min.css">
<link rel="stylesheet" href="//localhost/1System/select2/css/select2-bootstrap.min.css">
<script type="text/javascript" src="//localhost/1System/select2/js/select2.full.min.js"></script>
    Code (markup):
    in my 
    <head></head>
    Code (markup):
    JS
    <script>
$(document).ready(function() {
    $("#cCustomer").select2({
        ajax: {
            url: "../../autoComplete/autoAddQuotation1.php",
            dataType: 'json',
            delay: 250,
            data: function (params) {
                return {
                    q: params.term, // search term
                    page: params.page
                };
            },
            processResults: function (data, page) {
                // parse the results into the format expected by Select2.
                // since we are using custom formatting functions we do not need to
                // alter the remote JSON data
                return {
                    results: data.items
                };
            },
            cache: true
        },
        escapeMarkup: function (markup) { return markup; }, // let our custom formatter work
        minimumInputLength: 1,
        //templateResult: formatRepo, // omitted for brevity, see the source of this page
        //templateSelection: formatRepoSelection // omitted for brevity, see the source of this page
    });
});
</script>
    Code (markup):
    Lastly, PHP
    <?php
   include($_SERVER['DOCUMENT_ROOT']."/1System/php/connect.php");//calling connection file

   $conn=dbConnect();
   if (!$conn)
      die("Couldn't connect to MySQL"); /*when server is down, the statement will be showed*/

if(!empty($_GET['q'])){
   $query = "SELECT c.customerID, c.name AS cname FROM customer c WHERE c.name LIKE '%".strtoupper($_GET['q'])."%' or '%".($_GET['q'])."%'";
   $result = mysqli_query($conn, $query);
   $numCustomer = mysqli_num_rows($result);

   if($numCustomer != 0) {
      while(row = mysqli_fetch_array($result)) {
         $answer[] = array("id"=>$row['customerID'], "text"=>$row['cname']);
      }
   }else {
      $answer[] = array("id"=>"0", "text"=>"No Results Found...");   
   }
   echo json_encode($answer);
}
?>
    Code (markup):
    I am using mysqli to connect to my database. My connection is working for the other php pages.

    $conn = mysqli_connect($host, $username, $password);
    Code (markup):
    To be honest I don really know how should the PHP file look like? anyone can point me towards a good example or a right direction.

    PROBLEM:
    When I click on the input on my chrome, it indicated as no results found.

    Thanks for help in advance.
     
    Hubert jiang, Jun 2, 2015 IP
  2. Crazy Sdentau

    Crazy Sdentau Member

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    41
    #2
    Hello, have you tried to run the query in other scripts or phpMyAdmin? Maybe there is an error in the query.
     
    Crazy Sdentau, Jun 5, 2015 IP
  3. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #3
    I don't see where "params" would be getting "term" or "page" from as values -- though really all the jquery bloat, framework bloat, endless pointless garbage classes and DIV for nothing, and outdated SQL methodologies aren't helping make this any simpler. There's a reason I wouldn't put any of that on a website.

    Are you sure $_GET['q'] is even getting a valid value? You're using mysqli, USE IT, as in prepared queries not blindly pasting together a query string with zero safety/sanitization. Also in most cases LIKE is case insensitive, so there's no reason to do that twice. I'd also suggest checking isset BEFORE you go for !empty since they are not the same thing.

    Something like this:
    $answer = [];

if (isset($_GET['q']) && (!empty($_GET['q']))) {

	$q = '%' . %_GET['q'] . '%';
	$stmt = $conn->prepare('
		SELECT customerID, name
		FROM customer
		WHERE name LIKE ?
	');
	$stmt->bind_param('s', $q);
	$stmt->execute();
	$stmt->bind_result($id, $name);
	if ($stmt->fetch()) {
		do {
			$answer[] = [ 'id' => $id, 'text' => $name ];
		} while ($stmt->fetch());
	} else $answer[] = [ 'id' => 0, 'text' => 'No results found...' ];
	
} else $answer[] = [ 'id' => 0, 'text' => 'No search value sent from form.' ];

echo json_encode($answer);
    Code (markup):
    Is how I'd be on that from the PHP side of things... client side? Not sure what you are even doing, but I'd probably axe the bootcrap and jqueery rubbish as it's just taking something that looks simple and turning it into a mess of how NOT to build a website.
     
    deathshadow, Jun 5, 2015 IP