1

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 adsf

Any examples would be appreciated as I have limited experience.

junkfoodjunkie
  • 3,168
  • 1
  • 19
  • 33
James
  • 11
  • 2
  • Solve the first problem first. How are you going to get the follower counts from, or back to, the server? The jQuery plugin appears to format numbers as text, limit the result to 3 significant digits, update HTML element content client side in a browser, and run asynchronously for each social media type with no completion notifications - not a promise in sight. Have you considered forking the code? – traktor Mar 27 '17 at 00:45
  • Yeah, this is where I am sort of stuck, my teacher before he left said something about using Ajax & php, and my new teacher barely knows what she is meant to be teaching... From the suggestions made, and a bit of searching, it seems like there are two methods(?) other than forking the code, which would probably no be viable given my beginner level. The first being submitting the data using a hidden form, and the second being using Ajax. Would these methods work? Or am I missing something? If you think that these might be an option I'll have a play around with them. Thanks for the input. – James Mar 27 '17 at 05:52
  • Find out how to use cron to run a job daily e.g. per @Azee 's answer or http://stackoverflow.com/questions/16144350/executing-a-php-script-with-a-cron-job . For a PHP job, research how to make HTTP requests in PHP ([e.g. this question](http://stackoverflow.com/questions/21483479/how-to-make-an-http-request-in-php)) and look into translating SocialCounters ajax into PHP, (it uses MIT license). Alternatively run a [headless browser](http://stackoverflow.com/a/814929/5217142) to visit a page on the site that sends back raw results obtained using a modified and safer version of api.js – traktor Mar 27 '17 at 23:58

3 Answers3

1

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

AZee
  • 520
  • 1
  • 6
  • 22
0

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! :-)

Magnus Alexander
  • 64
  • 5
  • Thanks for the reply, from a brief search it seems cron jobs are not on windows, although schedule task is its alternative? (i am limited to using windows & wamp server, as its a school project and these are all that's available to us). The main problem currently, at least to me, is how can i get the values of these followers counters, which is collected through the link api.js, to something that I can enter into a database? From the searching I have done it seems as though a solution might be found using Ajax and php, but I have no idea how to go about this. Sorry for my limited knowledge. – James Mar 26 '17 at 22:37
0

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.stack.imgur.com/Vurqf.png (pic of it working).

James
  • 11
  • 2