gin

Oh, my mistake, I misread the question

AUGH, I apologize! I was looking at the wrong column used for sorting! It wasn't numbers vs letters at all. it was numbers vs. NULL. Below is my solution. Actual table SELECT * FROM table ORDER BY sort; +------+ | sort | +------+ | NULL | | 1 | | 2 | | 3 | +------+ Placing the null at the bottom: SELECT * FROM `table` ORDER BY (CASE WHEN sort IS NOT NULL THEN 1 ELSE 0 END) DESC; +------+ | sort | +------+ | 1 | | 2 | | 3 | | NULL | +------+

It works... for this example anyway, and I have no idea why... Could you elaborate? I'm trying to apply this to my actual query, and it's not working at all

Use a LEFT JOIN. Something like so: SELECT * FROM $day LEFT JOIN sip_data ON $day.id = sip_data.id ORDER BY zip This will make data on the right table only appear if there's data on the left table.

I have a table: SELECT * FROM table ORDER BY sort; +------+ | sort | +------+ | 1 | | 2 | | aaa | | bbb | | ccc | +------+ I want to make the numbers appear after the letters, like so: +------+ | sort | +------+ | aaa | | bbb | | ccc | | 1 | | 2 | +------+ Please advise.

Thank you, good to know! One more question. Which would be less strain on the server: my first query above, or doing the calculations after?

Observe the query below, not inserted as code so you can see the colours. The two lines in red are identical. ======================= SELECT IF ( qtype<>2, package, package*( SELECT usd_conv FROM quotes_default def WHERE def.date <= DATE(modified) ORDER BY def.date DESC LIMIT 1 ) ) as ptotal, IF ( qtype<>2, gpackage, gpackage*( SELECT usd_conv FROM quotes_default def WHERE def.date <= DATE(modified) ORDER BY def.date DESC LIMIT 1 ) ) as gptotal FROM jobs j ======================= Basically, I'm wondering if there's a way simplify this. Something like below, which I know doesn't work, but you get the idea? To put the results of the red sub-query into a variable, then use the variable elsewhere. ======================= SELECT (SELECT usd_conv FROM quotes_default def WHERE def.date <= DATE(modified) ORDER BY def.date DESC LIMIT 1) AS rate, IF ( qtype<>2, package, package*rate ) as ptotal, IF ( qtype<>2, gpackage, gpackage*rate ) as gptotal FROM jobs j =======================

Use a LEFT JOIN instead of an INNER JOIN?

Thank you both for your advice! mikosiko's suggestion works, and is much easier for me to understand to boot I didn't know sub-queries could be used like that.

No, I think you're thinking of one of the other languages where the formatting comes at the end. For MySQL, the formatting happens when you call the field. Ah, my mistake, here it is: SELECT DATE_FORMAT(shopdate,'%b %D, %Y') AS shopdate, shoptype, shoptime FROM workshop WHERE shopdate >= CURDATE() ORDER BY shopdate LIMIT 1 In my first response, the query would have returned a field called "DATE_FORMAT(shopdate,'%b %D, %Y')" along with "shoptype" and "shoptime".

You syntax is wrong. SELECT DATE_FORMAT(shopdate,'%b %D, %Y'), shoptype, shoptime FROM workshop WHERE shopdate >= CURDATE() ORDER BY shopdate LIMIT 1

