Trouble with pulling data from database using an Ajax "Suggest" function...

[justinmyoung]

Thanks to the help of many on this forum I've been able to put together a script that allows me to do some basic CMS functions, like pulling data from the MYSQL database, displaying it in rows/tables, etc.

Now I have tried to extend the functionality of my search by adding some javascript on an ajax framework, but I don't know how to pull the data from the database using this code. I keep getting an error, and I don't know the solution. I have no trouble pulling the data without the javascript, but with it, I'm running into an error.

Here's the code for the search page:

<div id="search-wrap">
<h1>Search with Auto Suggest</h1>

<input name="search-q" id="search-q" type="text" onkeyup="javascript:autosuggest()"/>

<div id="results"></div>
</div>

Here's the code for the search.php

<?php 
	
        include('config.php'); 
	$SQL_FROM = 'runners';
	$SQL_WHERE = 'last_name';

            ?>

<?php
	$searchq		=	strip_tags($_GET['q']);
	$getRecord_sql	=	'SELECT * FROM '.$SQL_FROM.' WHERE '.$SQL_WHERE.' LIKE "'.$searchq.'%"';
	$getRecord		=	mysql_query($getRecord_sql);
	if(strlen($searchq)>0){
	
	echo '<ul>';
	while ($row = mysql_fetch_array($getRecord)) {?>
		<li><a href="runners2.php"><?php echo $row['last_name']; ?> <small><?php echo $row['first_name']; ?></small></a></li>
	<?php } 
	echo '</ul>';
	?>
<?php } ?>

With the above code, I connect to my database, and when I type in a last name, all the similar last names in my database appear below the search form. But when I go to click the particular name, I get an error. The code for the page I'm trying to pull (runners2.php) looks like this:

<?php
// Include connection to your database
$con = mysql_connect("localhost","root","rilke123");
mysql_select_db("RUNNERS", $con) or die(mysql_error());

$name = $_POST['last_name']; 
$query = "SELECT * FROM runners WHERE last_name ='".$name."'"; 
$result = mysql_query($query) or die(mysql_error());



echo "<table class='sortable'>";
while ($list = mysql_fetch_assoc($result)) {
echo "<thead>";
echo "<tr>";
echo "<th>ID #</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>1 Mile</th>";
echo "<th>2 Mile</th>";
echo "<th>5k</th>";
echo "<th>10k</th>";
echo "<th>15k</th>";
echo "<th>20k</th>";
echo "<th>13.1</th>";
echo "<th>26.2</th>";
echo "</thead>";

echo "<tbody>";
echo "<tr>";
echo "<th>" . $list['id'] . "</th>";
echo "<th>" . $list['first_name'] . "</th>";
echo "<th>" . $list['last_name'] . "</th>";
echo "<th>" . $list['one'] . "</th>";
echo "<th>" . $list['two'] . "</th>";
echo "<th>" . $list['five'] . "</th>";
echo "<th>" . $list['ten'] . "</th>";
echo "<th>" . $list['fifteen'] . "</th>";
echo "<th>" . $list['twenty'] . "</th>";
echo "<th>" . $list['half'] . "</th>";
echo "<th>" . $list['full'] . "</th>";
echo "</tr>";


echo "</tbody>";
}
?>

The error I'm getting is on line 31 of this page, which is: $name = $_POST['last_name']; and the error says: Notice: Undefined index: last_name in /Users/laurenyoung/Sites/adr/runners2.php on line 31

I don't understand. As always, thanks for any suggestions.

[justinmyoung]

I should clarify that when the drop down list of people with similar last names comes down, I need to be able to click on a particular name, and return person's particular information from the database. The code I have now allows me to return all the people with similar last names. I need to pull just a single person's information. I think this is beyond my scope. Thanks again...

[mcd]

Where to start...

I assume that you mean this

<a href="runners2.php"><?php echo $row['last_name']; ?> <small><?php echo $row['first_name']; ?></small></a>

As the link you are clicking on? All that will do is try to run the page runners2.php. But you didn't pass any data to your script. This is not an error:

Notice: Undefined index: last_name in /Users/laurenyoung/Sites/adr/runners2.php on line 31

It is a notice that PHP gives you, letting you know that it really has no idea what you're trying to do with that value. Not exactly, but you get the idea. You're trying to read in $name from a POST value - not only are you not posting to the page at all, but you didn't pass anything (including last_name) to the page.

Change:

<a href="runners2.php?last_name=<?php echo urlencode($row['last_name']); ?>"><?php echo $row['last_name']; ?> <small><?php echo $row['first_name']; ?></small></a>

and

$name = $_GET['last_name']; 

note: urlencode simply prepares your data to be passed in a URL.

[justinmyoung]

