Display info from database, grouped by field

[furiousweebee]

Howdy,

 

I'm trying to display text from a table in a database. It's a list of quotes, so I just need to pull out the quote and the author name. However, the quote and name are not fields in the same record; they are separate records. Example data:

 

quoteid    name        value
1            content		You guys are great! Thanks for being awesome.
1            author		John Jackson
2            content		Gosh you're amazing! Always been so darn helpful!
2            author		Peter Davis

 

So, I just need to rip out the data from 'content' and 'author', and then group them together based on the quoteid. This is my code thus far:

 

$testimonial_resource = mysql_query("SELECT name, value FROM quotes GROUP BY quoteid ORDER BY author ASC") or die(mysql_error());

while ($testimonial = mysql_fetch_assoc($testimonial_resource)) {

echo '<p>'.$testimonial['value']'.<br /><strong>'.$testimonial['value'].'</strong></p>';

}

 

Any help would be greatly appreciated for this novice.

 

Cheers.

[Adam]

Can I ask why they're not in the same record? Looks like a bad design to be honest. If only at least for performance I'd recommend merging the two records, but if not you can join 2 records from the same table. It's just a little awkward and unnecessary:

 

select q1.value as name, q2.value as comment from quotes q1, quotes q2
where q1.quoteid = q2.quoteid
and q1.name='author'
and q2.name = 'content';

 

Returns:

 

mysql> select q1.value as name, q2.value as comment from quotes q1, quotes q2 where q1.quoteid = q2.quoteid and q1.name='author' and q2.name = 'content';
+--------------+---------------------------------------------------+
| name         | comment                                           |
+--------------+---------------------------------------------------+
| John Jackson | You guys are great! Thanks for being awesome.     |
| Peter Davis  | Gosh you're amazing! Always been so darn helpful! |
+--------------+---------------------------------------------------+

[furiousweebee]

Thanks, that worked perfectly.

 

And yeah, I don't know why the database was structured that way. It's a quotes module from CMS Made Simple and there was no way to display all records at once (only a random or sequential record). Pretty silly.

 

Anyway thanks again - I really appreciate it.

[Adam]

Ah, fair enough then.

 

No problem.