• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

need equivalent code in php for the following cfm

I followed the link http://www.petefreitag.com/item/605.cfm to add autofill feature to my website.
They had used the cfm files for getting the data from database. But I want to get the databse from php.

my form page code is like this

 <input type="text" name="occupation1" id="occupation1"/>
  <div id="suggestionBox"></div>

<script type="text/javascript" language="javascript">
  var myAutoCompleter = new Ajax.Autocompleter('occupation1', 'suggestionBox', 'suggest.cfm', {});
</script>

and its suggest.cfm code is like this.

<cfparam name="form.occupation1" default="" type="string">
<cfquery datasource="dsn" name="suggestions">
  SELECT occupation
  FROM contacts
  WHERE occupation LIKE
</cfquery>
<ol>
<cfoutput query="suggestions">
  <li>#suggestions.occupationl#</li>
</cfoutput>
</ol>


I need its equivalent code in php and mysql. Help me!!!





 
0
sriramvemaraju2000
Asked:
sriramvemaraju2000
  • 2
1 Solution
 
maeltarCommented:
Plenty of tuto's out thre (Also a lot of junk)

I would suggest that you go the jQuery route...

Code
http://papermashup.com/jquery-php-ajax-autosuggest/

Demo
http://papermashup.com/demos/autosuggest/

0
 
Ray PaseurCommented:
This is obviously untested code, but it is probably fairly close to the PHP and MySQL equivalent of the ColdFusion script.  It assumes that the AJAX Autocompleter will make a GET method request to the background script, passing along as many characters of the "occupation1" input as the client has typed.  We escape this for safety in the query, and add a percent sign to allow wild-card matching on the trailing characters (line 65).  We create the query (line 72), then run it and test for success.  If the query finds any matches (line 87) we create an HTML <ol> structure with one <li> line for each of the matches (lines 90-100).  We return this via standard output.

There are some man page references near the top. Good reading if you're going to be doing anything with PHP and MySQL!

HTH, ~Ray
<?php // RAY_temp_srira.php
error_reporting(E_ALL);


/* INFORMATION ABOUT COLDFUSION FROM THE POST AT EE

<cfparam name="form.occupation1" default="" type="string">
<cfquery datasource="dsn" name="suggestions">
  SELECT occupation
  FROM contacts
  WHERE occupation LIKE
</cfquery>
<ol>
<cfoutput query="suggestions">
  <li>#suggestions.occupationl#</li>
</cfoutput>
</ol>

*/


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES


// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_str = mysql_real_escape_string($_GET["occupation1"]);

// ADD A PERCENT SIGN FOR WILD-CARD MATCHING
$safe_str .= '%'


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT occupation FROM contacts WHERE occupation LIKE '$safe_str'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS


// IF WE GOT A RESULTS SET WITH DATA
if (mysql_num_rows($res))
{
    // CREATE THE OUTPUT HTML STRING
    $out = '<ol>' . PHP_EOL;

    // ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
    while ($row = mysql_fetch_assoc($res))
    {
        $out .= '<li>'
        $out .= htmlentities($row["occupation"]);
        $out .= '</li>' . PHP_EOL;
    }

    $out .= '</ol>' . PHP_EOL;

    // SEND THE OUTPUT STRING
    echo $out;
}

Open in new window

0
 
sriramvemaraju2000Author Commented:
Thanks it worked with some changes....

This one helped me in figuring some of the problems...
http://www.wiseguysonly.com/2008/03/09/autocompletion-with-scriptaculous-and-ajax/

my final code is like this below. It uses $_POST

1

<?php // RAY_temp_srira.php
error_reporting(E_ALL);
session_start();

include("includes/connect.php");

/* INFORMATION ABOUT COLDFUSION FROM THE POST AT EE

<cfparam name="form.occupation1" default="" type="string">
<cfquery datasource="dsn" name="suggestions">
  SELECT occupation
  FROM contacts
  WHERE occupation LIKE
</cfquery>
<ol>
<cfoutput query="suggestions">
  <li>#suggestions.occupationl#</li>
</cfoutput>
</ol>

*/


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php


// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_str = mysql_real_escape_string($_POST["statename"]);

// ADD A PERCENT SIGN FOR WILD-CARD MATCHING
$safe_str .= '%';
$safe_str = '%'.$safe_str;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT statename FROM state WHERE statename LIKE '$safe_str' LIMIT 15";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS


// IF WE GOT A RESULTS SET WITH DATA
if (mysql_num_rows($res))
{
    // CREATE THE OUTPUT HTML STRING
    $out = '<ul>' . PHP_EOL;

    // ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
    while ($row = mysql_fetch_assoc($res))
    {
        $out .= '<li>';
        $out .= htmlentities($row["statename"]);
        $out .= '</li>' . PHP_EOL;
    }

    $out .= '</ul>' . PHP_EOL;

    // SEND THE OUTPUT STRING
    echo $out;
}
0
 
Ray PaseurCommented:
Thanks for the points! ~Ray
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now