Page 1 of 1

Splitting up a string of values

Posted: Sun Jan 28, 2007 3:41 pm
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>';

Posted: Sun Jan 28, 2007 3:49 pm
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()

Posted: Sun Jan 28, 2007 4:04 pm
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

Posted: Sun Jan 28, 2007 4:33 pm
by superdezign

Posted: Sun Jan 28, 2007 8:36 pm
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.

Posted: Sun Jan 28, 2007 8:49 pm
by feyd
They are implicit inner joins. I do not recommend using them. Instead, I suggest using explicit inner joins.

Posted: Mon Jan 29, 2007 2:20 am
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 :)

Posted: Mon Jan 29, 2007 1:47 pm
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";

Posted: Mon Jan 29, 2007 2:56 pm
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.