Jump to content

kickstart

Staff Alumni
 View Profile  See their activity

  • Posts

    2,707

  • Joined

  • Last visited

 Content Type 

Posts posted by kickstart

    MySQL sorting problem

    in MySQL Help

    Posted

    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

    MySQL sorting problem

    in MySQL Help

    Posted

    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

     

    MySQL sorting problem

    in MySQL Help

    Posted

    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

     

    MySQL sorting problem

    in MySQL Help

    Posted

    Hi

     

    :facepalm:

     

    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

     

    beginner here, need a little help

    in PHP Coding Help

    Posted

    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

    JOINing 2 tables with LIMITations

    in MySQL Help

    Posted

    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

    Combining 2 tables and outputting top 5 results

    in MySQL Help

    Posted

    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

    Combining 2 tables and outputting top 5 results

    in MySQL Help

    Posted

    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

    MySQL Join Returns 1 Null Row

    in MySQL Help

    Posted

    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

    Age grouping by Date of birth

    in MySQL Help

    Posted

    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

    Query Multiple Fields with Multiple Keywords

    in MySQL Help

    Posted

    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

    change search from = to LIKE

    in MySQL Help

    Posted

    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

    change search from = to LIKE

    in MySQL Help

    Posted

    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

    Searching multiple tables.

    in MySQL Help

    Posted

    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

×
×
  • 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.