CODEWITHSUNDEEP
phpmysqlcodeigniterprintf
Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17576

Column value's string length reduced by one when the value has a Quote

Hi I have table for "comments" , here it has a field called "comment_text" , I insert the comment like this to table

   $query="insert into `comments` 
    (`id`,
    `comment_text`, 
    `date`) 
    values
    ('',
    '".sprintf("%-70s",mysql_real_escape_string(ucwords(trim($strTitle63_5))))."',
    '$date')";

As you can see i am setting the comment length to fix 70 characters (always the original comment is less than 70 characters.)

Everything works fine unless i insert a value with quote , if i insert a comment with a quote , in the database table i can see only 69 characters .

Example :

I insert these two comments

Comment 1 : "Flat Rate Overlaps With Another Flat Rate Code"

Comment 2 : "Claim Details Don't Match BSI/BRI Guidelines"

later when i try to check the string length . Comment 1 is 70 and Comment 2 is 69. Is there any reason for this ?

Every comment that has a quote gives me this issue . :( .

Thanks in advance .

UPDATE

My code

Model

function get_coc5_comment_details()
{
    $this->db->select("comment_text");
    $this->db->from("comments");
    $result=$this->db->get();
    
    return $result->result_array();
}

Controller

function validate_db()
{
    $result = $this->invoice_model->get_comment_details();
    $this->load->view("comment_details_view",array("result"=>$result));
}

view

foreach ($result as $row)
{
    $comment = $row['comment_text'];
        
    echo strlen($comment)."<br>";
}

Upvotes: 3

Views: 71

Answers (1)

Hanky Panky
Hanky Panky

Reputation: 46900

Everything works fine unless i insert a value with quote , if i insert a comment with a quote , in the database table i can see only 69 characters .

Because when there is a quote in your string and then you run it through mysql_real_escape_string it adds a \ in your string :)

And that's one extra character. Once that goes to the database \ is removed and you see 69.

Why don't you consider prepared statements?

Also, storing extra spaces in the database for no reason at all is not good design. You can write a wrapper method for display that appends your comments with extra spaces to complete the 70 character length when using those. I think you should be storing the original comments in the database. What if some day that length requirement becomes 75 for some reason?

For example

$str="test";
echo str_pad($str, 70,'*');   // This will take it to 70 characters.

Upvotes: 5

Related Questions