oracle765 Posted February 13, 2014 Share Posted February 13, 2014 <?php session_start();?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>compare</title> <link href="css/style.css" rel="stylesheet" type="text/css" /> <link href="css/hotelsGadget2013.css" rel="stylesheet" type="text/css" /> <link href="css/product.css" rel="stylesheet" type="text/css" /> </head> <body> <section> <div class="ac_results" style="width: 680px !important;"> <ul style="max-height: 405px; overflow: auto;"> <?php include('config.php'); if($_POST) { $q=$_POST['searchword']; /***************************************************************************************************************/ /* image url = link to image...brand=Apple,model_number = iphone 3g 8gb but could be Iphone%%3gb 8GB, price=only show the highest price in ajax dropdown /* is this query correct as i know there are definately two rows in the database with values of /* http://fonebank.com/blablabla.jpg, Apple, Iphone%3G%8gb, $14.00 /* http://mfyp.com/blablabla.jpg, Apple, iphone%%3G%8GB, $12.00 /***************************************************************************************************************/ $sql_res=mysql_query("select distinct image_url,brand,model_number,max(price) from fonehub where concat (brand, ' ',model_number) = upper(rtrim(ltrim('$q'))) order by concat(brand,' ',model_number)"); while($row=mysql_fetch_array($sql_res)) { $info=$row['model_number']; $price=$row['price']; $img=$row['image_url']; $re_info='<b>'.$q.'</b>'; $final_info = str_ireplace($q, $re_info, $info); ?> <li class="ac_even"> <div class="hoverd"> <span class="ref" style="display: none;">http://www.sellmymobile.com/phone/apple-iphone-5s-32gb/</span><a href="product_detail.php?product_name=<?php echo $info;?>" rel="nofollow"><img src="<?php echo $price;?>" height="50" width="53" class="phone"></a><div class="details"><h5> <a href="product_detail.php?product_name= <?php //$_SESSION['as']= $row['uid']; echo $info;?>" rel="nofollow"> <?php echo $final_info; ?></a></h5></div><h5 class="price"><a href="" rel="nofollow"><span style="color: black;">Get up to</span> <?php echo "$" .$price; ?>0</a></h5></div></li> <?php } } else { } ?> </ul></div> </section> </body> </html> Hi All I am trying to get a list of values from the database that are obviously unique when the user searches for a mobile phone. I have two companies in there so I know that when a user types for instance for an Apple iphone the query should find this. The database was extracting some values initially but was running very very slow and would take about ten minutes too update, but now I am getting no results since I have had a play around with it the brand is Apple within the database the model_number could have 1 or two spaces at the end, beginning and could also be a mixture of upper and lower case letters hence the upper rtrim ltrim commands EG %iphone%%3g%8gb or iphone%3G%8GB Because someone initially wrote the code, please find this attached for clarify sake Thanks in advance all Quote Link to comment Share on other sites More sharing options...
davidannis Posted February 13, 2014 Share Posted February 13, 2014 Don't see how this uses AJAX. Not sure why it is not returning results. Could you post some data from the database that you expect to be returned? Also, sanitize data that comes from a user's browser to keep hackers from taking over your site. use mysql_real_escape_string at the very least. Quote Link to comment Share on other sites More sharing options...
oracle765 Posted February 13, 2014 Author Share Posted February 13, 2014 product_name model_number image_url brand price Apple iphone 3G 8GB iphone 3G 8GB http://fonehub.co.uk/Pictures/Product/1/Large/Imag... Apple 12.0 Apple iPhone 3G 8GB iPhone 3G 8GB http://moneyforyourphone.com/productimage/mobile-p... Apple 14.0 Hi David Data as requested Quote Link to comment Share on other sites More sharing options...
davidannis Posted February 14, 2014 Share Posted February 14, 2014 I don't see the problem. Do you have the original code that worked (albeit slowly)? Why does this posted example EG %iphone%%3g%8gb have two spaces in the middle. Are you using % signs just in the post so that we can see spaces or are they in the data? One idea would be to try using a full text search which would take care of sapces http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html Quote Link to comment Share on other sites More sharing options...
oracle765 Posted February 14, 2014 Author Share Posted February 14, 2014 Hi David I am not so sure as someone else has been writing this for me but they can seem to get it right, if you type in iphone we get an iphone 3g 8GB but it does not take care of the space you mentioned. But sometimes when you type in C7 for a Nokia C7 it returns things like 02 which is baffling. I think they are only searching on model_number. its a bit complex for me....... What I have done for clarity sake is included the two php files its using. you can see what I mean on compareandchoose.com.au recyclers.php product_detail.php Quote Link to comment Share on other sites More sharing options...
davidannis Posted February 14, 2014 Share Posted February 14, 2014 It looks to me like it is working on the live site, but only using the model number not the brand. You need to use both and with an or condition, not concatenated. So if I type Apple I get all apple phones and if I type iphone I get all iphones. I'm guessing you are trying to add brand to the search. I'm guessing the speed issue is because brand is not indexed. I think that your where clause needs to be WHERE brand LIKE %$q% OR model_number LIKE %q% OR concat (brand, ' ',model_number) like '%$q%' Make sure that you define indexes on barnd, model_number, and create an index that has both. A lot of guesses in my answer. Sorry if it doesn't work. Quote Link to comment Share on other sites More sharing options...
oracle765 Posted February 15, 2014 Author Share Posted February 15, 2014 hey david thanks for this you are a legend I have limited knowledge of MySQL, how do you create an index on the brand and model_number when brand has multiple brands under iphone do they not have to be unique or am I misunderstanding indexes Quote Link to comment Share on other sites More sharing options...
davidannis Posted February 15, 2014 Share Posted February 15, 2014 Indexes do not need to be unique, though you can require that one be unique. http://www.tutorialspoint.com/mysql/mysql-indexes.htm or if you have phpMyAdmin (which I highly recommend) http://www.siteground.com/tutorials/phpmyadmin/phpmyadmin_database_management.htm Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.