Domain Manage

Update MySQL from a text file

Discussion in 'Scripts and Coding' started by admin, Jun 15, 2008.

Thread Status:
Not open for further replies.
  1. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    9,923
    Likes Received:
    82
    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:

    Code:
    <?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
     
  2. Domain Forum

    Acorn Domains Elite Member

    Joined:
    1999
    Messages:
    Many
    Likes Received:
    Lots
     
  3. accelerator United Kingdom

    accelerator Well-Known Member

    Joined:
    Apr 2005
    Posts:
    7,253
    Likes Received:
    102
    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
     
  4. accelerator United Kingdom

    accelerator Well-Known Member

    Joined:
    Apr 2005
    Posts:
    7,253
    Likes Received:
    102
    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
     
    Last edited: Jun 15, 2008
  5. DaveH

    DaveH Active Member

    Joined:
    Apr 2008
    Posts:
    592
    Likes Received:
    7
    I use Navicat for really simple stuff like this. No point in creating a script for something so simple IMHO.
     
  6. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    9,923
    Likes Received:
    82
    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?
     
  7. DaveH

    DaveH Active Member

    Joined:
    Apr 2008
    Posts:
    592
    Likes Received:
    7
    $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.
     
  8. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    9,923
    Likes Received:
    82
    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
     
    Last edited: Jun 15, 2008
  9. DaveH

    DaveH Active Member

    Joined:
    Apr 2008
    Posts:
    592
    Likes Received:
    7
    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
     
  10. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    9,923
    Likes Received:
    82
    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
     
  11. DaveH

    DaveH Active Member

    Joined:
    Apr 2008
    Posts:
    592
    Likes Received:
    7
    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
     
Thread Status:
Not open for further replies.

Share This Page