Splitting up a string of values

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Splitting up a string of values

Post by phpflixnewbie »

I have a script which retreives data from my database then output the results as html. However the final field (criticsratings) consists of a string of values 1,2,3,4,5,6,7 is it possible to use php to split these values up into seperate fields?
Below is the current php code:


Code: Select all

// Performing SQL query
$query = "select dvd_title, round(avg(rating),1) AS rounded_rating, prodn_year, date_format(dvd_rlsdate,'%d %b %y'), dvd_genre, director, GROUP_CONCAT(rating ORDER BY critic_id)
          from dvd_ratings, dvd_titles, dvd_genres, directors
          where dvd_titles.dvd_id=dvd_ratings.dvd_id AND dvd_genres.dvd_id=dvd_titles.dvd_id AND directors.dvd_id=dvd_titles.dvd_id
          group by dvd_ratings.dvd_id
          order by dvd_title";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());


// Printing results in HTML


echo '<table>';
echo "<tr><th>Title</th><th>Avg.<br>Rating</th><th>Year</th><th>DVD Release Date</th><th>Main Genre</th><th>Critics Ratings<></tr>";  // Setting Column Names
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {

           echo '<tr>',
                        '<td><a href="detail.php?id=', $row['dvd_id'], '">', htmlentities($row['dvd_title']), '</a></td>',
                        '<td>', htmlentities($row['rounded_rating']), '</td>',
                        '<td>', htmlentities($row['prodn_year']), '</td>',
                        '<td>', htmlentities($row['rlsdate']), '</td>',
                        '<td>', htmlentities($row['dvd_genre']), '</td>',
                        '<td>', htmlentities($row['criticsratings']), '</td>',
                '</tr>';

}


echo '</table>';
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Code: Select all

from dvd_ratings, dvd_titles, dvd_genres, directors
Out of curiosity, isn't that slow-ish ;) Use a JOIN!

The function you seek is called explode()
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

As far as i know , (comma) are semantically equivalent in the absence of a join condition. so they are joining.
Could you elaborate a little on the explode function and how i would integrate it intomy script?

thx
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Out of curiosity, isn't that slow-ish Wink Use a JOIN!
I'm not sure but the MySQL cookbook uses them all the time and they are much easier to read.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

They are implicit inner joins. I do not recommend using them. Instead, I suggest using explicit inner joins.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

feyd wrote:They are implicit inner joins. I do not recommend using them. Instead, I suggest using explicit inner joins.
Hmm, last time I tried this, the DB ended up making a cartesian join. I suppose the bitter lesson still has me frightened, while the DBs got smart and just tried to 'fix' their users :)
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Hi guys, im still very much learning, i have tried adding the explode function code below, but obviously its wrong, please advise:

Code: Select all

// Performing SQL query
$query = "select dvd_title, round(avg(rating),1) AS rounded_rating, prodn_year, date_format(dvd_rlsdate,'%d %b %y'), dvd_genre, director, GROUP_CONCAT(rating ORDER BY critic_id) as criticsratings
from dvd_ratings, dvd_titles, dvd_genres, directors
where dvd_titles.dvd_id=dvd_ratings.dvd_id AND dvd_genres.dvd_id=dvd_titles.dvd_id AND directors.dvd_id=dvd_titles.dvd_id
group by dvd_ratings.dvd_id
order by dvd_title";




// Printing results in HTML

$str = $row['criticsratings']

echo '<table>';
echo "<tr><th>Title</th><th>Avg.<br>Rating</th><th>Year</th><th>DVD Release Date</th><th>Main Genre</th><th>Critics Ratings</th></tr>";  // Setting Column Names
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {

           echo '<tr>',
                        '<td><a href="detail.php?id=', $row['dvd_id'], '">', htmlentities($row['dvd_title']), '</a></td>',
                        '<td>', htmlentities($row['rounded_rating']), '</td>',
                        '<td>', htmlentities($row['prodn_year']), '</td>',
                        '<td>', htmlentities($row['rlsdate']), '</td>',
                        '<td>', htmlentities($row['dvd_genre']), '</td>',
			'<td>', htmlentities(explode('|', $str, 2)$row['criticsratings']), '</td>',




                '</tr>';


}


echo "</table>\n";
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

explode() returns an array, so you would have to fetch the field you are wanting to split first, then split them into the array using explode() then reference the array in some way to show what you want to show.
Post Reply