Auto populate and Post with mysql

[shawn10642]

original sql

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

-- 
-- Table structure for table `countries`
-- 

CREATE TABLE `countries` (
  `id` int(6) NOT NULL auto_increment,
  `value` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=243 ;

-- 
-- Dumping data for table `countries`
-- 

INSERT INTO `countries` VALUES (1, 'Vancouver');

 

New Sql

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

-- 
-- Table structure for table `countries`
-- 

CREATE TABLE `countries` (
  `id` int(6) NOT NULL auto_increment,
  `value` varchar(250) NOT NULL default '',
  `code` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=243 ;

-- 
-- Dumping data for table `countries`
-- 

INSERT INTO `countries` VALUES (1, 'Vancouver', 'BC-50');

 

PHP code for the job

<?php

// PHP5 Implementation - uses MySQLi.
// mysqli('localhost', 'yourUsername', 'yourPassword', 'yourDatabase');
$db = new mysqli('localhost', 'root' ,'password', 'weather');

if(!$db) {
	// Show error if we cannot connect.
	echo 'ERROR: Could not connect to the database.';
} else {
	// Is there a posted query string?
	if(isset($_POST['queryString'])) {
		$queryString = $db->real_escape_string($_POST['queryString']);

		// Is the string length greater than 0?

		if(strlen($queryString) >0) {
			// Run the query: We use LIKE '$queryString%'
			// The percentage sign is a wild-card, in my example of countries it works like this...
			// $queryString = 'Uni';
			// Returned data = 'United States, United Kindom';

			// YOU NEED TO ALTER THE QUERY TO MATCH YOUR DATABASE.
			// eg: SELECT yourColumnName FROM yourTable WHERE yourColumnName LIKE '$queryString%' LIMIT 10

			$query = $db->query("SELECT your_column FROM your_db_table WHERE your_column LIKE '$queryString%' LIMIT 10");
			if($query) {
				// While there are results loop through them - fetching an Object (i like PHP5 btw!).
				while ($result = $query ->fetch_object()) {
					// Format the results, im using <li> for the list, you can change it.
					// The onClick function fills the textbox with the result.

					// YOU MUST CHANGE: $result->value to $result->your_colum
         			echo '<li onClick="fill(\''.$result->value.'\');">'.$result->value.'</li>';
         		}
			} else {
				echo 'ERROR: There was a problem with the query.';
			}
		} else {
			// Dont do anything.
		} // There is a queryString.
	} else {
		echo 'There should be no direct access to this script!';
	}
}
?>

 

What the original code does

1) you start typing your city (eg. Van)

2) when you type the first letter (eg. V), it looks into mysql and auto fills a dropdown menu with all possible cities

 

What i need it to do

1) you start typing your city (eg. Van)

2) when you type the first letter (eg. V), it looks into mysql and auto fills a dropdown menu with all possible cities

3) when you find your city you click it or press enter, and it POST's the city code as well

 

now how do i munipulate the script to do that...

 

another thing, when i put the extra sql entry in "code", the auto fill stopped working, why?

 

Thanks

[fugix]

if you want to grab the value of what a user is typing in, while they are typing it in...you will most likely want to use AJAX. unless you are talking about after the input is submitted? after adding the third field...no error occur, the script simply does not work?

[shawn10642]

The script is in ajax, but what i need it to do is post the 'code' so my other script can pick it up and process the city code

[shawn10642]

unless there's an easier way of doing so,

i need it to atleast auto populate the form based on the mysql below

and to post the city_code

 

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

-- 
-- Table structure for table `cities`
-- 

CREATE TABLE `countries` (
  `id` int(6) NOT NULL auto_increment,
  `city_name` varchar(250) NOT NULL default '',
  `city_code` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=243 ;

-- 
-- Dumping data for table `cities`
-- 

INSERT INTO `countries` VALUES (1, 'Vancouver', 'BC-50');

[xyph]

This will be JavaScript.

 

Have your AJAX call return both the city_name, and the city_code, split by a delimiter.

 

Split the delimiter in JavaScript, and have the dropdown options populated with value=city_code, innerHTML=city_name

 

When you submit, $_POST['dropdown'] should have the city_code defined.

[shawn10642]

so the scrpit works now :)

