Column value row length is decremented by one when value has a value
Hi I have a table for "comments", here it has a "comment_text" field, I insert such a comment into the 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 correct 70 characters (always the original comment is less than 70 characters.)
Everything works fine, if I do not insert a value with a quote, if I insert a comment with a quote, I can only see 69 characters in the database table.
Example:
I am pasting these two comments
Comment 1: "Bids too high with different fixed rate code"
Comment 2: "Claim details do not comply with BSI / BRI rules"
later when I try to check the length of the string. Comment 1 - 70 and comment 2 - 69. Is there a reason for this?
Every comment that contains a quote gives me this problem. :(.
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>";
}
source to share
Everything works fine, if I do not insert a value with a quote, if I insert a comment with a quote, I can only see 69 characters in the database table.
Because when there is a quote in your line and then you run it through mysql_real_escape_string
, it adds \
to your line :)
And that's one extra character. Once this is sent to the database \
, you delete 69.
Why aren't you considering prepared statements?
Also, keeping extra whitespace in the database is not good design for any reason. You can write a wrapper method for display that adds your comments with extra spaces to fill the 70 character length when used. I think you should keep the original comments in the database. What if it takes 75 for this length for some reason?
for example
$str="test";
echo str_pad($str, 70,'*'); // This will take it to 70 characters.
source to share