MYSQL :: Ordering Results By Number Of Times They Appear

Number Of Times
I have a query that grabs 500 rows from 1 field within 1 table.

In this table. Each user will have 500 rows associated to them from within that table. So if there are 2 users then there will be 1000 rows. Each time 1 user executes the query, it grabs their 500 rows. If 2 users execute the query simultaneously, then this means that there are 1000 rows, all of which will be selected; 500 by one user and 500 by the other. If 3 users access the table simultaneously, then there are 1500 rows and so on. My question is how many users can execute this query simultaneously before we see performance loss?

Ordering Results In Php
The code for displaying such holidays is:

<a href="availableproduct.php?region=fiji">

Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.

Ordering By Most Results
In my database I have a table called imagebank and each row has field with the URL to a picture and I field that says what breed the picture is of.
How can I make a query to my database that pulls all the info from that table, and displays them in order of what breed has the most pictures.

Ordering Results
If i have a list of titles (ex. movie titles). What would be the best way to order these results without the preceding word "the". Would I have to use a case statement or something.
After reading the mysql manual some more..I came up with this.
select * from titles order by IF(INSTR(title, 'the') = 1, substring(title from 5), title)
Its wrong i Know, but am I even close?

Ordering Results
I got a problem with order by:
I need to order alphabetically a list of strings, the problem is that many of them start with a number and I want to display first the ones beginning with letter
How can I do?

Ordering Fulltext Results
I'm new to fulltext searches and have a question. I need to do a fulltext search against 2 fields, but order the results so that the results that match field A come before field B. Is there a way to do this? Will it just happen?

Ordering Grouped Results
I'm trying to get a result ordered and then grouped together but it is coming out in the same (not in alphabetical) order that it comes out with without the order. Am I doing this wrong or is it not possible?

mysql> select GROUP_CONCAT("::ID",item.ID,"::NAME",item.Name SEPARATOR '') from item inner join item as Player where item.Container=Player.Container A
ND Player.ID=76 AND item.ID!=Player.ID order by item.Name ;

Ordering Results From Two Different Tables
I have a two tables as below:

Table: Invoices
id | InvNum | InvDate | CustNum | etc....
---------------------------------------------
1 | 100332 |2005-04-03| 1 |
2 | 100333 |2005-04-01| 2 |

Table: Customers
id | Name | Address | etc.....
--------------------------------------
1 | Bill's, Inc | 95 Lane |
2 | Abe's, LLC| |

Where Invoices.Custnum = Customers.id

I am obviously keeping two tables because the multiple invoices may have the same customer.

Anyway, I have a search tool for the invoices allowing the user to search by multiple variables (Such as invoice number, date, etc.) and order the ensuing results. One of the sorting options I need to have is by customers.name asc, invoices.invdate desc. What I need is a query that will give a result set of all the invoices matching the search parameters sorted first alphabetically by the customer's name and then by the date. I am sure that there is a way to do this but I am not very familiar with JOINS. So first does anyone have any ideas? Secondly, can anyone recommend to me a good tutorial on JOINS?

Ordering MySQL Results
PHP Code:
SELECT `name` FROM `contacts` ORDER BY `last_name` , `first_name` , `company`

In some rows, first name and last name are blank, but company is not, others company is blank, but first and last names are not.

In the select query above, rows with first and last blank, but company not are showing up first. The next row could be John Adams, but the first row is Web Smart Computing.

Ordering Results Using Custom Order.. Eh...
bad topic title, sorry.

I don't think this is possible, but it would be cool if it is somehow.
Let say I have the following query:

SELECT some_field, some_field2 FROM some_table WHERE some_field IN(5,4,8,2);
As I understand it, as soon as MySQL finds a row matching the IN statement, it'll be 'added' to the record set. Is there any way to have MySQL return the records ordered by the IN clause?

Something like:

some_field some_field2
---------- --------------
5 blah
4 woo
8 eh?
2 woo 2
Instead of the following, which represents the regular sorting of the table's data:

