Programming Forums > Web Development and Design > PHP

piercy · Nov 21st, 2006, 1:15 PM

i know this is probably really simple... but im writing a fairly complicated download script which adds info to database depending on what is located in the downloads folder. im trying to get php to delete the row from the database if the file gets deleted from the directory. i have figured easiest way is to compare arrays. one database array and one directory array. thing is im not sure how to get a SQL column as an array. all i need is one column as an array. i know php can put rows into arrays but i coudltn work out columns.

thanks in advance,
Piercy

Jimbo · Nov 21st, 2006, 4:10 PM

maybe something like

(Toggle Plain Text)

$results = mysql_query("SELECT theColumn FROM someTable WHERE someCondition", $db_link);
$thearray = new array();
while($row = mysql_fetch_array($results))
  array_push($thearray, $row[0])

this code is not tested in the least, but hopefully it's the right idea.

piercy · Nov 22nd, 2006, 12:23 PM

i tried that witha few adjustments here and there but it seemd to out put nothing at all. i had array printing in for debugging and even that aint working. plus the code originally outputted a table displaying all rows in the table. Also below is the whole of my code(twice one with you code one with my own that didnt work.) ALso should be output screen shots here:
http://www.1hess.com/piercy/images/

Heres the changes i made:

(Toggle Plain Text)

$results = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads");
$dbfilearray = new array();
while($row = mysql_fetch_array($results)) {
  array_push($dbfilearray, $row[0]);
  }

Code with your addition:

(Toggle Plain Text)

<?php
$server = 'eupub';
$etmain_cid = '10';
$etpub_cid = '13';
$jaymod_cid = '';
$etpro_cid = '';

$date = date('Y-m-d H-i-s');
$etmain = './etmain';
$etpub = './etpub';
$jaymod = './jaymod';

mysql_connect('localhost','***','*****');


////////////////////////////////////
////////////Etmain/////////////////
//////////////////////////////////




if($directory = opendir($etmain)){

$filearray = scandir($etmain);

print_r($filearray);
echo '<br><br>';

$results = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads");
$dbfilearray = new array();
while($row = mysql_fetch_array($results)) {
  array_push($dbfilearray, $row[0]);
  }

print_r($dbfilearray);
echo '<br><br>';
$diff = array_diff($filearray, $dbfilearray);
sort($diff);
$size = count($diff);

print_r($diff);
echo '<br><br>';
while ($num <= $size) {
$filedelete = $array[1];
mysql_query("DELETE * FROM c1hess_evo69.nuke_downloads_downloads WHERE `title`='$filedelete'");
array_splice($diff,1,1);
sort($diff);
$num++;
}
?>

My code before your addition:

(Toggle Plain Text)

<?php
$server = 'eupub';
$etmain_cid = '10';
$etpub_cid = '13';
$jaymod_cid = '';
$etpro_cid = '';

$date = date('Y-m-d H-i-s');
$etmain = './etmain';
$etpub = './etpub';
$jaymod = './jaymod';

mysql_connect('localhost','****','****');


////////////////////////////////////
////////////Etmain/////////////////
//////////////////////////////////




if($directory = opendir($etmain)){

$filearray = scandir($etmain);

print_r($filearray);
echo '<br><br>';
$dbrow = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads");
$dbfilearray = mysql_fetch_array($dbrow);

print_r($dbfilearray);
echo '<br><br>';
$diff = array_diff($filearray, $dbfilearray);
sort($diff);
$size = count($diff);

print_r($diff);
echo '<br><br>';
while ($num <= $size) {
$filedelete = $array[1];
mysql_query("DELETE * FROM c1hess_evo69.nuke_downloads_downloads WHERE `title`='$filedelete'");
array_splice($diff,1,1);
sort($diff);
$num++;
}

echo '<table border="1">Etmain additions:';
while($file = readdir($directory)) {


		if($file != "." && $file != ".." && $file != "index.html"){
		$stat = stat($etmain . '/' . $file);
		$filesize = $stat[7];
		$date = $stat[9];
		echo '<tr><td>' . $file . '</td><td>' . $filesize . '</td></tr>';


mysql_query("INSERT INTO c1hess_evo69.nuke_downloads_downloads (`cid`,`title`,`url`,`description`,`date`,`submitter`,`filesize`,`sub_ip`,`active`) VALUES ('$etmain_cid','$file','http://www.1hess.com/modsandmaps/$server/etmain/$file','Game File for [HESS] $server download to etmain.','$date','Piercy\'s directory bot','$filesize','$ip','1')");


}
}
echo '</table>';

closedir($directory);
}
?>

Ignore some of the excess variables. they bare there for easy change. (as i have to do this for 4 maybe more folders.).

I really appreciate your help,

Jimbo · Nov 22nd, 2006, 8:29 PM

this is a code excerpt I had laying around:

(Toggle Plain Text)

        $query = "SELECT contact FROM Contacts WHERE user = " . $this->id;
        $result = mysql_query($query, $this->link);
        if(!$result)
          throw new MySQLException("MySQL Error: " . mysql_error(), $query);
        $this->contactIDs = array();
        while($row = @mysql_fetch_array($result))
          array_push($this->contactIDs, $row[0]);

and it works fine. If I get a chance Friday, I'll try to look through your code again...

piercy · Nov 23rd, 2006, 3:13 PM

A friend has also geven me this to compare the database column with the folder. although his gives the same error as yours. Pretty sure its something in my code or maybe even server side.

(Toggle Plain Text)

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
if (file_exists("path/to/files/".$row["files"])) {
echo "file ".$row["files"]." exists.<br>";
} else {
echo "file ".$row["files"]." does not exist, removing from db.<br>";
mysql_query("DELETE FROM downloads WHERE files=$row['files']");
}
}

