store csv data coming from api to put in a csv file to later save in db

[natturefrk]

0

 

What is the best way to store this data coming from the api into a csv file to later put into the db. Output: rank, level, xp, rank, level, xp, etc. This api produces about 60 rows of data per name ran and x that by about roughly 300 names that equals a lot of data. Pretty much with my current code I am pretty much creating a endless loop almost that would take a long time to execute so updating said data would be a nightmare i would think. Is there a way to accomplish the same thing without the loop or I am not sure how to go about this. My current code is a mess that much I know I am gonna be told. This code works, just not efficiently. I am thinking there may be a better way to do this.

$query = $conn->prepare("SELECT name FROM users LIMIT 1");
$query->execute();

while($row = $query->fetch(PDO::FETCH_ASSOC)){
  $name = $row['name'];
  $url = 'https://secure.runescape.com/m=hiscore/index_lite.ws?player='. 
  $name . '';//api that the csv data is coming from 
  $highscores = file_get_contents($url);
  $fields = array("Name", "Rank", "Level", "Xp");//this is to add the headers 
 // for the csv files
  $implode1 = implode($fields, ",");//turn into csv format, not sure this is 
  //even needed
  $implode1 .= "\n";/*this is to add a line break so that the explode below 
   will properly put it into its own element*/
   //otherwise data starts joining togather
  $extra_data = array("$name");/*This is to add the name that the data
  pertains too*/
  $implode2 = implode($extra_data, ",");//turn into csv format
  $highscores =  $implode1 . $implode2 . $highscores;//join as one array    
  $highscores = explode("\n", $highscores);//turn each csv into an element of 
  //its own element
  a bunch of unsets to remove unwanted data. Omitted them to condense the code
  $i = 1;
  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="name.csv"');

  $data = $highscores;

  $fp = fopen('highscores/' . $name . '.csv', 'wb');
  foreach ( $data as $line ) {
     $val = explode(",", $line);
     fputcsv($fp, $val);
 }
   fclose($fp); 

The pdo part I was gonna include but the way I have it setup now wouldn't work. I was thinking that I would use mysql's LOAD DATA INFILE to insert the csv data to database. The end goal here is to be able to search for player stats and to use said data to keep track of xp earned during a xp competition. I PRAY that i have included enough info... I would really appreciate any feedback if possible.

[Barand]

Can you show us a sample portion of $highscores that you egt from the api call?

A point on data design - you have the user's name in the user table. You should not be repeating it in every row of the data table, they should contain the user's id value as a foreign key instead.

[natturefrk]

Example output of highscores is as follows

123456(rank compared to all other players), 58(level of the skill), 1234567(total xp in skill), and just keeps repeating for all 27 skills. Ill have to look into what you suggested... not sure what you mean but pretty sure something that google search can fix.

[Barand]

Strange there is nothing to say which of the 27 skills those numbers refer to.

Do you want the text that is in (..) also writing to the csv or just the number value?

[Barand]

To make the data you load useful yo need the skill and a date the scores applied.

+------------+                               +-------------+
|  user      |                               |  skill      |
+------------+                               +-------------+
| user_id(PK)|---+                    +------| skill_id(PK)|
| name       |   |                    |      | description |
| etc        |   |                    |      +-------------+
+------------+   |     +----------+   |
                 |     | score    |   |
                 |     +----------+   |
                 +----<| user_id  |   |
                       | skill_id |>--+
                       | rank     |   
                       | level    |   
                       | xp       |   
                       | date     |
                       +----------+

 

[natturefrk]

.  Yeah oddly enuff it dont so thats what I am doing with $fields to add those labels for the values.Which by the way you asked that,  needs to include the labels for the skill values as well, am I right?

Edited January 23, 2019 by natturefrk
Clarify

[Barand]

I still don't know what the data you are processing looks like.

Unless, if you echo $highscores, it gives

123456(rank compared to all other players), 58(level of the skill), 1234567(total xp in skill),

 

[natturefrk]

This is the raw data before any other changes that comes from $highscores. Rank, level, xp. All the negative vaules are what i am using the unsets for

172102,2367,204035949 244450,95,9326803 226715,99,13062902 314759,91,6351956 136596,99,24777093 112931,99,18388160 239587,94,8152884 57077,99,31947197 295686,85,3556200 269974,89,5121646 290728,85,3285716 238074,87,4161271 363824,80,2052681 169699,90,5581814 225138,85,3511420 241013,83,2701620 206930,94,8228281 162026,86,3972264 251269,73,1059054 147798,99,13294540 244330,73,1089646 166327,85,3457238 178831,82,2666078 208122,80,2176205 163361,96,10535979 215228,85,3479524 162388,84,3214554 138413,70,8883223 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 -1,-1 167565,4200 158618,1 -1,-1 233517,1 -1,-1 -1,-1

 

Edited January 23, 2019 by natturefrk
put the values in code brackets, clarify

[natturefrk]

irrelevant side note but wanted to point it out.. now I understand you point on database design, I am stupid uggh. Maybe I need to go back to the basics and learn proper database design apparently, not being snarky to you but to myself, because my approach was always to echo the name in each table. Actually I just learned about foreign keys, which I actually knew about but never knew what it was called or why you would use? I really really really thank you for pointing that for me. I also wanted to apologize for thinking generalizing the data from $highscores would help you understand how the data is being outputted and thinking it was a good idea instead of actually providing an actual example. I also wanted to thank you for you patience. I also learned the value of doing your own research as it helped me avoid asking a question on here in the past.

Edited January 23, 2019 by natturefrk

[Barand]

Are those really spaces between each set of three values or are they linefeeds (\n)? HTML has a way treating all whitespace as a space.

[natturefrk]

I believe they are linefeeds because 

<?php 
	$highscores = explode("\n", $highscores);
	//here is a example of what it exactly produces: 
	/*Array(
   [0] => "name, rank, level, xp"
   [1] => "natturefrk"
   [2] => "12345(number for rank), 100(number for level), 1234567(number of rank) 
	etc .... for 27 skills*/
?>

so am i right in my assumption or am I off

[Barand]

We'll find out when I try to process it.

 

[edit]

I split the data you posted into lines of 3 values. You said 27 but there are 28 before it starts getting weird.

Edited January 23, 2019 by Barand

[Barand]

Give this a try

// CREATE OUTPUT TABLE score
$db->exec("CREATE TABLE IF NOT EXISTS score (
            score_id int not null auto_increment primary key,
            user_id int,
            skill_id int,
            rank bigint(12) ,
            level bigint(12) ,
            xp  bigint(12) ,
            date date
            )
           ");
           
           
$query = $conn->query("SELECT user_id, name FROM users LIMIT 1");
$row = $query->fetch(PDO::FETCH_ASSOC);
$uid = $row['user_id'];
$anme = $row['name'];
$url = 'https://secure.runescape.com/m=hiscore/index_lite.ws?player='. $name ;   //api that the csv data is coming from

$highscores = file_get_contents($url);             // <--- substituting data from forum post

$placeholders = [];
$values = [];

$lines = explode("\n", $highscores);
$skill = 0;
foreach ($lines as $line) {
    if (strpos($line, '-1')!==false) continue;     // ignore lines with -1 value
    if (trim($line) == '') continue;
    $linevals = array_filter(explode(',', $line));
    $placeholders[] = "(?,?,?,?,?,?)";
    array_push($values, $uid, ++$skill, date('Y-m-d'), ...$linevals);
}

// INSERT ALL 29 ROWS WITH SINGLE INSERT QUERY
$insert = $db->prepare("INSERT INTO score (user_id, skill_id, date, rank, level, xp) VALUES " . implode(',', $placeholders));
$insert->execute($values) ;

 

Edited January 23, 2019 by Barand

[natturefrk]

sorry for late reply i just got off work about an hour ago, so I was trying to see if I see why its doing this but I get a sql error say that the user_id column doesn't exist when in fact it does. The only edit I made to the code was deleting the create table code as i just did it manually because for some reason I was getting a sql error on that one too, but I checked and everything was fine.

 

Edited January 24, 2019 by natturefrk