some_field some_field2
---------- --------------
2 woo 2
4 woo
5 blah
8 eh?
I can rearrange the result set using PHP, but I'd love to know if MySQL offers something for this

Ordering Select Results By Column
I have a table with three text columns and one time column (say text1,
text2, text3, and time). I wrote a query to select the rows where any
of
the text columns contains a certain keyword:
select * from table1 where
text1 like 'keyword' OR
text2 like 'keyword' OR
text3 like 'keyword';

Now I need to order the
results so that rows associated with text1 are listed first, then
text2,
then text3; and also have the results from each column sorted by
time. Is there a way to do this in mysql?

Big SELECT: Ordering Results By Where Matches Are Found
I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful.

I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So...

Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'.
'speakers' is a table of speakers, with id, name and some text fields.
'topics' is a list of topics they address
'speakers_topics' relates the above two by pairs of id numbers
'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id.

I want to search the data in the following order:
name from 'speakers'
topics
text data from 'speakers'
text data from 'articles' and 'other'
...and order the results according to where in that hierarchy a match is found.

So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering.

To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words.

I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient.

Float Number Results
1 table with a float column 'v', having inserted '0.01' into table, a query like 'select * from [table] where v=0.01' returns none of result row. when column type's changed to 'numeric', same query outputs 1 row with value 0.01 of 'v'. is float different of numeric type in mysql or in all DBMS?

Retrieve Results Starting With Number
if I want to retrieve records starting with the letter A, I could do "WHERE field LIKE 'a%'"

But what would I do to retrieve all results starting with anything *but* letters?

Get Total Number Of Results But Still Use LIMIT?
Is there a (better) way to get the toal number of results from a query
but still limit them with a LIMIT clause. For example, in a Google
search result, it displays 1-100 of 10,000,000 results. Do they hit
the database once for results, and again for a total count or is it
all in one query?

Select Lastest Input To Data Base And Select Ordering From Last Member To First Member, Limiting Output Diplay To A Specific Number
how to display the latest members that signup to website, a query that that can select ordering them base on the last registered member to the first... then limiting output by say 50 members.

a sql statement that can select lastest input to data base and select ordering from last member to first member, limiting output diplay to a specific number

Appear 2 Times
i'm having this problem finding out the right sql query.
i have two tables A & B
the userid in table A appears as FK in table B
i need to list userid that appear 2 times or less in table B which has a where clause column3='Yes'

how do i go about it>?

Dates And Times
I have a field in a DB called "customerJobDate" which is configured to store full dates (ODBC dates) e.g. "2006-03-06 08:30:00". Now this does work i can update insert and delete the records perfectly, But if i query the DB asking for all dates that match 2006-03-06 regardless of the time (Date only) then the result returns 0 rows unless any of the dates in the column have 00:00:00 as the time.

This works: 2006-03-06 00:00:00
This does not work 2006-03-06 08:30:00

I hope someone can help me as I am finishing this app and this is the only thing holding me back.

One To Many Multiple Times
I have a 'links directory' database that currently contains 4 tables: category, subcategory, region and links. My links table contains company names and, among others, three fields catID, subcatID and regID. Each of these are related to primary keys in the other three tables, category (catID), subcategory (subcatID) and region (regID).

My issue is that the records in the links table can be in multiple regions, multiple subcategories and multiple categories, but the way I have it designed, each record in links can only be associated with a single catID, subcatID and regID.

Currently the only workaround I can think of it to have duplicate entries in the links table for a single company, but with every variation of catID, subcatID and regID. This will result in a very large table, but I cannot currently think of any better idea.

Upload Times
I need to upload 2 star catalogues to the mysql database. the first time i tried this i filled up the HD in 2 Days, my instructor mentioned that journalling was taking up most of the space and decided to remove-it.

now, with journalling turned off, i have started re-uploading the catalogues, but have noticed a significant decrease in "write" speed.

