How to substr() longtex type data OR Triming longtex type data?

Discussion in 'PHP' started by KingCobra, Sep 30, 2020.

0
  1. #1
    Hi friend,

    I have long data (text/html) in my mysql table's longtext type field. I can retrieve and display that data using php.

    But I don't want to display full data from that field. I want to display a portion of data from that, suppose 30% data. How can I do that?

    If trimming/cropping data is not possible in percentage then can it possible using substr() function? But I found substr() doesn't work in long text.

    Can you please help me?

    Thanks
     
    KingCobra, Sep 30, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #2
    you can do the substr in the select statement

    select substring(mylongtext,1,100) as shortsentence from table;
    Code (markup):
     
    sarahk, Sep 30, 2020 IP
    JEET likes this.
  3. KingCobra

    KingCobra Well-Known Member

    Messages:
    289
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    103
    #3
    Can I do it in php side when printing the texts?
     
    KingCobra, Oct 1, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #4
    Probably but why get scripting to do something the database is designed to do?
     
    sarahk, Oct 1, 2020 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    Why won't substr work with long text data?
    Is it giving some error or warning or notice?
    If its failing, I think its because your memory_limit is set to a small number, and data coming from a long text mysql field can be very huge, in GB if I remember correctly.

    To solve the memory_limit problem, either increase that variable, and if you cannot increase that (if on shared host),
    then select partial data from mysql like @sarahk suggested.
    Select whatever is allowed by your memory_limit setting. Generally 5MB to 8MB is easily possible.
    Then you can do a substring on that partial data.

    "select substring( fieldName , 1, 5000000 ) as shortsentence from table";

    //That is about 5MB data selected.

    You cannot use percentage to do a substr, you need to give fixed length.
    You can do it like this:

    $d= "my data from long text field";
    $l= ceil( strlen($d) * (0.30) );
    $short= substr($d, 0, $l);

    //$short has your 30% data now.
     
    JEET, Oct 1, 2020 IP
    Efetobor Agbontaen likes this.