using autosuggest to fill in an input text field after user enters their input, using jquery.

Question

I am trying to use the autosuggest plugin of jquery to take a users input, jquery that input to a php mysql script to get information, then replace the users input with what was retrieved from the jquery request. Now this should stump you all for a minute.

first off...if I replace the returned text from the autosuggest script with just plain text my jquery javascript function returns that value perfectly. But if I try to return the queried data from the script then that is when I get a zero(0) for returned data.

I will provide the code necessary to work on. I will include in that code where the problem exist to my best knowledge. and I will give examples of what works and what doesn't work.

Before writing this up I did test several possible situations.

1. I made sure the autosuggest script connects to the db, that the query is valid, that the data does exist within the table, and that the result set found is being echoed out correctly by using a secondary form that just sends a post request to the script and it responds with exactly the data that it should, so my querying script I know works properly. But at the same time I suspect it as being part of the problem.

2. I tested that my javascript function AcctNmb is being triggered on change by using an alert, and also I can see the function carries thru all the way by the response I receive on the input text field as it changes, load class is turned on, value changes, load class is removed the function completes fully as it should.

3. Now to see the problem fail and succeed just go to the autosuggest query php and you will see my two lines one provides a valid response and the other provides a response of 0. This is the problem I cannot understand how the difference between the two lines makes all the difference in the world on this.

//EDIT I have tried to change out the query script to the old school method of querying and I still get the same results. If I echo the queried data I get a response of '0 ' if I echo a string of text then that text is returned to me. I have added the alternate querying method below. Please can anyone see what I am doing wrong? I just don't see the difference between echoing the variants and echoing a string of text as they should be as far as I know the same thing.

//html form code

  <form id="FormVoucher" name="FormVoucher" method="post" action="index.php">
    <table width="100%">
      <tr>
        <td>Supplier Number:</td>
        <td><input type="text" size="25" value="" name="Facctnmb" id="Facctnmb" onChange="AcctNmb(this)" AUTOCOMPLETE=OFF /></td>
      </tr>
      <tr>
        <td>Invoice Number:</td>
        <td><input type="text" name="Finvnmb" id="Finvnmb" size="25" maxlength="25" AUTOCOMPLETE=OFF /></td>
      </tr>
      <tr>
        <td>Invoice Amount:</td>
        <td><input type="text" name="Finvamt" id="Finvamt" size="25" maxlength="30" AUTOCOMPLETE=OFF /></td>
      </tr>
      <tr>
        <td>Invoice Date:</td>
        <td><input type="text" name="Finvdt" id="Finvdt" size="10" AUTOCOMPLETE=OFF /></td>
      </tr>
      <tr>
        <td>Purchase Order:</td>
        <td><input type="text" name="Fpo" id="Fpo" size="10" maxlength="8" AUTOCOMPLETE=OFF /></td>
      </tr>
      <tr>
        <td>Remark:</td>
        <td><input name="Fremark" id="Fremark" type="text" size="30" maxlength="30" AUTOCOMPLETE=OFF /></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
    </table>
    <div align="left">
      <p>G/L: <input name="Fgl[]" id="Fgl[]" type="text" size="12" maxlength="15" AUTOCOMPLETE=OFF /> Amount: <input name="Famt[]" id="Famt[]" type="text" size="15" maxlength="15" AUTOCOMPLETE=OFF /></p>
      <p id="add-element">Add More G/L Lines For Entry</p>
      <div id="content"></div>
      <input type="submit" value="Submit" />
    </div>
  </form>

//javascript code

function AcctNmb(inputString){
$('#Facctnmb').addClass('load');
  $.post("acctnmb.php", {queryString: ""+inputString+""}, function(data){
    if(data.length >0) {
      $('#Facctnmb').val(data);
      $('#Facctnmb').removeClass('load');
    }
  });
}

//autosuggest query script

<?php
$db = new mysqli('localhost', 'username' ,'password', 'data');

if(!$db) {

  echo 'Could not connect to the database.';
} else {

  if(isset($_POST['queryString'])) {
      $queryString = $db->real_escape_string($_POST['queryString']);
  if(strlen($queryString) >0) {
      $query = $db->query("SELECT acct, mailing_name FROM pub_addrs WHERE acct = '$queryString'");
      if($query) {
        $result = $query->fetch_object();
        $varresults = $result->acct." ".$result->mailing_name;
        echo $varresults; //this one fails
        //echo 'This works'; 

      } else {
        echo 'OOPS we had a problem :(';
      }
  } else {
  // do nothing
  }
  } else {
      echo 'There should be no direct access to this script!';
  }
}
?>

