Adding data into a database

[calumogg]

Hi I am using the code below to search a folder and add data into my database, but the problem with it is whenever I run the script it clears the database then adds all the data again, how can I change it so that it will only add the new files into the database?


<form id="update" name="update" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<p>
<input type="hidden" name="submit" value="true">

<input name="Submit" type="submit" value="Update">
</p>
<?
if ($_POST['submit']=='true') {

$host="******"; // Host name
$username="******"; // Mysql username
$password="******"; // Mysql password
$db_name="******"; // Database name
$tbl_name="******"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect server ");
mysql_select_db("$db_name")or die("cannot select DB");

$sql = "TRUNCATE TABLE `$tbl_name`";
mysql_query($sql);

// Define the full path to the folder whose contents you want to list
$path = ".";

//File Types allowed
$pattern="\.(jpg|jpeg)$";

// Open the directory
$dir_handle = @opendir($path) or die("Error opening $path");

echo "The Update was sucessful<br>";
echo "Files added are listed below:<br>";

// Loop through the files and filter out no jpgs
while(false !== ($file = readdir($dir_handle))){
if(eregi($pattern, $file)){

//Prints the list of pictures
echo "<a href=\"$file\">$file</a><br />";

//Insert data into database
$sql="INSERT INTO $tbl_name (file_name, large, thumbnail)VALUES('$file', 'http://www.calumogg.co.uk/landscapes/large/$file', 'http://www.calumogg.co.uk/landscapes/thumbs/$file')";
$result=mysql_query($sql);

}
}

// Close
closedir($dir_handle);

}

else { exit; }

?>

Thanks in advance for any help.

[sfowler]

The table is emptying because of these lines:


$sql = "TRUNCATE TABLE `$tbl_name`";
mysql_query($sql);

After that, to only add new files to the database, you need to do a select based on the final url to see if a record exists with that url already, then if
!(mysql_num_rows($result) > 0)

then insert the new url.

[calumogg]

Thanks for the reply, I think I follow what you mean, but I am not sure of how to put it into code. I know I need to remove :
$sql = "TRUNCATE TABLE `$tbl_name`";
mysql_query($sql);

but after that I am lost!

[calumogg]

I have tried changing the code I poster earlier and have come up with this new one:


<?
$host="******"; // Host name
$username="******"; // Mysql username
$password="******"; // Mysql password
$db_name="******"; // Database name
$tbl_name="******"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect server ");
mysql_select_db("$db_name")or die("cannot select DB");

// Define the full path to the folder whose contents you want to list
$path = ".";

//File Types allowed
$pattern="\.(jpg|jpeg)$";

// Open the directory
$dir_handle = @opendir($path) or die("Error opening $path");

// Loop through the files and filter out no jpgs
while(false !== ($file = readdir($dir_handle))){
if(eregi($pattern, $file)){

$query = "SELECT filename FROM $tbl_name";
$result = mysql_query($query) or die('Error, query failed one');
while($rows=mysql_fetch_array($result)){

if ($row['filename']=="$file") {

echo "<p>$file already exists in database</p>"; }

else {

//Insert data into database
$sql="INSERT INTO $tbl_name (filename, category, large, thumb)VALUES('$file', 'landscape' 'http://www.calumogg.co.uk/landscapes/large/$file', 'http://www.calumogg.co.uk/landscapes/thumbs/$file')";
$result = mysql_query($query) or die('Error, query failed two');

echo "<p>$file has been added to the database</p>"; }

}}}

// Close
closedir($dir_handle);

?>

And this still isnt working. Any help would be much appreciated

[calumogg]

Does no one else have any ideas?

[calumogg]

I have sorted this myself now