2

I have an exercise where i've been given a link where there's data stored on a .csv file.

Now:

  1. My php page (using fgetcsv) already parses for the csv fields from the link so i have an identical page but in html
  2. I already created a database with the same identical structure so i can import my data

I couldn't find anything useful around.. my questions are:

  1. How can i proceed to import the data from the output i have on my php page?
  2. Is what i'm trying to do actually a good way to solve this case?

Thanks in advance

@RiggsFolly
The code i use that parses and displays the data i need is:
UPDATED CODE:

$servername = "localhost";
$username = "user";
$password = "psw";

$conn = mysqli_connect("localhost","user" ,"psw","dbname");

if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
{
    echo "you did it";
}
$row = 1;
if (($handle = fopen("givenurl.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    $row++;
    for ($c=0; $c < $num; $c++) { 
$sql = "INSERT INTO tablename($columns) VALUES ($data[$c])";
    }
  }
  fclose($handle);
}

I'm actually wondering how to make the matching between the arrays i got parsing the csv and the columns i have on the database

I'll give an example of the arrays i get: [0] name; surname; email; [1] john; cleon; johncleon@gmail.com;

(The first array matches the database structure)

p.s. maybe i got something wrong and i should declare the columns with the

ANewbieDeveloper
  • 88
  • 5
  • 4
    To be honest I am not really sure what you are trying to do. – RiggsFolly Dec 31 '20 at 16:21
  • If you are trying to read a csv file and update the database from its contents, that should be basically .... Read Card and Update Master. The most basic of programs – RiggsFolly Dec 31 '20 at 16:25
  • SHow us the code for what you currently do, then show us what you want to do with it (the database schema) – RiggsFolly Dec 31 '20 at 16:32
  • @RiggsFolly sorry for the lack of details, had to put the code but i couldn't give the exact link as it has sensitive data, hopefully it'd be clear enough, thanks in advance – ANewbieDeveloper Dec 31 '20 at 16:55
  • Instead of the import idea, just write some code in the loop to INSERT/UPDATE this data to the database – RiggsFolly Dec 31 '20 at 17:30
  • I did some researches and i think that i should first connect to the database and then use the "INSERT INTO" method right? – ANewbieDeveloper Dec 31 '20 at 18:37
  • Yes, thats right – RiggsFolly Dec 31 '20 at 18:56
  • I tried doing a var_dump of the variable i'm using ($data[$c]) and it shows the data i want to send, however when i try to insert it into the table it won't work, i updated the code if you'd be that kind do check it out, thanks – ANewbieDeveloper Jan 01 '21 at 00:43
  • [Prepare and execute](https://www.php.net/manual/en/mysqli.prepare.php) you queries. Look at the examples lower down the page – RiggsFolly Jan 01 '21 at 10:57

1 Answers1

0

If you're already parsing the the CSV using fgetcsv to display it in HTML then you just need to write the CSV data (from the array that fgetcsv gives you) to your database table (many examples around if you just need help writing the array to mysql).

I'd suggest if you're going to store to a local database, you would be better then retrieving the data from your database to display on your page - rather than relying on the external linked CSV file.

If the CSV data changes regularly you'll need to think about how to handle those changes in terms of either updating your local database (assuming there's a unique ID in the CSV you can use to identify the right row in your table to update), or drop the existing data and replace at intervals. This could be done to a schedule (cron job perhaps) or perhaps a manual import script you run when you're aware the data has been changed.

steve
  • 2,469
  • 1
  • 23
  • 30
  • Oh thought that would be more difficult so a reference to writing the array to mysql could be https://stackoverflow.com/a/10054657/14919455 for istance? This was an exercise that i've been given so i'll have to rely on the external CSV file Thanks for the cron job reference, going to learn about that as well (i've been a front-end developer for most of my career, pardon my ignorance) – ANewbieDeveloper Dec 31 '20 at 16:52
  • Yes that example looks like a good starting point - make sure to read the comments and take note of things like escaping values. You may be better reading into parameterising queries with php and mysql as that takes on lots of the work in terms of sanitising/escaping values for you. Good luck with your learning - we all have to start somewhere and it's a big leap from just front-end to server side programming. – steve Dec 31 '20 at 17:01