PHP - Skip Bank Cells Csv Import

Hello ,
I've this code its skip the weekend ,and I've a table for special events has field datetime.
What I need when its come across this datetime skip it like it does with the weekend.
there's the code
Code: [Select]
<?php$database_date = '2011-08-12, 07:00:00'; //this comes from your database, $updated_time = strtotime('+' . hoursLeft($database_date,7,16,24) . ' hours',strtotime($database_date)); //Handle the date:$new_date = date('m-d-Y, H:i:s',$updated_time); //this is when the email should be sent.echo date('m-d-Y, H:i:s',strtotime($database_date)) . '<br />' . $new_date . '<-- you should be emailing at this time!'; //just echo's it to the screen.function hoursLeft($time,$work_starts,$work_ends,$time_to_email,$skip_weekends = true) { //this function handles the count of how many hours you need to fulfill your task. $parts = explode(' ',$time); //split the time off of the date. list($hour, $minute, $second) = explode(':',$parts[1]); //get the hours, minutes, seconds. if($minute == 0) { //if the minutes is over 0, then return a full hour for it $hours = $work_ends - $hour; } else { //otherwise, take into account that this isn't a full hour. $hours = ($work_ends - 1) - $hour; } $day_count = 0; //original day count is 0; for($i = $work_ends,$count = $hours; $count <= $time_to_email; $i++) { //Start the increment at 16, the count at the current hours, keep the count below 25, increment on each loop. if($i > 24) { //if the increment is over 24, reset it to 0. $i = 0; ++$day_count; //increment day_count when i goes over 24 hour limit. if($skip_weekends == true) { //if you want to skip weekends $day = date('l',strtotime("+ {$day_count} days",strtotime($time))); //get the full text of the current day the hours are pointing to. if(in_array($day, array('Saturday','Sunday'))) { //and current day is in the weekend. $hours += 24; //add 24 hours to the count. continue; //restart loop, before any counting takes place. } } } if($i > $work_starts && $i <= $work_ends) { //if the increment is between 7 and 16, add to the count, which will break the loop at 24. $count += 1; } $hours += 1; //add to the hours, the loop breaks at a count of 24, which will give us the total hours to add to the updated_time above. } return $hours; //return hours}?>


OUTPUT:

Code: [Select]

08-12-2011, 07:00:0008-16-2011, 14:00:00<-- you should be emailing at this time!9 hours for Friday:9 hours for Monday:7 hours for Tuesday:= 24 hours.

I'm waiting for your replys..

How do I skip to the next index in a foreach loop? I've tried the following:
Code: [Select]
foreach($c as $val){
  if(1==1){
   next;
  }
  echo "HI"
}

In the code above it still echos HI

How do I fix this?

hi, how is everybody doing? i hope well, i have a problem with some php code. i am trying to split some text in two parts once they have been split, they are display, but sometimes the php code break the source code of the page such as images and instead of the image i get the source code like this
Quote

add-plugins-paintnet-1.2-800x800
3857cff9733e6a.jpg" style="border-width: 1px; border-color: #000000; border-style: solid; margin: 4px auto 5px; display: block;" />

i have tried for about 2 days now to fix it and i haven't been able to, so any help or guidence would be greatly appreciated. this is the code that i am currently using, i have tried to change the variable in the $findTag but nothing seems to work
Code: [Select]
<? php
$article =$this->article_>text;
$countchars=strlen($article);
$divide=$countchars/2;
$firstpart=substr($article,0,$divide);
$secondpart=substr($article,$divide);
//i keep changing the <p /> variable to <img /> and $image and many others but nothing seems to work
$findTag = strpos($secondpart, "<p /> ");
$var = $divide + $findTag
$firstpart = substr($article, 0, $var);
$secondpart = substr($article, $var);
?>
thanks

I have a basicc form that takes users info and when they submit it it show on a new web page in the proper order. I need to know how do I skip a blank field. This form takes a users First, Middle, and Last name but many times they leave the middle name blank so I would want to skip this field from showing up when the form is posted. Here is what I have for the basic php script ..Would like to know how to skip a field if it is left blank on the form.
 
echo $_POST["First"], print "."; ?><?php echo $_POST["Middle"], print "."; ?><?php echo $_POST["Last"], print "."; ?><?php echo $_POST["Persona1"], print ".";<br />