one more thing if

 

echo $_POST["cityd"];

posts city code

 

how do i echo the city itself?

 

 

// form 
echo "<form method='POST' action =''>";
echo "<select name='cityd'>";
while($row = mysql_fetch_array($res))
{
echo "<option value='{$row['city_code']}'>{$row['city_name']}</option>";
}
echo "</select>";
echo "<input type='submit' name='weaCode' value='Submit'/>";
echo "</form>";
echo $_POST["cityd"];
?>

 

[fugix]

echo $row['city_name'];  ?

[shawn10642]

echo $row['city_name'];  ?

 

Nope wish it was that easy

already tried that.

[fugix]

what do you receive when you echo $row['city_name']?

[shawn10642]

blank, nothing 

[shawn10642]

i've tried

 

echo $_POST["city_name"]; 
echo $_POST["city_code"];
echo $_POST["$res"];
echo $_POST["$row"];
echo $row['city_name'];
echo $row['city_code'];
echo $res['city_name'];
echo $res['city_code'];

[fugix]

its blank because you are trying to echo it outside of your while loop ....try

echo "<form method='POST' action =''>";
echo "<select name='cityd'>";
while($row = mysql_fetch_array($res))
{
echo "<option value='{$row['city_code']}'>{$row['city_name']}</option>";
}
echo "</select>";
echo "<input type='submit' name='weaCode' value='Submit'/>";
echo "</form>";
echo $_POST["cityd"];
while($row = mysql_fetch_array($res))
{
echo $row['city_name'];
}
?>

[shawn10642]

Nope still not working i also tried putting  echo $row['city_name']; before the While close tag  }

 

how would i go POST the city_name when pushing submit, if it's even possible?

 

its blank because you are trying to echo it outside of your while loop ....try

echo "<form method='POST' action =''>";
echo "<select name='cityd'>";
while($row = mysql_fetch_array($res))
{
echo "<option value='{$row['city_code']}'>{$row['city_name']}</option>";
}
echo "</select>";
echo "<input type='submit' name='weaCode' value='Submit'/>";
echo "</form>";
echo $_POST["cityd"];
while($row = mysql_fetch_array($res))
{
echo $row['city_name'];
}
?>

[fugix]

if nothing works than how is your option tag being populated with your city_name?

echo "<option value='{$row['city_code']}'>{$row['city_name']}</option>";

how would i go POST the city_name when pushing submit, if it's even possible?

what exactly are you trying to ask?

[shawn10642]

Basically whats happening, there's one dropdown form with a submit button on the side.

echo "<option value='{$row['city_code']}'>{$row['city_name']}</option>";

code above

city_code is the value/$_POST, and city_name is the visible value in the dropdown form

echo "<form method='POST' action =''>";
echo "<input type='submit' name='weaCode' value='Submit'/>";
echo "</form>";

Code about, posts the selection as $_POST $city

there a switch put it as $_POST $cityd for my other script can process the city_code

 

Rundown

 

MYSql --->city_name ---->city_code = form selection--->city_name--->city_code---->$city---->$cityd ---->to weather script

 

What i need aswell is

 

MYSql --->city_name ---->city_code = form selection--->city_name---> Echo

 

 

Hope i makes sence                                          

 

if nothing works than how is your option tag being populated with your city_name?

echo "<option value='{$row['city_code']}'>{$row['city_name']}</option>";

how would i go POST the city_name when pushing submit, if it's even possible?

what exactly are you trying to ask?

[fugix]

but what im asking is...if you arent able to receive a value when you echo $row['city_name'], then how is it that $row['city_name'] has a value in your option tag..

[shawn10642]

but what im asking is...if you arent able to receive a value when you echo $row['city_name'], then how is it that $row['city_name'] has a value in your option tag..

 

Great question, it may be a one time grab...if that makes sence, but it's wierd i can echo $_POST['city_code']...?!?!?