Random Query, How To Keep Sort Order Locked?
PHP Version 5.1.6 - mySQL 5.0.22
Let's pretend we have 100.000 rows in a table (all containing images with details), and I want to display 100 images at a single frontend page, but randomly retrieved and keeping the sort order once initiated to a user session.
With the normal RAND function, you have a chance to stumble on the same images when you navigate to the next page with again 100 items on. But even worse, going back to page 1, will return totally different results, and will freak out the viewer
How to get a (fake) random listing on a fairly large database, where every requested row is unique, and the sort order is being locked (until you press a randomize button ie, or start a new session) while keeping speed in mind (so trying to avoid reading out all rows up front).
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Order By Random Then Sort
query: SELECT * FROM softwares group by type order by random(), type ASC unfortunately, it does not sort by type. say my table has: Quote: name, type A 1 B 1 C 2 D 2 it would randomly show 2 software but it will not sort by type, it could be the result a type 2 lists first then followed by type 1, next result would be vice versa. My goal is, to group the result set by type so i only get 1 software of each type, randomly and then sort it by type.
View Replies !
View Related
Select Query Sort Order
Example DB: Orders Table Order ID, Date, WeekID, Amount 1, 5/10/08, 0, 100.00 2, 9/3/08, 0, 200.00 3, 0/0/00, 1, 150.00 Weeks Table WeekID, DueDate 1, 7/2/08 I need the output to be in the following order base off of date ASC OrderID Date Amount 1 5/10/08 100.00 3 7/2/08 150.00 2 9/3/08 200.00
View Replies !
View Related
Random Order
Suppose I have a table with 10 records in it. I can select them with either ascending or desending order. Is it possible that I can select them in random order, so sequences is different each time?
View Replies !
View Related
Random Row Order
What do I need to put into a SQL statement for it to retrieve 100 Random Rows (containing certain criteria in the WHERE clause). Something like this (theoretically) is what i'm looking for: SELECT * FROM table WHERE field="yes" ORDER RANDOM LIMIT 0, 25 Any Ideas?
View Replies !
View Related
SQL Sort (Order By)
I have sql statement below , SELECT serial_no,host_name,chasis_model,chasis_flash_size ,chasis_dram_size, country,city,building,other,chasis_sw_version,stat us,chasis_eos,chasis_eol,chasis_user_field_1,chasi s_user_field_2,chasis_user_field_3 FROM tbl_chassis ORDER BY country = '', country However , my user would like to sort by country,city,building and other columns . Anybody have ideas how to do that ? I do order by country,city,building,other gave me the result which i not expected. For example below data before sort Country City Building Other Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 83 Aber rd Thailand Bangkok Thailand Bangkok 12 Godieon Rd Thailand Bangkok 900 Telepark After sort Country City Building Other Thailand Bangkok 83 Aber rd Thailand Bangkok 12 Godieon Rd Thailand Bangkok 900 Telepark Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok 208 Wireless RD Thailand Bangkok
View Replies !
View Related
ORDER BY Sort
When a number is stored as a character string, in what order does ORDER BY sort? ASCII-betical? Or numeric? I can't believe I've spent half an unsuccessful hour trying to Google an answer to such a simple question! I'm curious because I want to store a bunch of numbers, some integers and some decimals, and want to know if it makes any difference whether I store them all as decimals or as characters
View Replies !
View Related
No Sort Order?
I have a SELECT statement such as: SELECT category, title, pubdate FROM ds WHERE category = 3 OR category = 5 OR category = 4 OR category = 101 OR category = 7 I want the result set to return the results in the order that I have queried above (ie: 3,5,4,101,7) but MySQL seems to sort the category so I get the results as (3,4,5,7,101). Other than splitting into 5 SELECT statements, is there any way I can use a query to say "DO NOT SORT"??
View Replies !
View Related
ORDER By 2 Different Collumns Or Random
I want to order by a certain collumns, but if there are rows that contain the same value in the collumn that I do the order by on then I would like to display the results differently everytime I do the query.... For example: id lvl 1 1 2 2 3 2 Now if I do SELECT * FROM table ORDER by lvl DESC the result will always be: id lvl 2 2 3 2 1 1 I would like to see the result above change randomly to id lvl 3 2 2 2 1 1 and perhaps next time the query is executed the result is again id lvl 2 2 3 2 1 1 Is there a way to add this random feature? I thought possible to do something like SELECT * FROM table ORDER by lvl DESC AND ORDER by id '$x'(I would set $x randomly to ASC or DESC)Any suggestions (I hope I made it clear enough)?
View Replies !
View Related
ORDER BY :: Random Records
I'm trying to have the first column (home) order by ascending while randomizing. The problem is that my second order gets affected by this as well. Is there a way I can randomize column1 while having column2 to stay put without using UNION? ORDER BY `column1` ASC,RAND(), `column2` DESC
View Replies !
View Related
ORDER BY List Of Random Ids
I have a list of id-numbers, for example (4,19,2,5). Now I need to get rows from a database in that order, 4 first, 19 second and so on... Is this even possible to get the rows in such a special order? I now have select statement like: SELECT stuff FROM TABLE WHERE id IN(4,19,2,5) ORDER BY ??? but of course that returns the rows in order 2,4,5,19,
View Replies !
View Related
Custom Sort Order
I know you can order your recordsets by field either ASC or DESC. However, is it specifically possible to set a specified order? Example: Display records as B, C, A Instead of either A, B, C or C, B, A.
View Replies !
View Related
With Rollup - Sort Order
PHP SELECT B.BatchID, J.JobID, P.Project, J.JobNumber, P.ShipLocation, J.JobType, P.Country, JR.ReleaseName, SUM(B.Price) AS TotalPrice, JR.JobReleaseID, JR.TargetProductionDate, JR.ATF, JR.Reviewed, JR.ProductionStatusID, JR.ToShop AS TS, S.FirstName AS SalesmanFirstName, S.LastName AS SalesmanLastName, S.Nickname AS SalesmanNickname, C.AccountStatus, C.Company AS Customer, C.Nickname, B.TargetDate, B.ExchangeRate, P.JobClassificationID, C.CompanyID, P.ProjectID FROM Project P, Job J, JobRelease JR, Batch B, Company C LEFT JOIN User S ON S.UserID = P.Salesman_UserID WHERE J.ProjectID = P.ProjectID AND JR.ProductionStatusID != 4 AND JR.JobID = J.JobID AND JR.ProductionLocationID = '$SessionData[ProductionLocationID]' AND P.Customer_CompanyID = C.CompanyID AND J.JobID = B.JobID AND JR.JobReleaseID = B.JobReleaseID GROUP BY C.CompanyID, J.JobID, B.BatchID WITH ROLLUP I want to organize this report to show the highest "ROLL UP" tally of TotalPrice, is that possible... I need some genius help and fast, if possible, I am trying to get something done for our guys first thing tomorrow.
View Replies !
View Related
ORDER BY Doesn't Sort As Expected
Would anyone be able to look at the following statement and tell me why ORDER BY is not behaving as expected. SELECT RTeamName, SUM( RGamePld ) , SUM( RGameWon ) , SUM( RGameLost ) , SUM( RGameDraw ) , SUM( DLegFor ) , SUM( DLegAgainst ) , SUM( RPointsSum ) FROM LeagueTable WHERE RDivision = 'A' GROUP BY RTeamName ORDER BY RPointsSum DESC LIMIT 0 , 30 All of the Database fields are correctly spelt and the database has two records in for each team. The results displaying in the SUM() fields is correctly calculated but is not sorting by RPointsSum. The team with the most points is currenty halfway down the table. Any suggestions why this isn't sorting so that the team with two points is at the top of the table? I can send screen shots if that helps. Cheers
View Replies !
View Related
Query/connection State Getting Locked
I am using MySql server 4.1.10a. I am facing problem while firing big query Like join with multiple tables multiple conditions Whenever I fire this kind of query, all other requests from other connections got locked. I am using MyISAM storage engine for storage. And database size near about 1.5 GB. It's a linux on IBM server duel xeon processor 2GB ram.
View Replies !
View Related
Dual Sort In ORDER BY Clause?
I'm wondering if there's a way to order results by two criteria? I want to order forum threads by (1) their "stuck status" (enum, 0 or 1) and (2) by their "last post date" (int, unix timestamp). The resulting output would have the "stuck" threads at the top regardless of their last post date, followed by the "unstuck" threads ordered by their last post date. Can this be done with SQL?
View Replies !
View Related
How To Sort 1/2 Digit Integers With Order By
trying to order the results from a select but I found the query choses the first digit of the integer as the index. Example, after trying "select * from A order by B I get the entries in this order: 1 10 11 12 13 2 3 4 5 Am I missing something or is this something particular from the application (HP Quality Center)?
View Replies !
View Related
Products Sort /' Display Order
I have one category with 4 products. How to set order of display of products. for example: product id 1, product id 2, product id 3 and product id 4. I want to show product id 3 in the first row and product id 4 in the second row etc.
View Replies !
View Related
Have My Recordset Table Show Up In Random Order
I have the following code on my PHP page and my goal is to have the recordset show up in a random order each time the page is visited. I have tried a ORDER BY RAND() and come up with errors only. Is there anyway to find out what code I need to use and where I would place it in this piece of code? Code:
View Replies !
View Related
Slotting (or Balanced/ratioed Sort Order)
Having tried to find any documentation on (what we used to call) slotting - I've found nothing. Perhaps the naming convention has changed during my absence?! I have a PRODUCT table: ProductID Supplier Type Price Stock I have several suppliers of the same type of product in a catalogue and would like to give each a fair amount of exposure in the search results. For example, if suppliers A, B & C each have 100, 60 and 40 screwdrivers (Type) in my catalogue, I would like to interweave the search results such that the first (say) twenty results would contain screwdrivers from each supplier in the ratio 10:6:4 but (preferably) jumbled up. Each supplier's products get a fair search order ranking in relation to the amount of product they have in the catalogue for that product type. We use to call this "slotting" in my day, but again I can find no reference to it or how to implement with a simple QUERY in MySQL.
View Replies !
View Related
Finding Sort Order For An Existing Key Column Associated With An Index
I am trying to find within the INFORMATION_SCHEMA tables the Sort Order (ASC or DESC) of a specific Key Column associated with an existing Index. I thought maybe KEY_COLUMN_USAGE might have it but unfortunately it doesn't from what I read. It has ORDINAL_POSITION but that only lists the position of that column against other key columns for a given index. Anyone know where I might find this information or if this information is not found in the INFORMATION_SCHEMA tables? Parsing the index DDL is not considered an option for me at the moment.
View Replies !
View Related
Random Query
I have been tasked with getting daily quotes out of a sql database, there needs to be 3 that are randomly selected. I have not found any documentation in regards to doing this. How would I qurey a database and pull 3 quotes of the day out randomly. This should even occur when a user refreshes the page, is there a sql function that would do this? or do I need to do it via php, perl, etc...
View Replies !
View Related
Saving Random Query
I want to select about 10 rows randomly from my table every day. I know how to randomly select rows, but is it possible to save that select so that I get the same 10 randomly selected rows again?
View Replies !
View Related
Sort A Complex Query
$query = "SELECT page.* FROM `page` LEFT JOIN `keywords` USING (`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$radio_keyword' IN BOOLEAN MODE) UNION SELECT page.* FROM `page` WHERE MATCH (`title`, `descrip`) AGAINST ('$radio_keyword' IN BOOLEAN MODE) UNION SELECT page.* FROM `page` LEFT JOIN `url_pages` USING (`page_id`) WHERE MATCH (`url_pages`.`page_url`) AGAINST ('$radio_keyword' IN BOOLEAN MODE)"; How do I ORDER BY `page`.`title` for the entire query? ie. I want to get the whole query sorted by the page.title field, but I don't see a way to slap on an "ORDER BY" on the whole query.
View Replies !
View Related
Random City During Query Circulation
data in myTable1 (id) name (1) Danaka (2) Jane (3) Jean data in myTable2 (id) city (1) Tokyo (2) New York (2) Chicago (2) L.A. (3) Paris (3) Lyon I have two tables like the above. The following codes produce the following results. code1 select myTable1.id, name, city from myTable1 left join myTable2 on myTable1.id=myTable2.id where myTable1.id=2 result1 (2) Jane New York (2) Jane Chicago (2) Jane L.A. code2 select myTable1.id, name, city from myTable1 left join myTable2 on myTable1.id=myTable2.id where myTable1.id=2 ORDER BY RAND() LIMIT 0,1 result2 (2) Jane L.A. //random city I like to produce my target result below. target result (2) Danaka Tokyo //random city (2) Jane Chicago //random city (2) Jean Lyon // random city
View Replies !
View Related
INSERT Query With Random Date
I have about 2,000 entries to add to my database and I want each one to have a random date of between, for instance, 2007-01-01 and 2007-03-28 [today]. Any ideas on what's the best way of doing this? I did try and come up with a script that grabs a line, updates it and then moves on to the next line, but I keep getting "This page will repeat forever" errors coming up.
View Replies !
View Related
Select One Distinct And Several Random Values In One Query
I'm wondering if it's possible to select an id (let's say its 13) and several random id's between 1 and 100 in one query, excluding for example the id's 14 and 15. I've tried different ways with UNION - but when I include 'order by rand()' in my query, I'll never get the id 13 back. Could anybody help me please? Here's a query which did not work, because i will always get the the same result, without random values: SELECT * FROM table WHERE id = '13' UNION DISTINCT SELECT * FROM table WHERE id != 14 AND id != 15 AND (id BETWEEN 0 AND 100) LIMIT 3
View Replies !
View Related
SQL Query Problems (for Use With A Sort Of Live Search)
I created some code that loaded information from an XML file, but many thanks to jimfraser on here I'm working on a solution that'll drag it directly from the database. Problem is, it's not displaying anything at all, so I assume there's a problem with the SQL query or the way it's being presented on the page. PHP <?php $network = new COM("WScript.Network"); include_once('../../../functions/datalib.php'); $db = new oracleClass(); $conn = $db->connect(); //get the q parameter from URL $q=$_GET["q"]; //lookup all links from the xml file if length of q>0 if (strlen($q) > 0) { $hint=""; // connect to the database $sqlstatement = 'select * from TEST."WFO_REPORTS_ALLEMPS" order by "Surname", "Forenames"' $sqlListings = OCI_Parse($conn, $sqlstatement); OCI_Execute($sqlListings); while(OCI_Fetch($sqlListings)) { $name = oci_result($sqlListings, "Forenames") . ($sqlListings, "Surname") //find a link matching the search text if (strchr($name,$q)) { if ($hint != "") { $hint .= "<br />"; } $hint .= "<a href='#' onclick='employee_form.Line_Manager_Staff_No.value=" . oci_result($sqlListings, "Gc_Staff_Number") . ";checknumber('Line_Manager_Staff_No','displayusername');'>" . oci_result($sqlListings, "Surname") . ", " . oci_result($sqlListings, "Forenames") . "</a>"; } } } // Set output to "no suggestion" if no hint were found if ($hint == "") { $response="No user found."; } else { $response=$hint; } //output the response echo $response; ?> On a side note, is there a way I can change it so that the search will present any users based on their first name, as well as the surname?
View Replies !
View Related
Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc. To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table: categoryname vegetables categoryid 1234 categorycreated 10th Jan 2000 categoryowner Bob firstitemid 4567 firstitemname cucumber firstitemcreated 12th Jan 2000 firstitemdesc It's long and green Here's an example record in the itemdata table: itemid 4568 itemname orange itemcreated 13th Jan 2000 itemdesc It's round and, er, yeah. This seems really bad design to me but what do I know. Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields. In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order. itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids. I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest. I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).
View Replies !
View Related
How To ORDER BY The Order Requested In The Query?
Here's my query: SELECT * FROM myTable WHERE id=14 OR id=3 OR id=8 Simple stuff, I know. The result of the query is three rows that are all sorted by their 'id' in ascending order. I don't want this. What I want returned are rows sorted by the order in which I requested them. I need the query to return row #14, #3 and then #8 in that order.
View Replies !
View Related
Random Rows, Non-random Ordering
Normally to select random rows from a table I'd do something like: SELECT field1, field2, RAND() AS random FROM mytable ORDER BY random LIMIT 5 Unfortunately, this time I need 5 random rows sorted on a different field: SELECT field1, field2, RAND() AS random FROM mytable ORDER BY field2 LIMIT 5 Is there a way that I can still sort by field2 and yet still select 5 rows at random? Could the former query be run as a subquery and then a second ORDER BY clause run on those results? Worst-case I can re-sort the results in my host language, but a pure SQL solution would be best I think. Bonus points if the answer can always put a particular row first in the results.
View Replies !
View Related
Order By Query
We recently changed servers for a bunch of clients and this new server is running MySQL 4.1.22. A bunch, if not all, of all queries which consist of ORDER BY statements are failing to order as they should. Below is a sample of our code that worked fine on our previous server but now is giving us problems. $query = "SELECT * FROM jobs WHERE status = 'active' ORDER BY 'date_full' DESC"; $result = mysql_query($query) or die (mysql_error()); I believe I have pinpointed the issue to the 'date_full' DESC part as if I remove the single quotes, the query works properly. My problem is that we have alot of clients and we've used this type of query for a ton of sites. Before I go through each and every site to remove the single quotes, is there any other solution?
View Replies !
View Related
Query Order By
I haven't done this sort of query before so looking for a bit of guidance! I have a table call members and a table call event_bookings. This is for an event management system i am developing. I want to be able to display a listing of all members who have booked into an event, but display them by surname. The database structure is MEMBERS TABLE member_id firstname surname EVENT_BOOKINGS TABLE booking_id member_id event_id The query without ordering it would be some "SELECT member_id FROM EVENT_BOOKINGS WHERE event_id = '$e'" How would I add an order by to sort this acording to the members surname...
View Replies !
View Related
Locked Myself Out
I setup MYSQL about 3 monthes ago(one of the latest versions, but don't know the exact version) and have had no problems since. Today I was fooling around with getting PHPNuke setup, but I was having some problems. Anyway, I went in and started fooling around with PHPadmin and checked some box and now I can't log into MYSQL. I get an access denied error. I think the password is fine, it's the permissions that are screwed up. I followed the suggestions in this link: http://dev.mysql.com/doc/mysql/en/resetting-permissions.html but I'm hitting a wall. I shut down the service fine, but when I try to restart the service with C:mysqlinmysqld-nt --skip-grant-tables it comes back without any errors, but it never restarts the application. I cannot do anything more in the process because I can't get MYSQL to start. I can restart the service fine, but the command above will not start MLSQL with the special permissions.
View Replies !
View Related
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
View Related
Query: Order By Price?
I have been wracking my brain for too long on this. Usually when you are thinking about something too long you miss the easy answer. Essentially I need to order a query by the price of the items: SELECT * FROM items ORDER BY itemprice Great but... when there is a sale... the itemprice is not correct you need the sale price. I have a digit on/off field for this: itemprice decimal(9,2) itemsale tinyint(1) itemsaleprice decimal(9,2) When the item is on sale itemsale is 1 and itemsaleprice is used instead of itemprice. Question is how do I order by all the current prices whether the item is on sale or not (and still include the sale prices where the item is on sale)?
View Replies !
View Related
Locked Processes
When I execute the show processlist command I see locked( in state locked ) processes and if I kill one all the other get locked and the db freezes. When I kill all of them the problem is solved. This problem has been re-occurring. Can anyone tell me what and how to prevent it. Its a multithreaded mysql 3.23.41 server on linux. The table types in the DB are MyISAM.
View Replies !
View Related
Subsys Locked
Shut down my Linux PC running MySqld and rebooted this morning and = cannot get mysqld up. In services when I click on mysqld I get the = message "Mysqld dead subsys locked" when i restart the server it says = mysql succesfully restarted, but it isnt. When I try to stop the server = it comes back with an error message "failed to shutdown". So I rebooted = and during shutdown- shutdown mysql.............failed and- mysql = killall...............failed. Anyone have any ideas as to how I can sort this? would a reinstall of = Mysql work? would I lose legacy data doing this?
View Replies !
View Related
|
TRACKER
