Page 1 of 1

Converting Values

Posted: Sat Jan 27, 2007 9:03 am
by phpflixnewbie
Hi,

I have a php script which pulls data from my database and outputs as html, and also adds hyperlinks to the first column of table data.
I would like to change the values for the outputted data for the last column to an abbreviated value,

i.e) JoeBloggs becomes JB

Is this easily done with php?

Below is the current php code:

Code: Select all

// Performing SQL query
$query = "select dvd_titles.dvd_id, dvd_title , round(avg(rating),1) AS rounded_rating , prodn_year , date_format(dvd_rlsdate,'%d %b %y') as rlsdate  , dvd_genre, GROUP_CONCAT(CONCAT(critic,'=',rating)order by rating) as criticsratings
          from dvd_ratings , dvd_titles , dvd_genres , dvd_critics
          where dvd_titles.dvd_id=dvd_ratings.dvd_id AND dvd_genres.dvd_id=dvd_titles.dvd_id AND dvd_critics.critic_id = dvd_ratings.critic_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</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($row['criticsratings']), '</td>',
                '</tr>';


}

echo '</table>';

Posted: Sat Jan 27, 2007 9:34 am
by feyd
Regular expressions? You'll have to define exactly how one is to calculate abbreviations to the parser.

Posted: Sat Jan 27, 2007 9:55 am
by anjanesh

Code: Select all

<?php
echo abbreviate("JOEBLOGGS");
echo '<br/>';
echo abbreviate("JoeBloggs");
echo '<br/>';
echo abbreviate("Joe Bloggs");

function abbreviate($str)
 {
        $s = trim($str);

        if (strpos($s, ' ') > 0)
         {
                preg_match_all('#(^|\s)\w#', $s, $matches);
                $a = '';
                while(list($key, $val) = each($matches[0]))
                 {
                        $a .= substr($val, -1);
                 }
                return $a;
         }
        elseif (strtoupper($s) == $s) return substr($s, 0, 1).substr($s, strlen($s)/2, 1);
        else
         {
                preg_match_all('#[A-Z]#', $s, $matches);
                $a = '';
                while(list($key, $val) = each($matches[0]))
                 {
                        $a .= $val;
                 }
                return $a;
         }
 }
?>

Posted: Sat Jan 27, 2007 10:37 am
by phpflixnewbie
thx anjanesh,

Im a beginner, so how would i integrate this into my code so that it applied to the retrieved data?

Posted: Sat Jan 27, 2007 10:52 am
by anjanesh
Which mysql field do you want to abbreviate ?

Posted: Sat Jan 27, 2007 11:20 am
by phpflixnewbie
anjanesh wrote:Which mysql field do you want to abbreviate ?
Values in the alias criticsratings field (combined values from critic and rating fields)

Posted: Sat Jan 27, 2007 11:35 am
by anjanesh
change

Code: Select all

htmlentities($row['criticsratings'])
to

Code: Select all

htmlentities(abbreviate($row['criticsratings']))
Make sure this exists somewhere in your page

Code: Select all

function abbreviate($str) 
 {
 .
 .
 .
 }

Posted: Sat Jan 27, 2007 11:41 am
by phpflixnewbie
It abbreviated the critics names, but it also removed the actual critics ratings values from the field, pls advise

Posted: Sat Jan 27, 2007 11:54 am
by anjanesh
You can display both

Code: Select all

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>',
                        '<td>', htmlentities(abbreviate($row['criticsratings'])), '</td>',
                '</tr>';

Posted: Sat Jan 27, 2007 12:01 pm
by phpflixnewbie
The reason i want to abbreviate those names is so the table fits nicely onto the webpage. I want to just add a key add the beginning of the page explaining what each abbreviation stands for and leave the criticsratings with the abbreviations and ratings together in one field.