Update MySQL from a text file

[Admin]

Hi,

I want to update a MySQL DB I have with the contents from a text file but it's a bit beyond me.

I connect to my DB
Search for a Domain Name against one from the text file.

Now I want to update 1 field for that Domains record from the text file, this is what I am using:

<?php
if ($_REQUEST["do"]!="update") {
?>
<form enctype="multipart/form-data" action="update.php" method="POST">
Please choose a file: <input name="uploaded" type="file" /><br />
<input type="hidden" name="do" value="update" />
<input type="submit" value="Update" />
</form> 
<?php } else {
$ok=1; 
$db = mysql_pconnect($hostname_db, $username_db, $password_db) or die(mysql_error());
if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target)) { 
 echo "The file has been uploaded, now processing...."; 
 $row = 0;
 $handle = fopen($target, "r");
 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
  $num = count($data);
  echo "<p> Checking : $data[0]\n";
  $sql = "SELECT Domain_Name FROM domains WHERE Domain_Name = '$data[0]'";
  mysql_select_db($database_db, $db);
  $query_Search = $sql;
  $Search = mysql_query($query_Search, $db) or die(mysql_error());
  $row_Search = mysql_fetch_assoc($Search);
  $all_Search = mysql_query($query_Search);
  $totalRows_Search = mysql_num_rows($all_Search);
  if ($totalRows_Search !=0){
   echo " -> updating domains\n";
       $sql = " ";
     $sql= "UPDATE `domains` SET (`expiration`) VALUES ('','";
   $sql .= $data[1] ."');";
 
   mysql_select_db($database_db, $db);
   $query_Search = $sql;
   $Search = mysql_query($query_Search, $db) or die(mysql_error());
   echo " -> completed<br /></p>\n";
      $row++;
  }
 }
 fclose($handle);
 echo "<p> ". $row." domains updated.<br /></p>\n";
} 
else { 
 echo "Sorry, there was a problem uploading your file."; 
} 
} 
?>

When I run it tells me there is an error near 'expiration'.

Any help would be appreciated.

Admin

 

[accelerator]

First I would suggest that for:

$sql= "UPDATE `domains` SET (`expiration`) VALUES ('','";
$sql .= $data[1] ."');";

try writing it all as one line instead of splitting it over two lines.

If that doesn't help, check carefully that all your single quotes and double quotes in this line are right as errors are often related to that.

Hope that helps.

Rgds

 

[accelerator]

This might work:

$sql= "UPDATE `domains` SET (`expiration`) VALUES ('','". $data[1] ."');";

I think you might have been missing a full stop, I feel the error is probably around the $data statement anyway.

Hope that might help.

Rgds

 

[DaveH]

I use Navicat for really simple stuff like this. No point in creating a script for something so simple IMHO.

 

[Admin]

It's definately failing on that line but your suggestion didn't work. Thanks anyway

Is there a way to validate which bit of the code?

 

[DaveH]

$sql= "UPDATE `domains` SET (`expiration`) VALUES ('','";
$sql .= $data[1] ."');";


should be

$sql= "UPDATE `domains` SET (`expiration`) VALUES ('";
$sql .= $data[1] ."');";

You've got one to many parameters in the values bit.

 

[Admin]

Checking : domain.co.uk -> updating domains You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`expiration`) VALUES (' 2009-05-11')' at line 1

In my text file, the first bit of data is the domain name and the second the expiry dat, so i want to match but not update the domain, then update the expiration field.

Thanks for the help

 

[DaveH]

Ah you're doing an UPDATE statement in the format of an INSERT

you need to do something like

$sql= "UPDATE `domains` SET `expiration` = '". $data[1] ."';";

I also notice you need to trim that space in the date

 

[Admin]

Your command works but over-wrote every domain in the DB with the value from the first domain in the text file.

Basically, I need it to match each domain in the file and update the expiration date with the one also in the file relevant to that particular domain.

thanks

 

[DaveH]

You're missing your WHERE clause on the update statement try:

$sql= "UPDATE `domains` SET `expiration` = '". $data[1] ."' WHERE `Domain_Name` = '".$data[0] ."' ;";

ensuring that Domain_Name is the actual column name

DISCLAIMER: Run at your own risk

 

[Admin]

haha thanks David, I have a backup don't worry, I'm glad of your help.