|
PHP - Mysql Like Search Query - How To Get Most Accurate Row First.
On my search result page, I wanted to search for "Nintendo DS Lite - Pink"
I used following code: Ex: Code: [Select] $search_text = "Nintendo DS Lite Pink"; $kt=split(" ",$search_text);//Breaking the string to array of words // Now let us generate the sql while(list($key,$val)=each($kt)) { if($val<>" " and strlen($val) > 0) { $q .= " name like '%$val%' or "; } }// end of while //Remove the last 'OR' $q=substr($q,0,(strlen($q)-3)); Than the $q would be: Code: [Select] SELECT * FROM `products` WHERE name like '%Nintendo%' or name like '%DS%' or name like '%Lite%' or name like '%Pink%' And i am getting Mysql Output given below: 1) Activity Meter - DS. 2) Nintendo DS Red. 3) Nintendo DS Lite Pink. 4) Nintendo DS Lite Turquoise. But the third result is most accurate/relevant then first two result. Please help me out to get the most accurate row first then the relevant rows as per their relevancy with search term "$search_text" Many Thanks in Advance. Similar Tutorialshey, i'm working on an online dictionary for my native language. i noticed when searching, the results are sometimes inaccurate. this is my query. Code: [Select] SELECT * FROM words WHERE word LIKE '%$q%'" for example, if i search for "tive" (supposing this is a real word) it doesnt return the word tive and will return "native" because it occurs first in the database. so i suppose basically i'm looking for a way to sort them by relevancy? Hello, I can't really explain it so a example will be better: my_table: col1 col2 col3 col4 1 1 23 A 2 1 25 A 3 2 21 B 4 3 24 A 5 3 28 A the query is-- SELECT col3 FROM my_table WHERE col2=SELECT(MAX(col2) FROM my_table WHERE col4='A') For some reason this is illegal, I can also not use a limit because the number of rows is not known a priori. I remember seeing some sort of redirection in some other forum but would appreciate a more explicit response. Thanks This is not legal for some reason -- is there a way Say a user puts in a support request, and for every request it generates a unqiue string, and enters it into the database. Ok, now say there is a text field, when the user enters their unique string and it finds a match, it displays the data along with it. How can I accomplish this? Im kind of new to mysql, but I know basic SQL. Would be great if somebody could point me in the right direction! Thanks I want to have a search product feature, but I would like members to be able to search multiple fields in one go i.e. product_code, Product_name in one MySQL query. The thing is, members have to be logged on, so the query must also only show results relating to that specific member, via the session[member_ID], my current query for listing products for that specific member is : Code: [Select] $sql = "SELECT productId, productCode, image, name, price, stock_level FROM product_inventory WHERE memberr_ID = '" . $_SESSION['SESS_mem_ID'] . "; How would I change the above into a search query to search for productcode, productname and still only show results beloging to this member using the session data ? all help appreciated Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it? Thanks For Your Help Guys! The result pages is supposed to have pagination like google help me please
Hi all How do I modify the below code to search multiple tables in mySQL database? $query = "select * from store_items where description like \"%$trimmed%\" or title like \"%$trimmed%\" or dimensions like \"%$trimmed%\" order by id ASC"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); It is for a search function and I need it to search another table called 'about_text' and 'faq_text' Thanks Pete Here is my code: // Start MySQL Query for Records $query = "SELECT codes_update_no_join_1b" . "SET orig_code_1 = new_code_1, orig_code_2 = new_code_2" . "WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)"; $results = mysql_query($query) or die(mysql_error()); // End MySQL Query for Records This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this??? Code: [Select] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1 If you also have any feedback on my code, please do tell me. I wish to improve my coding base. Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference. Code: Code: [Select] mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)"); Full code: Code: [Select] <?php include_once("includes/config.php"); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title><? $title; ?></title> <meta http-equiv="Content-Language" content="English" /> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <link rel="stylesheet" type="text/css" href="style.css" media="screen" /> </head> <body> <div id="wrap"> <div id="header"> <h1><? $title; ?></h1> <h2><? $description; ?></h2> </div> <? include_once("includes/navigation.php"); ?> <div id="content"> <div id="right"> <h2>Create</h2> <div id="artlicles"> <?php if(!$_SESSION['user']) { $username = mysql_real_escape_string($_POST['username']); $password = mysql_real_escape_string($_POST['password']); $name = mysql_real_escape_string($_POST['name']); $server_type = mysql_real_escape_string($_POST['type']); $description = mysql_real_escape_string($_POST['description']); if(!$username || !$password || !$server_type || !$description || !$name) { echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>"; echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'> <option value='Any'>Any</option> <option value='PvP'>PvP</option> <option value='Creative'>Creative</option> <option value='Survival'>Survival</option> <option value='Roleplay'>RolePlay</option> </select></td></tr> <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>"; echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>"; } elseif(strlen($password) < 8) { echo "Password needs to be higher than 8 characters!"; } elseif(strlen($username) > 13) { echo "Username can't be greater than 13 characters!"; } else { $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1"); if(mysql_num_rows($check1) < 0) { echo "Sorry, there is already an account with this username and/or server name!"; } else { $ip = $_SERVER['REMOTE_ADDR']; mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)"); echo "Server has been succesfully created!"; } } } else { echo "You are currently logged in!"; } ?> </div> </div> <div style="clear: both;"> </div> </div> <div id="footer"> <a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop </div> </div> </body> </html> the php web video script that I'm trying to modify allows Users to purchase videos successfully, however, the video price that is reflected in the db ('u_paid_videos') table (where transaction info is stored) appears to show the default video price (video_play_price from the 'config' db table) every time, instead of the accurate video price. I'm not sure if this code needs to be tweaked so that the actual price will show in the 'video_play_price' column (in 'u_paid_videos' table):
// get cost video // get the default video price, to use if there is no per video play price
$db->where('name', 'video_play_price');
$db_cost = $db->getOne('config');
$video_cost = (float)$db_cost->value;
// the number of submitted videos - used to determine if all records were inserted
$count_video = count($id_array);
$user_id = $user->id;
$wallet = (float)str_replace(',', '', $user->wallet);
$balance = (float)str_replace(',', '', $user->balance);
// add up the video prices
$amount = 0;
foreach ($id_array as $id) {
$video_id = (int)PT_Secure($id);
// get video data
$video = $db->where('id', $id)->getOne(T_VIDEOS);
// add the video play price if any, or the default price
$amount += $video->video_play_price?$video->video_play_price:$video_cost;
}
// determine if the user has enough credits
if( ($wallet >= $amount) OR ($balance + $wallet >= $amount) ) {
$db->startTransaction();
$inserted_records = 0;
foreach ($id_array as $id){
$video_id = (int)PT_Secure($id);
// get video data
$video = $db->where('id', $id)->getOne(T_VIDEOS);
// use the video play price if any, or the default price
$video_cost_new = $video->video_play_price?$video->video_play_price:$video_cost;
$uploader_amount = $video_cost_new *0.50;
// add data to paid table
$insert_buy = $db->insert('u_paid_videos', [
'id_user' => $user_id,
'id_video' => $video_id,
'session_key' => $_SESSION['session_key'],
'video_play_price' => (string)$video_cost,
'video_title' => $video->title,
'user_id_uploaded' => $video->user_id,
'earned_amount' => $uploader_amount
]);
Hi - I have an HTML form where the customer can amend text dates. However, I have noticed that if you for example change 01 Jun 2012 to something like, 01 July 2012, 50% of the occasions, strtotime() will ignore the change and just go back to 01 jun 2012. This is not unique to June, is there anything I can do to make things better ?? Thanks ! I am trying to calculate the date and time difference between two timestamps. I am using this for an auction style website and I thought it was working fine until I woke up this morning and it said that there was only 1 hr left in auction when I actually started a 24 hour auction at like 7pm last night. //trying to find date diff accurately Code: [Select] $now = mktime(date("g"), date("i"), date("s"), date("m") , date("d"), date("Y")); $end = $timestamp; $start = new DateTime(); $start -> setTimestamp($end); $current = new DateTime(); $current -> setTimestamp($now); $interval = date_diff($current, $start, true); $days = $interval ->format('%d'); $hours = $interval ->format('%h'); $minutes = $interval ->format('%i'); $seconds = $interval ->format('%s'); This is how I created the timestamp to store in the database for auction. Code: [Select] //Creates timestamp for when auction ends using auction //length chosen. $timestamp = mktime(date("g"), date("i"), date("s"), date("m") , date("d")+$auction, date("Y")); Hi everyone! So, this is probably one of the most simplest things to all you pros out there, but unfortunately I think I'm using the wrong words in Google as I can't seem to remember what they're called (Yeah I'm not that brilliant.) Anyway, a quick one for you guys. What I'm looking for is a simple script or method to send a Search Query to end with a URL. For example. Lets say I'm on http://mysite.com/index.php, and on that page is a search box. Now, lets say I search the word "Pizza" in that search box. I want the end result to be "http://myspace.com/search.php?s=pizza" I already have the script I'd like to use to read that value, I just simply don't know how to put that value there in the first. Yes, I know, noobish question, I'm sorry. XD Thanks in advance! Hi All. I am looking to search a database of products based upon someone typing the name of the product they are looking for. I am using the below SQL request: Unfortunately this only shows what ever your typing and anything before or after (%) is there any way that it could also search unplaced string instances? For example $q = "SELECT * FROM products WHERE prod_name LIKE '%$id%' order by fee asc LIMIT 20"; If i type black ops ps3 it brings back search result until i get to the PS3 part. because entries in the db are "black ops for the PS3" because i havent conformed it says no results. I need it to show all results. is there another way? or can someone point me in the right direction? This is for a faux stock photo website. When I only use the $query string to search for $searchSubject, everything goes golden. Once I put everything else in there though, the search function will not work at all. Any help will be greatly appreciated. I've been staring at this for too long and it's starting to give me a headache. PS. This is my first post here. Seems like a great community! <?php $searchSubject = $_POST['searchSubject']; $searchPhotoname = $_POST['searchPhotoname']; $searchLocation = $_POST['searchLocation']; if (isset($_POST['search'])) { //Select records from the database $query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%' OR `name` LIKE '%" . $searchPhotoname . "%' OR `location` LIKE '%" . $searchLocation . "%' "; } else { $query = "SELECT * FROM photos"; } $result = mysql_query($query); // count the number of rows in the database $num = mysql_num_rows($result); ?> If I leave it at this: $query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%'"; Then everything works perfect. Hi, i have the following problem. I use a script to enter data in a mysql database and for now i only have three variable... Quote <h2>Invoeren</h2> <form action="insert.php" method="post" class="cmxform"> <fieldset> <legend>Gegevens</legend> <table border="2"> <tr> <td>1. </td> <td><label for="merk">merk:</label></td> <td><input id="merk" name="merk" /></td> </tr> <tr> <td>2. </td> <td><label for="cpu">cpu:</label></td> <td><select name="cpu" id="cpu" /> <option value="">Maakt niet uit</option> <option value="amd">amd</option> <option value="intel">intel</option> </select></td> </tr> <tr> <td>3. </td> <td><label for="hdd">hdd:</label></td> <td><select name="hdd" id="hdd" /> <option value="">Maakt niet uit</option> <option value="160GB">160GB</option> <option value="320GB">320GB</option> <option value="500GB">500GB</option> </select></td> </tr> </table> <input type="submit" value="Verstuur" /> </fieldset> </form> Data goes in to the database correctly but now i use the following search script... Quote <h2>zoeken</h2> <form action="search.php" method="post" class="cmxform"> <fieldset> <legend>Gegevens</legend> <table border="2"> <tr> <td>1. </td> <td><label for="merk">merk:</label></td> <td><input id="merk" name="merk" /></td> </tr> <tr> <td>2. </td> <td><label for="cpu">cpu:</label></td> <td><select name="cpu" id="cpu" /> <option value="0">Maakt niet uit</option> <option value="amd">amd</option> <option value="intel">intel</option> </select></td> </tr> <tr> <td>3. </td> <td><label for="hdd">hdd:</label></td> <td><select name="hdd" id="hdd" /> <option value="0">Maakt niet uit</option> <option value="160GB">160GB</option> <option value="320GB">320GB</option> <option value="500GB">500GB</option> </select></td> </tr> </table> <input type="submit" value="Verstuur" /> </fieldset> </form> The values go correct to the search script because i checked this with an echo function. The search.php loks like this Quote <?php $con = mysql_connect("localhost","admin","t1tan1um"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("model", $con); $qry = "SELECT * FROM eigenschappen WHERE 1=1"; if (isset($_POST['merk']) && $_POST['merk'] != 0) { $qry .= " AND merk='" . mysql_real_escape_string($_POST['merk']). "'"; } if (isset($_POST['cpu']) && $_POST['cpu'] != 0) { $qry .= " AND cpu='" . mysql_real_escape_string($_POST['cpu']). "'"; } if (isset($_POST['hdd']) && $_POST['hdd'] != 0) { $qry .= " AND hdd='" . mysql_real_escape_string($_POST['hdd']). "'"; } $result = mysql_query($qry) or die(mysql_error()); // lelijke foutmelding, zelf mooier maken of $result door een if statement halen while($row = mysql_fetch_array($result)) { echo $row['merk'] . " " . $row['cpu'] . " " . $row['hdd']; echo "<br />"; } ?> When i search for hdd wich is last in de search list the query only returns the records wich contain that value. When the condition has to be for example Intel cpu AND 500GB it returns everyting with 500GB but also every kind of CPU. Anyone? I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate. What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop. It doesn't matter if I get to the next page using a header redirect or a form submit. I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. Any ideas? hi all, in mysql, how can i extract 100 words before and 100 words after my search key? example: "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla est nibh, mattis eu mattis id, pulvinar eu augue. Duis ut sem nisi. Sed id ante sed orci vestibulum lacinia ac id nibh. Donec cursus, elit eget auctor semper, ipsum eros laoreet quam, nec ullamcorper" vestibulum is my search key Can someone please double check my search query to make sure it is the minimum and most efficient coding. My goal is an exact match search of field1 from my database table and list field1, field2, field3, field4 and field5 if the part number is in the database or no results if it is not. The query works as is but I have a feeling it could be done more efficiently or professionally and minimum work for my poor little server. Thanks in advance. Code: [Select] <?php //connect to the database include("./databaseconnect.php"); //get query $q=$_GET['q']; //convert query to uppercase & remove all spaces and special charactars $q=strtoupper(preg_replace("/[^A-Za-z0-9]/","",$q)); //if blank query if ($q == "") { echo "You did not enter a search term"; } else { //exact match only query $query = "SELECT * FROM ".$dbtable." WHERE field1 like \"$q\""; $result = mysql_query($query); //if query returns no results if(mysql_num_rows($result)==0) { echo "<span class=\"noresults\">There were no results for your search</span>"; //display database results } else { while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "<span class=\'results\'>SEARCH RESULTS</span><br /> <span class=\'list\'>Part Number: ".$row['field1']."<br /> Manufacturer: ".$row['field2']."<br /> Cost per unit: ".$row['field3']."<br /> Warehouse Location: ".$row['field4']."<br /> Quantity Available: ".$row['field5']."<br /> Notes: ".$row['field6']."<br /> </span>"; } } } ?> Hi guys, I have a php file that will go to a site and scrape the data I need. However this site is setup to use pagination so when I try to scrape all the players names I have to do separate queries to search each page. Is there a way to find out by using code how many pages there are and query all the pages at same time? I use this code Code: [Select] <?php //first page //turn error reporting on libxml_use_internal_errors(true); //get data from this page $dom = new DOMDocument; $dom->loadHTMLFile('http://www.gametracker.com/server_info/76.73.3.42:1716/top_players/?searchipp=50#search'); $xpath = new DOMXPath($dom); // Get the total player count $rows2 = $xpath->query('//div[@class="block774"]/div'); // Get the rows from the search list $rows = $xpath->query('//table[@class="table_lst table_lst_spn"]/tr'); for ($i=1; $i<$rows->length-1; $i++) { $row = $rows->item($i); // Get the columns for a row $cols = $row->getElementsByTagName('td'); // Get the player rank (1st column) echo 'Rank:'.trim($cols->item(0)->textContent).PHP_EOL; // Get the player name (2nd column) echo 'Name:'.trim($cols->item(1)->textContent).PHP_EOL; // Get the player score (3rd column, actually 4th but number 3 is hidden) echo 'Sco '.trim($cols->item(3)->textContent).PHP_EOL; echo "<br />"; } ?> <?php //secondpage //turn error reporting on libxml_use_internal_errors(true); //get data from this page $dom = new DOMDocument; $dom->loadHTMLFile('http://www.gametracker.com/server_info/76.73.3.42:1716/top_players/?searchipp=50&searchpge=2#search'); $xpath = new DOMXPath($dom); // Get the rows from the search list $rows = $xpath->query('//table[@class="table_lst table_lst_spn"]/tr'); for ($i=1; $i<$rows->length-1; $i++) { $row = $rows->item($i); // Get the columns for a row $cols = $row->getElementsByTagName('td'); // Get the player rank (1st column) echo 'Rank:'.trim($cols->item(0)->textContent).PHP_EOL; // Get the player name (2nd column) echo 'Name:'.trim($cols->item(1)->textContent).PHP_EOL; // Get the player score (3rd column) echo 'Sco '.trim($cols->item(3)->textContent).PHP_EOL; echo "<br />"; } ?> I also have to go to that website first to see how many pages there are so I can have enough queries. |
|||||||