my question is, does journalling speed up writing data into mysql tables? i have roughly 180GB of data to upload, and it took me 4 days to upload 3GB's when last time it took me this time to upload 100GB.

Dates And Times.
You can't have two timestamp columns in a database both with the default now().
- why I would want to is not the issue.

You can't use the data and time functions, such as CURRDATE()/CURRTIME() provided by MySQL as defaults for date/time type columns.

Is there a way to use something like 'select currtime();' as the default for a column?

so my question is, how do I use ANYTHING other than one timestamp with the default value of now() for date/time defaults in MySQL.

About Dates And Times
I have form with drop down boxes, where folks enter dates and times, I have used implode and explode to format the dates, now I want to write those dates to mysql, I know mysql has its own date and time functions, is there a way to convert my php formatted dates and times into something that mysql will recognize and store as a time and date or does php have a function that will convert it and then allow it to be stored in date/time format in the database?

Records Between Two Times
I have a table for a restaurant database with two fields open_time and close_time in TIME format. I want to be able to select all records that fall between open_time (when the restaurant opens) and close_time (when the restaurant closes).

I have this query:
Quote: Select hours_id from hours where curtime() between 'open_time' AND 'close_time';

It partially works, but its not working if the close_time is after midnight (i.e 1am = 01:00:00 in database). I can't figure out the issue with the difference between AM and PM.

Comparing Times
I'm creating a voting system, and want to prevent people from cheating. Therefore I'm logging their IP, and the time they voted, and what I'm planning to do is have a script which will delete any records which have been in the database for say 24 hours.

To get the length of time I was gonne use:

HOUR(TIMEDIFF(NOW(),time))

Measuring Query Times
I have read a few posts here and see that some people have questions regarding the length of time it can take to perfrom their queries.

I would be quite happy reaching that stage because at least I would know how long mine take. :|

How can the time delay be forced to output? Using MySQL 5.0.

Subtract Two Event Times
I am trying to formulate a complex query to get some good info straight from SQL, rather than post-processing in XL.

I have a table as follows:
EventID Integer (key)
ActionID Integer, 1 = event on, 2 = event off
dtStamp Date

I want to get the duration of each Event.

I.e. I need, for each Event:
dtStamp(actionID = 2) - dtStamp(actionID = 1)

I was going to try:

>create view EventOnTime as select EventID, dtStamp as EventOnDtStamp from EventLog where ActionID = 1;

>create view EventOffTime as select EventID, dtStamp as EventOffDtStamp from EventLog where ActionID = 2;

>select eventOnTime.EventID, eventOnTime.EventOnDtStamp - eventOffTime.EventOffDtStamp as EventDuration where eventOnTime.EventID = eventOffTime.EventID;

... but I can't create views with this version of SQL (server version: 4.0.20-standard-log)

Dump File Times Out?
I have a huge Access database (about 50,000) records. I use Access dumb to create a sql file which I can them go into phpmyadmin and import the records. Well, after a while the process times out. Is there a way I can import large amount of data into MYSQL without timing out?

Also the access database that I convert over gets updated every week. I don't know which records get updated or added. Also the app I am using only works with Access so I can not completely convert over. Is there a way I can update the MYSQL without having to import that huge file again. Maybe something that will not override the records that are same just change the modifed ones and add the new records.

Struggling With Dates And Times!
I'm using MySQL 3.23 - I know it's old but it comes as default on Linux distribs and I just want something consistent between home, my ISP and work.