Thanks for the help,

piercy · Nov 23rd, 2006, 5:06 PM

cant work out to edit the above post. i managed to get the above code working. it all works fine. All i need help on now is Zipping files. (in another thread)

Thanks for your effort,

Nov 21st, 2006, 1:15 PM	#1
piercy Programmer Join Date: Apr 2006 Posts: 35 Rep Power: 0	SQL column as array i know this is probably really simple... but im writing a fairly complicated download script which adds info to database depending on what is located in the downloads folder. im trying to get php to delete the row from the database if the file gets deleted from the directory. i have figured easiest way is to compare arrays. one database array and one directory array. thing is im not sure how to get a SQL column as an array. all i need is one column as an array. i know php can put rows into arrays but i coudltn work out columns. thanks in advance, Piercy __________________ this forum rules you guys are great! thanks to all who help piercy

Nov 21st, 2006, 4:10 PM	#2
Jimbo Battle Programmer Join Date: Feb 2006 Location: Bellevue, WA, USA Posts: 763 Rep Power: 3	maybe something like (Toggle Plain Text) $results = mysql_query("SELECT theColumn FROM someTable WHERE someCondition", $db_link); $thearray = new array(); while($row = mysql_fetch_array($results)) array_push($thearray, $row[0]) $results = mysql_query("SELECT theColumn FROM someTable WHERE someCondition", $db_link); $thearray = new array(); while($row = mysql_fetch_array($results)) array_push($thearray, $row[0]) this code is not tested in the least, but hopefully it's the right idea. __________________ <insert disclaimer here> <insert shameless plug for Visual Studio here>

