aviddv1

Thanks for the help guys.

Hey there, I've got three tables. One table, t1, has a list of attributes like work, home, image and swf. t1: +----+----------+ | aid | attribute | +----+----------+ | 1 | work | | 2 | home | | 3 | image | | 4 | swf | +----+----------+ The second table, t2, has a list of ids relating t1 to t3. nid 3 appears twice because it is related to both the 'work' attribute as well as the 'swf' attribute. t2: +----+----+ | nid | aid | +----+----+ | 1 | 3 | | 2 | 4 | | 3 | 4 | | 3 | 1 | | 4 | 3 | +----+----+ The third table, t3, has a list of my articles and sort orders. There are four articles. t3: +----+----------+---------------+ | nid | name | sort_order | +----+----------+---------------+ | 1 | article 1 | 1 | | 2 | article 2 | 0 | | 3 | article 3 | 0 | | 4 | article 4 | 2 | +----+----------+---------------+ I want any records associated with the two location attributes (home or work) to come first in the results followed by the records associated with image or swf ordered by their sort order. I also want to show the attributes as location and type. records that aren't associated with a location should just show null. If I run a query based on t2 I will have five records where one record is a duplicate. I'm looking to eliminate the duplicate record. desired results: +----+----------+------------+-------------+ | nid | name | location | type | +----+----------+------------+-------------+ | 3 | article 3 | work | swf | ---> comes first since it's a location attribute (home or work) | 2 | article 2 | null | swf | ---> sorted by sort order in t3 since there is no location association | 1 | article 1 | null | image | only image or swf for nid 1,2,4. | 4 | article 4 | null | image | +----+----------+------------+-------------+ Hope this wasn't too confusing. I've tried grouping. I've tried distinct. I'm a moron. Thanks, Howie

I don't know how, but the query I posted will actually list all the projects correctly. The first projects listed are associated with the current user and they are sorted by modification time descending.  Then all of the other projects (ones the user has not modified, but the user's company own's) are sorted by the project name. So effectively you've got company projects modified by the user ordered by mod time then the rest of the company's projects ordered by the project name. like I said it's getting so complicated I'm not even sure why I need this functionality.

that's what I'm thinking.  I just thought maybe there was a more efficient way of doing it. This is what I have now and somehow it appears to work: [i] select * from (select p.project_id, p.project_name, p.project_client_id, wo.work_order_id, wo.work_order_name, t.task_id, t.task_modified, if (t.task_user_id=1,1,0) cur_user from projects p left join work_orders wo on wo.work_order_project_id=p.project_id left join tasks t on t.task_work_order_id=wo.work_order_id where p.project_active=1 order by p.project_name, t.task_modified desc) p, users u where p.project_client_id=u.user_client_id  group by p.project_name order by p.cur_user desc, p.task_id, p.work_order_id, p.project_name[/i]

Hey there, I've got a pretty complex situation for some reason, but I'll try my best to describe it.  I have 3 tables: projects, work orders, and tasks.  Each task is related to a work order and each work order is related to a project.  Each task has a modification timestamp.  I'm trying to return list of all the projects.  At the top of the list would be all the projects with work orders and at the bottom would be projects without work orders.  The first set with work orders would be ordered by the task modification time in descending order so that the project with the work order with the most recently modified task would appear first.  Here's there query I have so far: [i]select p.project_id, p.project_name, wo.work_order_id, wo.work_order_name, t.task_id, t.task_modified from projects p left join work_orders wo on wo.work_order_project_id=p.project_id left join tasks t on t.task_work_order_id=wo.work_order_id where t.task_user_id=1 and wo.work_order_active=1 and p.project_active=1 order by p.project_name, t.task_modified desc[/i] I want each project to appear only one time.  I tried adding a group by clause using p.project_id, but grouping before the order means task modification time isn't factored in beforehand.  I'm looking for a way to first order the records then group the results. You can check out mysql source files here: [url=http://ws.2wsx.ws/tables_dump.zip]http://ws.2wsx.ws/tables_dump.zip[/url].  Any ideas? Thanks, Howie

Hey there, I've got two tables.  One table is called 'Press' the other is called 'News'.  Both tables have a datetime field.  I want to query both tables and return the record with most recent datetime.  Other than the datetime field none of the fields match. Thanks, ward

Hey there, I've got a table with a field called sort_order.  I want to be able to change the sort order of a particular record and move all the other affected records either up or down. so if I've got 1,2,3,4,5 and i want 3 to be 1 then it would do this: 3 => 1 1 => 2 2 => 3 4 5 any ideas?  I'm sure I can use a loop in php, but I'm wondering if there's a more efficent way using mysql. thanks, Howie

I'm trying to order a set of records with a field called "date" so that any records where date is >= now() will show first and be in ascending order.  Any records where date< now() will show after the first set and be in descending order. so if today is 10/05/2006 it would look something like this: 10/05/2006 10/15/2006 10/31/2006 10/04/2006 9/28/2006 9/15/2006 9/01/2006 Any ideas? Thanks.

Hey there, I'm trying to write a query that will select 1 random row from a table only if the value 0 does not exist in a field called random.  If 0 does exist then i want it to return that row instead of randomly selecting one. thanks, ward

Maybe I'm a moron or something.  I can't figure out the most efficent way to do this. I've got a string: "the test string contants a link. [link]page.php||id=1||click here[link] to see the link." I want replace [link]...[link] with something like: <a href="<?php echo make_link('page.php','id=1'); ?>">click here</a> Basically I'm trying to embed a link in a field in a mysql table and then pass the link info to a function called make_link that will add some other parameters and return the full url. How on earth can I do this?  Any good ideas? Thanks, ward