MYSQL :: This Query Freezes When Adding ORDER BY

Mysql Freezes On Startup
I've got a mysql server running on linux. It was working fine until this
morning when a customer wrote and told me it was not working. On
investigation it had frozen. I stopped and restarted it this is on an fc3
box, i get the starting mysql message but never get a prompt back. I don't
see any output in my /var/log/messages.1 file.

Myodbc 3.51 Connector Freezes
I just installed mysql server 5.0 and myodb driver 3.51. Now I'm trying to make a DSN on my system. When I enter the Data Source Name, Server, User and Password, then when I click the Database drop-down menu, the program freezes. It does not do anything, just sits there. I set up the database properly, and it is configured and ready to go but I cannot get past this.

Is Adding Two Fields Together In A Query Possible?
Is something like this possible:

startDate, endDate, stop and eventType are all columns in the database.

SELECT * FROM `calendar` WHERE startDate >= ?' and (endDate + stop) <= ?' and eventType = 'Days'

Adding Literal Text To A Query
What I want to do is select 50 chars of a field and then put '...' at the
end of this selection, eg:

SELECT LEFT(DESC,50) + '...' FROM MyTable

The above doesn't work, but there must be some form of MySQL syntax that
lets me do this - right?

Adding Literal Text To A Query
This is probably very simple, but I just don't know the answer to it.

What I want to do is select 50 chars of a field and then put '...' at the end of this selection, eg:

SELECT LEFT(DESC,50) + '...' FROM MyTable

The above doesn't work, but there must be some form of MySQL syntax that lets me do this - right?

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...

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?

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)?

How Can I Do Proximity Order In Query?
I am writing a search engine that uses mysql to keep a word index. I have just added the "position" of each word, in relation to the document it is in.

My table thus is as follows:

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.

Help With Query: Order By Association Count
Allright, here are my tables:
Posts:
id | title | text
Comments
id | post_id | name | comment
Basically one post may have many comments. Now, how would I select the 10 posts with most comments, and if only 5 posts contained comments (but there are in fact 10 posts) how could I make this query still return a total of 10 posts?

Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given.

I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at.

The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images.

SELECT pic_id
WHERE pic_id < $current_pic_id
ORDER BY pic_id DESC
LIMIT 3
I need the results to show like this:
oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic

I've got the new pics sorted out but the old pics are causing a little problem...

So basically, is there a way that I can reverse the results of my query?

Mixed ORDER BY Query Results
Is there a way to order a query result a specific way?

For instance, I'm querying using

WHERE id IN(23, 25, 19)
I would like the results to stay in that order instead of by ASC or DESC.
Is this possible?

Order By Not Working Correctly, Query Problem?
I am using the following query to get a list of anime names and related info from my mysql db. As anime are sometimes known by several names (the actual japanese kantakana/kanji, the romanji translation and the english word). So the info_anime table has all the info about the anime and the info_animename just has the relevent animeid and the name.

The query below is ment to check all the names in info_animename and join them to info_anime.

SQL
SELECT n.*, a.* FROM info_animename AS n, info_anime AS a
WHERE
a.animeid = n.animeid
ORDER BY ".$_GET[orderby]." ".$_GET[order]."

Now all the names are displayed properly with the various info but if you look here:

Live Anime - Anime List

You will see that the names are not in alphabetical order, for the most part they are just anime with several names are not working correctly if you look at the bottom of the page you will see:

Yakusoku no Basho Kumo no Mukou followed by
Beyond The Clouds, The Promised Place

Which are the same anime under different names.

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).

Query - Top 20 Results In Ascending Alphabetical Order
I have a table which is a list of categories in an online directory.. each time the category is visited in increases the hits column by one..

I want to get the top 20 results (ie. the top twenty most popular categories) and then sort them into a list alphabetically.

I tihnk I have to use a nested select but when I do this I only get the top 20 results sorted by alphabet not by hits..

Here is the table structure:

CODE#
# Table structure for table `category`
#

CREATE TABLE category (
autoID mediumint(9) NOT NULL auto_increment,
title varchar(100) NOT NULL default '',
hits int(11) NOT NULL default '0',
PRIMARY KEY (autoID)
) TYPE=MyISAM;

Grouping For A Query And Limiting Returned Data In Order Of IN()
I'm trying to pull some data from the database in which it's supposed to return 3 rows from each f.forumid in the order in which i have the forumids, however it seems to be returning all the data from the table, what am i doing wrong?

PHP Code:

