PHP / mySQL Grouping

[rule69]

well hmm where can i  start im tryng to get data from a mysql db..(obvious one) ... anyways i wana only get the data, if a certain column,(artist in this case) , has only one song.. if they have more then one i want it to be grouped into one link like so

 

 

http://yourdomain.com/dl.php?alpha=a&artist=somthng

 

instead of it being in the usual page as a singular link

 

 
function track($alpha,$cats)
{
  if($_GET['page']) // Is page defined?

 {

	 $page = $_GET['page']; // Set to the page defined

 }else{

	 $page = 1; // Set to default page 1

 }

$max = 10; // Set maximum to 10

  

$cur = (($page * $max) - $max ); // Work out what results to show

  

$getdata = mysql_query("SELECT * FROM `cr-mp3` WHERE alpha='".$alpha."'  ORDER BY `id`  DESC LIMIT $cur, $max") or die(mysql_error()); // select the results
$getdata2 = mysql_query("SELECT * FROM `cr-mp3` WHERE alpha='".$alpha."'  ORDER BY `id`  DESC LIMIT $cur, $max") or die(mysql_error()); // select the results

$data = mysql_fetch_array($getdata); // get the data

  

$counttotal = mysql_query("SELECT * FROM `cr-mp3` WHERE alpha='".$alpha."' ") or die(mysql_error()); // select all records		

$counttotal = mysql_num_rows($counttotal); // count records

  

$total_pages = ceil($counttotal / $max); // dive the total, by the maximum results to show 

$pgy = $_GET['page'];
  if($pgy > 1)
  {
  $nxt_tag = "«";
  }
  else
  {
  $nxt_tag = "»";
  }


   while($sql3 = mysql_fetch_array($getdata2)) {
 $link = $sql3[0]; 
 $name = $sql3[2];
   $artist = $sql3[1];
 echo "» ";
 echo "<a href=\"../get.php?step=1&id=$link\">";
	 echo "$artist - $name";
 echo "</a>
\n"; 
 }

for($i = 1; $i <= $total_pages; $i++) // for each page number

			 {

				 if($page == $i) // if this page were about to echo = the current page

					 {



						 echo' '.$nxt_tag.' <b>' . $i .'</b> '; // echo the page number bold

							 } 

							 else {

						 echo ' '.$nxt_tag.' <a href="?page=' . $i . '&alpha=' .$alpha. '">' . $i . '</a> '; // echo a link to the page

					 }

			 }

  if($page > 1){ // is the page number more than 1?

			 $prev = ($page - 1); // if so, do the following. take 1 away from the current page number

			 echo '
<a href="?page=' . $prev . '&alpha=' .$alpha. '">« Previous</a>'; // echo a previous page link

			 }


if($page < $total_pages){ // is there a next page?

				 $next = ($page + 1); // if so, add 1 to the current

			 echo '<br/><a href="?page=' . $next . '&alpha=' .$alpha. '">Next »</a>'; // echo the next page link

				 }


				 }

 

wells here is my function.. i can only get it to fetch ALL the info and group it by id. but now theres no use since all the tracks by a particular artist will be all over the show.. 

 

well its a function and jsut a few vars weren't posted since they already in other files anyways.. i just cnt get it to group the tracks if the artist got more then one song.  ???

[GingerRobot]

Use the GROUP BY clause:

 

SELECT * FROM tbl GROUP BY artist

[rule69]

i know that.. u see this is what i wana do.. it will group all the track by "id" since they must be form newest to oldest .. but IF the artist has more then 1 song  it must print a new link that links to the artists own specific page which will list all his/her tracks .. if u know what im sayn ???

[GingerRobot]

Im afraid I really don't know what you're saying.

 

What is the output you want? You want to display songs in the order newest to oldest but you also want them grouped by artist? That doesn't make a lot of sense to me.

[rule69]

Ok look here

 

   while($sql3 = mysql_fetch_array($getdata2)) {
 $link = $sql3[0]; 
 $name = $sql3[2];
   $artist = $sql3[1];
 echo "» ";
 echo "<a href=\"../get.php?step=1&id=$link\">";
	 echo "$artist - $name";
 echo "</a>
\n"; 
 }

 

that will print

 

» Rick Astley - Never Gonna Give You Up

» Rick Astley - Never Gonna Give You Up1

» Rick Astley - Never Gonna Give You Up3

» Rick Astley - Never Gonna Give You Up 4

» Chuck Norris - roundhouse kick

» yes - no

» no - yes

 

u see thats what will show BUT i want to do this IF the artist got more then 1 song >>

 

» Rick Astley [ 4 ]

» Chuck Norris - roundhouse kick

» yes - no

» no - yes

 

seee how now? .. that Rick Astley will link to a page that will return all the rick astley songs

 

??? makes sense?

 

 

[GingerRobot]

You'll obviously need to change field/table names and add your formatting, but i would do something like this:

 

