Level 67
No, laravel uses pdo so you don't need to use mysqli commands. Everything uses parameter binding.
What is the collation/charset of the source AND destination tables? It seems your text has unicode in it. Should be utf8mb4.
Oct 20, 2018
7
Level 7
do I still need mysqli_real_escape_string in eloquent?
I need to migrate my tables over from the old site to the new site weekly so I can work with up to date data. I just have a blade that shows the total DB entries form old and new DB. Now it was going really well until I did the support ticket replies. When I do the migrations, I'm only taking the data that I really need and get a chance to rename my fields.
\App\SupportReply::truncate();
$Old_Replies = \App\Old_Tickets_Reply::selectRaw('(ats_post_id) id, (ats_ticket_id) ticket_id, (created_by) user_id, (content) message, (ats_attachment_id) image_id, (created_on) created_at')->get()->toArray(); ///
$New_Reply = new \App\SupportReply;
for ($i = 0; $i < (count($Old_Replies)); $i++)
$New_Reply->insert($Old_Replies[$i]);
My dd() data looks fine before I save
array:177 [▼
0 => array:6 [▼
"id" => 538
"ticket_id" => 224
"user_id" => 4894
"message" => """
Hi Nigel,\n
\n
thanks for the prompt response, I can't wait to try it!\n
\n
Regards\n
Stefan
"""
"image_id" => ""
"created_at" => "2018-10-04 05:48:37"
]
1 => array:6 [▼
"id" => 539
"ticket_id" => 225
"user_id" => 3044
"message" => b"""
Hi,\r\n
\r\n
We have ignite loaded on to one computer. How to I disable licence in that computer and shift the licence to another computer?\r\n
\r\n
Sabitha
"""
"image_id" => ""
"created_at" => "2018-10-06 05:20:38"
]
It looks like the longtext is having an error. Do I still need to use mysqli_real_escape_string in Laravel? and how do I use that in eloquent?
Here is the error. It looks like the coma in the text is breaking it.
Illuminate\Database\QueryException thrown with message "SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xA0Hi,\x0D\x0A...' for column 'message' at row 1 (SQL: insert into `support_replys` (`id`, `ticket_id`, `user_id`, `message`, `image_id`, `created_at`) values (539, 225, 3044, �Hi,
We have ignite loaded on to one computer. How to I disable licence in that computer and shift the licence to another computer
Sabitha, 2018-10-06 05:20:38, ?))"
Thanks
Level 7
Thanks for looking.
Both tables are longtext utf8mb4_unicode_ci. If you look at the error it has a ", ?" like something has shifted. It is trying to put "?" in my datetime.
Is there a way to clear strings for unwanted chars in laravel?
Level 7
or all the /r /n could be throwing it off?
Level 67
No, \r\n wouldn't do anything. Those are just "return/enter" codes for a line break.
The problem is in the message field, with this string
Incorrect string value: '\xA0Hi,\x0D\x0A...' for column 'message'
Is there a way to clear strings for unwanted chars in laravel?
Sure, Laravel is just php, and there are many ways to replace text in php.
Both tables are longtext utf8mb4_unicode_ci
Also specifically check the message column. You can set collation on columns as well as tables.
Level 7
Cool, thanks that did it!
$New_Reply = new \App\SupportReply;
for ($i = 0; $i < (count($Old_Replies)); $i++){
$Old_Replies[$i]['message'] = str_replace(' ', '-', $Old_Replies[$i]['message']);
$Old_Replies[$i]['message'] = preg_replace('/[^A-Za-z0-9\-]/', '', $Old_Replies[$i]['message']);
$New_Reply->insert($Old_Replies[$i]);
}
Level 67
You're welcome. Just a side note on your loop.
foreach ($Old_Replies as $old)
is less efficient than
for ($i = 0; $i < (count($Old_Replies)); $i++){
The reason is the count(). It has to calculate it on every iteration, which makes it a bit slower.
$New_Reply = new \App\SupportReply;
foreach ($Old_Replies as $old){
$old['message'] = str_replace(' ', '-', $old['message']);
$old['message'] = preg_replace('/[^A-Za-z0-9\-]/', '', $old['message']);
$New_Reply->insert($old);
}
It's also shorter code because then you don't need to reference the index [$i] for everything.
Your regex is also stripping out line breaks, spaces, and punctuation like periods and commas.
1 like
Level 7
Thanks, good to know. I'm coming from C++ so all new to me.
Have a good weekend
Please or to participate in this conversation.