Using substr() in a MySQL query

Go To StackoverFlow.com

-2

I use this function substr(tbarticles.articlebody,1,200) as description1 in a query but for some articles I see that the table layout change and page layout also change. It seems that it counts html characters also. Please let me know how can I fix it?

I used also mb_substr but it doesn't return anything for description1.

2012-04-05 21:31
by Kaveh
The database sees this as a string, there's no way for it know that it's HTML. You're gonna need to parse the HTML with PHP, and then shorten the values to 200 characters - Rocket Hazmat 2012-04-05 21:36


0

As @Rocket says you will need to use php, here is a function taken from a similar question:-

function html_cut($text, $max_length)
{
$tags   = array();
$result = "";

$is_open   = false;
$grab_open = false;
$is_close  = false;
$in_double_quotes = false;
$in_single_quotes = false;
$tag = "";

$i = 0;
$stripped = 0;

$stripped_text = strip_tags($text);

while ($i < strlen($text) && $stripped < strlen($stripped_text) && $stripped < $max_length)
{
    $symbol  = $text{$i};
    $result .= $symbol;

    switch ($symbol)
    {
       case '<':
            $is_open   = true;
            $grab_open = true;
            break;

       case '"':
           if ($in_double_quotes)
               $in_double_quotes = false;
           else
               $in_double_quotes = true;

        break;

        case "'":
          if ($in_single_quotes)
              $in_single_quotes = false;
          else
              $in_single_quotes = true;

        break;

        case '/':
            if ($is_open && !$in_double_quotes && !$in_single_quotes)
            {
                $is_close  = true;
                $is_open   = false;
                $grab_open = false;
            }

            break;

        case ' ':
            if ($is_open)
                $grab_open = false;
            else
                $stripped++;

            break;

        case '>':
            if ($is_open)
            {
                $is_open   = false;
                $grab_open = false;
                array_push($tags, $tag);
                $tag = "";
            }
            else if ($is_close)
            {
                $is_close = false;
                array_pop($tags);
                $tag = "";
            }

            break;

        default:
            if ($grab_open || $is_close)
                $tag .= $symbol;

            if (!$is_open && !$is_close)
                $stripped++;
    }

    $i++;
}

while ($tags)
    $result .= "</".array_pop($tags).">";

return $result;
}

Edit: I suspect the reason your page layout is changing is because substr is breaking your html. What you need to do is return all of tbarticles.articlebody and once you have it in a variable in the php (e.g. $result['articlebody']) then use the function below to trim it to the correct length.

So something like:-

 # Get entire field from database, don't use substr
 $result = mysql_query("SELECT tbarticles.articlebody as description1 FROM your_table");
 $row = mysql_fetch_array( $result );

 # Now make the description the correct length using the function above
 $short_description = html_cut($row['description1'], 200);

Hope this helps.

2012-04-05 21:41
by rgvcorley
I guess that works. Personally, I'd use a DOM parser, like DOMDocument - Rocket Hazmat 2012-04-05 21:48
@rgvcorley;thanks, i'm biginner in php and i'm testing your codes - Kaveh 2012-04-06 02:55
@rgvcorley, i have confused because it is inside query ,and i must say that it is not important for me the number of character that return,but it is important that my page layout does not change,please send me an example.how i must use your function - Kaveh 2012-04-06 07:04
You asked about substr - which extracts part of a string. I have no idea about your page layout so unless restricting the number of characters is going to stop your page layout from being changed then you'll need to give more information for anyone to be able to help - rgvcorley 2012-04-06 08:41
See edit to my answer abov - rgvcorley 2012-04-06 08:51
Thanks,your solution with html_cut() is ok - Kaveh 2012-04-06 10:26
Ads