<?php
$sql = "SELECT trackname,artist,COUNT(*) FROM tbl GROUP BY artist";
$result = mysql_query($sql) or die(mysql_error());
while(list($trackname,$artist,$count) = mysql_fetch_row($sql)){
    if($count > 1){
        echo '<a href="artists.php?artist='.$artist.'">'.$artist.' ['.$count.' songs]</a>';
    }else{
        echo $artist.' - '.$trackname;
    }
    echo '<br />';
}
?>

[rule69]

thanks will try now and get back to u ...

[rule69]

well i tried it out

 

	
$sql4 = "SELECT id,name,artist COUNT(*) FROM cr-mp3  WHERE alpha='".$alpha."' GROUP BY artist";
$result = mysql_query($sql4) or die(mysql_error());

   $link = $sql4[0]; 
   $name = $sql4[2];
   $artist = $sql4[1];

while(list($name,$artist,$count) = mysql_fetch_row($sql4)){
    if($count > 1){
        echo '<a href="artists.php?artist='.$artist.'">'.$artist.' ['.$count.' songs]</a>';
    }else{
        echo $artist.' - '.$trackname;
    }
    echo '<br />';
}

lets say my sql skills have gotten scratchy over the months of not coding :/

 

i get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(*) FROM cr-mp3 WHERE alpha='a' GROUP BY artist' at line 1

 

thnx in advance...

[GingerRobot]

You were mising a comma in your list of fields. Adding a field to the field list also means you need to add it to the list() function. Try:

 

<?php
$sql4 = "SELECT id,name,artist,COUNT(*) FROM cr-mp3  WHERE alpha='".$alpha."' GROUP BY artist";
$result = mysql_query($sql4) or die(mysql_error());

//the below is pointless. $sql4 is a string. By doing what you have done below, you would just have the 1st,3rd and 2nd characters of that string.Not sure what you were trying to achieve with it.
   //$link = $sql4[0]; 
   //$name = $sql4[2];
   //$artist = $sql4[1];

while(list($id,$name,$artist,$count) = mysql_fetch_row($sql4)){
    if($count > 1){
        echo '<a href="artists.php?artist='.$artist.'">'.$artist.' ['.$count.' songs]</a>';
    }else{
        echo $artist.' - '.$trackname;
    }
    echo '<br />';
}
?>

 

See the comment, also.

[rule69]

thanks but i still see:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-mp3 WHERE alpha='a' GROUP BY artist' at line 1

 

 

maybe this will help:

 

--

-- Table structure for table 'cr-mp3'

--

 

CREATE TABLE `cr-mp3` (

  id int(10) NOT NULL auto_increment,

  artist varchar(50) NOT NULL,

  `name` varchar(60) NOT NULL default '',

  link varchar(500) NOT NULL,

  cat varchar(50) NOT NULL,

  alpha varchar(10) NOT NULL,

  PRIMARY KEY  (id),

  UNIQUE KEY `name` (`name`),

  FULLTEXT KEY artist (artist,`name`,link,cat,alpha)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

thats the database structure...

 

...

 

this was my old sql query btw

$getdata = mysql_query("SELECT * FROM `cr-mp3` WHERE alpha='".$alpha."'  ORDER BY `id`  DESC LIMIT $cur, $max") or die(mysql_error()); 

[GingerRobot]

Oh, i didn't notice your table name. Because of the dash, you'll need to put backticks around the table name in the query:

 

SELECT id,name,artist,COUNT(*) FROM `cr-mp3`  WHERE alpha='".$alpha."' GROUP BY artist

 

 

[rule69]

ok now it works . actually i used that method already but it was faulty as well this what it shows:

 

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in D:\Program Files\xampp\htdocs\corewap\content\funcs.php on line 147

 

the pagination that i have qorks so i know that that the query is going thru BUT its not echoing the content :/

[GingerRobot]

This line:

 

while(list($id,$name,$artist,$count) = mysql_fetch_row($sql4)){

 

Should read:

 

while(list($id,$name,$artist,$count) = mysql_fetch_row($result)){

 

[rule69]

w00t finally thanks a mill man.. 1 last question is it possible to Order them by ID now? so the latest entries appear first?

 

 

[GingerRobot]

As far as im aware, the database would select the first occuring ID for each artist, so it wouldn't really work. That is, if you ordered by ID, it would be in the order of the first track added to a particular artist; the order wouldn't change if you added a second track to that same artist.

[rule69]

yeh i get what u mean .. thanks now to get my pagination workng normally again.. cn i reply to this topic or must i create a new one.. cus i can for see that this pagination will end up like this :

 

» 1 » 2 » 3 » 4 » 5 » 6 » 7 » 8 » 9 » 10 » 11 » 12 » 13 » 14

Next »

 

wen infact i thnk this is neater:

» 1 » 2 » 3 » 4 ... » 14

Next »

 

anyways thanks again  bro