SELECT t.threadid, t.title, t.lastpost, t.forumid, t.open, t.replycount, t.postusername, t.postuserid, t.lastposter, t.dateline, t.views, t.firstpostid, f.title AS forumtitle, p.pagetext AS preview
    FROM thread t
    INNER JOIN forum f ON (t.forumid = f.forumid) AND t.visible = 1
    LEFT JOIN post p ON(p.postid = t.firstpostid)
    WHERE t.forumid IN(200,250,2,30,15,60,70,90)
    GROUP BY f.title, t.title
    HAVING COUNT(*) <= 3
    ORDER BY f.forumid, t.dateline DESC

Changing Order Of Columns Using Mysql Query Browser
I have a table that is already created and I wanted to add a column, but put it somewhere in the middle of the table. is there a drag and drop (or something just as easy) method to move it up in the table? or do I have to re-create the whole table?

Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query:

SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact
FROM surfacings
INNER JOIN
(SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider)
AS tmp
WHERE surfacings.timestamp = tmp.most_recent;

to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....

ORDER BY WHERE + Include Rest Of Table Or ORDER? Possible?
I need to create some SQL that when run returns all the rows which a column is equal to number that has been specified, but then the query needs to return the rest of the rows in the table. How could this be done? is it even possible? Is their order syntax that could do this better?

Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie.

*** SEE ATTACHED IMAGE PLEASE

I have tried all sorts of SQL statements, e.g:

SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC

Nothing seems to work.

I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ?

I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.

How To ORDER BY DESC Properly. It Makes 150 Before 50 If ASC Order!?
I have this list:
50
150
200
250
300
350
400
450
500

If I order it by ASC order then mysql results this:
150
200
250
300
350
400
450
50
500

What the hell ?? How do I order it properly?

How To Query Records That Have Same Words But Maybe Different Words Order?
I am trying to find a way to do a select query that can find records that have same words in any orders. e.g. "hello good morning", "good hello morning", "morning good hello"... they should all returned by the select query.

Please don't tell me that I can use "or" inside select query. It's because what if the string has 10 words? There are tons of permutations.

How Does MySQL Order Records With No ORDER BY?
If you don't specify an ORDER BY clause, how does MySQL order the records? Is it just the order in which they found in the database?

Dynamic LIKE And Dynamic ORDER BY (was "need Help With Mysql Query")
there are two tables, download, and download_cat
Im using one query to select something from two tables,
PHP Code:

select download.*, download_cat.* from download left join donwload_cat on download_cat.autoid='$_GET[cat]'
and download.cat=download_cat.autoid where donwload.name like '%$_GET[key]%'
order by download.'$_GET[order]' $_GET[desc]

Selection Order Without Order By!?
I have a table with this structure

trans
-------
t_no (int auto_increment)
t_timestamp (datetime)
t_price (double)
t_vol (int)

I've inserted two record into trans table with same t_timestamp value. When i do a select * from trans it doesn't always return ascending order based on t_no? But sometimes it does?

I would like to know how MySql order selection result wihtout the order by command based on this scenario.

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for....

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they
live. In the comments they are also
allowed to put text, that might contain a county listing also. I want to
have the results put the records
that have hcounty matches first, then the records that have a county match
in the comments field after the first set of records. I have tried all sorts
of order by and group by and can't get the result I'm looking for. Any hint
or assistance would be greatly appreciated.

$query = "SELECT * FROM contacts WHERE hcounty ='$searchcounty' or comments
like '%$searchcounty%' order by state, hcounty, dtestart ";

Force Order By Order??
I have 6 rows of data... each has a name, eg: c1, c2, mrgs1, totalfte.
they are all under the one column "field". Is there a way I can force their order??

at the mo, I use "ORDER BY field DESC" and its coming out lile

mrgs1, totalfte, c1, c2

I need

totalfte, mrgs1, c1, c2

is it possible to sort like this??

Custom Order For ORDER BY
I'm grabbing six specific records, like so:

$result = mysql_query("SELECT * FROM casestudies WHERE id=1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 ORDER BY ___________",$db);

I'd like to post them in a custom order, specifically: 1,3,4,5,6,2. Can't figure out how to do this with ORDER BY, if that's even possible. Anything I'm missing?

ORDER BY Out Of Order - Fixed
I have a query that uses two tables that I want to produce output like the following:

+-------------+-------------+-----------+
| LOCATION | GROSS SALES | NET SALES |
+-------------+-------------+-----------+
| Location 1 | 11,860,735 | 2,907,552 |
| Location 4 | 4,814,029 | 1,077,003 |
| Location 3 | 2,711,795 | 710,804 |
| Location 5 | 2,660,040 | 666,255 |
| Location 2 | 2,049,470 | 563,830 |
| Location 8 | 2,227,730 | 543,220 |
| Location 7 | 1,766,880 | 425,483 |
| Location 6 | 1,721,681 | 367,252 |
| Location 10 | 13,424 | 2,253 |
+-------------+-------------+-----------+

