Frustrated with results...

[SBuzzT]

This might be easier if I show what I am getting and what I need...

What I am getting:

Title 1 ,12
Title 1 ,15
Title 1 ,19
Title 2 ,20
Title 2 ,23
Title 2 ,28
Title 3 ,3
Title 3 ,7

Here is what I need:

Title 1 ,12, 15, 19
Title 2 ,20, 23, 28
Title 3 ,3, 7

Here is the code. I have tried several variations to no avail.

$dataname = 'Playlist'.$_POST['report'].$_POST['year'].$_POST['month'];

$query = odbc_exec($odbc, "SELECT * FROM $dataname WHERE runtime > '00:30:00'order by title, day, block");

$content="
<table width=98% cellpadding=5 cellspacing=0 border=0><tr><td class=\"txt\">";

while ($row = odbc_fetch_array($query))
	{
  	$title = str_replace("'","''",$row['title']);
  	$playtime = $row['day'];
  	
  	$content .= $title." ,".$playtime.'<br>';
	} 


// CLEANUP
odbc_close($odbc);

$content .= "</td></tr></table>";

$body = str_replace("#content#", $content, $body);
echo $body;

 

[kodr]

Two queries?

select distinct title

Then loop through that result, querying the runtime's based on the distinct title list.

 

[SBuzzT]

I tried that, but came up with errors (something to the effect of not a valid ODBC result).

 

[kyru]

Well you get the info anyway... So you can work on your results.

Anyway to do that directly you might need to restructure your databese with something like:

Table A:

Field1:Key id Field2:'Title of song'

Table B:

Field1: id Field2:'day'<- Both fields act as key.

This way you could get first the title of the song and later the dates.

Good luck.

 

[SBuzzT]

Is there not a way in one query to just have all the dates for each title to be included on the same line as the title (rather than make a new query inside the query)? And I am still a little unclear as to how to put all the results on the same line as the first result.

 

[sd0t1]

Yes, you need to use "GROUP BY" function.

$query = odbc_exec($odbc, "SELECT * FROM $dataname WHERE runtime > '00:30:00'order by title, day, block GROUP BY title");

This may be the wrong syntax but that's what you want to research. GROUP BY.

Hope that helps.

 

[SBuzzT]

So here's the code, and still, no love. I'm really not sure how to get this working:

$dataname = 'Playlist'.$_POST['report'].$_POST['year'].$_POST['month'];

$query  = odbc_exec($odbc, "SELECT distinct title, day, block FROM $dataname WHERE runtime > '00:30:00' GROUP BY title, day, block");

$content="
<table width=98% cellpadding=5 cellspacing=0 border=0><tr><td class=\"txt\">
<center><a href=\"mainmenu.php\">Back To Main Menu</a><br><br></center> 
Copy the following text to Notepad and save as .csv file.<br><br>";

while ($row = odbc_fetch_array($query))
	{
  	   $title = str_replace("'","''",$row['title']);
  	   $playtime = $row['day'];
	   $content .= $title." ,".$playtime . "<br>";
	
	}

GROUP BY seems to work just like ORDER BY, so I'm not sure if that''s the solution.

 

[SBuzzT]

I tried a query within the results from the first query

$query  = odbc_exec($odbc, "SELECT distinct title FROM $dataname WHERE runtime > '00:30:00' GROUP BY title");

while ($row = odbc_fetch_array($query))
	{
  	   $title = str_replace("'","''",$row['title']);
  	   $query  = odbc_exec($odbc, "SELECT * FROM $dataname WHERE title=$title group by title");
  	   //$content .= $title." ,";
  	   while ($row = odbc_fetch_array($query))
  	   {
  	     $playlist = $row['day'];
  	     //$content .= $playlist . ", ";
  	   }

  	   //$playtime = $row['day'];
	
	}

I though it made sense, but I get this error

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'title=Absolute Ass 3'., SQL state 37000 in SQLExecDirect on line 22

Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource on line 24

Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource on line 19

 

[jpadie]

try this instead. a simpler approach, perhaps. this will send the file down the line to you as an attachment, rather than screen display.

<?php
$query  = odbc_exec($odbc, "SELECT  title,day FROM $dataname WHERE runtime > '00:30:00' order by title");

while ($row = odbc_fetch_array($query)){
	$titles[$row['title']][] = (int) $row['day'];
}
odbc_close ($odbc);
$content ='"title","playdays"' . "\r\n";
foreach ($titles as $title=>$data){
	$content .= '"'. addslashes($title) . '",';
	$content .= implode (',', $data);
	$content .= "\r\n";
}
header("Content-Type: application/octet-stream; ");
header("Content-Transfer-Encoding: binary");
header("Content-Length: " . strlen($content) ."; ");
header("filename=\"playdays.txt\"; "); 
echo $content;
exit;

?>

if you want screen display instead that comment out the header lines.

 

[SBuzzT]

Awesome! Thanks!