How do I go about making this site?

[Mackey18]

Hi guys I am trying to make a retail site. My supplier gave me an excel file with all their product information how would I link this to my website? My domain came with PHPmyAdmin with MySQL version 5.0. I downloaded and installed MySQL 5.0 locally, am i right in saying that I need to create the MySQL database and the website locally then export it to the internet? What is the easiest way to go about doing this?

[MadTechie]

Well i do everything locally then when I'm happy I put it on my live server, but I don't see what that has to do with an excel import!

i think saving the excel as a CSV then importing the CSV into a database would be an easier option, no matter where the database resides

[Mackey18]

So I have to convert the excel file into a CSV file, then... can you quickly run me through the process, I am new to all of this, so get confused easily, sorry.

[MadTechie]

you could do this directlt in the a SQL query (probably the easiest route)

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3); 

 

a simple php file would be

<?php
  $fcontents = file ('excel.csv'); // file to array
  foreach($fcontents as $line){ //array loop
      $line = trim($line); 
      $arr = explode(",", $line); //break line into array (in case to use a tab delimiter change "," to "/t")
     
      $sql = "insert into TABLENAME values ('".implode("','", $arr) ."')"; 
      mysql_query($sql);
      //echo $sql ."<br>\n"; //debug
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}
?>

[Mackey18]

Sorry, I don't really understand this, could you explain where to input this information, is this all local?

[MadTechie]

Okay by local you mean on the computer the database it running... right!

 

So you have an excel file with 3 columns  name, address, phone number

 

Now on your have a website with a MySQL database,

 

Create a table ie "MyExcelStuff" with 3* fields ie "fields name, address, phone"

Now you open the excel file on the client PC and save as CSV,

you upload that CSV to the website and then run this query

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE MyExcelStuff
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(`name`, `address`, `phone`); 

the table MyExcelStuff will now have all the date from the CSV (excel file without formatting)

 

 

*Should be 4 field as you want an auto number as well, but were get to that later

[Mackey18]

Now on your have a website with a MySQL database,

 

Umm not sure, I'm running MySQL on my Mac, and my web host (One.com) gave me a link: dbadmin.one.com . It has something to do with PHPmyAdmin.

 

Create a table ie "MyExcelStuff" with 3* fields ie "fields name, address, phone"

 

Do you mean in Terminal with MySQL?

 

you upload that CSV to the website and then run this query

 

When I try uploading the CSV file to website I mentioned earlier, i got an error.

 

Sorry if i seem stupid but i'm only 14.

 

[MadTechie]

PHPmyAdmin, allows you to create databases and tables on your server,

is this going to be an on going or a one off ?

 

if its going to be a one time thing then, you could do the whole thing in PHPmyAdmin,

do you have an sample of your excel file, i can see, (I'll see if i can give you a step by step)

 

[Mackey18]

It's going to be on going, I will update it every week.

 

In the attachment is a picture of 1 row from the table my supplier game me.

 

Thanks again

 

[attachment deleted by admin]

[MadTechie]

Okay your going to need to create a database and a table, with all those fields,

then save the excel as CSV, use PHPMyAdmin, to import the the CSV, to check everything, (PHPMyAdmin will show you the SQL statement it used) once your happy, copy that query and upload the CSV to the server and write a simple script to run the same query with the uploaded file, once that works, create a file uploader (simple form) and combine the 2 scripts,

to allow upload and import,

 

here is a quick PhpMyAdmin Tutorial, to get you started

[Mackey18]

Ok first things first:

 

Okay your going to need to create a database and a table, with all those fields,

 

in MySQL?

[Mackey18]

Hello?

[MadTechie]

Yes in MySQL (use phpmyadmin to make it easier)

[Mackey18]

Ok I'll start making the table in MySQL then i'll come back here and tell you.

 

Thanks