kickstart

Hi It should, but should also not be necessary with the code I was trying to come up with! MySQL is a bit more tolerant of it, but most flavors of SQL require all non aggregate columns to be in the group by clause. This is still a good idea in MySQL to avoid some slightly strange results. All the best Keith

Hi Problem is as it stands you will get the maximum time, irrespective of whether that time was on the maximum date Ie, say you had 3 records 2012/01/11 - 10:00 2012/01/11 - 10:30 2012/01/01 - 11:00 Getting the max date will bring back 2012/01/11 (as you want) but the max time will be 11:00 which is from a record which does not have the max date. Your solution might well work but it is (as you say) a bit lazy and leads to a load of duplicated data. Can you export the table declares and the sample data? If you can do this I can attempt to try out the SQL I have suggested. All the best Keith

Hi Can't see any reason for that. Possibly need the table layouts and sample data to do a bit of checking. To correct the problem with not getting the latest times for the latest days the following should help:- SELECT s.id, s.shop_name, s.payment_plan, ss.add_date, sss.add_time FROM shops s LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id LEFT JOIN ( SELECT ss.shop_id, add_date, MAX(add_time) AS add_time FROM search_up ss GROUP BY shop_id, add_date ) sss ON s.id = ss.shop_id AND ss.add_date = sss.add_date WHERE s.shop_region = 'kansai' AND s.publication = 'yes' ORDER BY s.payment_plan ASC, ss.add_date DESC, sss.add_time DESC All the best Keith

Hi Yep, above is probably correct. As it is at the moment for each shop id your subselect will bring back the max date and the max time rather than the max time within the max date All the best Keith

Hi As above, or if you want the details output to update a users page then maybe put it in a small script to be used for an AJAX call. All the best Keith

Ignore using $info for now. My suggested code had an alternative id hard coded in it. See what that returns. All the best Keith

Hi That suggests to me that when you change the link to get tt1421545 that it doesn't bring back the page you expect. Try $link = file_get_contents("http://www.imdbapi.com/?i=tt1421545"); $json = json_decode($link,true); echo "$link <br />"; print_r($json); All the best Keith

Hi Can't see anything obvious. Can you paste an example of the actual output you get? All the best Keith

Hi I think I would use a subselect to find the date of each rate, and the following date. Something like this should do it:- SELECT exchange.date, exchange.rate, MIN(exchange2.date) AS enddate FROM exchange LEFT OUTER JOIN exchange exchange2 ON exchange.date < exchange2.date GROUP BY exchange.date, exchange.rate Down side of this is that it brings back a null for the end date of the most recent item. Integrating that into a query would give something like the following:- SELECT * FROM jobs INNER JOIN ( SELECT exchange.date, exchange.rate, MIN(exchange2.date) AS enddate FROM exchange LEFT OUTER JOIN exchange exchange2 ON exchange.date < exchange2.date exchange.date, exchange.rate) datesubquery ON (jobs.date >= datesubquery.date AND jobs.date < datesubquery.enddate) OR (jobs.date >= datesubquery.date AND datesubquery.enddate IS NULL) Not tested (and for the real version don't use SELECT *) but hopefully gives you the idea All the best Keith

Hi Try a print_r($json); The error suggests that $json is an array which does not have an index of 'title'. All the best Keith

Hi Or if you have a table of people then maybe this (ie a table that the email address links to), although not tested and not sure that adding the 2 SUMs together will work OK. SELECT a.email, SUM(b.total_amount) + SUM(c.total_amount) FROM People a LEFT OUTER JOIN cashsumtotals b ON a.email = b.email AND b.cause= '$causename' LEFT OUTER JOIN cardsumtotals c ON a.email = c.email AND c.cause = '$cause_id_get' GROUP BY a.email ORDER BY SUM(b.total_amount) + SUM(c.total_amount) DESC LIMIT 5 All the best Keith

Hi Ah, missed the slightly different names. Doesn't seem to be any point to summing things separately, so try the following. SELECT email, SUM(total_amount) as total FROM ( SELECT email, total_amount FROM cashsumtotals WHERE cause= '$causename' UNION ALL SELECT email, total_amount FROM cardsumtotals WHERE cause = '$cause_id_get' ) tmp GROUP BY email All the best Keith

Hi Also a touch confused, but if you did mean 2 items from the same table something like this:- SELECT email, SUM(total_amount) FROM cashsumtotals WHERE cause = '$causename' OR cause = '$cause_id_get' GROUP BY email ORDER BY SUM(total_amount) DESC LIMIT 5 All the best Keith

Hi Think this is the subquery you want::- SELECT `user_id` AS PersonId, COUNT(`partner_id`) AS PartnerCount FROM `partners` WHERE `approved` = 1 GROUP BY UNION SELECT `friend_id` AS PersonId, COUNT(`partner_id`) AS PartnerCount FROM `partners` WHERE `approved` = 1 GROUP BY `friend_id`) AS SubQuery GROUP BY PersonId Trying to integrate that into your main query gives something like:- SELECT `users`.`id`, `users`.`firstname`, `users`.`lastname`, `users`.`username`, `users`.`email`, `users`.`gender`, `users`.`accounttype`, `users`.`personalweb`, `users`.`guestviews`, `users`.`iviews`, `users`.`eviews`, `users`.`credentials`, `users`.`specialties`, `users`.`country`, `users`.`city`, `users`.`state`, `users`.`phonenumber`, `users`.`dateofbirth` AS `dob`, `users`.`mail_status`, `users`.`status`, `markers`.`lat`, `markers`.`lng`, `user_privacy`.`avatar` AS `privacy_avatar`, `user_privacy`.`city` AS `privacy_city`, `user_privacy`.`location` AS `privacy_location`, `investor_info`.`investor_type`, DATE_FORMAT(`users`.`dateofbirth`,'%D') AS `dayofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%c') AS `monthofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%Y') AS `yearofbirth`, DATE_FORMAT(`users`.`dateofbirth`,'%D \of %M %Y') AS `dateofbirth`, DATE_FORMAT(`users`.`signupdate`,'%h:%i %p %M %e, %Y') AS `signupdate`, SUM(`investor_info`.`capital_available`) AS `totalavailable`, `companytype`, `capital`, SumPeople.PartnerCount FROM `users` LEFT JOIN `investor_info` ON `users`.`id` = `investor_info`.`uid` LEFT JOIN `markers` ON `users`.`id` = `markers`.`uid` LEFT JOIN `user_privacy` ON `users`.`id` = `user_privacy`.`uid` LEFT OUTER JOIN employees ON users.id = employees.userid LEFT OUTER JOIN ( SELECT companies.companyid, companies.companytype AS `companytype`, SUM(companies.capital) AS `capital` FROM `companies` GROUP BY companies.companyid) SumCompanies ON employees.companyid = SumCompanies.companyidz LEFT OUTER JOIN (SELECT PersonId, SUM(PartnerCount) AS PartnerCount FROM (SELECT `user_id` AS PersonId, COUNT(`partner_id`) AS PartnerCount FROM `partners` WHERE `approved` = 1 GROUP BY UNION SELECT `friend_id` AS PersonId, COUNT(`partner_id`) AS PartnerCount FROM `partners` WHERE `approved` = 1 GROUP BY `friend_id`) AS SubQuery GROUP BY PersonId) SumPeople ON users.id = SumPeople.PersonId WHERE `users`.`status` > 2 All the best Keith

