MYSQL :: SELECT And UPDATE In One Order.

Order By Something I Don't Want To Select?
I have a table something like this for all intents & purposes:

id, date, player, opponent, time
1 1/1/07 joe jeff 8:00
2 12/31/07 joe jeff 7:00
3 1/1/07 bob jim 7:00

Now I can search the table using date and I need to return an output like:

bob 7:00
jim
joe 8:00
jeff

Looks easy, but bear with me. I want to return the results 2 rows per record as you can see above so I use a UNION query:

(SELECT player AS name, time FROM table WHERE date = Ƈ/1/07') UNION (SELECT opponent AS name, '' AS time FROM table WHERE date = Ƈ/1/07')
The problem is I want to keep Bob & Jim together and keep Joe & Jeff together while sorting by time! Since time is selected as '' (empty string) for the 2nd of the two players, I cannot do this because it will output like this:

jim
jeff
bob 7:00
joe 8:00

Now it would be easy to do if I were to just select id in each query and order by id, however I would rather not select additional columns. The reason is that this query is part of a very intricate program which takes each selected column and uses that in the output and I don't want to output id so I only select the columns I want to output. For the same reason, I select '' AS time for the 2nd player so the output isn't repetitive. I'm thinking I may have to modify the whole script though given this dilemma.

Select A Row By Its Order In Db, Not By Its Id
i have generated a random number and need to select a row from db that is that random position. because row id's can get out of order, i don't want to do it by selecting a row with specific id.

i want to count from top and select teh row that's at that number position.

SELECT With An ORDER BY
i'm trying to have a select statement that will order the results in a certain way. if the field realname != "none" i want it sorted by realname, otherwise custom_name

here is how i am trying to do it:

$sortby = "IF realname != 'none' THEN realname ASC ELSE custom_name ASC ENDIF";

$sql_query = "t1.store_id, t2.id, t2.realname AS realname, t1.custom_name AS custom_name FROM details AS t1, stores AS t2 WHERE t1.store_id = t2.id ORDER BY $sortby";

i get syntax error so i think i'm doing the IF statement wrongly but i can't figure it out. the mysql manual doesn't have any real examples for this.

Select ORDER BY
I want to:

SELECT * FROM messageboard_links WHERE fatherid = '0' ORDER BY ?????????

Ordered by the results of this query:

SELECT id FROM messageboard_links WHERE fatherid != '0' AND categoryid = '$categoryid' ORDER BY date DESC