I want to work out the time difference between two entries on my database (I'm using PostNuke and have two posts which are stored as integers, ie unix times). I can see that there is now a datediff() and timediff() function but they don't work hence why I mentioned the version I'm using.

Can anyone tell me how to get the difference between two dates - I am assuming I subtract one from the other but don't know how to convert the result into something useful (like days and hours etc) because I'm not sure what the unix time is made up of.

I guess an easier version of that lot is : is a unix time just a second count?

Disabling An Entry More Then 3 Times A Day.
Like if you enter 3 times a day the same text. It echo's something instead of writing in the database. Do i need to add a date on where the line was added to it?

Call To Database Times Out
using ZenCart 1.3.7, and when it calls to the database after clicking on "go to checkout" it hangs and then returns the following error message:

I get this message after clicking on "go to checkout"
2013 Lost connection to MySQL server during query in:

[select sum(tax_rate) as tax_rate from (tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) ) where (za.zone_country_id is null or za.zone_country_id = 0 or za.zone_country_id = '223') and (za.zone_id is null or za.zone_id = 0 or za.zone_id = '12') and tr.tax_class_id = '1' group by tr.tax_priority]
2006 MySQL server has gone away in:

[select count(*) as total from sessions where sesskey = 'd60f1f220b761a5a176738c1a65de4b2']

Warning: Unknown(): A session is active. You cannot change the session module's ini settings at this time. in Unknown on line 0

I've checked with my host, and they are able to do a php call to the database just fine.

Adding 1 Year To All Times
suppose that

dbX -> tableX

id columnX
1 1190815874
2 1192975748
3 1196695027
4 1196796789

how can i add 1 year all value where year is 2007 ?

my sql do not run

UPDATE `tablex` SET `columnx` = DATE_ADD(`columnx`, INTERVAL 1 YEAR)
WHERE DATE_FORMAT(FROM_UNIXTIME(`columnx`),'%X')) = 2007;

Slow Loading Times
It takes more than 30 seconds to load the phpmyadmin page. On a localhost server.
It says
Fatal error: Maximum execution time of 30 seconds exceeded in c:appservwwwphpmyadminlibrariesmysql_wrappers.lib.php on line 101

This sometimes also happens when loading my local website that uses MySQL.
I've tried reinstalling, but it doesnt help.

Export Records Times Out
I try to export all records from my db about once a week. I use phpmyadmin and select all tables structure and data and export as file. Well the problem now is it always times out when I do this. The db only contains about 18 tables and maybe 20,000 lines of data. The tables average about 12 fields each. So now I'm exporting a few tables at a time. Is there something I can do to change this?

Left Join 2 Times
SELECT f.id as id, f.forum_name as forum, f.forum_desc as description,
count(forum_id) as threads, u.name as mod
FROM forum_forum AS f
LEFT JOIN forum_posts AS p
ON f.id = p.forum_id
AND p.topic_id=0
LEFT JOIN forum_users AS u
ON f.forum_moderator = u.id
GROUP BY f.id

Output: 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 'mod FROM forum_forum AS f LEFT JOIN forum_posts AS

Does this query contain any syntax errors? Can mysql perform left join 2 times?

Select Same Row Multiple Times Using Column
I've seen this asked before and although someone answered that it is possible, they did not post the solution, so if anyone can enlighten me that would be helpful.

Basiclly with table info

name | quantity
john | 3
anne | 1
will | 2
Return results

john
john
john
anne
will
will

Who Do I Write Select With Dates And Times
i have to cols, date and time. who can i do, SELECT between (date 1,time 1) to (date 2, time 2)?

for example i want to get the recordes between 2007-01-01 23:00:00 to 2007-01-03 03:00:00 ?

How To Find Out How Many Times A Query Has Been Executed
How can you count the number of times your query has been executed? So I'm trying to set it up, so that I get a variable on every time someone views a review.

I have two querys right now, but honestly don't really know what I'm doing. And I'm assuming I have to increment viewcount everytime that query gets executed.

This query looks up the review:

Report Of Query Execution Times
Does mysql have a tool to record and report a list of all queries executed and how long it took to execute each query? If not can anyone suggest a tool I can use to do this?

I've heard of mysql query profiler but does it capture queries from all sessions?

Trace SQL Queries Date/times
I have a web application connected to mySQL 4.1 database. I would like to know the date/time for the queries executed on mySQL DB when any operations performed from the UI.