Hey all, first post to this site. I'm somewhat new to PHP, so bear with me - this might be an easy question, it might not be. Just looking for a little help.

Basically, I have a query which takes commands from a form through AJAX (day, time, time1). The query executes a wildcard on a table named sip_data, searches for linked $id (so if $id=3, for example in multiple tables, it spits out the $name, $zip in a div).

Here's the code:

Code: [Select]
<?php
$dbhost = "localhost";
$dbuser = "";
$dbpass = "";
$dbname = "";

//Connect to MySQL Server
$link = mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String
$id = $_GET['id'];
$name = $_GET['name'];
$zip = $_GET['zip'];
$server_url = $_GET['server_url'];
$day = $_GET['day'];
$time = $_GET['time'];
$time1 = $_GET['time1'];

// Escape User Input to help prevent SQL Injection
$id = mysql_real_escape_string($id);
$name = mysql_real_escape_string($name);
$zip = mysql_real_escape_string($zip);
$server_url = mysql_real_escape_string($server_url);
$day = mysql_real_escape_string($day);
$time = mysql_real_escape_string($time);
$time1 = mysql_real_escape_string($time1);
//build query

$query = "SELECT * FROM $day,sip_data WHERE $day.id=sip_data.id AND $day$time<=>$day$time1 ORDER BY zip ASC";

//Execute query
$qry_result = mysql_query($query) or die(('No Results'));

//Build Result String
while($row = mysql_fetch_array($qry_result))

{
echo "<table id=query_result align=left>";
echo "<tr>";
    if($zip = $row[zip]); 
echo "<th><b>$row[zip]</b></th>";
echo "</tr>";
echo "<tr>";
echo "<td><a href=$row[server_url] rel=ajaxDiv>$row[name]</a></td>";
echo "</tr>";
echo "</table>";
}
echo $display_string;




?>





Basically, my issue with this is that some of the results within the sip_data DB will have zip codes that repeat. The current code prints out each results with the zip code and name - here's an image of a sample result:



What I would like to do is avoid repeating the zip code and just group the results under each zip code, kinda like this:

55408           55412        55423
---------     ------------    ----------
example      example       example
example                         example
example

Any help would be greatly appreciated. I feel like I'm so close to the answer, but just need a little guidance. Who knows, I might be way off.

Thanks in advance!

Having a find and replace nightmare and dumbfounded by a way to correct this.

I am exporting a large inner join SQL statement in CSV format. I then want to be able to edit and replace values as needed from the other tables.

ex:
Orlando = 1
Miami = 2
Jacksonville = 3

Well if I want to go through and replace everything 'Orlando' with the id 1, so my inner joins dont break.
I am doing this in the following method.


// read the file
$file = file_get_contents('data.csv');
// replace the data

$result = $db->query('SELECT id_baseprices, baseprices FROM baseprices');
while ($line = $db->fetchNextObject($result)) {
$file = str_replace(',"'.$line->baseprices.'",', ',"'.$line->id_baseprices.'",', $file);
}

$result = $db->query('SELECT id_cities, cities FROM cities');
while ($line = $db->fetchNextObject($result)) {
$file = str_replace(',"'.$line->cities.'",', ',"'.$line->id_cities.'",', $file);
}

// write the file
file_put_contents('revamped.csv', $file);


Problem with the above is it replaces "Orlando" everywhere with 1. I really only want this to happen inside column 8.

I am lost and google must hate me tonight, so thank you for any thoughts or comments.

Hello,

I'm having trouble with the following code 

$file = file_get_contents("database.sql");

$queries = explode(";",$file);

for ($i=0,$c=count($queries);$i<$c;$i++){
mysql_query($queries[$i],$mlink);
}

the problem is the sql file I'm trying to import has ; alllll over the place, not just at the end of the queries, so the queries are not getting inputted correctly. I could see a way to fix this if each query was on a single line, but some of them are over 100 lines each.


How do I make sure it only explodes using the ; at the end of query? and not the ; that are actually in the tables etc.




thanks

Hello all -

Currently I have functions that will parse a csv file and insert the values into a database.  That code is working.  However, I want to take it a step further and ONLY insert selected fields from the csv file.  For example, I have 14 rows in the csv and of those I only want to import values into the database of the rows that I define. 

So, gName, fName and id -> take those and pump those values into the database.  I'm sure this a possible but not sure of the direction I should take.  Any help on my problem would be excellent!

Here's the code:

Code: [Select]
csv_file_to_mysql_table('tim 3.csv', 'growers');

function csv_file_to_mysql_table($source_file, $target_table, $max_line_length = 10000)
{
    if (($handle = fopen("$source_file", "r")) !== FALSE) {
        $columns = fgetcsv($handle, $max_line_length, ",");
        foreach ($columns as &$column) {
            $column = str_replace(".", "", $column);
        }

        $insert_query_prefix = "INSERT INTO $target_table (" . join(",", $columns) . ")\nVALUES";
        while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {

            while (count($data) < count($columns))

                array_push($data, NULL);

            $query = "$insert_query_prefix (" . join(",", quote_all_array($data)) . ");";
            mysql_query($query) or die(mysql_error());

        }

        fclose($handle);
    }
}

function quote_all_array($values)
{
    foreach ($values as $key => $value)
        if (is_array($value))
            $values[$key] = quote_all_array($value);
        else
            $values[$key] = quote_all($value);
    return $values;
}

function quote_all($value)
{
    if (is_null($value))
        return "NULL";

    $value = "'" . mysql_real_escape_string($value) . "'";
    return $value;
}
 

Here's some sample data:

Code: [Select]
Long,Lat,id,gName,fId,fName,fldId,fldName,fldAcers,featureID,objID,fInsu,fFSA,fBid
-82.38306422,40.38439870,2,Norris| Tim,3,Hallinan,4,H1 - 10.0,9.900,1,1, , ,29
-82.22279060,40.42760230,2,Norris| Tim,4,Holt,5,Ho1,11.50,1,1, , ,30
-82.21917211,40.42838107,2,Norris| Tim,4,Holt,6,Ho2,15.10,1,1, , ,31
-82.21710436,40.42454375,2,Norris| Tim,4,Holt,7,Ho3,17.90,1,1, , ,32
-82.21833571,40.42367314,2,Norris| Tim,4,Holt,7,Ho3,17.90,2,2, , ,32
-82.21595345,40.42200315,2,Norris| Tim,4,Holt,9,Ho4,9.100,1,1, , ,34
-82.36538195,40.37711617,2,Norris| Tim,5,Home,10,H1 - 36.3A,36.20,1,1, , ,35
-82.36159625,40.37804250,2,Norris| Tim,5,Home,11,H2 - 3.9A,3.900,1,1, , ,36
-82.36196265,40.38085335,2,Norris| Tim,5,Home,12,H3 - 6A,7.000,1,1, , ,37
-82.41030962,40.38997625,2,Norris| Tim,2,Kenyon,13,K10-17A,17.00,1,1, , ,38
-82.38584288,40.35998635,2,Norris| Tim,2,Kenyon,14,K11-14A,14.52,1,1, , ,39
-82.41644710,40.37927258,2,Norris| Tim,2,Kenyon,15,K3 - 18.2A,18.20,1,1, , ,40
-82.40744700,40.37788250,2,Norris| Tim,2,Kenyon,16,K4 - 26.2A,26.12,1,1, , ,41
-82.40390048,40.37467350,2,Norris| Tim,2,Kenyon,17,K5 - 8.9A,8.874,1,1, , ,42
-82.38605720,40.36920760,2,Norris| Tim,2,Kenyon,18,K6 - 19.3A,18.22,1,1, , ,43
-82.39960597,40.38844915,2,Norris| Tim,2,Kenyon,19,K7 - 18.4A,18.37,1,1, , ,44
-82.39515150,40.39498212,2,Norris| Tim,2,Kenyon,20,K8 - 7.3A,7.324,1,1, , ,45
-82.38817795,40.39458225,2,Norris| Tim,2,Kenyon,21,K9 - 40.4A,40.28,1,1, , ,46
-82.38836722,40.39172487,2,Norris| Tim,2,Kenyon,21,K9 - 40.4A,40.28,2,2, , ,46
-82.40294059,40.35565598,2,Norris| Tim,10,Lane,2,1,6.900,1,1, , ,47
-82.40579843,40.35399913,2,Norris| Tim,10,Lane,22,2,7.200,1,1, , ,48
-82.38322795,40.37619695,2,Norris| Tim,6,Leach,23,L1 - 1.5,1.500,1,1, , ,49
-82.38334655,40.38060737,2,Norris| Tim,6,Leach,24,L2 - 17.6,17.60,1,1, , ,50
-82.38032235,40.38354262,2,Norris| Tim,7,Robinson,25,R1 - 7.5,7.400,1,1, , ,51
-82.39919331,40.35353322,2,Norris| Tim,9,Shorey,26,SH1,5.757,1,1, , ,52
-82.40033216,40.35715336,2,Norris| Tim,9,Shorey,28,SH2,13.39,1,1, , ,54
-82.37072642,40.31789197,2,Norris| Tim,8,Stream,27,S1,17.80,1,1, , ,53