MySQL client version: 5.0.67 What I want: I need to get the exchange rate applicable to a job, based on the date of the job. Each job will be assigned the exchange rate that fell on or before it's own date. Therefore: Job 1 = rate 2.0 Job 2 = rate 2.2 Table `jobs`: +----+------------+------+ | id | date | cost | +----+------------+------+ | 1 | 2011-12-01 | 100 | | 2 | 2012-01-10 | 150 | +----+------------+------+ Table `exchange`: +------------+------+ | date | rate | +------------+------+ | 2011-09-15 | 1.9 | | 2011-10-01 | 2.0 | | 2011-12-05 | 2.1 | | 2012-01-03 | 2.2 | +------------+------+ What I have: I have tried the code below, which works in one instance but not in the other SELECT * FROM jobs LEFT JOIN ( SELECT * FROM exchange ORDER BY date DESC LIMIT 1 ) tbl2 ON tbl2.date <= jobs.date WHERE id=1; +----+------------+------+------------+------+ | id | date | cost | date | rate | +----+------------+------+------------+------+ | 1 | 2011-12-01 | 100 | NULL | NULL | <-- Oh noes! +----+------------+------+------------+------+ SELECT * FROM jobs LEFT JOIN ( SELECT * FROM exchange ORDER BY date DESC LIMIT 1 ) tbl2 ON tbl2.date <= jobs.date WHERE id=2; +----+------------+------+------------+------+ | id | date | cost | date | rate | +----+------------+------+------------+------+ | 2 | 2012-01-10 | 150 | 2012-01-03 | 2.2 | <-- Yay! +----+------------+------+------------+------+ What I'm trying: I'm guessing I need to add a WHERE in the subquery, but my attempt below throws an error. SELECT * FROM jobs LEFT JOIN ( SELECT * FROM exchange WHERE date <= jobs.date ORDER BY date DESC LIMIT 1 ) tbl2 ON tbl2.date <= jobs.date WHERE id=1; Error #1054 - Unknown column 'jobs.date' in 'where clause' I could also remove the LIMIT like below, but that gets me more data than I need (and since this is a cut down of my actual data, there would be a LOT of data I don't need) SELECT * FROM jobs LEFT JOIN ( SELECT * FROM exchange WHERE date <= jobs.date ORDER BY date DESC ) tbl2 ON tbl2.date <= jobs.date WHERE id=1; +----+------------+------+------------+------+ | id | date | cost | date | rate | +----+------------+------+------------+------+ | 1 | 2011-12-01 | 100 | 2011-10-01 | 2.0 | <-- Yay! | 1 | 2011-12-01 | 100 | 2011-09-01 | 1.9 | <-- Oh noes! +----+------------+------+------------+------+ Any advice much appreciated.

Yes, correct! And thanks so much for the help! So simple

Let's say a user goes to http://mysite.com/clients/login.php After they login, they'll see an interface showing whatever files/folders they have. However, if they click on a link: http//mysite.com/clients/data/client1/folder1/file1.txt They should see a login page, then after login, the file1.txt should display. However, the htaccess just redirects to the login page, so after they login, they see the default interface. As if they logged in normally. I'd like help changing the redirect to somehow pass the "client1/folder1/file1.txt" part of the link to the PHP script.

Apologies if I'm posting in the wrong forum. I have a PHP client area, where users log in and see files in their own folder only. This works dandy. If the users click on a direct link to one of the folders or files in their folder, it should request the log in, then proceed to the aforementioned folder or file. Unfortunately, this does not quite work. After log in, users see the top level of their folder, as if they'd logged in normally. I get that I should be passing variables into the PHP script somehow, but I'm at a loss how. Please advise. My htaccess is as follows: <IfModule mod_rewrite.c> RewriteEngine on RewriteCond %{HTTP_REFERER} !^http://(www\.)?mysite\.com [NC] RewriteRule ^(.*)$ http://mysite.com/login.php [R,L] </IfModule> I'm not sure it it's relevant, but my file layout is as below: Client Area (login.php) |-- Data (htaccess) |-- client1 (client's files are in these folders) |-- client2

Thank you! Such a slight change; I spent days cracking my head and getting no where.

Um, sorry, I seem to have twisted myself all the way back to the start. I still don't know how to filter out the old data. SELECT a.date, a.id, a.category FROM staff_acccat a LEFT JOIN staff_acccat b ON a.date > b.date AND a.id = b.id +------------+-------+----------+ | date | id | category | +------------+-------+----------+ | 0000-00-00 | lyn | 3ds | | 0000-00-00 | moonc | flms | | 0000-00-00 | moonc | vfxd | | 2011-09-02 | buddy | 2ds | | 2011-09-02 | buddy | 3dj | | 2011-09-01 | buddy | dess | +------------+-------+----------+

Sorry, I'm just not getting it. This is the closest I've gotten: SELECT a.date, a.id, GROUP_CONCAT(a.category) FROM staff_acccat a INNER JOIN staff_acccat b ON a.date > b.date AND a.id = b.id GROUP BY id; +------------+-------+--------------------------+ | date | id | GROUP_CONCAT(a.category) | +------------+-------+--------------------------+ | 2011-09-02 | buddy | 2ds,3dj | +------------+-------+--------------------------+ At least I got rid of the old data, but data with no updates are all gone, so not quite there yet. Unless the idea is to then join the two sets of data somehow? Could you elaborate a bit lot more?