This is what I've got so far, but obviously the order by isn't working (or I wouldn't have to ask )

$query = "SELECT * FROM messageboard_links WHERE fatherid = '0' ORDER BY ( SELECT id FROM messageboard_links WHERE fatherid != '0' AND categoryid = '$categoryid' ORDER BY

SELECT And Order By
I have 2 tables:

"users" Table which contains:
ID, Username, name, password...etc..

and and "rates" table which contains:
ID (user's id from above table), total, votes.

where total = the total score, and votes=number of votes.
each rating is coresponded to the user in the users table, so what I want to do now is list all the users from table users order by the rating of "rates" calculated by "total/votes"
is that possible using 1 SQL query? (thats if you understand what im saying at all

Select Distinct Order
I have a database containing my server log files. A sample of the rows would be similar to:

+--------------+------------+-----------------+--------+
| ip | time | resource | status |
+--------------+------------+-----------------+--------+
| 195.93.21.71 | 1112868867 | /about/ | 200 |
| 195.93.21.4 | 1112868869 | /css/normal.css | 200 |
| 195.93.21.2 | 1112868872 | /css/print.css | 200 |
+--------------+------------+-----------------+--------+

Some of the IPs and resources will be the duplicates in the full table. I can select out the number of unique IPs and resources with the following query:

SELECT COUNT(DISTINCT ip) FROM stats

But what I now want to do is find which resource has been requested the most. By this I mean extract the distinct resources (one of each) ordered by number of times it occurs. I've been trying variations on the following with no luck:

SELECT resource from stats ORDER BY COUNT(DISTINCT resource)

Can anyone tell me if this is even possible and/or point me in the right direction to go?

Random Order Select
I have a user database which I use for meetings. What it does is it creates notes for everyone thats going to be in the meeting. Right now, the order people give notes is dependant on the order the select statement returns (right now I have it as desc using the ID#). My boss would like it if it could "randomly" arrange the users. i.e

Day 1:
1 - Bob
3 - John
2 - Beth
5 - Susie
....

Day 2:
5 - Susie
2 - Beth
1 - Bob
3 - John
....

etc.. So no one feels like they are being picked on or it doesn't get stagnant.

What would be nice would be "Select blah blah Order by Random" but I don't think thats an option. Is there anything in mysql to do this or is this something I'm going to have to manipulate after I have the data out of the database?

SELECT * ORDER By Date
There is a column table containing dates in this format DDMMMYY like 10MAY08. Is it possible to Select * Order by this column and get rows sorted by date?
Something like convert 10MAY08 to 20080510 before sorting.

Best Index For Select With Where/order/limit
CREATE TABLE `db`.`messages` (
`unixtime` int( 10 ) NOT NULL default Ɔ',
`username` varchar( 20 ) NOT NULL default '',
`sender` varchar( 20 ) NOT NULL default '',
`content` mediumtext NOT NULL ,
KEY `username` ( `username` ) ,
KEY `unixtime` ( `unixtime` )
) TYPE = MYISAM ;
This is the kind of select that will be used a lot :

SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY unixtime DESC LIMIT $x,10
username has an index on it, because of the WHERE part. Unixtime has a time(); value in it wich I use to display the date, and because I also use it for ordering by date it also has an index.

I dont think this current setup is wrong, but it could be improved, and I want to know if the following things are right :

I could add an 'id' int (10) column, and set it to auto-increment and primary key and drop the key on the unixtime column. That way the messages will already be stored by date (new messages will always get a higher id value), and for mysql it will be easier to find the 10 posts that I want. I can not simply change 'unixtime' to a primary key, because on busy moments there is a real chance two users might send a message at the exact same second, resulting in a non unique value for that column.

SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY id DESC LIMIT $x,10
It will use the key of username to find all messages for this person, and it will then use the primary key to just select the 10 messages that are wanted. Those messages are already ordered by id (thus by time) when reading them so it will not need an extra sorting process ? unlike the ordering by unixtime wich probably requires all rows of that person to be read, and then all of them to be sorted to find the 10 that are wanted. So ordering by primary key would be faster and/or use less memory ?

I could go even further and also drop the key on username, and instead have one primary key consisting of ('username','id') , with the id still an auto-incrementing INT. This way there is only one index that has to be loaded, wich can be used for both the WHERE and ORDER BY.

The table would be like this then :

CREATE TABLE `db`.`messages2` (
`id` int( 10 ) NOT NULL AUTO_INCREMENT ,
`unixtime` int( 10 ) NOT NULL default Ɔ',
`username` varchar( 20 ) NOT NULL default '',
`sender` varchar( 20 ) NOT NULL default '',
`content` mediumtext NOT NULL ,
PRIMARY KEY ( `username` , `id` ) ,
) TYPE = MYISAM ;
I have done some testing , and used 'explain' too. With my first table it uses the username key and a filesort, on the second with the double primary key it reads a bit more rows, but its not using filesort anymore... so that should be an improvement ? I think it is, but would like some confirmation before I change it on dozen tables in the live database. And are there any other changes in index that would be even better ?

SELECT/ORDER BY/GROUP BY Question
Just wondering if anyone could help me.
I am looking to order my results in the following way:-

SELECT * from TblResults ORDER BY 'first', 'second', 'third' //or something similar

so my results look like

RESULT1 | first
RESULT5 | first
RESULT2 | second
RESULT4 | second
RESULT3 | third

Select Data From Two Tables And Order By One Of Them
I have two table book and book_images. book contains a list of books and book_images is a table that lists the books and the assigned image (there can be more than one image assigned to more than one book);

The process to assign a image to books is this. User selects the image. then from the list of books selects which one/ones they want to assign it to.

As there are over 7000 books i want to add a search. so when someone types in "Vellum" then the book Vellum will be returned. But the problem is i would also like the books that are in the book_image table.

Select, Join, Order Assistance
I have 2 tables similar to

T1
-------------------------------------------
ID | initial |

T2
------------------------------------------
initial | full_name |

I want output based on the T1.ID row which has a given ID=' X '
(If ID = 7 then pretend initial = initial3, as example)

The first or last row of output to be

initial | full_name expanding the T1.initial from T2 where T1.ID=' X '
then all subsequent rows from T2

In a rough sense would need to look like

T1.initial3 | T2.full_name3 where ID=' 7 '
T2.initial2 | T2.full_name2
T2.initial1 | T2.full_name1
T2.initial4 | T2.full_name4

Select Union Order Limit
I must select all the rows of a table and sort them in decreasing order by insertion date. The first rows that must be printed should have a certain value in relation with the user_id

this is my table:

user_id - user_regdate - username

1 - 1000 - fabio
2 - 1300 - andrea
3 - 500 - alessandro
4 - 300 - pippo
5 - 900 -chiara

i must get

5 - 900 -chiara
3 - 500 - alessandro
4 - 300 - pippo

2 - 1300 - andrea
1 - 1000 - fabio

i succeeded with this query

$query_singles_on = "(SELECT user_id , username, user_regdate FROM `phpbb_users` WHERE `user_id` IN(3,4,5) ORDER BY user_regdate DESC)
union
(SELECT user_id , username, user_regdate FROM `phpbb_users` ORDER BY user_regdate DESC) LIMIT $start,$step";

and locally, on mysql 4.0.15 it works perfectly and without glitchs.
problem is, remotely, on a 4.1.11a-4sarge7 doesn't work and I get this (wrong) result:

3 - 500 - alessandro
4 - 300 - pippo
5 - 900 -chiara

1 - 1000 - fabio
2 - 1300 - andrea

: they are not sorted by decreasing user_regdate.

Order A Union Select Using Joined Table Row
I am having trouble sorting this select using the "model name" which is from "inventory_model.model".

The following gives me this error "Table 'm' from one of the SELECTs cannot be used in global ORDER clause".

If I removed the order line (ORDER BY m.model DESC), the query works fine with no errors, but the results are not alphabetical, they are rather random.

PHP

$selectM = mysql_query("
(select i.id, i.Model, m.Model
   from inventory i, inventory_category c, inventory_model m
  where (i.catid=c.id AND c.parent=ཫ') AND i.Make='$id' AND i.Model=m.id AND i.I_IC_C_S!='S' AND i.N_U_R_C!='C' GROUP BY i.Model)
  union all
(select i_sub.id, i_af.model, m.model
   from inventory i_real
   join inventory_alsofits i_af
     on i_af.itemid = i_real.id
   join inventory i_sub
     on i_af.itemid = i_sub.id
   join inventory_category c_real
     on i_real.catid = c_real.id
   join inventory_model m
     on i_af.model = m.id
  where i_af.make='$id' AND c_real.parent=47 AND i_real.I_IC_C_S!='S' AND i_real.N_U_R_C!='C' GROUP BY i_af.model)
  ORDER BY m.model DESC
  ") or die(mysql_error());

Limit On Select Produces Records Out Of Order
I have a Java applet that displays descriptions (from an sql table) in a text area. The latest description is shown on load-up with max(time) ok, and there is a button to step through them in reverse order from latest to earliest.

I have a problem with the following query:

SELECT idescription FROM descriptions where unit = 'A' and iname = 'plate' order by 'time' limit "+nextrecord+",1

This query is executed on a java button press. The variable 'nextrecord' has already been set to 1 (to get the second latest, i.e. 'very useful item') and then 1 is added to it each time the button is pressed to get the next previous one. But the resulting order is squiffy, e.g. when applyed to the following table Code:

How Do I SELECT Records From A Database In Alphabetical Order
how to SELECT records from a database in alphabetical order,

'order By Alternating Table' Results And 'begins With' Select
I am just starting to learn SQL and have been working on a couple of questions and yes this is homework related. I am having trouble with 3 questions. I have the basis for the answers but am stuck on a couple of points.

I don't expect a direct answer but a push in the right direction would be helpful. Also, if anyone can recommend additional resources for assisting with learning SQL, that would be great. The first question is as follows:

A company is preparing invitations to its annual holiday celebration and wants to invite its sales staff (everyone in the SALESREPS table) and its customers (everyone in the CUSTOMERS table). Write a SQL statement that returns output looking like the following (including the sort order): Code:

SELECT And UPDATE
I was wondering if it is possible to combine a select and update command.

Basically selecting a single row and returning it; while updating a value in that same row?

Update & Sub-select
I'm getting the error msg "you can't specify the target table 'orders" in the from part of an update" when executing the following query

update orders set status='READY'
where status='PENDINIG' and parent_id in (
select p_orders.id from orders p_orders where p_orders.status='OK')

Does this mean that I can't do a sub-select on the same table as I'm updating in MySQL (I'm using ver 5.x) ?

Would anyone know a way around this - I'd basically like to update an order once its parent order is in the state 'OK'.

Ref:

create table orders (
id bigint(18) unsigned not null auto_increment,
status varchar(32) not null,
parent_id bigint(18) unsigned,

constraint fk_order_parent foreign key (parent_id) references orders(id),
primary key (id)
);

insert into orders values (null, "SENT", null);
insert into orders values (null, "OK", null);
insert into orders values (null, "PENDING", 2);

UPDATE Using SELECT
Coming from an Oracle background I am used to doing this:

UPDATE accomodation a
SET a.country = (SELECT c.country
FROM country c
WHERE c.country_id = a.country_id);

I could not get this to work so looked up the syntax for this:

update accomodation a
join country c on a.country_id=c.country_id
set a.country=c.country;

Which doesn't work either !!! Basically I am trying to do an update using a select from another table rather than a static value but cannot get it to work. Is this possible in MySQL ?

Update And Select...
Can I do an update and select in the same query?

I want to show a record and also update the "times it was viewed"...is that how I'd do it?

Update And Select
an update that doesn't affect any records. As a test, I switched it to a Select statement. Both statements have the same where clause. The select statement returns the number of rows I expected to update. Here are both queries. Any help greatly appreciated.

UPDATE program_event set measure=239, measure_date='2007-01-25' WHERE program_goal_id = 112 AND measure_date >= '2007-01-22' AND measure_date < '2007-01-27'

select * from program_event WHERE program_goal_id = 112 AND measure_date >= '2007-01-22' AND measure_date < '2007-01-27'

Update And Select
Code:

"UPDATE `links` SET
`in`=`in`+1
WHERE `url` LIKE '%google.com%' LIMIT 1"

alright, so i want to a) update the link to in+1, but also b) return a variable called 'user' containing the 'id' of the link, all in one statement because it is faster and gives me the same link's id that was updated. is it possible?

Select + Update
I managed to do something like this:

$query = sprintf("SELECT CONCAT('UPDATE articles SET visits=(visits+1) WHERE title='".$name_article."'')
FROM articles a
LEFT JOIN autors b ON a.autor = b.name
WHERE a.title='".$name_article."'");

$result = mysql_query($query);

Without the part underlined, it works pretty good. What I want is to be able to select all columns from the article table and autors table, based on the article name, and, at the same time, update the visits field in the article table. I really dont know if its even possible to do that in one single query.

SELECT But Not UPDATE
If I have used SELECT to review a row of data from a table and then only want to allow it to UPDATE certain fields, even if the other fields were changed, would I use the UPDATE command, and choose which fields can be updated? Will this stop the other fields from being updated? I am trying to prevent people from changing certain values in the table.

SELECT And UPDATE
I'm a newbie in MySql, so please bear with me. What I want is to select all the informations concerning a specific article, and then to update a field of the resulting query. So, with some help I managed to do something like this

CODE$query = sprintf("SELECT CONCAT('UPDATE articles SET visits=(visits+1) WHERE title='".$name_article."'')
                 FROM articles a
                 LEFT JOIN autors b ON a.autor = b.name
                 WHERE a.title='".$name_article."'");

$result = mysql_query($query);

Select Update
I am trying to populate a column in one table using a calculation from values in another table.I have got something like this.
Code:

update orders_header
set od_gross = (SELECT sum((price * qty) + vat) FROM orders_detail)
where orderref = 'COR0010-000061'

Select And Update In One Query
My remoteurl table has 3 columns: id, hits and url.

PHP

UPDATE remoteurl SET hits=hits+1 WHERE id=2

SELECT url FROM remoteurl WHERE id=2

Instead of using two queries I want to use one query which could select url and update hits column. Is it possible?

Select + Update In One Statement
I have these statements.

$selectdata = "SELECT * FROM imagetrack WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

$query = mysql_query($selectdata) or die(mysql_error());
$update = "UPDATE imagetrack SET seen = seen+1 WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

Basically i was wondering if there was a statement where i can select everything and update the seen in one statement.

Atomicity Of A SELECT/UPDATE
I want to get any one of a number of rows that matches some criteria and
update it so that it is marked as 'mine'. How can I do this safely?

Given something like this table:

create table tbl_new (
t_value varchar(16) primary key not null,
dt_used datetime,
l_pid int(9)
);

I want to get any t_value WHERE dt_used is null and then set dt_used to
prevent anyone else getting the same t_value.

If I use locking (but I'm using C and I don't see any locking functions)
(nor a START TRANSACTION?) I could: Code:

Select And Update Statement
Is it possible to combine a select and update statement in mysql? If
so, what is the syntax.

Select And Update Concurrently
I have a db used for shop floor data collection that I have converted from a Progress V8 RDBMS. It is recording some 50 data points from each of 6 shop floor machines every 5 seconds. The problem I have is one of incremental backup of this (the main data logging) table. In the old system, there is a LOGICAL (boolean) flag field that is set (in code) when the data records have been copied to the backup file (could be another table). I can't seem to find in MySQL documentation or in these forums, a way to select and update in the same query.

What I would like to do (in as plain english as possible) is:
Select all records from the source table where the 'copied' flag is not set, and send them to a destination (which could be a flat file or another table), simultaneously setting the 'copied' flag in the source table.
I thought of doing this in two passes, selecting the records first, and outputting them, then setting the flag, but the problem this poses is that there may (probably will) be fresh data written to the table during and between the passes. This will cause records to flagged as copied that have not actually been backed up.

SELECT And UPDATE In One Query
Does there exist a way to do a SELECT and an UPDATE in one query?

What I am trying to do is building a shop that´s articles are saved in a mySQL db and I wanna count the impressions of each article that is shown by e.g. a search function.

would be something like that:

article no | text | category | impressions
1 | aaaa| 1 |1
2 | bdsd| 2 |1
3 | aaaa| 2 |1
4 | bdsd| 3 |1

and so on

now I want e.g. to search for all articles that are in category 2 AND add +1 to the impressions cell of those articles that are found.

I hope it´s understandable what I am trying to do. I guess it should be possible, but since I only so far used very simple queries I am not sure how to?

Update With Embedded Select
What is wrong with this syntax ? Using MySQL 3.23.51-nt
UPDATE products SET manu_release_date = (SELECT dates.reldate FROM dates, products WHERE dates.id = products.stock_code )
I get the error:
Error MySQL said:

You have an error in your SQL syntax near 'SELECT dates.reldate FROM dates, products WHERE dates.id = products.stock_code )' at line 1

Select, Update, Insert. Efficiently?
First, I want to say that I didn't design the databases and I wish I could fix them, but I can't.

There is a table, s01_Baskets, that tracks the users basket throughout my store.
There is a table s01_StoreKeys, that holds the next ID for every field used in tables. In this case I'm interested in is the basket_id.

What I need to do is create a new "basket" for a customer. To do this I need to select the key for this basket_id:

SELECT
s01_StoreKeys.maxvalue
FROM
s01_StoreKeys
WHERE
s01_StoreKeys.type = 'Baskets'
Then I can "create" a basket by inserting a row:

INSERT INTO
s01_Baskets
(
s01_Baskets.session_id
, s01_Baskets.cust_id
, s01_Baskets.basket_id
, s01_Baskets.order_id
, s01_Baskets.order_proc
, s01_Baskets.last_update
, s01_Baskets.ship_id
) VALUES (
'".session_id()."'
, Ɔ'
, Ɔ'
, Ɔ'
, Ɔ'
, '".microtime()."'
, Ɔ'
)";
Then I have to update that field for next key for the next basket.

UPDATE
s01_StoreKeys
SET
s01_StoreKeys.maxvalue = '".$basket_id+1."'
WHERE
s01_StoreKeys.type = 'Baskets'
UGH. I'm not thrilled doing it like this (one bad query, or if another process jumps in at the wrong time, I've messed up the baskets....)

Is there a better query for this? I'm pretty sure I can do the insert and select as one, but I need the basket_id(+1) for the update also. I don't know of any way to do all three queries in one. Can anyone see a better way to do this queries (ignore the poor design)?

SSH: Select Database And Update Rows
I'm trying to update a few rows in my database via SSH. I have multiple databases on my server and I need to know what I'm doing wrong. I login via SSH and enter

$: mysql -u root -p

I want to select the database and then run my update command, but when I type. > select [db_name] It just jumps to a new line without any prompt. Can anyone tell me really quick when they do to select a database?

Using Limit With Select Or Update Command
How come I can do:

select field1 from database limit 10
*shows first 10 records like it should

select field1 from database limit 1,5
*It returns first 5 rows correctlly

update database set field1='first set" limit 10
*It correctlly updates the field for the first 10 records

update database set field1="top 5" limit 1,5
*I get an error that says problem with limit at ^5 check command for correct syntax

Is there different syntax for limit when using with a select statement Vs. an update command?

Nested Select In Update Statement
my nested Select-Update-Statement doesn't work. I think it's because the SELECT-Statement returns an empty set.. is there a posibillity to run this statement if there is no empty set and insert NULL if it is? Or is there another error at all?

CODEUPDATE proj_user_suche SET acronym = (SELECT name FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23") WHERE id_proj_user_suche="17"

Convert SELECT Statement To UPDATE
How do I convert the following SELECT statement to UPDATE statement? If I'm not mistaken, UPDATE statement could not involve more than one table.

SELECT * FROM F0116 LEFT JOIN F0101 ON F0116.ALAN8=F0101.ABAN8 WHERE F0116.ALCTR='' AND F0101.ABMCU='1'

Limitations In Mysql Select/update Statements
I'm working on several tables at one time and once in a while I need to
update them based on a previous select statement.

This ends up with 3-5 statements but every single time I do it this way,
mysql chokes after the 3rd statement, no matter what order.

I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27.
Everything else works like a charm but not these 3-more statements.

Limitations In Mysql Select/update Statements
I'm having a little problem here that seems difficult to solve - to me.

I'm working on several tables at one time and once in a while I need to
update them based on a previous select statement.

This ends up with 3-5 statements but every single time I do it this way,
mysql chokes after the 3rd statement, no matter what order.

I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27.
Everything else works like a charm but not these 3-more statements.

I assume it's something about configuration, but unsure.

Any ideas?

Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this:

create table myTable(
token varchar(255) not null primary key,
token_count int default 1,
frequency int
) type=INNODB;

A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic:

(1) See if the String to be inserted is present in myTable.(Uses SELECT)
(2) If not present the insert into myTable (uses INSERT)
(3) If yes update the table after adding the current frequency (USES UPDATE)

After processing each file issue COMMIT.

This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key.

My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance.

A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency.

After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).

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.

SELECT And UPDATE In One Order.

i would like to select some random lines from a table and count that they were selected. My method: SELECT * FROM table ORDER BY RAND(Now()) LIMIT 50; while ($row = entry) do UPDATE SET Used=Used+1 WHERE Id=$rowd[Id] I dont think this is realy fast so i dont like it, is there a faster method?

i would like to select some random lines from a table and count that they were selected.

My method:

SELECT * FROM table ORDER BY RAND(Now()) LIMIT 50;
while ($row = entry) do
UPDATE SET Used=Used+1 WHERE Id=$rowd[Id]

I dont think this is realy fast so i dont like it, is there a faster method?