1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

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

  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,494
    Likes Received:
    4,457
    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,494
    Likes Received:
    4,457
    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,825
    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.