However, I cannot get the "order by" part of the statement to work. I have tried adding a zero to the order by alias. And when I attempt to specify the field in the order by with the calculation and not the alias I get an error.

The following examples are queries without the zero and with a zero added to the alias in the order by line. I have also added the details for the table below for reference. Code:

Adding A Value +
This is what I am trying to do in my PHP code:

mysql_query("UPDATE users SET tourneys_1st='tourneys_1st+1' WHERE id='$row[p1]'");

I've tried it both with and without the quotes, but neither work... does this not work at all or do I just have something slightly wrong?

Adding
How could I add 30 onto 'PayDuration' where `Account` = 'neopian', in the fastest possible way using mysql queries.

Adding
I am trying to figure out how exactly to display that someone has signed up for a bunch of specific projects.How would I need to set my tables up?
I would need one for users and one for projects. To add multiple signed up projects a person, would I have to create a new column in the users table for each project or could I store it all in one field somehow?

Adding A Value
I want to add a value to a value currently in the database... I have

INSERT INTO products(quantity) VALUES ((select quantity from products where ProductId = '1')+10) ;

Adding 1
i cant find this anywhre.all i need to do is add 1 to a INT column. (so if the number is 10, i want it to be 11).
sure, i can select from database, add 1, then update the database. but figure there has to be a better more effecient way of doing this.

Adding Images
How can i get people to add images for me automaticaly? I have a site that i wish people to have the ability to add images to They need to be in two different sizes. Would i get them just to send in an email and sent to me, or i htere a way of getting the, to do it?

Adding To Value Of Column
Hi I am new to mysql. Is there a way to add to a field instead of just replacing it. I want to create a query that adds an input to what is already in the table. So for instance if I already have 2 of a product in stock and get 3 more of that item in i want my the stock column of my table to equal 5.

this is what i have now:

db_query ("UPDATE products SET stock='$newstock' WHERE productid='$pid');

Adding Birthdate Via Php
I'm playing around in the dark. I've created the necessary columns in MySQL and one is "birthdate" and so far I can only get the year entered and not the month or day. I've searched some sites and come up with the variation below and I don't know if I'm on the right track. If it is wrong can somebody correct it please? ....

Adding To A Where Statement
I have a select query that gets put together by use checkbox selection. That works okay, however I am now trying to add between two dates in the query.

//$from_date = "2006-01-01";
        //$to_date = "2006-09-24";

        //$where=$where AND date_added BETWEEN '".$from_date."' AND ".$to_date."'";

I cannot seem to get the correct syntax for the above to work. Tried many permutaions but cannot get there. Anyone know how to do it.

Adding A New Column
To ADD a new column I can say that I want it AFTER some named column.

But what if I want this new column to be the first, please, Experts?

Adding Values?
How can i add the values of different rows in mysql using php?

ie:

id field!
1 3
2 2
3 4
4 2
5 6
6 20
7 7
8 5
n 1
-----------------
total = value of id#1 upto id# n.

for this example total should be = 50

Adding New Column
After i added a new column to my existing table, i cant insert any information using the same form i was using all along. if i remove the column (drop) then i can use the form fine..

Adding New Users
I can't seem to add a new user to my database! I've tried it through phpMyAdmin (under privileges) and also running the query

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

which returned no errors and according to phpMyAdmin the user is entered into the `users` table. But when I try to login with that name I get

Access denied for user: 'myusername@localhost' (Using password: YES)

So all I can use to login is 'root' with no password. Not very secure!

Avoid Adding More Than Once
im trying to do what i think is a fairly easy query but im having some problems.

basically i want to only include a certain row of data in the results if it has previously met a certain criteria. however if it has met the criteria more than once i still only want it to return once.

Adding Two Fields Together
I have two fields, on called Price one called Qty, i want the Third Field to be Price * Qty.

Is there any way of doing this.

Adding 1 To An INT Fields Value
What is the SQL command for adding 1 to a specific field. This all that I can come up with:

UPDATE poll SET av1 += 1.

Adding Entries
I've got a database with approx 300 entries but i've got another 1000 to input and was wondering if anybody knows of a way (apart from using Microsoft Access) of entering the data into the table without having to complete each entry individually as I have all the information on my hard drive.

Adding To A Field
I need a MySQL script that will add a number to a field. Every time you click a button or something, in database "Database1" and table "Player1", the field "Test1" will then add a "5, " to it. So the first time it is "5," then "5,5," then "5,5,5," . I can't seem to get it to work.