Adding LIVE ajax searching to search box

Hi, i am trying to add live ajax searching to the search box... i have succeeded in doing it without using zens functions. but, then my output on certain character rendering doesnt work. so i would like to use the built in zen functionality since it is displaying these characters correctly in other parts of the site.

some background...
i am using scriptaculous / prototype for the js library. this is loaded and functioning. in my search form i added:
$content .= '<div id="autocomplete_choices" class="autocomplete"></div>
<script>
new Ajax.Autocompleter("autocomplete","autocomplete_choices","search/ajaxsearch.php",{paramName: "search"});
</script>';
after the input tag.

i loaded the js files into template/mytemplate/jscript and put references in my header.

the param above search/ajaxsearch.php calls the query on the database. following is the code: the first set commented is the code that works (but returns weird characters on letters with accents) the second code block doesnt return anything on the query.

<?php /* mysql_connect('localhost', 'user', 'password'); mysql_select_db("mydatabasename"); if(!isset($_REQUEST["search"])) exit; $search=mysql_real_escape_string(trim($_REQUEST["search"])); $result = mysql_query("SELECT products_name, products_description.products_id, products.products_id, products_status FROM products_description, products WHERE products_description.products_id = products.products_id AND products_status=1 AND products_name LIKE '%$search%';"); if(mysql_num_rows($result)) { echo "<ul>"; while($r=mysql_fetch_assoc($result)) { $title = $r["products_name"]; echo "<li>$title</li>"; } echo "</ul>"; } */ ?>

//////NEW BLOCK HERE///////

<?php require('includes/application_top.php'); $content = ''; if(!isset($_REQUEST["search"])) exit; $search=mysql_real_escape_string(trim($_REQUEST["search"])); $product_query = mysql_query("SELECT a.products_id, b.products_id, b.products_name FROM products a, products_description b where a.products_id = b.products_id order by b.products_name AND products_name LIKE '%$search%';"); $product_result = $db->Execute($product_query); $content .= "<ul>"; while (!$product_result->EOF) { $content .= '<li>'. $product_result->fields['products_name'] . '</li>'; $product_result->MoveNext(); }; $content .= "</ul>"; ?> <?php echo $content; ?>

any help would be really really appreciated. i have gotten really far on this and i think a lot of people would like this adaptation. oh and this ajaxsearch.php file is in a subfolder search from the root. not sure if that is the issue or not...

Minnie

Do your tables have prefix?

no they dont..

Did you try to catch the returned result and see what is returned?

nothing is returned, which is why i think i am not connecting to the database. The query is good. i have tested it in a module sidebox.

Minnie Mouse:

nothing is returned, which is why i think i am not connecting to the database. The query is good. i have tested it in a module sidebox.
Pretty weird, did you try to access this file directly? What do you get then? (You can edit the code to put a test string for $search.)
If you get a blank page, use this https://www.zen-cart.com/tutorials/index.php?article=82

thanks, i did that and the script didnt write errors. (maybe because application_top is not connecting??). sigh. i think the include for application_top is not working.

Where are you placing the script? Are you sure the script can access the path to application_top that you used?

change include to require, if it cant get the file you will get a fatal error right away.

Otherwise, it's possible that the sql search doesnt return anything? place several "echo" at various point to see how far the script gets to.

okay gotta go now, i will try all that tomorrow!!

thanks!!
MM

okay, i have tried everything i can think of. i downloaded like 10 different modules to see how they worked. i can get a sidebox mod to list all the products no problem. just cant get it to load when i do it for the search. it seems to be in the sql call, but i cant figure out why since if i do the same call outside of zen it works. this was my last iteration of the ajaxsearch.php code. which returns no error and no content.

<?php chdir('../'); require('includes/application_top.php'); if(!isset($_REQUEST["search"])) exit; $search=mysql_real_escape_string(trim($_REQUEST["search"])); $product_query = "SELECT a.products_id, b.products_id, b.products_name FROM products a, products_description b WHERE a.products_id = b.products_id AND products_name LIKE '%$search%'"; $product_result = $db->Execute($product_query); echo $product_result; //echo "helloworld"; echo "<ul>"; while(!$product_result->EOF) { echo '<li>'. $product_result->fields['products_name'] . '</li>'; $product_result->MoveNext(); }; echo "</ul>"; ?>

okay i think i know the problem, i just dont know the solution yet. the call to the includes/application_top.php is not being processed through because the page is being called by the ajax call?? is that possible? that's why it works if i include the database info in the page, but not otherwise?

Minnie Mouse:

okay i think i know the problem, i just dont know the solution yet. the call to the includes/application_top.php is not being processed through because the page is being called by the ajax call?? is that possible? that's why it works if i include the database info in the page, but not otherwise?
If so, it's probably the include doesnt work for some reason.

Thanks so much for your help. i finally got my own script working (bypassing zen) by adding a content header.

<?php mysql_connect('localhost', 'user', 'pass'); mysql_select_db("dbname"); header('Content-Type: text/xml; charset=iso-8859-1'); if(!isset($_REQUEST["search"])) exit; $search=mysql_real_escape_string(trim($_REQUEST["search"])); $result = mysql_query("SELECT products_name, products_description.products_id, products.products_id, products_status FROM products_description, products WHERE products_description.products_id = products.products_id AND products_status=1 AND products_name LIKE '%$search%';"); if(mysql_num_rows($result)) { echo "<ul>"; while($r=mysql_fetch_assoc($result)) { $title = $r["products_name"]; echo "<li>$title</li>"; } echo "</ul>"; } ?>

Note that this script will put some performance penalty on your server as well.

Further improvement such as caching is recommended.

Also, you should limit the number of results return by putting LIMIT in the query string as well.

Thanks for the tips, not sure how to do caching yet, but the limit is in the works right now... i am waiting for the client to tell me how many. i also am thinking of changing the search to a fulltext one. would that help the server hit?

okay yellowed... can you point me in the right direction to add caching? is it through the php or ajax? etc..

Minnie Mouse:

okay yellowed... can you point me in the right direction to add caching? is it through the php or ajax? etc..
Here i s what you can do:

1. md5 the search string to get a unique name
2. Check if a file with that name exists
3. If it does, read the file content, return that
4. If not, move on to the query
5. Do not echo out each line, but append all content to a var called $content for example,
6. At the end, write the content to the new file (with the name above)
7. echo the $content
   You may also want to set an expiration time fore the cache files, so that your script will re-run the sql query even if the file exists, given that a certain time has passed. Google for more php cache snippets and such.

Also, your js should not try to get the result from the server too often, usually these scripts allow you to set an interval, do not choose 1 sec, something like 5 sec is better.

Thanks! i must be getting better at this stuff, i understood all that!
last question // the speed is fine in firefox and safari, will caching pickup the speed time in ie, or just the pageload time?
MM

sorry i forgot, here it is in action....
http://www.markethallfoods.com

Minnie Mouse:

Thanks! i must be getting better at this stuff, i understood all that!
last question // the speed is fine in firefox and safari, will caching pickup the speed time in ie, or just the pageload time?
MM
You may not notice any difference now, since you are still in development state, so you are the only user sending requests to the server. But think about this: if the site has a number of customers online and at any given time you may have 50 doing the search so a large number of sql queries will needed to be performed.

Some sites may never have this problem :p, but as a developer you should prepare your script to handle the worst case scenario.

It's almost the same for many forums, during the peak time the search function is turned off completely or else the server will die.

Hi minimouse,..:smile:

i see the script on your page. Nice..
Can i get the full script, please..?:yes: