The following is the code that adds 200 recent tweets (JSON provided by Twitter API) into the DB for a Twitter-User. Here the $users array just holds one user (eg: @katyperry) but eventually it would hold more. For each of the users in the array 200 tweets are being brought in through the Twitter API. All this data collection works fine.
Here is the problem now: For each user I am inserting 200 tweets in the MySQL DB (I have to use MySQL only, no other choice) table. Now I understand that each TwitterResp JSON stringified is huge (maybe that is the issue).
Example of TwitterResp:
{"created_at":"Thu Jul 23 18:25:30 +0000 2015","id":624284214704390145,"id_str":"624284214704390145","text":"when your fragrance is ud83dudd25#madpotion https://t.co/UfyPQIwIj4","source":"<a href="http://instagram.com" rel="nofollow">Instagram</a>","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":21447363,"id_str":"21447363","name":"KATY PERRY","screen_name":"katyperry","location":"","description":"CURRENTLYu2728BEAMINGu2728ON THE PRISMATIC WORLD TOUR 2014/2015!","url":"http://t.co/fxFJjKX30d","entities":{"url":{"urls":[{"url":"http://t.co/fxFJjKX30d","expanded_url":"http://www.katyperry.com","display_url":"katyperry.com","indices":[0,22]}]},"description":{"urls":[]}},"protected":false,"followers_count":73404466,"friends_count":157,"listed_count":143175,"created_at":"Fri Feb 20 23:45:56 +0000 2009","favourites_count":1663,"utc_offset":-28800,"time_zone":"Alaska","geo_enabled":false,"verified":true,"statuses_count":6566,"lang":"en","contributors_enabled":false,"is_translator":false,"is_translation_enabled":true,"profile_background_color":"CECFBC","profile_background_image_url":"http://pbs.twimg.com/profile_background_images/378800000168797027/kSZ-ewZo.jpeg","profile_background_image_url_https":"https://pbs.twimg.com/profile_background_images/378800000168797027/kSZ-ewZo.jpeg","profile_background_tile":false,"profile_image_url":"http://pbs.twimg.com/profile_images/609748341119844352/7dUd606e_normal.png","profile_image_url_https":"https://pbs.twimg.com/profile_images/609748341119844352/7dUd606e_normal.png","profile_banner_url":"https://pbs.twimg.com/profile_banners/21447363/1428015534","profile_link_color":"D55732","profile_sidebar_border_color":"FFFFFF","profile_sidebar_fill_color":"78C0A8","profile_text_color":"5E412F","profile_use_background_image":true,"has_extended_profile":false,"default_profile":false,"default_profile_image":false,"following":true,"follow_request_sent":false,"notifications":false},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":5366,"favorite_count":10510,"entities":{"hashtags":[{"text":"madpotion","indices":[24,34]}],"symbols":[],"user_mentions":[],"urls":[{"url":"https://t.co/UfyPQIwIj4","expanded_url":"https://instagram.com/p/5fRc5mP-YB/","display_url":"instagram.com/p/5fRc5mP-YB/","indices":[35,58]}]},"favorited":false,"retweeted":false,"possibly_sensitive":false,"lang":"en"}
So when I am inserting in a loop as shown in the code for TwitterResp being a huge string, in the end I see something like 154 rows in the table not 200. For some other user I see 186 not 200 and so on. Now matter how many times I run the code for katy perry I get 154 only and similar for other users. I was wondering why may this be? Is the insertion process slow for this loop with huge string insertions that it is skipping some rows?
$users = array("result" => ["@katyperry"]);
foreach ($users['result'] as $user) {
// strip the initial character '@' and get 200 Twitter Responses for that screen-name.
$twitterResp = getTweet(substr($user, 1), 200);
$count = 1;
mysqli_query(getConnection(), "START TRANSACTION;");
foreach($twitterResp as $response){
$object = $response;
$query = "INSERT INTO Tweets(Number, TwitterHandle, TwitterResp) VALUES('".(string)$count."', '".$user."', '".json_encode($object)."');";
$count += 1;
$res = mysqli_query(getConnection(),$query);
}
mysqli_query(getConnection(), "COMMIT;");
}
PS: I also tried adding the tweets in one query by basically Adding a lot of VALUES(), VALUES(), ..... That also didn't work.
How can I fix this? Any Suggestions?
At first, modify your code to handle MySQL errors. This will definitely give you a hint what's going wrong.
$res = mysqli_query(getConnection(),$query);
if(false === $res) {
echo "Insertion error: " . mysqli_error();
}
My guess is that you're exceeding max length for TEXT or BLOB type, whatever you've used for TwitterResp column.
It may seem that data length for TEXT or BLOB type is unlimited, but it's not. TEXT / BLOB may handle up to 65536 bytes (~64KB), while MEDIUMTEXT / MEDIUMBLOB have capacity of ~16MB and LONGTEXT / LONGBLOB up to ~4GB.
Note that these are only type limits, you also have to consider size of connection buffer, number of available memory, etc, which may also cause data truncation. See MySQL documentation for more info.
In conclusion, you may try to change column type to MEDIUMTEXT or MEDIUMBLOB which have higher capacity. Yet if it is not enough I'd suggest to store data in file instead and save file path into database.