0

Hi All
I'm sure I'm missing something very small - and embarrassing!
I'm being lazy - I could have entered the data manually, but wanted to automate it.

I have a small csv file containing swimming meets, dates and locations along the following lines:

May-13;;;;
17;other;Level 1 Program 1;Arboretum;
24;other;All Levels Program 2;King's Park;
June-13;;;;
15;other;Level 2 3 Program3;Pmb;
.
. and so forth

I read this into an array, run through the array to correct the date format and then insert each line into a mySQL database table.
All easy, but my code is inserting multiple entries for each key in the array! Sometimes it inserts 6 records for each key and sometimes 19. It jsut seems to be quite random!

My code:

//===== Read csv into array =====
if (($handle = fopen("../uploads/KZNCal.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $Cal[] = $data;
    }
  fclose($data);
}
$con  = new mysqli($HOST, $USER, $PASS, $NAME);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = $con->prepare("INSERT INTO `kzn_upcoming` (`meet_name`, `location`, `start_date`) VALUES (?, ?, ?)");
foreach ($Cal as $Key => $Event) {
    //===== First 2 rows not needed =====
    if ($Key > 1) {
        if (!is_numeric(substr($Event[0], 0, 1))) {
            //===== Get month and year =====
            $Month = date('m', strtotime(substr($Event[0], 0, 3)));
            $Year  = '20' . substr($Event[0], 4);
        }
        else {
            $Day        = trim(substr($Event[0], 0, 2));
            if ($Day < 10) {$Day = '0' . $Day;}
            // === Discarded $Event[1] - of no consequence for this table. ===
            $Name       = $Event[2];
            $Location   = $Event[3];
            $Start      = "$Year-$Month-$Day";
            //===== Inserted into "clean" array to see if there was something wrong =====
            $Calendar[] = array($Name, $Location, $Start);
        }
    }
}
echo "<br>Count: " . count($Calendar) . "<br>"; //===== Produces expected result of 39 =====

$query = $con->prepare("INSERT INTO `kzn_upcoming` (`meet_name`, `location`, `start_date`) VALUES (?, ?, ?)");
foreach ($Calendar as $Key => $Meet) {
    echo "$Key: $Meet[0] - $Meet[1] - $Meet[2]<br>"; //===== All displayed correctly =====
    $query->bind_param("sss", $Meet[0], $Meet[1], $Meet[2]);
    $query->execute();
}

$query->close();
$con->close();

There are a whole 39 rows in the $Calendar array. The echo produces the expected result - 39 rows, as per the count(). I get random duplication of each record inserted into the table! One run, I had 91 records for each Meet, another "only" 6!
What have I missed?

3
Contributors
15
Replies
39
Views
4 Years
Discussion Span
Last Post by RoryGren
Featured Replies
  • Why are there two exactly the same lines like this: `$query = $con->prepare("INSERT INTO `kzn_upcoming` (`meet_name`, `location`, `start_date`) VALUES (?, ?, ?)");` ? :) One on line 14 and one on 37, while you seem to be only using one. Could you try removing one ;)? Not sure if it … Read More

  • Sorry, I ran your code five times and every time was successful. The only thing that did error was your fclose - you need to change it to fclose($handle); Read More

0

Are you able to provide an attachment of real data, of perhaps fake data that also results in the duplicate records?

0

Pfew, I'm no expert on working with csv files, but I'd say try:

