|
PHP - Sql Sort/order Search Results
Hi guys,
I have this SQL query which I use in my PHP search script Code: [Select] $query_for_result=mysql_query("SELECT * FROM customer WHERE c_name like '%".$query."%' OR c_telephone like '%".$query."%' OR c_address like '%".$query."%'"); It works fine, the only problem is now my database is bigger I would like to be able do SORT or ORDER BY id so that my search results are in order from asc to desc. Is this possible? if so can you help me. Thanks Similar TutorialsHi guys, Having problems sorting mysql results. I can easily order/sort table columns but would like to order the results by a variable.(Distance in Miles). Havent much experience in PHP and really struggling with this. Basically a query echoing out car make and models and how far they are located from a postcode. Can get it to work no porblem, but not to order/sort by least distance. Heres what i'm working with: Code: [Select] $res=mysql_query("SELECT * FROM cars"); while ($row = mysql_fetch_assoc($res)){ echo $row['Make']; echo $row['Model']; //variable will be determimed by user input form $postcode="W12 3SR"; $start=$postcode; $ends = array(); //finds value from database field { $ends[]=$row['Location']; } // Postcode entered by user via input form $result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$start'"); while($row=mysql_fetch_array($result)){ $gridn[0]=$row['Grid_N']; $gride[0]=$row['Grid_E']; } foreach($ends as $fin){ // Postcodes within mysql table $result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$fin'"); while($row=mysql_fetch_array($result)){ $gridn[1]=$row['Grid_N']; $gride[1]=$row['Grid_E']; } // TAKE GRID REFS FROM EACH OTHER TO WORK OUT DISTANCE. $distance_n=$gridn[0]-$gridn[1]; $distance_e=$gride[0]-$gride[1]; // CALCULATE THE DISTANCE BETWEEN THE TWO POSTCODES AND DIVIDE BY 1.6 TO CONVERT KM TO MILES $hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e))/1.609; //VARIABLE FOR DISTANCE AND ROUNDED OF TO NEAREST WHOLE NUMBER. $distance=''.round($hypot/1000,0).''; echo " $distance miles"; echo "<br>"; } } ?> I wish i could just do something like this but isnt possible, is it ? Code: [Select] "Select * FROM cars ORDER BY $distance"; Thanks! Hi All i am wanting a column list like this http://extensions.joomla.org/extensions basically the database is set up as (id, category, parent) I want the parent to group the category section and list like the the joomla example in three columns search various threads throughout the internet but none seem to cover this entirely can any one please help????? For some reason every time I run this form it inserts an undefined value into the field called sortorder into my database table. The rest of the form inputs insert properly but that one. Here's the necessary coding. Anyone see why? form page <?php // Include the database page require ('../inc/dbconfig.php'); ?> <script type="text/javascript"> $(document).ready(function() { $('div.message-error').hide(); $('div.message-success').hide(); $("input.submit").click(function() { $('div.message-error').hide(); var charactername = $("input#charactername").val(); if (charactername == "") { $("div.message-error").show(); $("input#charactername").focus(); return false; } var charactershortname = $("input#charactershortname").val(); if (charactershortname == "") { $("div.message-error").show(); $("input#charactershortname").focus(); return false; } var sortorder = $("input#sortorder").val(); if (sortorder == "") { $("div.message-error").show(); $("input#sortorder").focus(); return false; } var style = $("select#style").val(); if (style == "") { $("div.message-error").show(); $("select#style").focus(); return false; } var status = $("select#status").val(); if (status == "") { $("div.message-error").show(); $("select#status").focus(); return false; } var alignment = $("select#alignment").val(); if (alignment == "") { $("div.message-error").show(); $("select#alignment").focus(); return false; } var division = $("select#division").val(); if (division == "") { $("div.message-error").show(); $("select#division").focus(); return false; } var dataString = 'charactername=' + charactername + '&charactershortname=' + charactershortname + '&sortorder=' + sortorder + '&style=' + style + '&status=' + status + '&alignment=' + alignment + '&division=' + division + '&submitcharacter=True'; $.ajax({ type: "POST", url: "processes/character.php", data: dataString, success: function() { $('div.message-error').hide(); $("div.message-success").html("<h6>Operation successful</h6><p>Character " + charactername + " saved successfully.</p>"); $("div.message-success").show().delay(10000).hide("slow"); $(':input','#characterform') .not(':submit') .val('') return true; } }); return false; }); }); </script> <!-- Form --> <form action="#" id="characterform" > <fieldset> <legend>Add New Character</legend> <div class="field required"> <label for="charactername">Character Name</label> <input type="text" class="text" name="charactername" id="charactername" title="Character Name"/> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <div class="field required"> <label for="charactershortname">Character Short Name</label> <input type="text" class="text" name="charactershortname" id="charactershortname" title="Character Short Name"/> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <div class="field required"> <label for="sortorder">Sort Order</label> <select class="dropdown" name="sortorder" id="sortorder" title="Sort Order"> <option value="0">- Select -</option> <?php $sortorder = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0-9"); foreach($sortorder as $so): ?> <option value="<?php echo $so; ?>"><?php echo $so; ?></option> <?php endforeach; ?> </select> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <div class="field required"> <label for="style">Character Style</label> <select class="dropdown" name="style" id="style" title="Style"> <option value="0">- Select -</option> <?php $query = 'SELECT id, stylename FROM styles LIMIT 2'; $result = mysqli_query ( $dbc, $query ); // Run The Query while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) { print "<option value=\"".$row['id']."\">".$row['stylename']."</option>\r"; } ?> </select> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <div class="field required"> <label for="status">Character Status</label> <select class="dropdown" name="status" id="status" title="Status"> <option value="0">- Select -</option> <?php $query = 'SELECT id, statusname FROM statuses'; $result = mysqli_query ( $dbc, $query ); // Run The Query while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) { print "<option value=\"".$row['id']."\">".$row['statusname']."</option>\r"; } ?> </select> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <div class="field required"> <label for="alignment">Alignment</label> <select class="dropdown" name="alignment" id="alignment" title="Alignment"> <option value="0">- Select -</option> <?php $query = 'SELECT id, alignmentname FROM alignments'; $result = mysqli_query ( $dbc, $query ); // Run The Query while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) { print "<option value=\"".$row['id']."\">".$row['alignmentname']."</option>\r"; } ?> </select> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <div class="field required"> <label for="division">Division</label> <select class="dropdown" name="division" id="division" title="Division"> <option value="0">- Select -</option> <?php $query = 'SELECT id, divisionname FROM divisions'; $result = mysqli_query ( $dbc, $query ); // Run The Query while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) { print "<option value=\"".$row['id']."\">".$row['divisionname']."</option>\r"; } ?> </select> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <input type="submit" class="submit" name="submitcharacter" id="submitcharacter" title="Submit Character" value="Submit Character"/> </fieldset> </form> <!-- /Form --> <!-- Messages --> <div class="message message-error"> <h6>Required field missing</h6> <p>Please fill in all required fields. </p> </div> <div class="message message-success"> <h6>Operation succesful</h6> <p>Character was added to the database.</p> </div> <!-- /Messages --> validation page <?php // Include the database page require ('../inc/dbconfig.php'); if (isset($_POST['submitcharacter'])) { $charactername = mysqli_real_escape_string($dbc, $_POST['charactername']); $charactershortname = mysqli_real_escape_string($dbc, $_POST['charactershortname']); $sortorder = mysqli_real_escape_string($dbc, $_POST['sortorder']); $style = mysqli_real_escape_string($dbc, $_POST['style']); $status = mysqli_real_escape_string($dbc, $_POST['status']); $alignment = mysqli_real_escape_string($dbc, $_POST['alignment']); $division = mysqli_real_escape_string($dbc, $_POST['division']); $query = "INSERT INTO `characters` (charactername, charactershortname, status_id, style_id, division_id, alignment_id, sortorder, creator_id, datecreated) VALUES ('$charactername','$charactershortname','$status','$style','$division', '$alignment', '$sortorder', 1, NOW())"; mysqli_query($dbc, $query) ; } ?> Hi, I have a loop which creates an array: $productions[] = array( 'url'=>get_the_permalink(), 'year'=>$production_year->name, 'title'=>get_the_title() ); When I output the results, the year is in the wrong order:
2019 What I can't work out is how to order 2014 - 2020 I tried ksort(array, SORT_STRING) and ksort(array, SORT_NUMERIC) I also tried natsort But still have the same issue - am I missing something? Thanks Hey guys, I am having trouble figuring this out. I currently have a database that automatically sorts itself by the id of the data. But, I am wanting the user to be able to change the sort order via a select box. I already have the select box programmed with the options, but I am not sure how to go about coding the options to change the sort order of the displayed data. What would the best way to go about programming this be? Thank you very much ahead of time!! I am retrieving data using AJAX. I would like to sort them from newest to oldest date. Currently they show from oldest to newest. How do I do that?
<div id="output"></div>
<script>
$(document).ready(function(){
$.ajax ({
type: 'GET',
url: "https://3rdpartywebsite.com/api/getcustomers",
dataType: 'json',
success: function(data) {
$.each(data, function(i, v) {
var customerId = v.customerId;
var firstName = v.firstName;
var lastName = v.lastName;
var createdDateTime = v.createdDateTime;
$('#output').append('<ul class="output-ul">' + '<li>' + customerId + '</li>' + '<li>' + firstName + '</li>' + '<li>' + lastName + '</li>' + '<li>' + createdDateTime + '</li>' + '</ul>');
});
}
});
});
</script>
I am running a script to retrieve numerical value from a webpage, the script echoes the numbers. However I need to add all the numbers to an array or txt file and then have them sorted from highest value to lowest value. Any hints or tips to how I would achieve this? Hello, I'm developing a website that asks the user to submit a keyword for a search. The results should display matches for this keyword, but also show matches for related keywords (in order of relevenace!). I'm planning on building up a library of which search terms users use in the same sessions (e.g. if someone searches for "it jobs" and "php jobs", I'll know the terms are correlated), and I'll also measure the click-through rates of the items on the results list. I've been spending all weekend trying to map out the design for this but it's proving incredibly complicated and I think the solution is likely to be on the internet somewhere already?! Please could someone point me in the right direction if you've come accross this problem before? Thanks a million, Stu I'm trying to make a function which will display the top X results of the row Y which I set, for this instance I'm using the row browser and the top 5 results from my table statistics, the where is just to eliminate Search Bot results from showing up. I also want it to return the count of the amount of rows as well. So say there was 10 results for the browser 'Safari', then it would return the count of 10 for that result as well as the result itself. Code: [Select] $display->show_list('statistics', 'browser', '5', 'WHERE browser!=\'Search Bot\''); Here is my function. I'm cleaned it up a bit to remove certain checks and outputs if the query were to fail, etc. Code: [Select] function show_list($table, $row, $limit = 5, $where = NULL) { $item = mysql_query("SELECT DISTINCT $row FROM $table $where LIMIT $limit"); $result = array(); while ($fetch = mysql_fetch_array($item)) { $result[] = $fetch; } return $result; } Basically, how would I go about making it count the amount of rows for the row I've set, and then to output that along side the result? Hi Guys I have a product page in mysql as below: id itemnumber order_id 1 348939012 2 2 535432454 1 3 543253424 4 4 987698769 3 I need to order this in my PHP mysql_fetch_assoc by order id. What I mean is I need to list them by order_id: 1,2,3,4 I have used ORDER BY order_id but still it wont work, any ideas? How do I present these documentaries like this:
0-9
10 Things You Need to Know About Sleep
20 Animals That Will Kill You
A
Ant Kingdom
Atkins Diet
B
Battle of the Brains
Body Talk
I don't even know where to start!
I'm not asking anyone to do this for me; I just need a push in the right direction.
Any help will be appreciated.
I wish to order my results by its occurrence inside the $status array. Basically all of the results with the status 'New' are to be listed first, then the results with the status Today, Tomorrow, Soon and so on. Is this possible, or is there alternatively a different approach to ordering my results how I desire? Code: [Select] $status = array('New', 'Today', 'Tomorrow', 'Soon', 'Future', 'Complete'); $display->get_results("SELECT * FROM todo ORDER by status DESC"); How can I make this output in descending order from greatest to least? Right now, it appears like: 1940 1941 1942 1943 1944 etc... i'd like it to be like 2012 2011 2010 2009 etc.. until it gets to 1940 any ideas? here is my code: <? $i = 1939; while ($i < 2012) { $i++; echo '<option value="'.$i.'">'.$i.'</option>'; } ?> Here's the query: Code: [Select] $sql = mysql_query("SELECT * FROM resources WHERE gameCat = '' ORDER BY systemCat,order_id"); I want something to happen when the systemCat changes in the results. Like this: Code: [Select] if($row['systemCat'] CHANGES){ echo '<div id="seperator"></div>'; } Any ideas? Thank you! Hello dear Community, i have a large document - and i want it to parse it and spit out only this part: schule.php?schulnr=80287&lschb= Question: How to i parse the stuff!? Well i try it with FireBug and FirePath (the Xpath-tool) i do it like the following: * Load the document into my browser, if possible * start Firebug extension/add-on * run the FirePath extension * and run the xpath //a[contains(@href, "schule")]/@href * then i click "Eval" button. i find 2030 results - that are marked - how do i get them out of the firebug - to work with them?! If i have to be more precise - please let me know!! Perhaps i have to write more - and to add more information See the screenshot - here http://img259.imageshack.us/img259/7360/sceenshoteval5.jpg how can i copy and paste the results - in order to do further processing from thereon. look forward to hear from you regards What im trying to achieve is a user contact system, when the user types input in the textarea its saved in a database along with his userid on the site and the date, I have an admin side coded up where it retrieves the data, but there is no order. I want to have it display the most recent results on top. I have looked into this in the mysql manual, but you know how that is its so hard to make sense of anything in that damn manual. Im using a while loop to display the data. How can I achieve this? i want to sort the available words by the "searched word"...is there any way ? Code: [Select] <?php $query = "SELECT * FROM ce_cour_chap WHERE nom LIKE'%$find%' ORDER BY nom"; $result = mysql_query($query); while($search = mysql_fetch_array( $result )) { echo $search['nom']." - ".$search["chid"]."<br/>"; } ?> Hi guys, I'm trying to sort an array. The array has information about listings. ID, name, info etc. An unrelated set of data also contains the listing ID's and I have made a function to sort this array and show an new order for the ID's. I now need to sort the original array by this new ID order... This snippet shows that the original listings array can be sorted by link_id: sort($this->links, $this->link->link_id); //This works. It sorts links by link ID! In a foeach loop I have created this: $sleeps_array[] = array("ID" => $link->link_id, "Sleeps" => $sleeps); // Makes an array of arrays of a custom field with listing ID. It makes this: array ( 0 => array ( 'ID' => '9', 'Sleeps' => '2', ), 1 => array ( 'ID' => '3', 'Sleeps' => '4', ), 2 => array ( 'ID' => '6', 'Sleeps' => '6', ), ) I can then sort this array of arrays with this function: function compare_sleeps($a, $b){ return strnatcmp($a['Sleeps'], $b['Sleeps']); } # sort alphabetically by name usort($sleeps_array, 'compare_sleeps'); It then gives me a sorted array according to "Sleeps" (as in, the number of people a property can cater for). It has the correct order of ID to then output but how do I organise the original array by ID with this new array? Sounds complicated, I hope you got that! Cheers, RJP1 hey guys, i have a search bar i am implementing into my navmenu but keeping getting errors returned and im not sure why This is the code in my navmenu to display the search bar Code: [Select] echo '<form action="search.php" method="post">Search: <input type="text" name="term" /> <input type="submit" name="submit" value="Search"/>'; Code: [Select] Here is the php file that query's the search $term = $_POST['term']; $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); $query = mysqli_query("select * from ob_listings where subject like '%$term%' or description like '%$term%' "); while ($row = mysql_fetch_array($dbc, $query)){ echo 'Subject: '.$row['subject']; echo '<br/> Date: '.$row['date']; echo '<br/> Price: '.$row['price']; echo '<br/> City: '.$row['city']; echo '<br/><br/>'; } any help is much appreciated here are the errors being returned Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\vhosts\HeadFirst\ourbazzar\search.php on line 16 (line 16 is the $query) Warning: mysql_fetch_array() expects parameter 1 to be resource, object given in C:\vhosts\HeadFirst\ourbazzar\search.php on line 17 (line 17 is the while loop) I am trying to write a script that will allow me visitors to my site to search my database for items. I followed a tutorial that I had found and tweaked it to fit my needs. I am not getting any results it keeps telling that there was no search results found. Even though I have the items that I have been testing on in my database. I have two files the main search.php file and the search_func.php file Here is the search.php file Code: [Select] <form method='post' action='search.php'> <fieldset> <input type='text' name='search' id='search' value='type name here'/> <input type='submit' name='search_submit' id='search_submit' value='search'/> </fieldset> </form> <?php include('includes/search_func.php'); if(isset($_POST['search'])) { $keywords = mysql_real_escape_string(htmlentities(trim($_POST['search']))); $errors = array (); if (empty($keywords )){ $errors[] = 'Please enter a search term'; } else if (strlen($keywords ) < 3 ) { $errors[] = 'Search term must be more then three characters long'; } else if (search_results($keywords == false)) { $errors[] = 'Your search for ' .$keywords . ' showed no results'; } if (empty($errors)) { $results = search_results($keywords ); $results_num = count($results); $suffix = ($results_num != 1) ? 's' : ''; echo 'Your search for <strong>'. $keywords .'</strong> returned <strong>' .$results_num.'</strong> result'.$suffix.''; foreach($results as $result) { echo $result['first']; } } else { foreach($errors as $error){ echo $error; } } } ?> Here is the search_func.php file Code: [Select] <?php include('includes/config.php'); connect(); function search_results($keywords ) { $returned = array(); $where = ""; $keywords = preg_split('/[\s+]/', $keywords ); $total = count($keywords ); foreach($keywords as $key=>$keyword) { $where .= "`l_name` LIKE '%$keyword%'"; if($key != ($total - 1)){ $where .= "OR"; } } $results = "SELECT `l_name`,`f_name`,`image`,`item_return` FROM `ttmautos` WHERE $where"; $results_num = ($results = mysql_query($results)) ? mysql_num_rows($results): 0 ; if ($results_num === 0) { return false; } else { while($results_row = mysql_fetch_assoc($results)) { $returned_results[] = array( 'last' => $results_row['l_name'], 'first' => $results_row['f_name'], 'image' => $results_row['image'], 'item' => $results_row['item_return'] ); } return $returned_results; } } ?> If anyone could give me an idea of what I am doing wrong I would appreciate it. |
|||||||