Update existing table from a text file-doesn't work

grumpy

I am trying to update a table from a text file but I can't figure it out. I have an existing table where I need to update the insertdates from a text file by matching the row's ID . The text file is comma delimited and looks like this:
23638, 2005-05-26 20:18:00.0 23704, 2005-06-16 15:10:00.0 23782, 2005-07-05
18:45:00.0 23994, 2005-08-17 09:29:00.0 24219, 2005-10-12 18:47:00.0 24724,
2005-12-14 15:13:00.0 25043, 2006-02-16 16:10:00.0 25059, 2006-02-20 20:45:00.0

In other words, for example, the record date in my table with resID= 23638 should be updated from its current insertdate to 2005-05-26 20:18:00.0

Below is my code. I don't get any errors, but it doesn't do the update. Any help would be appreciated. Thanks.

error_reporting(E_ALL);


$lines = file('insertupdate.txt');

$entries = array();
$insertdate = '';
foreach ($lines as $line)
{
$entries[$insertdate][] = $line;
}

foreach ($entries as $insertdate => $resID)
{
    if (count($resID) > 0)
    {
        $resID = "'" . implode("','", $resID) . "'";

    $sql = "UPDATE table SET insertdate = '$insertdate' WHERE resID IN ($resID)";
}
}

cahva

This MIGHT be stupid question, but where is the actual query execution(for example mysql_query($sql))?

Try also printing out the sql and run the sql in phpmyadmin(or whatever you use) to see if its valid.

grumpy

I'm trying to use the code line: $lines = file('insertupdate.txt'); to get the text and using $sql = "UPDATE table SET insertdate = '$insertdate' WHERE resID IN ($resID)"; to update the table.
Won't that work? What do I have wrong? Thanks.

cahva

I mean, where is the actual query execution? Now you just assign $sql = "..." but dont execute it.

$sql = "UPDATE table SET insertdate = '$insertdate' WHERE resID IN ($resID)"; 
mysql_query($sql); // Run the query

grumpy

I added the code you supplied but the table is still not updating and I'm not getting any errors. What else can I do. Thanks for your help.

cahva

You should really read answers more carefully 🙂 In my first answer, I said that print the generated $sql to see how it looks like and if its valid. So after the $sql:

echo $sql;

..and see if it is what its supposed to be. You can also use [man]mysql_error[/man] to see if it gives any mysql related error.

grumpy

When I echo the sql this is what I get, but I don't know what's wrong:

UPDATE resumes-v2 SET insertdate = '' WHERE resID IN ('23638, 2005-05-26 20:18:00.0 23704, 2005-06-16 15:10:00.0 23782, 2005-07-05 ','18:45:00.0 23994, 2005-08-17 09:29:00.0 24219, 2005-10-12 18:47:00.0 24724, ','2005-12-14 15:13:00.0 25043, 2006-02-16 16:10:00.0 25059, 2006-02-20 20:45:00.0 ','25076, 2006-02-23 22:14:00.0 25340, 2006-04-21 15:11:00.0 25883, 2006-07-10 ','11:31:00.0 25891, 2006-07-11 13:24:00.0 25928, 2006-07-18 10:17:00.0 25987, ','2006-07-26 16:22:00.0 26040, ....etc.

cahva

Woah.. Thats definately not the query you should be running.

If you have the csv like this:

23638, 2005-05-26 20:18:00.0
23704, 2005-06-16 15:10:00.0
23782, 2005-07-05 18:45:00.0

When you are going through the lines, you ofcourse need to split those to get proper fields. Anyway, heres the code:

$lines = file('insertupdate.txt');

foreach ($lines as $line)
{
	$tmp = explode(',',trim($line)); // Trim removes newline and spaces from start and end

$resid = trim($tmp[0]);
$date = trim($tmp[1]);

$sql = sprintf("UPDATE table SET insertdate = '&#37;s' WHERE resID=%d",
	mysql_real_escape_string($date),
	mysql_real_escape_string($resid)
);

$result = mysql_query($sql);
}

grumpy

Sorry, I guess I' m not understanding. When I echo the sql now I get:
UPDATE table SET insertdate = '2005-05-26 20:18:00.0 23704' WHERE resID=23638UPDATE table SET insertdate = '2005-08-17 09:29:00.0 24219' WHERE resID=18UPDATE table SET insertdate = '2005-12-14 15:13:00.0 25043' WHERE resID=24724UPDATE table SET insertdate = '2006-02-23 22:14:00.0 25340' WHERE resID=20UPDATE table SET insertdate = '2006-07-10 11:31:00.0 25891' WHERE resID=25883UPDATE table SET insertdate = '2006-07-26 16:22:00.0 26040' WHERE resID=10UPDATE table SET insertdate = '2006-08-02 10:43:00.0 26470' WHERE resID=26087UPDATE table SET insertdate = '2006-09-12 00:06:00.0 26503' WHERE resID=14UPDATE table SET insertdate = '2006-09-20 13:49:00.0 26564' WHERE ...etc.

The insertdates include the ID number and some of the resID are incorrect. What am I missing? Thanks.

cahva

Give couple of lines from the csv (with code tags, thank you). Obviously the CSV is not in the format like I put in my last message.

grumpy

Here are the first few lines from the simple text file. There are no code tags. If it would help, instead of this format, I can generate the file with any delimiting characters. I'm getting it by querying a MSSQL db using cold fusion. Thanks.

23638, 2005-05-26 20:18:00.0 23704, 2005-06-16 15:10:00.0 23782, 2005-07-05 18:45:00.0 23994, 2005-08-17 09:29:00.0 24219, 2005-10-12 18:47:00.0 24724, 2005-12-14 15:13:00.0 25043, 2006-02-16 16:10:00.0 25059, 2006-02-20 20:45:00.0 25076, 2006-02-23 22:14:00.0 25340, 2006-04-21 15:11:00.0 25883, 2006-07-10 11:31:00.0 25891, 2006-07-11 13:24:00.0 25928, 2006-07-18 10:17:00.0 25987, 2006-07-26 16:22:00.0 26040, 2006-08-01 11:42:00.0 26087, 2006-08-02 10:43:00.0 26470, 2006-09-05 13:11:00.0 26473, 2006-09-05 14:01:00.0 26502, 2006-09-12 00:06:00.0 26503, 2006-09-12 20:18:00.0 26551, 2006-09-20

cahva

I meant by code tags this forum's code tags. Just try to get the data as I showed earlier and the code should work:

23638, 2005-05-26 20:18:00.0
23704, 2005-06-16 15:10:00.0
23782, 2005-07-05 18:45:00.0

BTW, if you use MSSQL, which as the name suggests is an SQL server, you should be able to get a backup of the database and restore it straight with mysql.

grumpy

Okay. Thanks for your help.