· 8 years ago · Jun 29, 2018, 08:00 AM
1<?php
2// Very Basic Reddit data scrape. Get in, Get out
3//I run on local host, else i would not use basic username/password.
4
5$servername = "xxx.xxx.xxx.xxx"; // server ip address or localhost
6$username = "User"; // mysql username
7$password = "password"; //mysql users password
8// Create connection - local connectio so i don't worry about security that much, my mysql instance is firewalled.
9$conn = new mysqli($servername, $username, $password);
10// Check connection
11if ($conn->connect_error) {
12 die("Connection failed: " . $conn->connect_error);
13}
14// Charset
15$conn->set_charset("utf8");
16// Get New posts from json feed - keep it simple
17$string_reddit = file_get_contents("https://www.reddit.com/r/ukpolitics/new.json?limit=100");
18// Deocde json stream
19$json = json_decode($string_reddit, true);
20// lazy jump to branch
21$master = $json['data'];
22//loop through post entried
23foreach ($master['children'] as $field) {
24 //store available/wanted data in vars
25 $RR_author = $field['data']['author'];
26 $RR_created_utc = $field['data']['created_utc'];
27 $RR_domain = $field['data']['domain'];
28 $RR_id = $field['data']['id'];
29 $RR_permalink = $field['data']['permalink'];
30 $RR_subreddit = $field['data']['subreddit'];
31 $RR_subreddit_id = $field['data']['subreddit_id'];
32 $RR_title = $field['data']['title'];
33 $RR_url = $field['data']['url'];
34 $urlData = parse_url($RR_url);
35 $domain = $urlData['host'];
36 //date format
37 $format = "Y-m-d H:i:s";
38 //set timezone as in different timezone
39 date_default_timezone_set("Europe/London");
40 //format date into mysql friendly format
41 $local_datetime = date($format, $RR_created_utc);
42 //build query string
43 $sql = "SELECT pid FROM reddit.posts where pid ='" . $RR_id . "'";
44 //search database for current post pid
45 $result = mysqli_query($conn, ($sql));
46
47 //if post already exists in database update vote tallys to post_history database
48 if (mysqli_num_rows($result) > 0) {
49 //post exists, insert into post_history
50 $sql = "INSERT INTO reddit.post_history (pid,captured_utc,ups,downs,commentcount) VALUES (\"" . $RR_id . "\",\"" . $now_datetime . "\",\"" . $RR_ups . "\",\"" . $RR_downs . "\",\"" . $RR_num_comments . "\")";
51
52 if ($conn->query($sql) === TRUE) {
53 $i++;
54 } else {
55 //lazy no errror handling
56 }
57
58 }
59
60 else {
61 //post does not exist - append to posts table
62 $sql = "INSERT INTO reddit.posts (pid,author,created_utc,url ,domain ,permalink ,subreddit ,title) VALUES (\"" . $RR_id . "\",\"" . $RR_author . "\",\"" . $local_datetime . "\",\"" . $RR_url . "\",\"" . $RR_domain . "\",\"" . $RR_permalink . "\",\"" . $RR_subreddit . "\",\"" . $RR_title . "\")";
63
64 if ($conn->query($sql) === TRUE) {
65 $i++;
66 } else {
67 //lazy no errror handling
68 }
69
70 //post does not exist - also update vote tallys to post_history database
71 $sql = "INSERT INTO reddit.post_history (pid,captured_utc,ups,downs,commentcount) VALUES (\"" . $RR_id . "\",\"" . $now_datetime . "\",\"" . $RR_ups . "\",\"" . $RR_downs . "\",\"" . $RR_num_comments . "\")";
72
73 if ($conn->query($sql) === TRUE) {
74 $i++;
75 } else {
76 //lazy no errror handling
77 }
78
79
80 }
81
82}
83//example file stream_bucket_append
84//----
85//----$FileOutPutText ="example file contents";
86//----header('Content-Description: File Transfer');
87//----header('Content-Type: application/octet-stream');
88//----header('Content-Disposition: attachment; filename="'.basename($file).'"');
89//----header('Expires: 0');
90//----header('Cache-Control: must-revalidate');
91//----header('Pragma: public');
92//----echo $FileOutPutText;
93mysqli_close($conn);