Jump to content

gin

Members
  • Posts

    82
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

gin's Achievements

Member

Member (2/5)

0

Reputation

  1. Oh, my mistake, I misread the question
  2. 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 | +------+
  3. 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
  4. 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.
  5. 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.
  6. 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?
  7. 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 =======================
  8. Use a LEFT JOIN instead of an INNER JOIN?
  9. 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.
  10. gin

    Date format

    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".
  11. gin

    Date format

    You syntax is wrong. SELECT DATE_FORMAT(shopdate,'%b %D, %Y'), shoptype, shoptime FROM workshop WHERE shopdate >= CURDATE() ORDER BY shopdate LIMIT 1
  12. 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.
  13. Yes, correct! And thanks so much for the help! So simple
  14. 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.
  15. 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
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.