Solved

import csv to mysql with controle of the same record

Posted on 2011-03-14
6
235 Views
Last Modified: 2012-05-11
Hi
i have a mysql database and one of the tables is called customer,sometimes we have new customers send to us in excel but we change it to csv,and i want to import this list automatically to my database using php
the problems:
sometimes there is the same customer means i have a customer and he can be added to the table i must make a controle,
secondly not all the fields are imported to the database only a few of them
any help will be appreciated
thank you
0
Comment
Question by:asaidi
  • 4
  • 2
6 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
A quick Filegetcontents and explode on \r\n will get you each line of the csv. Then explode in commas to get every field. Now you can loop through and build the insert or update queries you need.
0
 

Author Comment

by:asaidi
Comment Utility
Hi aarontomosky
ok i start to write the script and now when i start to import my csv the script stops because there are names that have (') like o` M` etc
how i can ignore these characters and i used specialchars but did not work for me.
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
i wrap all fields in the mssqles (ms sql escape string) I made below. It trims and escapes quotes and also gets rid of a chr(31) that pissed me off at some point.
function mssqles($string_to_escape) {
	$replaced_string = str_replace("'","''",trim($string_to_escape));
	$replaced_string = str_replace(chr(31),"",trim($replaced_string));
	return $replaced_string;
}

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:asaidi
Comment Utility
Hi aaron
you know i m a bit new in php
where i will put this function or how i can call it from my script means is it in insert clause or before
thank you
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
I put my stock functions in a file called inc.php
if it's in the same folder, just do:
require_once("inc.php");
at the top of your page.

Then you call the function like in the code below when you build your query:

 
$query = "insert into tablename (column1,column2,column3) values(";
$query .= "'".mssqles($column1)."','".mssqles($column2)."','".mssqles($column3)."');";

Open in new window

0
 
LVL 38

Accepted Solution

by:
Aaron Tomosky earned 500 total points
Comment Utility
Since you're new to this, I'll also mention that you can do a bunch of rows with one insert statement.
Insert into tablename(column1,column2,column3) values (1,2,3),(4,5,6),(7,8,9)

Damn, I just noticed you are using mysql. Forget my function. Thats for microsoft sql. PHP already has a function built in:
mysql_real_escape_string();
So just replace my mssqles in the example with mysql_real_escape_string
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to Insert a File Using Text Editor 9 51
update field on focusout 15 19
wordpress issue 2 15
Help cleaning out CSS 2 22
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now