How to store a counter value to a MySQL database at regular intervals

Question

I am trying to make a website that displays social counters displaying the current followers for each social media account, which I have achieved using https://github.com/juanv911/SocialCounters.

However, my next problem is how can I store these follower values, at regular intervals (once a day?) into a MySQL table, so that i can use them to make a line graph (which i have working, but with static values i entered into the table).

Html:

<a class="item twitter"><i class="fa fa-twitter"></i><span class="count"></span>Followers</a>
<a class="item google"><i class="fa fa-google-plus"></i><span class="count"></span>Followers</a>
<a class="item facebook"><i class="fa fa-facebook"></i><span class="count"></span>Likes</a>
<a class="item youtube"><i class="fa fa-youtube"></i><span class="count"></span>Subscribers</a>
<a class="item instagram_sandbox"><i class="fa fa-instagram"></i><span class="count"> </span>Followers</a>
<a class="item pinterest"><i class="fa fa-pinterest"></i><span class="count"></span>Followers</a>  

JavaScript:

I cant post here because of the minimum of 10 rep or more to post multiple links which it calls, but http://www.juanvargas.net/SocialCounters/js/api.js

To give you an idea of what im trying to do, here is a pic

Any examples would be appreciated as I have limited experience.

Answer 1

So, i figured out a way to do it a week or two ago, and thought i put my answer up in case someone else needs it.

The way I was able to do it was to scrape the data using PyQt4 and BeautifulSoup, and then just wack on the bit to dump it into my database, here is the code (probably not the most efficient, but it works!);

import sys  
import pymysql
from PyQt4.QtGui import *  
from PyQt4.QtCore import *  
from PyQt4.QtWebKit import * 
from bs4 import BeautifulSoup
#must enter activate py35 in terminal before running python "E:\Working.py"
class Render(QWebPage):  
  def __init__(self, url):  
    self.app = QApplication(sys.argv)  
    QWebPage.__init__(self)  
    self.loadFinished.connect(self._loadFinished)  
    self.mainFrame().load(QUrl(url))  
    self.app.exec_()  

  def _loadFinished(self, result):  
    self.frame = self.mainFrame()  
    self.app.quit()  
#website its point at 
url = 'http://localhost/social-feed/followers.html'  
#render the website
r = Render(url)  
#gather into html
html = r.frame.toHtml() 
#pass html to bs so that it can be manipulated
soup = BeautifulSoup(html, "lxml")
#getting just the values from <span class="count">
count = soup.find_all('span', {'class' : 'count'})
#gets rid of all the markup text apart from the values inside
elements = [tag.text for tag in count]
#connects to server
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
#inserts data into the table "scriptdump", and into the columns in the order that appears on the webpage
try:
    with connection.cursor() as cursor:
            sql = "INSERT INTO `scriptdump` (`twitter`, `google`, `facebook`, `youtube`, `instagram`, `pintrest`) VALUES (%s, %s,%s, %s,%s, %s)"
            cursor.execute(sql, (elements))
connection.commit()
finally:
    connection.close()

This works for me in Python 3.5.2, and for any newbs like me, you'll have to pip install PyQt, bs4 etc. (The comments in the code should guide you through what to change so it works for you) https://i.sstatic.net/Vurqf.png (pic of it working).

Answer 2

You could write a script to fetch data at given intervals through cron. I don't have enough information on how you collect the numbers for each social account, or if you have access to setting up cron - but if you do, the idea is simple.

Note that this example will not work unless you customise it for your own needs, paths and database.

<?php
//Collect the numbers and put them into variables: 
$twitter_followers = '500';
$facebook_followers = '1000';
$instagram_followers = '400';

//Connect to database
$con=mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); }

//Insert into database (or update, if this is what you need) 
mysqli_query($con,"INSERT INTO SocialMedia (twitter,facebook,instagram) 
VALUES ($twitter_followers,'$facebook_followers', $instagram_followers)");

mysqli_close($con);
?>

Save this file somewhere, and open crontab through ssh on the server by using

crontab -e

Enter a new line:

0 0 * * * php /path/to/file/social_media_counter.php

And save. This will execute the file each day at midnight.

(If you use cPanel, Plesk or similar management software you can edit crontab in a GUI through the control panel).

Sorry I couldn't be more specific! If you come back with more details on the setup, I'm sure I or someone else in here can help you better.

Have a nice day and good luck with the script! :-)

Answer 3

First you need to do a cron job with PHP which will run every 24 hours and check your social media count.

https://code.tutsplus.com/tutorials/scheduling-tasks-with-cron-jobs--net-8800

Second part is to store/retrieve data from database with the help of your cron job.

https://www.codeofaninja.com/2011/12/php-and-mysql-crud-tutorial.html