[SOLVED] 'SELECT max(id),Title ... GROUP BY' screwed up

[tbare]

hey... what gives here?

 

<?php
function fGetPreviousHumorFile($table,$id,$subCategory)
{
$query = "PREPARE GetFileList0 FROM 'SELECT max(ID),Title FROM ".$table." WHERE ID < ".$id." AND SubCategory LIKE ".$subCategory." GROUP BY ID'"; $result = mysql_query($query) or die(mysql_error());
// execute the prepared statement for Item Insert
$query = "EXECUTE GetFileList0"; $result = mysql_query($query) or die(mysql_error());
// deallocate the prepared statement for Item Insert
$query = "DEALLOCATE PREPARE GetFileList0"; $resultDeallocate = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array ($result, MYSQL_BOTH);
return $row;
}



$previousFile = fGetPreviousHumorFile('HumorVideo','611','5');

print_r($previousFile);
?>

 

is returning the FIRST file in subcategory 5 (ID 321... should be 609)...

 

why?

 

additionally, if i GROUP BY Title, it returns ID 348.. (still not right...)

 

any ideas? i'm pulling my hair out on this... known bug or am i doing something wrong?

[tbare]

fixed... changed my GROUP BY ID (or Title) to SubCategory...

 

not sure why the others didn't work, but this seems to clear it all up...

 

working code:

<?php
function fGetPreviousHumorFile($table,$id,$subCategory)
{
$query = "PREPARE GetFileList0 FROM 'SELECT max(ID),Title FROM ".$table." WHERE ID < ".$id." AND SubCategory LIKE ".$subCategory." GROUP BY SubCategory'"; $result = mysql_query($query) or die(mysql_error());
// execute the prepared statement for Item Insert
$query = "EXECUTE GetFileList0"; $result = mysql_query($query) or die(mysql_error());
// deallocate the prepared statement for Item Insert
$query = "DEALLOCATE PREPARE GetFileList0"; $resultDeallocate = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array ($result, MYSQL_BOTH);
return $row;
}

$previousFile = fGetPreviousHumorFile('HumorVideo','611','5');

print_r($previousFile);
?>

[tbare]

apparently not... now it's getting the correct ID, but not the correct Title... wtf?

[Barand]

GROUP BY id will summarise all the records with a matching id into a single row in the results. The manual states that any column other that the group by column (id in this case) will have unpredictable values, but it normally it takes the values from the first row encountered for each id.

[tbare]

hmm.. that sucks... is there a better to get a more than one column, then?

here's the setup:

 

table: HumorVideo

(relevant) cols: ID (int, auto increment), SubCategory (int), Title (VarChar)

 

basically, i want to get the previous and next files where 'SubCategory' is the same... any thoughts? the ID may or may not be the next one in line...

[tbare]

really thing i got it this time:

 

<?php
function fGetPreviousHumorFile($table,$id,$subCategory)
{
$query = "PREPARE GetFileList0 FROM 'SELECT t.Title,t.ID FROM ".$table." t,(SELECT Title,max(ID) AS maxID FROM ".$table." WHERE ID < ".$id." AND SubCategory LIKE ".$subCategory." GROUP BY SubCategory) maxIDresult WHERE t.ID = maxIDresult.maxID'"; $result = mysql_query($query) or die(mysql_error());
// execute the prepared statement for Item Insert
$query = "EXECUTE GetFileList0"; $result = mysql_query($query) or die(mysql_error());
// deallocate the prepared statement for Item Insert
$query = "DEALLOCATE PREPARE GetFileList0"; $resultDeallocate = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array ($result, MYSQL_BOTH);
return $row;
}

function fGetNextHumorFile($table,$id,$subCategory)
{
$query = "PREPARE GetFileList0 FROM 'SELECT t.Title,t.ID FROM ".$table." t,(SELECT Title,min(ID) AS maxID FROM ".$table." WHERE ID > ".$id." AND SubCategory LIKE ".$subCategory." GROUP BY SubCategory) maxIDresult WHERE t.ID = maxIDresult.maxID'"; $result = mysql_query($query) or die(mysql_error());
// execute the prepared statement for Item Insert
$query = "EXECUTE GetFileList0"; $result = mysql_query($query) or die(mysql_error());
// deallocate the prepared statement for Item Insert
$query = "DEALLOCATE PREPARE GetFileList0"; $resultDeallocate = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array ($result, MYSQL_BOTH);
return $row;
}

$previousFile = fGetPreviousHumorFile('HumorVideo',$_GET['ID'],'5');

print_r($previousFile);
?>

seems to work as far as i can tell.. anybody see an issue with it?

 

I'll test some more then close this if it works 100%

[tbare]

like a charm!

[tbare]

just as an update:

 

easier way than above:

<?php
function fGetPreviousHumorFile($table,$id,$subCategory)
{
$query = "PREPARE GetFileList0 FROM 'SELECT Title,ID FROM ".$table." WHERE ID LIKE (SELECT max(ID) AS maxID FROM ".$table." WHERE ID < ".$id." AND SubCategory LIKE ".$subCategory.")'"; $result = mysql_query($query) or die(mysql_error());
// execute the prepared statement for Item Insert
$query = "EXECUTE GetFileList0"; $result = mysql_query($query) or die(mysql_error());
// deallocate the prepared statement for Item Insert
$query = "DEALLOCATE PREPARE GetFileList0"; $resultDeallocate = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array ($result, MYSQL_BOTH);
return $row;
}
?>