Nov 22nd, 2006, 12:23 PM	#3
piercy Programmer Join Date: Apr 2006 Posts: 35 Rep Power: 0	i tried that witha few adjustments here and there but it seemd to out put nothing at all. i had array printing in for debugging and even that aint working. plus the code originally outputted a table displaying all rows in the table. Also below is the whole of my code(twice one with you code one with my own that didnt work.) ALso should be output screen shots here: http://www.1hess.com/piercy/images/ Heres the changes i made: (Toggle Plain Text) $results = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads"); $dbfilearray = new array(); while($row = mysql_fetch_array($results)) { array_push($dbfilearray, $row[0]); } $results = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads"); $dbfilearray = new array(); while($row = mysql_fetch_array($results)) { array_push($dbfilearray, $row[0]); } Code with your addition: (Toggle Plain Text) <?php $server = 'eupub'; $etmain_cid = '10'; $etpub_cid = '13'; $jaymod_cid = ''; $etpro_cid = ''; $date = date('Y-m-d H-i-s'); $etmain = './etmain'; $etpub = './etpub'; $jaymod = './jaymod'; mysql_connect('localhost','*','**'); //////////////////////////////////// ////////////Etmain///////////////// ////////////////////////////////// if($directory = opendir($etmain)){ $filearray = scandir($etmain); print_r($filearray); echo '<br><br>'; $results = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads"); $dbfilearray = new array(); while($row = mysql_fetch_array($results)) { array_push($dbfilearray, $row[0]); } print_r($dbfilearray); echo '<br><br>'; $diff = array_diff($filearray, $dbfilearray); sort($diff); $size = count($diff); print_r($diff); echo '<br><br>'; while ($num <= $size) { $filedelete = $array[1]; mysql_query("DELETE FROM c1hess_evo69.nuke_downloads_downloads WHERE `title`='$filedelete'"); array_splice($diff,1,1); sort($diff); $num++; } ?> <?php $server = 'eupub'; $etmain_cid = '10'; $etpub_cid = '13'; $jaymod_cid = ''; $etpro_cid = ''; $date = date('Y-m-d H-i-s'); $etmain = './etmain'; $etpub = './etpub'; $jaymod = './jaymod'; mysql_connect('localhost','*','**'); //////////////////////////////////// ////////////Etmain///////////////// ////////////////////////////////// if($directory = opendir($etmain)){ $filearray = scandir($etmain); print_r($filearray); echo '<br><br>'; $results = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads"); $dbfilearray = new array(); while($row = mysql_fetch_array($results)) { array_push($dbfilearray, $row[0]); } print_r($dbfilearray); echo '<br><br>'; $diff = array_diff($filearray, $dbfilearray); sort($diff); $size = count($diff); print_r($diff); echo '<br><br>'; while ($num <= $size) { $filedelete = $array[1]; mysql_query("DELETE FROM c1hess_evo69.nuke_downloads_downloads WHERE `title`='$filedelete'"); array_splice($diff,1,1); sort($diff); $num++; } ?> My code before your addition: (Toggle Plain Text) <?php $server = 'eupub'; $etmain_cid = '10'; $etpub_cid = '13'; $jaymod_cid = ''; $etpro_cid = ''; $date = date('Y-m-d H-i-s'); $etmain = './etmain'; $etpub = './etpub'; $jaymod = './jaymod'; mysql_connect('localhost','**','*'); //////////////////////////////////// ////////////Etmain///////////////// ////////////////////////////////// if($directory = opendir($etmain)){ $filearray = scandir($etmain); print_r($filearray); echo '<br><br>'; $dbrow = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads"); $dbfilearray = mysql_fetch_array($dbrow); print_r($dbfilearray); echo '<br><br>'; $diff = array_diff($filearray, $dbfilearray); sort($diff); $size = count($diff); print_r($diff); echo '<br><br>'; while ($num <= $size) { $filedelete = $array[1]; mysql_query("DELETE FROM c1hess_evo69.nuke_downloads_downloads WHERE `title`='$filedelete'"); array_splice($diff,1,1); sort($diff); $num++; } echo '<table border="1">Etmain additions:'; while($file = readdir($directory)) { if($file != "." && $file != ".." && $file != "index.html"){ $stat = stat($etmain . '/' . $file); $filesize = $stat[7]; $date = $stat[9]; echo '<tr><td>' . $file . '</td><td>' . $filesize . '</td></tr>'; mysql_query("INSERT INTO c1hess_evo69.nuke_downloads_downloads (`cid`,`title`,`url`,`description`,`date`,`submitter`,`filesize`,`sub_ip`,`active`) VALUES ('$etmain_cid','$file','http://www.1hess.com/modsandmaps/$server/etmain/$file','Game File for [HESS] $server download to etmain.','$date','Piercy\'s directory bot','$filesize','$ip','1')"); } } echo '</table>'; closedir($directory); } ?> <?php $server = 'eupub'; $etmain_cid = '10'; $etpub_cid = '13'; $jaymod_cid = ''; $etpro_cid = ''; $date = date('Y-m-d H-i-s'); $etmain = './etmain'; $etpub = './etpub'; $jaymod = './jaymod'; mysql_connect('localhost','**','*'); //////////////////////////////////// ////////////Etmain///////////////// ////////////////////////////////// if($directory = opendir($etmain)){ $filearray = scandir($etmain); print_r($filearray); echo '<br><br>'; $dbrow = mysql_query("SELECT `title` FROM c1hess_evo69.nuke_downloads_downloads"); $dbfilearray = mysql_fetch_array($dbrow); print_r($dbfilearray); echo '<br><br>'; $diff = array_diff($filearray, $dbfilearray); sort($diff); $size = count($diff); print_r($diff); echo '<br><br>'; while ($num <= $size) { $filedelete = $array[1]; mysql_query("DELETE FROM c1hess_evo69.nuke_downloads_downloads WHERE `title`='$filedelete'"); array_splice($diff,1,1); sort($diff); $num++; } echo '<table border="1">Etmain additions:'; while($file = readdir($directory)) { if($file != "." && $file != ".." && $file != "index.html"){ $stat = stat($etmain . '/' . $file); $filesize = $stat[7]; $date = $stat[9]; echo '<tr><td>' . $file . '</td><td>' . $filesize . '</td></tr>'; mysql_query("INSERT INTO c1hess_evo69.nuke_downloads_downloads (`cid`,`title`,`url`,`description`,`date`,`submitter`,`filesize`,`sub_ip`,`active`) VALUES ('$etmain_cid','$file','http://www.1hess.com/modsandmaps/$server/etmain/$file','Game File for [HESS] $server download to etmain.','$date','Piercy\'s directory bot','$filesize','$ip','1')"); } } echo '</table>'; closedir($directory); } ?> Ignore some of the excess variables. they bare there for easy change. (as i have to do this for 4 maybe more folders.). I really appreciate your help, __________________ this forum rules you guys are great! thanks to all who help piercy