Hi all

So... I am creating an import script for putting contacts into a database. The script we had worked ok for 500kb / 20k row CSV files, but anything much bigger than that and it started to run into the max execution limit. Rather than alter this I wish to create something that will run in the background and work as efficiently as possible.

So basically the CSV file is uploaded, then you choose if the duplicates should be ignored / overwritten, and you match up the fields in the CSV (by the first line being a field title row), to the fields in the database.

The field for the email address is singled out as this is to be checked for duplicates that already exist in the system.

It then saves these values, along with the filename, and puts it all into an import queue table, which is processed by a CRON job. Each batch of the CRON job will look in the queue, find the first import that is incomplete, then start work on that file from where it left off last. When the batch is complete it will update the row to give a pointer in the file for the next batch, and update how many contacts were imported / how many duplicates there were

So far so good, but when checking for duplicity it is massively slowing down the script. I can run 1000 lines of the file in 0.04 seconds without checking, but with checking that increases to 14-15 seconds, and gets longer the more contacts are in the db.

For every line it tries to import its doing a SELECT query on the contact table, and although I am not doing SELECT * its still adding up to a lot of DB activity. One thought was to load every email address in the contacts table into an array before hand, but this table could be massive so thats likely to be just as inefficient.

Any ideas on optimising this process?

I have a table I am trying to import to mySQL
I am having trouble assigning the right variable which represents a column  header to the correct column header in the mySQL table.



<?php

/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "xxx";
$databasename = "xxx";
$databasetable = "names";
$databaseusername ="xxx";
$databasepassword = "xxx";
$fieldseparator = "\t";
$lineseparator = "\n";
$csvfile = "AddEnt2.txt";

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$fcontents = file ('AddEnt2.txt');

# expects the csv file to be in the same dir as this script

for($i=0; $i<sizeof($fcontents); $i++) {

$line = trim($fcontents[$i],'\t'); // '\t' for tab delimeted
$arr = explode('\t', $line); // '\t' for tab delimeted
$name=implode("','",$arr);
$PA=implode("','",$line);
# if your data is comma separated
# instead of tab separated
# change the '\t' above to ','

$sql = "insert into $databasetable values ('$PA','$name')";

$sql = str_replace("''", mysql_escape_string("NULL"), $sql);

$sql = str_replace("' '", mysql_escape_string("NULL"), $sql);

mysql_query($sql); echo $sql ."<br>\n";

if(mysql_error()) {
echo mysql_error() ."<br><b><font color=red>\n</b>";
}
}
?>

Hi,

i have a script to import a csv into a database but some of my product descriptions have ' in the names, this causes mysql to error, how can i get around this?

here is my code.

Code: [Select]
if(isset($_POST['submit']))
{ $i=0;
     $fname = $_FILES['sel_file']['name'];
     
     $chk_ext = explode(".",$fname);
     
     if(strtolower($chk_ext[1]) == "csv")
     {
      mysql_query("truncate table products") ;
         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");
   


         while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
         {
if($i > 0)
{
            $sql = "INSERT into products(prod_id,prod_name,prod_description,prod_cat,prod_sub_cat,tax,prod_price,active) values('$data[0]','$data[1]','$data[9]','$data[14]','$data[15]','$data[19]','$data[35]','$data[65]')";
            mysql_query($sql) or die(mysql_error());
}
$i++;
         }
   
         fclose($handle);
         echo "Successfully Imported<br>";


     }
     else
     {
         echo "Invalid File";

     }   
}

Hi,

I have two xml files, one has 750 products, and the other one has 900 products (including product informations, stock information, sizes and photos)

so shortly one product in xml file is like this;

product_name: x t-shirt
product_code: x-t-shirt-101 (Alphanumerical)
Stocks(loop):
+Small: 50
+Large:200
Photo1 : http://....../main.jpg
Photo2: http://....../2.jpg

And my tables are like this:

Product (product_id (numerical), product_code (alphanumerical), name)
Photos (product_id, id.. )
Stocks (product_id, id..)

(So that I need to insert one product in my db, and take the id, and insert the xml-photos-data with the correct product_id to photos table, same goes with the stocks table)

So I m thinking about the easiest and most healthy way to import this amount of data, and I need to update it daily or weekly (planning to do it by cron jobs for now).  I have found some solutions but since I have not done this before could not decide which is the most secure way. Need to add that I can have other xml files from different companies to import later on, so I think I should code for long term.

Thank you in advance for your help.

Edited December 17, 2018 by Rommeo

I have mysql exported in the following sql format:

Code: [Select]
-

CREATE TABLE IF NOT EXISTS `pma_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `db` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `table` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `timevalue` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sqlquery` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`,`db`,`table`,`timevalue`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='SQL history for phpMyAdmin' AUTO_INCREMENT=3761 ;

--
-- Dumping data for table `pma_history`
--

INSERT INTO `pma_history` (`id`, `username`, `db`, `table`, `timevalue`, `sqlquery`) VALUES
(2, 'root', 'information_schema', 'FILES', '2010-03-04 19:26:33', 'SELECT * FROM `FILES`')

Just as an example anyway.

What I need to be able to do, is import this using PHP.

Does anyone know of a suitable class for doing this?

I could make my own, but I cant quite work out the best way to seperate the queries out?

Thanks

I have a problem with my project. i want to import Excel sheet directly into mysql database for PHP language without any conversion of Excel sheet into another format. i am very fed about this from last two weeks. So Please help me How I Do it. any expert please help me   

Hey guys, I'm having a nightmare at the moment.   I am trying to import a CSV file into a MySQL table, some of the data is interested properly into the rows however some of the CSV row do not get imported.   Here is my PHP script:

<?php

$databasehost = "localhost";
$databasename = "name";
$databasetable = "import";
$databaseusername="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/test.csv";

if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}

try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}

$pdo->exec("TRUNCATE TABLE `$databasetable`");

$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
FIELDS OPTIONALLY ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator)."
IGNORE 1 LINES");

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

As you can see the script is importing the CSV into the table however only 40 of the rows are added to the SQL table when there are actually 80 rows in the CSV. I am also telling the script to IGNORE 1 LINES if that has anything to do with the issue.   Here is one of the rows from the CSV file:

"AETA83919","AETV34458584","SD57VUP","BLACK","Diesel","2007","76778","Hatchback","3","VAUXHALL","CORSA","1.3 CDTi [90] SXi 3dr [AC]","1200","3989","","Manual","http://images.autoexposure.co.uk/AETA83919/AETV34458584_1b.jpg","0","3","","0","2007 57 reg VAUXHALL CORSA 1.3 CDTi [90] SXi 3dr [AC], Manual, Hatchback, Diesel, 76,778 miles. BLACK, Comprehensive Warranty Included In Price. HPI Checked! Pre Delivery Health Check!, 5 seats, LOVELY VAUXHALL CORSA SXI 1.3CDTI SXI WITH AIR CONDITIONING!! VERY WELL CARED FOR AND MAINTAINED AND JUST SERVICED BY DRIVEN CAR SALES LTD AT 76K!! GREAT COMBINATION OF PERFORMANCE AND ECONOMY!! VIEWING AND TEST DRIVING IS RECOMMENDED!! EACH CAR COMES WITH A MININIUM 9 MONTHS MOT. FULL MECHANICAL HEALTH CHECK GUARANTEES CAR IS PREPARED TO THE HIGHEST STANDARD. COMPREHENSIVE RAC WARRANTY INCLUDED. DRIVEN CAR SALES WELCOME ANY INSPECTION. BUY WITH CONFIDENCE AND REASSURANCE AS DRIVEN CAR SALES LTD ARE OFFICIAL MEMBERS OF RETAIL MOTOR INDUSTRY FEDERATION. VIEW OUR REVIEWS ON TRUSTPILOT. 3,989","","0","1","","UK","0","","0","0","","","",""

As far as I can see everything looks fine with the row, all of the rows in the CSV end like this.   One thing I have noticed is it looks like the rows that are missing are in fact being placed in the last column of every other row here is an example:         So as you can see it looks like one row is being inserted into the last column of some rows and I am so confused to why this might be happening?   Any ideas?