//EDIT //alternate autosuggest query script

<?php
require_once('../Connections/workdata.php');
//select database
mysql_select_db($database_workdata, $workdata);

if(isset($_POST['queryString'])) {
  $queryString = $_POST['queryString'];
  $query = sprintf("SELECT acct, mailing_name FROM pub_addrs WHERE acct = '%s'",$queryString);
  $result = mysql_query($query, $workdata) or die(mysql_error());
  $row_result = mysql_fetch_assoc($result);
  echo $row_result['acct']." ".$row_result['mailing_name'];//this fails
  //echo 'this works';
}
?>

//mysql table test data

-- phpMyAdmin SQL Dump
-- version 3.3.6deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 05, 2010 at 04:12 PM
-- Server version: 5.1.49
-- PHP Version: 5.3.2-2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `workdata`
--

-- --------------------------------------------------------

--
-- Table structure for table `pub_addrs`
--

CREATE TABLE IF NOT EXISTS `pub_addrs` (
  `acct` int(11) NOT NULL,
  `mailing_name` varchar(50) NOT NULL,
  `special_payee` int(11) NOT NULL,
  `pub_vendor` varchar(1) NOT NULL,
  `longaddress` varchar(20) NOT NULL,
  `alpha_name` varchar(40) NOT NULL,
  PRIMARY KEY (`acct`),
  KEY `longaddress` (`longaddress`),
  KEY `special_payee` (`special_payee`),
  KEY `alpha_name` (`alpha_name`),
  FULLTEXT KEY `mailing_name` (`mailing_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `pub_addrs`
--

INSERT INTO `pub_addrs` (`acct`, `mailing_name`, `special_payee`, `pub_vendor`, `longaddress`, `alpha_name`) VALUES
(701, 'DO NOT MAIL- Deleted Account ', 701, 'C', 'C24250 ', '**** DUPLICATE SET-UP **** '),
(702, 'DO NOT MAIL- Deleted Account ', 702, 'C', 'C24603 ', '**** DUPLICATE SET-UP **** ');

Answer 1

Hi there, I will get round to answering this one, once my development server has finished updating itself. However, I would try an alternate PHP script to the one you list.

<?php
require_once('../Connections/workdata.php');
//select database
mysql_select_db($database_workdata, $workdata);

if(isset($_POST['queryString'])) {
  $queryString = $_POST['queryString'];
  $query = sprintf("SELECT acct, mailing_name FROM pub_addrs WHERE acct = '%s'",$queryString);
  $result = mysql_query($query, $workdata) or die(mysql_error());
  $row_result = mysql_fetch_assoc($result);
  echo $row_result['acct']." ".$row_result['mailing_name'];//this fails
  //echo 'this works';
}
?>

If echo'ing 'this works', does actually work as expected then the problem exists in this portion of code.

<?php

        // Include the auxiallary files as required
        require_once('../../valentxt3.class.php');

        $testVar = 701;

        // Establish db connection.
        $DBase = new dbConn();
        $tmpStr = "SELECT `acct`, `mailing_name` FROM `pub_addrs` WHERE `acct` = '".$testVar."';";
        $result = $DBase->runQuery($tmpStr);

        // Lets see how many texts there are to send.
        $howMany = mysql_num_rows($result);

        $row_result = mysql_fetch_assoc($result);
        echo $row_result['acct']." ".$row_result['mailing_name']; //this fails
        echo 'this works';

        exit;
?>

The code above produces the following output:

php test.php
X-Powered-By: PHP/5.2.13
Content-type: text/html

701 DO NOT MAIL- Deleted Account this works

Obviously, this is ran via a PHP CLI, but should work through a POST or GET request. Once I've got my development server ready, I'll test in more detail.

Answer 2

The answer to the question is to pass the value of 'this' rather than the object 'this' in the form. Firebug reported that I was passing the object of the text field rather than the value.

//firebug reported.

queryString [object HTMLInputElement] Source queryString=%5Bobject+HTMLInputElement%5D

//the fix is to use 'this.value'

<input type="text" size="25" value="" name="Facctnmb" id="Facctnmb" onChange="AcctNmb(this.value)" AUTOCOMPLETE=OFF />

Answer 3

I dont read the question.

I will try to read it on weekend. :)

Try this

http://www.devbridge.com/projects/autocomplete/jquery/