Is there any mechanism to trace the information related to any query updates happening on the DB when operations are performed from the UI.

Datetime And Timestamp Recording Different Times
I have a datetime field that records date and time. This field records
when rec is created. I also have a timestamp field that records
whenever the record is updated. I noticed that when i first create the
record, the update time is about 3-5 minutes before the create time.
The update time and create time has to be identical. How can one lag
the other? Im executing sql from a php page.

Joining A Table Multiple Times
I was wondering if it was possible to join 2 tables on 3 different feilds, but I don't want the 3 feilds to limit my results... I'll try and explain:

Table 1 = "offices"
Table 2 = "employees"

The offices table has the addresses of all the offices and 3 extra feilds "manager", "supervisor", and "employee". (these 3 feilds have a numeric value equal to the primary key in the employees table)

The employees table has the employee information like address and phone number and such.

I would like to write a query that would pull the office name as one feild, followed by "manager", "supervisor" and "employee".

Currently I have this, but it only pulls the employee and I'm not sure how to get the manager and supervisor to be a part of the query:

SELECT CONCAT(office.id_store, ' - ', office.storename) as offices, CONCAT(user.lastname, ', ', user.firstname) as fullname
FROM office LEFT JOIN user ON office.empoyee = user.id_user

Mysql Result Times Over 15 Seconds
I am having problems with result times. I run a search from my website and it takes around 15 seconds to generate the mysql query and then 40 seconds to load the page.

MySQL Query Times Out From Page But Not Console...
The query works ok when executed from the console, but never executes from the any page. - When I use MySQL Administrator to view the threads this particular thread gets stuck on "copying to tmp table on disk"...

Calculating Time Difference In Minutes Between 2 Times
I have two columns in a table start_time and end_time (stored as datetime). I wish to work out the time difference in minutes between these two values. If the difference is 1hr 30 minutes I wish to get 90 (mins)

I have tried:

SELECT TIME_FORMAT(TIMEDIFF(start_time, end_time), '%i') FROM table ... This returns justs the minutes (e.g. 30)

and

SELECT TIME_FORMAT(start_time - end_time, '%i') FROM table .... no such luck.

Linking Table Entries Multiple Times
I have no idea how to handle certain kinds of relations between tables:

I have a table called 'labels' and a table called 'artists'. Every artist belongs to at least one label, but could also belong to any higher number of them. How would I solve this in my database architecture?

I discarded the idea of simply adding 10 columns called 'label1', 'label2', etc; Also I have found the ENUM and SET datatypes, but the possible values entered there should be set at table creation time which is also not quite what I'm looking for.

Which Is Faster In MySQL? Comparing Unix Times, Or DATETIMES?
I'm leaning towards using unix times inserted into my MySQL for ease of use in manipulating the data with PHP. I think this will be the final decision, which one would be faster?

--

Mysql:
* INT (4 bytes)
* DATETIME (8 bytes)
* TIMESTAMP (4 bytes)

--

My PHP application does alot of querying from the database to compare specific data within TIMERANGES, so always, I'm querying with "time >" or "time <".

My question is would doing the WHERE time query with the native MySQL DATETIME be faster, than doing a WHERE query with the unix timestamp?

Ordering
I've got a few 'course' records with titles and dates. The result set should be ordered like this:

title4|29.10.2007
title2|03.11.2007
title2|07.11.2007
title3|04.11.2007
title1|20.12.2008

So, the records should be grouped by title and *within* the same group they should be ordered descending by date (title2 in example). However, overall the first record should be the one with the closest date (i.e. date descending).

The fact that title3 comes third, is obvious then (I think) because its date is "higher" than the closest title2 record.

Ordering A NOW() Column?
I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DB column using the NOW() mysql function inside the INSERT query.

But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY now_col (which is the NOW() column). Why is this happening? Is NOW() not a good function to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution?

Add-In: The column type is DATETIME