Domain Forum from Acorn Domains

Today's Drop Dates are: 16-08-2014 or 23-08-2014   All times are GMT +1. The time now is 10:00:01 PM.
Domain Sales Prices Services Offered Domain Drop Dates
Go Back   UK Domain Forum Acorn Domains > Website Design and Promotion > Website Design > Scripts and Coding

Scripts and Coding PHP, MySQL, scripts

Closed Thread
 
LinkBack Thread Tools Display Modes
Old 15-06-2008, 02:40:37 PM     #1 (permalink)
Administrator
 
admin's Avatar
 
Join Date: Jun 2004
Posts: 9,447
admin has disabled reputation

Update MySQL from a text file

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
admin is offline  
Old 15-06-2008, 09:09:32 PM     #2 (permalink)

 
accelerator's Avatar
 
Join Date: Apr 2005
Location: England
Posts: 6,112
accelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond repute

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 is offline  
Old 15-06-2008, 09:11:31 PM     #3 (permalink)

 
accelerator's Avatar
 
Join Date: Apr 2005
Location: England
Posts: 6,112
accelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond reputeaccelerator has a reputation beyond repute

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 by accelerator; 15-06-2008 at 09:17:24 PM.
accelerator is offline  
Old 15-06-2008, 09:51:17 PM     #4 (permalink)

 
DaveH's Avatar
 
Join Date: Apr 2008
Location: Southern England
Posts: 595
DaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond repute

I use Navicat for really simple stuff like this. No point in creating a script for something so simple IMHO.
DaveH is offline  
Old 15-06-2008, 10:10:36 PM     #5 (permalink)
Administrator
 
admin's Avatar
 
Join Date: Jun 2004
Posts: 9,447
admin has disabled reputation

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?
admin is offline  
Old 15-06-2008, 10:14:09 PM     #6 (permalink)

 
DaveH's Avatar
 
Join Date: Apr 2008
Location: Southern England
Posts: 595
DaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond repute

$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.
DaveH is offline  
Old 15-06-2008, 10:33:45 PM     #7 (permalink)
Administrator
 
admin's Avatar
 
Join Date: Jun 2004
Posts: 9,447
admin has disabled reputation

Quote:
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

Last edited by admin; 15-06-2008 at 10:36:39 PM.
admin is offline  
Old 15-06-2008, 10:39:55 PM     #8 (permalink)

 
DaveH's Avatar
 
Join Date: Apr 2008
Location: Southern England
Posts: 595
DaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond repute

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
DaveH is offline  
Old 15-06-2008, 11:02:25 PM     #9 (permalink)
Administrator
 
admin's Avatar
 
Join Date: Jun 2004
Posts: 9,447
admin has disabled reputation

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
admin is offline  
Old 15-06-2008, 11:07:46 PM     #10 (permalink)

 
DaveH's Avatar
 
Join Date: Apr 2008
Location: Southern England
Posts: 595
DaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond reputeDaveH has a reputation beyond repute

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
DaveH is offline  
Closed Thread



Bookmarks

Tags
update mysql text file

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Similar Threads
Thread Thread Starter Domain Name Community Replies Last Post
Revenue Update NameDriver NameDrive 0 06-05-2008 09:05:51 PM
MySQL help fish Services Wanted 9 10-09-2007 11:59:49 AM
UK Zone File - where can I find the last publically released version (2003)? TravelSite Domain Research 2 08-03-2007 12:31:08 PM
Help with Photoshop PSD file admin Website Design 3 26-05-2006 11:36:51 PM


All times are GMT +1. The time now is 10:00:01 PM.


Powered by vBulletin® Version 3.7.3
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.6.1
All content on Acorn Domains is member generated and is not moderated before posting. All content is viewed and used by you at your own risk and AD does not warrant the accuracy or reliability of any of the information. The views expressed are those of the individual contributors and not necessarily those of AD. Please contact us to report any issues or send a PM to "Admin".