Hi No problems. The field adata was just one in a random table I used to check the date calculations in. All the best Keith

Main things were a lack of brackets and not reinitialising the $where array. All the best Keith

Hi Yes, that makes sense. You need the age at the start of the season. I would be tempted to do it a cheap and nasty way and add 122 days to the date now (to account for the days of the year after 31st august) and use that to get the year:- SELECT c.*, a.ageGroup, DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(DATE_ADD(NOW(), INTERVAL 122 DAY))-1, '-08-31'))-TO_DAYS(`adate`)), '%Y')+0 AS age FROM child c INNER JOIN ageGroup a ON DATE_FORMAT(FROM_DAYS(TO_DAYS(CONCAT(YEAR(DATE_ADD(NOW(), INTERVAL 122 DAY))-1, '-08-31'))-TO_DAYS(`adate`)), '%Y')+0 BETWEEN a.min_age AND a.max_age WHERE a.agegroup_ID = team GROUP BY c.child_ID ORDER BY c.lastName ASC All the best Keith

Hi Quick play and I think something like this will so it:- <?php function queryall($table, $keyword) { $where = array(); $whereclause = array(); $result = mysql_list_fields($this->db,$table); $sql = "SELECT * FROM ".$table." WHERE "; $keywords = explode(' ', $keyword); foreach ($keywords as $kw) { $kw = get_magic_quotes_gpc() ? trim($kw) : addslashes(trim($kw)); for($i = 0; $i < mysql_num_fields($result); $i++) { if($i!=0) { $where[] = mysql_field_name($result,$i)." LIKE '%$kw%'"; } } $whereclause[] = "(".implode(" \nOR ", $where).")"; $where = array(); } $sql = $sql . implode(" \nAND ", $whereclause); return $sql; } ?> This is checking for ALL key words in any field. All the best Keith

Hi Ah, right. Seem what is confusing me. GetSQLValueString returns a string with inverted commas already in place. Try this:- $colname_hotel_name_RS = "-1"; if (isset($_POST['name'])) { $colname_hotel_name_RS = $_POST['name']; } mysql_select_db($database_contractors, $contractors); $query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", GetSQLValueString('%'.$colname_hotel_name_RS.'%', "text")); echo "$query_hotel_name_RS"; $hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error()); $row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS); $totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS); All the best Keith

Hi Try this:- $colname_hotel_name_RS = "-1"; if (isset($_POST['name'])) { $colname_hotel_name_RS = $_POST['name']; } mysql_select_db($database_contractors, $contractors); $query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", '%'.GetSQLValueString($colname_hotel_name_RS, "text").'%'); echo "$query_hotel_name_RS"; $hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error()); $row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS); $totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS); See what the SQL is that it outputs All the best Keith

Hi Err, it is now 2012 so that is looking for his age on 31/08/2012 (ie 17). Do you want the year derived some other way? All the best Keith

Hi Don't beleive it can be done without playing around with a single counter table to source the values from. All the best Keith

Hi Err, something off there as the error you are getting is an SQL error. That should prevent you outputting the SQL after it is created but before it is executed. All the best Keith

Hi You current SQL will do a cross join, producing a row for every combination of rows on the 2 tables and then bring back any of those combination rows where either name is 'something'. So if you have 100 rows on each table with only 2 rows on one table where the name matches it will bring back 200 rows. All the best Keith

Hi To me that looks like you have some odd mix of php and mysql. The inverted commas around the % signs should have disappeared before it gets to MySQL (ie, they are just delimiting a php string). All the best Keith