MySQL: 5.1.53 I have a table: +------------+---------+----------+ | date | id | category | +------------+---------+----------+ | 0000-00-00 | lyn | 3ds | | 0000-00-00 | moonc | flms | | 0000-00-00 | moonc | vfxd | | 2011-09-02 | buddy | 2ds | | 2011-09-02 | buddy | 3dj | | 2011-09-01 | buddy | dess | +------------+---------+----------+ I want the latest categories for each id. The thing is, there may be more than 1 category, so I used a GROUP_CONCAT. Unfortunately, this gives me all the categories since time immemorial. Observe: SELECT id, MAX(date) as date , GROUP_CONCAT(category) FROM staff_acccat GROUP BY id; +---------+------------+------------------------+ | id | date | GROUP_CONCAT(category) | +---------+------------+------------------------+ | buddy | 2011-09-02 | 2ds,3dj,dess | | lyn | 0000-00-00 | 3ds | | moonc | 0000-00-00 | flms,vfxd | +---------+------------+------------------------+ What I actually want: +---------+------------+------------------------+ | id | date | GROUP_CONCAT(category) | +---------+------------+------------------------+ | buddy | 2011-09-02 | 2ds,3dj | <-- 1 less item | lyn | 0000-00-00 | 3ds | | moonc | 0000-00-00 | flms,vfxd | +---------+------------+------------------------+ Any advice much appreciated!

Thanks for all your help, I've finally gotten it! For other people who might have similar problems, here's my solution: SELECT q.id, d.item_id, price, def.usd_conv FROM quote_details d LEFT JOIN quotes_section s ON d.sec_id = s.id LEFT JOIN quote_items i ON d.item_id = i.item_id LEFT JOIN quotes q ON d.quote_id = q.id LEFT JOIN (SELECT * FROM quotes_default ORDER BY date DESC) def ON def.date <= DATE(modified) WHERE quote_id="12-3" AND ( (date_start<=DATE(modified) AND (date_end>=DATE(modified) OR date_end="0000-00-00")) OR (date_start IS NULL AND date_end IS NULL) ) GROUP BY item_id ORDER BY s.sort, i.sort There's a nested query on the 5th line, which ensures I'm using the latest data, as otherwise the GROUP BY would just pickup the first (ie. oldest) row.

Okay, looking at post #4 above, the first query only selects the item_id & price fields. I also want to select the usd_conv field (as in the second query); however, this field necessitates JOINing in another table. Using the second query as an example, I only want the usd_conv "WHERE date <= DATE(modified)"; however, there are lots of results, and I only wanted the latest one so I did a "ORDER BY date DESC LIMIT 1". So now I'm trying to combine the two, but I don't know how to get the latest usd_conv. That means I get more than one set of data in my third query. This is because there is more than 1 usd_conv when applying "WHERE date <= DATE(modified)". What I really need is "WHERE date <= DATE(modified), but just the latest one", but I don't know how. Hoping that makes sense.

Progress! Some, anyway. I've managed the GROUP BY, and now I'm now trying to combine one query to another. This is a working query SELECT d.item_id, price FROM quote_details d LEFT JOIN quotes_section s ON d.sec_id = s.id LEFT JOIN quote_items i ON d.item_id = i.item_id LEFT JOIN quotes q ON d.quote_id = q.id WHERE quote_id="12-3" AND ( (date_start<=DATE(modified) AND (date_end>=DATE(modified) OR date_end="0000-00-00")) OR (date_start IS NULL AND date_end IS NULL) ) ORDER BY s.sort, i.sort Output: ============= $data[0][item_id] = 'admin'; $data[0][price] = 500; I want to add the below to the above somehow. The $mod variable you see is the same as the DATE(modified) from the query above. SELECT usd_conv FROM quotes_default WHERE date<="'.$mod.'" ORDER BY date DESC LIMIT 1 I've tried the following, but it gives me additional sets of data. The trouble is that the second query is limited by LIMIT 1, and I don't know how to cobble that in with the JOIN. SELECT q.id, d.item_id, price, def.usd_conv FROM quote_details d LEFT JOIN quotes_section s ON d.sec_id = s.id LEFT JOIN quote_items i ON d.item_id = i.item_id LEFT JOIN quotes q ON d.quote_id = q.id LEFT JOIN quotes_default def ON def.date <= DATE(modified) // <-- New addition here WHERE quote_id="12-3" AND ( (date_start<=DATE(modified) AND (date_end>=DATE(modified) OR date_end="0000-00-00")) OR (date_start IS NULL AND date_end IS NULL) ) ORDER BY s.sort, i.sort Output: ============= $data[0][item_id] = 'admin'; $data[0][price] = 500; $data[0][usd_conv] = 2.9; $data[1][item_id] = 'admin'; // <-- I don't want this set of data! $data[1][price] = 500; $data[1][usd_conv] = 2.8;