if (($handle = fopen("../uploads/KZNCal.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
    $Cal[] = $data;
    }
    fclose($data);
}

// Check if $Cal is what you need it to be:
echo '<pre>'; print_r($Cal); echo '</pre>';

Checking if $Cal is what you need it to be, and:

$i = 1;
foreach ($Cal as $Key => $Event) {
    echo '<p>$i: ' . $i . '</p>';
    $i++;

    // code
}

And:

$i = 1;

foreach ($Calendar as $Key => $Meet) {
    echo '<p>$i: ' . $i . '</p>';
    $i++;

    // code
}

Checking how many times your loops are executed.

0

Thanks for replying, guys.
I added loop counters into the foreach loops and the $Cal one came out at 52 and $Calendar came out at 39, both correct!
The "debug" counters, print_r()'s and echo's I put into the foreach loops all display the information correctly!
Just the database entries that went haywire!

Edited by RoryGren

0

Paul, the following is tha actual data:

KZN Aquatics Programme 2013/14;;;;
;;;;
May-13;;;;
26;Sun;Qualifying Gala All Levels;Kings Park;10H00
Jun-13;;;;
1;Sat;Sprint Gala ;Kings Park;13H00
9;Sun;Seals Winter Gala 1;Jolliffe (PMB);
22;Sat;Level 0 Gala 1;Kings Park;13H00
23;Sun;Seals Winter Gala 2;Jolliffe (PMB);
29;Sat;Age Group Gala 1;Kings Park;13H00
Jul-13;;;;
9 to 15;Tues;Seals Prestige Gala;Kings Park;08H00
20;Sat;Level 0 Gala 2;Kings Park;13H00
21;Sun;SWIMBOARD AGM;Kings Park;10H00
27;Sat;Age Group Gala 2;Kings Park;13h00
Aug-13;;;;
10;Sat;Westville Jnr TYR Sprints;Kings Park;08H00
17;Sat;Age Group Gala 3;Kings Park;13H00
23 To 25;Fri;Beavers Gals;Kings Park;07H00
31;Sat;Level 0 Gala 3;Kings Park;13h00
Sep-13;;;;
7;Sat;KZN Jnr Champs;Kings Park;07H00
14;Sat;Age Group Gala 4;Kings Park;13H00
21 to 23;Sat;Seagulls Short Course;Kings Park;07H00
28;Sat;Level 0 Gala 4;Kings Park;13H00-
Oct-13;;;;
12;Sat;Age Group Gala 5;Kings Park;13H00
13;Sun;Level 0 Gala 5;Arena Park;10H00
18 to 20;Fri;Action Swim Champs;Kings Park;07H00
Nov-13;;;;
3;Sun;Lake Eland Mile;Margate;TBA
9;Sat;Age Group Gala 6;Kings Park;13H00
10;Sun;Level 0 Gala 6;Stanmore;10H00
15 To 17;Fri;Westville TYR Sprints;Kings Park;07H00
23 To 24;Sat;Capital K ;Midmar;TBA
29;Fri;KZN Relay Gala;Kings Park;18H00
Dec-13;;;;
8;Sun;Level 0 Gala 7;Umlaza B;10H00
10 to 14;Tues;KZN Champs Level 2 & 3;Kings Park;07H00
15;Sun;KZN OWS Champs;TBC;
Jan-14;;;;
12;Sun;Level 0 Gala 8;Firwood Rd;10H00
18;Sat;Age Group Gala 7;Kings Park;13H00
Feb-14;;;;
1;Sat;Age Group Gala 8;Kings Park;13H00
2;Sun;Level 0 Gala 9;Austerville;10H00
8 to 9;Sat;Midmar Mile;DTBC;TBC
15 to 16;Sat;KZN Level 1/0 Champs;Kings Park;07H00
22;Sat;Age Group Gala 9;Kings Park;13H00
23;Sun;Level 0 Gala 10;TBA;10H00
Mar-14;;;;
1;Sat;Age Group Gala 10;Kings Park;13H00

1

Why are there two exactly the same lines like this:

$query = $con->prepare("INSERT INTOkzn_upcoming(meet_name,location,start_date) VALUES (?, ?, ?)");

? :) One on line 14 and one on 37, while you seem to be only using one. Could you try removing one ;)? Not sure if it will matter but just to be sure.

Edited by minitauros

1

Sorry, I ran your code five times and every time was successful. The only thing that did error was your fclose - you need to change it to fclose($handle);

Votes + Comments
Thank you!
Attachments swimming-data.jpg 107.44 KB
0

minitauros - thanks, that was actually commented out in my code. It's from a previous attempt!

Paul, I changed the fclose($handle), thanks for that. I'm still getting this multiple entry story! Your results are exactly what I would expect!
There are 72 records already in the table, adding 39, I would expect 101 records.... 852??

3842b841bbe5530fb40cd130b38142b8d0582c070f432f14c62c5e698a308a01

0

A quick look at your screenshot would suggest that you are not truncating the table - simply running the script over and over. e.g. id numbers for Westville Jnr are in increments of 39.

0

Paul, I actually drop the table and restore the existing records from a backup and then run my script. Before the script - 72 rows. 39 is the number of records I'm adding. It appears to run my script multiple random times.
30c2d9dba314fa5815e19f1cd8d47fbd
After the script, this time 813 rows!
1b7aa6497776a9dd4b9e7d42fd4ea0be

Edited by RoryGren

0

Are you running these operations manually, or is there some wrapper script doing the work for you ?

0

I'm running the page in Safari on a Mac. It seems that every time I view the page, it runs the script again, even if I don't reload it!
I gave up and went into my db manager - I use Sequel Pro - and deleted all the records with ID > 111. There were only 111 records in the table. I went back to Safari, just to view the actual website report. The page with the script was open. I changed tabs to view the main page and there were another 800-odd records! I deleted those manually and went to Safari to close the script page and when I refreshed the database view, there were another 500-odd records!
Closed the tab, manually deleted the extra records, re-set the auto-increment and all is well!
I'll have to try that same script in another browser, just out of curiosity!
Really weird!
Thanks for all the help! You'll robably hear from me again!

0

Well good luck with that, then. Sounds weird indeed, but luckily most of the times when you run into a weirdass problem, it's just one really ini mini minor thingy that has been typed incorrectly, which you find out after hours and hours of searching :p.

0

Yup! That's what I was hoping this was!
Maybe I should put it into a wrapper.... Yeah, well... The data is correct, I was trying to save a few minutes and ended up wasting a day! You win some, you lose some!
Thanks again, guys! Much appreciated!
Ups to both of you!

0

Weeeh you gave me minus kudos instead of plus haha ^^. Yea I ran into stuff like that all the time, but of all those things, some turn into a goldmine of time saving material, which makes it all worth trying :).

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.