Jump to content


Photo

Please help on formatting retrieved data with PHPfrom MySQL database


  • Please log in to reply
4 replies to this topic

#1 erikjan

erikjan
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 04 July 2006 - 01:09 PM

I made a database which is filled with a main table which contains names of artists, a work they made, details of that work etc.

I retrieve these details the following way. Visitors can click any letter of the alphabet and then get a list of the artists whose name end with that specific letter. So for instance, if they click on the letter "A", they see the following:

Alexander Adonixander (1900-1987) 
Apollo
1959 | Unsigned
Oil on canvas
51 x 55 cm


Alexander Adonixander (1900-1987) 
Daphne
1966 | Unsigned
Oil on canvas
87 x 77 cm


Alexander Adonixander (1900-1987) 
Pandemonium
1974 | Signed
Oil on canvas
123 x 140 cm


Antoine Adrax (1899-1956) 
Cleopatra
1921 | Unsigned
Oil on canvas
12 x 34 cm


(Of course the layout is different, but that is not important for my question).

My question is: Is it possible to get a formatting like this (instead of the one above), so without repeating the name of the artist?

Alexander Adonixander (1900-1987)

Apollo | 1959
Daphne | 1966
Pandemonium  | 1974


Antoine Adrax (1899-1956)

Cleopatra | 1969


I have tried several things, but I didn't succeed until now. I would be extremely greatful if someone could give me a clue. Thanks in advance!!





#2 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 04 July 2006 - 01:43 PM

$artist='none';
$query=mysql_query("select * from table WHERE LEFT(artist', 1) = '$startswith' ORDER BY artist ASC");
while ($row=mysql_fetch_array($query)) {
if ($artist !== $row['artist']);
  $artist=$row['artist'];
  echo('<Br>'$artist.' ('.$date.')<Br>');
}
echo($var1.' - '.$var2.'<Br>');
}



I think that will do the job mate but i havn't tested it..

Hope it point you in the right direction any way :)

Regards
Liam
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#3 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 04 July 2006 - 01:51 PM

Sort the query by name (i.e. SELECT * FROM table_name ORDER BY `name`).

Then display the name whenever it changes. Something like this:

// First do open, select DB, query and place in $result

$name = '';  // Initialize

while ($row = mysql_fetch_assoc($result)) {

    if ($row['name'] != $name) {   // Has the name changed?
        $name = $row['name'];
        echo '<br/><br/>', $name, '<br/>';   // Display heading/name every time it changes
    }

    echo $row['work'], ' | ', $row['work_year'], '<br/>';  // Display other data

}

p.s. Posted too late.


#4 erikjan

erikjan
  • Members
  • PipPipPip
  • Advanced Member
  • 33 posts

Posted 04 July 2006 - 02:46 PM

TOPLAY and SHOKER-Z, both thank you so much!!

The script you sent me, TOPLAY, i understand and it works. Simple and elegant!

Sorry SHOKER-Z, but i'm sure yours works as well, but i don't get it
quite. WHERE LEFT(artist', 1) =, can you explain? if you have time of course . .
and is it correct the third { should be a } , while i am missing a { after if ($artist !== $row['artist']);

Yours both.

#5 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 04 July 2006 - 02:55 PM

As i said i hadn't tested it :P

left(artist,1) will display the first letter of each artist there for if the 1st letter = $startswith then it starts with that letter same thing as WHERE artist LIKE '$startswith%' like you may have used

yeah i forgot the {} i'm at work what else can i say lol

Customers are eating away at my head by the minute every time i answer the phone! :)

I spose it would have been easier if i didnt write the query like toplay


Regards
Liam
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users