Thanks. That makes sense. That alteration allows me to pull data of all the people with the same last name; how do you suggest I just allow data from one person in particular, say John Smith, instead of Joe Smith, Nathan Smith, etc. When the names drop down in a menu, how do I return data for a specific name?

Thanks again

[mcd]

Well, you could always use the db primary key to access a specific record. This is the most common way.

But if you want to keep with using the names, use the fullname as such:

Change the link:

<a href="runners2.php?name=<?php echo urlencode($row['first_name'].' '.$row['last_name']); ?>"><?php echo $row['last_name']; ?> <small><?php echo $row['first_name']; ?></small></a>

and the receiving script:

$name = $_GET['name'];

"SELECT * FROM runners WHERE CONCAT(first_name,' ',last_name) LIKE ='%$name%'";

Or course there are variations on how you could address the whole name: last,first for example.

[justinmyoung]

After changing the receiving page (runners2.php), I get a message telling me the query is empty? Not sure why...

Here's the way it looks:

$name = $_GET['name']; 

"SELECT * FROM runners WHERE CONCAT(last_name,' ',first_name) LIKE ='%$name%'";

$result = mysql_query($query) or die(mysql_error()); 

echo "<table class='sortable'>";
while ($list = mysql_fetch_assoc($result)) {
echo "<thead>";
echo "<tr>";
echo "<th>ID #</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>1 Mile</th>";
echo "<th>2 Mile</th>";
echo "<th>5k</th>";
echo "<th>10k</th>";
echo "<th>15k</th>";
echo "<th>20k</th>";
echo "<th>13.1</th>";
echo "<th>26.2</th>";
echo "</thead>";

echo "<tbody>";
echo "<tr>";
echo "<th>" . $list['id'] . "</th>";
echo "<th>" . $list['first_name'] . "</th>";
echo "<th>" . $list['last_name'] . "</th>";
echo "<th>" . $list['one'] . "</th>";
echo "<th>" . $list['two'] . "</th>";
echo "<th>" . $list['five'] . "</th>";
echo "<th>" . $list['ten'] . "</th>";
echo "<th>" . $list['fifteen'] . "</th>";
echo "<th>" . $list['twenty'] . "</th>";
echo "<th>" . $list['half'] . "</th>";
echo "<th>" . $list['full'] . "</th>";
echo "</tr>";


echo "</tbody>";
}
?>

[justinmyoung]

I changed the query to this $query = "SELECT * FROM runners WHERE CONCAT(first_name,' ',last_name) = '$name'"; and it worked great. If I wanted to make the query more complex, like identify the user by his/her age and city/state, would I simply add (first_name,' ', last_name,' ', city, ' ', age) etc?

thanks again

[mcd]

No

SELECT * FROM runners WHERE CONCAT(first_name,' ',last_name) = '$name' AND city='$city' AND age='$age'

etc.

CONCAT stands for concatenation: the joining of two string together, which makes sense with a first and last name. However, Bob JohnsonNew York23 isn't really hat you want to pass, and anyway, the age field is probably numeric.

[justinmyoung]

When I added those variables, I was prompted that those variables were undefined. So, I tried to define them like this, but it didn't seem to work.

$name = $_GET['name'];
$city = 'city';
$age = 'age';

$query = "SELECT * FROM runners WHERE CONCAT(first_name,' ',last_name) = '$name' AND city='$city' AND age='$age'";

Thanks for continuing to look at my issues...

[mcd]

I think I'm missing some key information. This:

$name = $_GET['name'];
$city = 'city';
$age = 'age';

...has defined your variables. Only the first on is capturing the data you sent from the previous page, and furthermore, I can only assume that you're assigning values of city, and age, for testing purposes?

The query would run (and the PHP for that matter) with the script you described, and here is the query you just built:

$query = "SELECT * FROM runners WHERE CONCAT(first_name,' ',last_name) = 'Ben Johnson' AND city='city' AND age='age'";

Pretty useless, eh?

Honestly, at this point (and I'm not trying to be a dick, I'm just trying to help, you probably want to check out the PHP tutorial at w3 schools. They're the best introduction to a number of technologies. You have a grasp of many things, but for what you're trying to do it would help you immensely to start at the beginning with some other technologies.

Trust me, it's fun and it will benefit you in the long run.

PM me if you need direct links.

[justinmyoung]

No, I understand. I'm just learning. I've looked at W3 and will continue to do so. Honestly, so far I've learned more from these forums than from anywhere else. Sorry to ask silly questions so frequently. Nevertheless, I do appreciate your (and others) help and advice.

[mcd]

If you keep asking, I (and others) will keep answering. Fun to learn this stuff and fun to help learn. Most everyone still has a lot to learn, including most of all, me.