Nov 22nd, 2006, 8:29 PM	#4
Jimbo Battle Programmer Join Date: Feb 2006 Location: Bellevue, WA, USA Posts: 763 Rep Power: 3	this is a code excerpt I had laying around: (Toggle Plain Text) $query = "SELECT contact FROM Contacts WHERE user = " . $this->id; $result = mysql_query($query, $this->link); if(!$result) throw new MySQLException("MySQL Error: " . mysql_error(), $query); $this->contactIDs = array(); while($row = @mysql_fetch_array($result)) array_push($this->contactIDs, $row[0]); $query = "SELECT contact FROM Contacts WHERE user = " . $this->id; $result = mysql_query($query, $this->link); if(!$result) throw new MySQLException("MySQL Error: " . mysql_error(), $query); $this->contactIDs = array(); while($row = @mysql_fetch_array($result)) array_push($this->contactIDs, $row[0]); and it works fine. If I get a chance Friday, I'll try to look through your code again... __________________ <insert disclaimer here> <insert shameless plug for Visual Studio here>

Nov 23rd, 2006, 3:13 PM	#5
piercy Programmer Join Date: Apr 2006 Posts: 35 Rep Power: 0	A friend has also geven me this to compare the database column with the folder. although his gives the same error as yours. Pretty sure its something in my code or maybe even server side. (Toggle Plain Text) while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { if (file_exists("path/to/files/".$row["files"])) { echo "file ".$row["files"]." exists.<br>"; } else { echo "file ".$row["files"]." does not exist, removing from db.<br>"; mysql_query("DELETE FROM downloads WHERE files=$row['files']"); } } while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { if (file_exists("path/to/files/".$row["files"])) { echo "file ".$row["files"]." exists.<br>"; } else { echo "file ".$row["files"]." does not exist, removing from db.<br>"; mysql_query("DELETE FROM downloads WHERE files=$row['files']"); } } Thanks for the help, __________________ this forum rules you guys are great! thanks to all who help piercy

Nov 23rd, 2006, 5:06 PM	#6
piercy Programmer Join Date: Apr 2006 Posts: 35 Rep Power: 0	cant work out to edit the above post. i managed to get the above code working. it all works fine. All i need help on now is Zipping files. (in another thread) Thanks for your effort, __________________ this forum rules you guys are great! thanks to all who help piercy

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
changing size of an array	Eric the Red	Java	3	Apr 3rd, 2006 8:19 PM
How To Split Array Into Two Seperate Arrays?	blackmagic	Perl	1	Apr 30th, 2005 2:16 PM
Pointers in C (Part I)	Stack Overflow	C	4	Apr 28th, 2005 7:03 PM
Installing IPB 2.03	bh4575	Other Web Development Languages	0	Apr 23rd, 2005 2:36 AM
Converting 1-dimensional array to 2-dimensional array	Tazz_Mission_13	Java	6	Apr 8th, 2005 11:58 AM

Programming Forums > Web Development and Design > PHP

SQL column as array