Hi mikosiko, below is my table schema. I will appreciate any insight given. These first two tables consist of actual quote data. The `quotes` tables has the 1-per-quote data, such as the quote ID & comments. The `quote_details` table contains the items in the quote. There will be multiple lines of details, linked to a quote via the quote_id. CREATE TABLE `quotes` ( `id` varchar(7) NOT NULL, `username` varchar(15) NOT NULL, `modified` datetime NOT NULL, `ptitle` varchar(50) NOT NULL, `qtype` tinyint(1) NOT NULL, `letter_id` smallint(4) NOT NULL, `usd_conv` float NOT NULL, `foreign_per` smallint(3) NOT NULL, `external_per` smallint(3) NOT NULL, `extprod_per` smallint(3) NOT NULL, `total` int(9) NOT NULL, `package` int(9) NOT NULL, `gtotal` int(9) NOT NULL, `gpackage` int(9) NOT NULL, `comments` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- CREATE TABLE `quote_details` ( `quote_id` varchar(7) NOT NULL, `item_id` varchar(20) NOT NULL, `sec_id` varchar( NOT NULL, `desc` varchar(100) NOT NULL, `price` int(9) NOT NULL, `quant` float NOT NULL, PRIMARY KEY (`quote_id`,`item_id`,`sec_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Next are the setup tables. These are used when setting up a blank quote form for a user to fill in. The `quotes_default` contains details, like the conversion rate, foreign percentages, etc. that apply to the quote at the time the quote is made. They can be overwritten by the user (and the new value saved in the `quotes` table), but often they are not (and therefore that column in the `quotes` table is left blank). The defaults are changed periodically, so which set of defaults apply to a quote depends on when a quote was last modified (`quotes`.mod). CREATE TABLE `quotes_default` ( `date` date NOT NULL, `usd_conv` float NOT NULL, `foreign_per` smallint(3) NOT NULL, `external_per` smallint(3) NOT NULL, `extprod_per` smallint(3) NOT NULL, `terms` varchar(1500) NOT NULL, PRIMARY KEY (`date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The items that can be quoted are separated into 11 sections. The only reason there are separate item & section tables is due to the possibility of a section being renamed and/or re-sorted (as in display order). The jobrep_name column is for job reports, which have different names for each section (and different items in each section, yegads). CREATE TABLE `quotes_section` ( `id` varchar( NOT NULL, `name` varchar(25) NOT NULL, `sort` tinyint(2) NOT NULL, `jobrep_name` varchar(14) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; There are >100 items that a user can quote, each with a default rate. Occasionally, the rates are updated, or the item removed, which is where the date_start and date_end come into it -- I put a date_end on the outdated info, then enter a insert row with updated info. We've also got 4 different types of quotes, which display different sets of items, which is the reason for the boolean type* fields. CREATE TABLE `quote_items` ( `id` int(9) unsigned NOT NULL AUTO_INCREMENT, `sort` tinyint(3) unsigned NOT NULL, `date_start` date NOT NULL, `date_end` date NOT NULL, `item_id` varchar(20) NOT NULL, `sec_id` varchar( NOT NULL, `type1` tinyint(1) NOT NULL, `type2` tinyint(1) NOT NULL, `type3` tinyint(1) NOT NULL, `type4` tinyint(1) NOT NULL, `name` varchar(255) NOT NULL, `rate` mediumint(7) NOT NULL, `cost` decimal(9,2) NOT NULL, `unit` varchar(10) NOT NULL, `ts_map` varchar(14) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; How I got that query (in the top post) At it's most basic, I have to pull from the `quotes` and `quote_details` (joined by quotes.id) tables to get all the user-entered data. I also have to check `quotes_default` (joined by quotes.mod date) for default conversion rates, etc. The `quotes_section`(joined by quote_details.sec_id) and `quote_items` (joined by quote_details.item_id) tables are pulled purely for display purposes; the order in which the items are displayed, and the full name of the section and item - which has changed in the past, even when the rates didn't change. There's no choice in this. Users need to view a quote exactly as it was sent to a client, even years later. Looking forward to any comments.

Hey guys, as per your advice, I'm looking into changing the MySQL query. If you have the time, take a look at the topic here: http://www.phpfreaks.com/forums/index.php?topic=337473.0

MySQL server version: 5.1.36 Note: This is a followup on this topic: http://www.phpfreaks.com/forums/index.php?topic=337378.0 I've started this new topic on their advice that I should change the SQL rather than the PHP. The setup: My system keeps quote data spread over several MySQL tables. Sometimes a job involves 2 or more quotes, and I need to display them in a combined manner so we can see how much in total was quoted for each item for the whole job. What I want: Quote 1 Admin charges: $500 Courier charges: $200 Quote 2 Courier charges: $300 Despatch: $400 Output Admin charges: $500 Courier charges: $500 <-- Combined total Despatch: $400 What I have: I have an array of related quote IDs. I also have a $mod date, because prices of quoted items change, and we have to see older quotes exactly as they were. Below is an example. $quotes[0]['id'] = 1; $quotes[0]['mod'] = '2011-01-01'; $quotes[1]['id'] = 2; $quotes[1]['mod'] = '2011-01-05'; I have the MySQL query to correctly extract the data for one quote. It works fine. $query = 'SELECT d.item_id, price, jobrep_name AS sec_name, i.name AS name FROM quote_details d LEFT JOIN quotes_section s ON d.sec_id = s.id LEFT JOIN quote_items i ON d.item_id = i.item_id WHERE quote_id=\''.$q['id'].'\' AND ( (date_start<="'.$q['mod'].'" AND (date_end>="'.$q['mod'].'" OR date_end="0000-00-00")) OR (date_start IS NULL AND date_end IS NULL) ) ORDER BY s.sort, i.sort'; I then placed the MySQL query in a PHP loop. This also works fine. foreach ($quotes as $q) { // $query (as above) is here if ($data = $db->read($query)) // class to connect to & read from MySQL $qdata[] = $data; } My results from above: $qdata[0][0][item_id] = 'admin'; $qdata[0][0][price] = 500; $qdata[0][0][sec_name] = 'Administration'; $qdata[0][0][name] = 'Administrative charges'; $qdata[0][1][item_id] = 'courier'; $qdata[0][1][price] = 200; $qdata[0][1][sec_name] = 'Administration'; $qdata[0][1][name] = 'Courier charges'; $qdata[1][0][item_id] = 'courier'; $qdata[1][0][price] = 300; $qdata[1][0][sec_name] = 'Administration'; $qdata[1][0][name] = 'Courier charges'; $qdata[1][1][item_id] = 'despatch'; $qdata[1][1][price] = 400; $qdata[1][1][sec_name] = 'Administration'; $qdata[1][1][name] = 'Despatch'; What I need: I need to GROUP the items together such that they appear as below. $data[0][item_id] = 'admin'; $data[0][price] = 500; $data[0][sec_name] = 'Administration'; $data[0][name] = 'Administrative charges'; $data[1][item_id] = 'courier'; $data[1][price] = 500; // see the subtotal? $data[1][sec_name] = 'Administration'; $data[1][name] = 'Courier charges'; $data[2][item_id] = 'despatch'; $data[2][price] = 400; $data[2][sec_name] = 'Administration'; $data[2][name] = 'Despatch'; The trouble is that since each ID has a different mod date, is this even possible? I'm also having a hard time wrapping my brain around the GROUP BY to accomplish this. Any advice much appreciated! EDIT: And just to make life harder, each quote also has a $currency. Quotes with foreign currency need to be converted back into the local currency before any SUM happens. The conversion rate is also somewhere in a MySQL table, and the exact rate is again dependent on the $mod date.