BenWeston

Hi Barand. That's my other issue. There are two tables - this one covers WEEKEND prices, the other covers MIDWEEK prices and fills in those gaps. It's hugely complex and I'm beginning to wonder if what they want me to do is even possible. As I can see it, the calculator will need to take the date range, see how many 'seasons' it covers, work out how many days it covers each seasons for so that it can work out how many Fri - Sun are in that range, refer to the other table to get the pricing for that, price up the rest of the days on the midweek prices and spit out the total. It seems monstrously complex. And I need it finished for tomorrow. Typical.

Essentially, if possible, it would be great for the query to spit out each season 'band' the date range falls into and how many days of each it covers. I can then calculate the rate based on, say, 3 days @ medium season, 10 days @ high season, 2 days @ easter season, for example.

Hi Requinix. Thanks for the reply. Not sure how you implement DATEDIFF within the first query and what it actually achieves?

Hi all This is a one I've spent DAYS on and can't figure out This is for a wedding venue and their pricing schedule is divided into low, medium, high and very high seasons. I.e. low season runs from 8 Jan 16 to 28 Feb 16, medium runs from 4 Mar 16 to 20 Mar 16, etc. The database for this is laid out as attached. I would like people to be able to enter their arrival and departure date and for it to be able to choose which seasons the date range falls under. I.e. If they stay 8 to 12 Jan, that's no problem to calculate and falls under LOW season. However, if they stay 27 Feb to 10 Mar, this falls under both LOW and MID seasons, with the majority of the stay in the MID season (which we'd price with but can use PHP to choose the 'highest' season from the array). I'm a bit stumped on this one so any help is hugely appreciated! I'm sure it's something quite simple that I'm missing.

THANK YOU! That worked a treat. is DATE()'s default behaviour to group by day then?

Hi all I have a MySQL table of product reviews that, among others, has one TIMESTAMP column (date) with the date the review was left and another with a unique ID for the review (which I'm not currently using for this query as I don't think it's necessary?). I'd like to show reviews per day on a graph and am trying to get a list of dates with the number of reviews left on each one. The following query isn't working and is returning some very odd numbers – what am I missing? SELECT date, COUNT(*) AS graphcount FROM `reviews` GROUP BY DAY(date) ORDER BY date ASC For example, this query is returning '18' against the date 2014-04-08 19:21:28, when it should be 2!

Thanks for the help so far, requinix! Here's the complete .htaccess: RewriteEngine On # If it's not a directory... RewriteCond %{REQUEST_FILENAME} !-d # ...and it has a trailing slash then redirect to URL without slash RewriteRule ^(.+)/$ /$1 [L,R=301] # Rewrite rules for profiles and username mapping RewriteRule ^([a-z0-9\-]+)$ /user/index.php?prof=$1 [L] RewriteRule ^([a-z0-9\-]+)/treatments$ /user/treatments.php?prof=$1 [L] RewriteRule ^([a-z0-9\-]+)/reviews$ /user/reviews.php?prof=$1 [L] RewriteRule ^([a-z0-9\-]+)/photos$ /user/photos.php?prof=$1 [L] # Rewrite rules for salons RewriteRule salon/([a-z0-9\-]+)$ /salon-stub/index.php?salon=$1 [L] RewriteRule salon/([a-z0-9\-]+)/treatments$ /salon-stub/treatments.php?salon=$1 [L] The last two lines are the ones I'm concerned with but I understand something else may be causing these to be ineffective. Essentially, I want exactly the same redirection for 'salons' as I have for 'profiles and username mapping' (that already works), except the salons are under the /salons/ directory, and usernames and profiles are under root.

Sorry, I mistyped the first one, it should be: RewriteRule salon/([a-z0-9\-]+)/treatments$ /salon-stub/treatments.php?salon=$1 [L] (which doesn't work)

Hi all I'm trying to get my .htaccess file to internally redirect from, say: mysite.com/salon/the-name-of-someone/treatments to... mysite.com/salon/treatments.php?salon=the-name-of-someone I'm trying the following, but it won't work: RewriteRule salon/([a-z0-9\-]+)/treatments$ /salon-stub/index.php?salon=$1 [L] I think the following rule which redirects mysite.com/salon/the-name-of-someone to mysite.com/salon/index.php?salon=the-name-of-someone (successfully), might be causing the problem: RewriteRule salon/([a-z0-9\-]+)$ /salon-stub/index.php?salon=$1 [L]

Thanks mac_gyver! The problem now is that running this query: INSERT INTO `pageviews` (prof_id , day , views) VALUES ('$prof_id' , CURDATE() , 1) ON DUPLICATE KEY UPDATE views = (views+1); first inserts a new row of 18 - 2014-03-03 - 1 then, when run again, inserts a second row of 18 - 2014-03-03 - 2, then fails to insert any more rows because prof_id and day are indexed and unique. The ON DUPLICATE KEY is looking for all columns to be identical, not just prof_id and day.

Hi all. I want to log pageviews per day for each profile ID using this: INSERT INTO pageviews (prof_id,day,views) VALUES ($prof_id,CURDATE(),1) ON DUPLICATE KEY UPDATE views=views+1; The trouble is, I can't make any of the columns unique as none of them will be so how do I make it update, for example, the following entry: Prof ID: 18 Day: 02-23-2014 Views: 1 rather than writing another row with Views: 1 again?

For the benefit of anyone else Googling, I can confirm the above SQL query DOES work. Very helpful article on JOINs at the following link: http://www.sitepoint.com/understanding-sql-joins-mysql-database/

Great, thanks! So, perhaps, something like this (if I'm interpreting the JOIN documentation properly)? SELECT treatment FROM treatments JOIN treatments_offered ON (treatments_offered.treatment_id = treatments.id) WHERE treatments_offered.user_id = '$user_id'; ...as the users table will actually be redundant, seeing as the user's ID has a foreign key to user_id in treatments_offered?

Hi all I have an InnoDB database with three tables – users, treatments and treatments_offered. The users table, predictably, stores user information with an ID for each. The treatments table stores a list of treatments with an ID for each and the treatments_offered table just stores the ID of a user and an ID of a treatment that they offer. Some users may only offer one treatment, some may offer 20+. Foreign keys are in place to make sure all this stays in sync. My question is what is the quickest MySQL query I can run (via PHP but I'm not sure that actually matters), to select and list all the treatments provided by a user of ID, say, 3? Is there one query I can run or do I have to run several separate ones like this: $treatment_ids = mysql_fetch_array(mysql_query("SELECT treatment_id FROM treatments_offered WHERE user_id = '$user_id';"), MYSQL_ASSOC); $treatments = array(); foreach ($treatment_ids as $treatment_id) { $temp = mysql_fetch_array(mysql_query("SELECT treatment FROM treatments WHERE id = '$treatment_id';"), MYSQL_ASSOC); $treatments[] = $temp['treatment']; } That seems VERY inefficient to me – I think I'm missing something!