While $row > obviously not

[darthmahon]

Hey,

I have a list of information in a database such as names and addresses. I want people to be able to download a file of these, but I only want these names and addresses to be given to one person.

So I wrote the following:

<?php

mysql_connect(localhost, user, pass);
mysql_select_db(dbname);

$result = mysql_query("SELECT id, firstname, lastname, email FROM userstest WHERE registered=1 LIMIT 10");

$file = fopen("registered.csv", "w");

while($row = mysql_fetch_row($result))

{
    $line = implode(",", $row) ."\n";
    fwrite($file, $line);
    mysql_query ("DELETE FROM userstest WHERE id='$id'");

}

fclose($file);

echo "<font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">File has been completed, click <a href='registered.csv'>here</a> to download the file.</font>";

?>

Now this selects the 10 people as required, but it doesn't delete their record from the database. Any ideas how I need to go about doing this, while it has the row selected?

Cheers,
Chris

[bastien]

bad dog, no cross posting

[tbach2]

-OK-

I don't see why you would ever want to have something in a database only to write it once in a file... That's kinda backwards.

The reason it is not deleting is because $id is not defined in or out of the loop.

If you're wanting to take the contents of the database and allow someone to download it as a comma separated file, use the header function to change the way the browser displays the file, ie

$download_me = $_GET['download_me'];
if($download_me) {
   // make browser output downloadable
   header("Content-type: application/octet-stream"); 
   header("Content-Disposition: attachment; filename=registered.csv"); 
   header("Content-Transfer-Encoding: binary");

   // get stuff out of database
  mysql_connect(localhost, user, pass);
   mysql_select_db(dbname);

   $result = mysql_query("SELECT id, firstname, lastname, email FROM userstest WHERE registered=1 LIMIT 10");

   while($row = mysql_fetch_row($result)) {
       $line = implode(",", $row) ."\n";
       print $line;
   }
   // this stuff is being downloaded; stop here before default stuff is printed
   die;
}

printf("<div>Click here to <a href=\"%s\">download</a> registered users</div>",
        $_SERVER['PHP_SELF'] . "?download_me=true"
        );
?>

[darthmahon]

The client wants to be able to update the list themselves easily, so having it stored in the DB is easiest I think. They can easily upload a new list to it.

One thing, I have changed the following code:

while($row = mysql_fetch_row($result)) { 
       $line = implode(",", $row) ."\n"; 
       print $line;
       mysql_query ("DELETE FROM userstest WHERE id=' ".$row['id']." ' ");
   }

This doesn't seem to delete the row though, I'm sure I've got the WHERE id part right...or am I putting it in the wrong section?

Thanx for your help so far tho! 😃

[ForgotMyPass]

Im not sure but I think its the whitespace in the id part. Did you try removing it?

[tbach2]

sounds like its time for debugging 101

$query = "DELETE FROM userstest WHERE id=' ".$row['id']." ' ";
print $query;
//mysql_query ($query); 

[darthmahon]

What's that is doing is outputting none of the id numbers. So it's not selecting them properly or something.

Here is the code in full as it stands:

<?php

mysql_connect(localhost, user, pass);
mysql_select_db(dbname);

$download_me = $_GET['download_me']; 
if($download_me) { 

   // make browser output downloadable 
   //header("Content-type: application/octet-stream");  

   //header("Content-Disposition: attachment; filename=registered.csv");  

   //header("Content-Transfer-Encoding: binary"); 

   $result = mysql_query("SELECT id, firstname, lastname, email FROM userstest WHERE registered=1 LIMIT 400"); 
   $query = "DELETE FROM userstest WHERE id=' ".$row['id']." ' ";

   while($row = mysql_fetch_row($result)) { 
       print $query;
	   //$line = implode(",", $row) ."\n"; 
       //print $line;
   } 
   // this stuff is being downloaded; stop here before default stuff is printed 
   die; 
} 

printf("<div>Click here to <a href=\"%s\">download</a> registered users</div>", 
        $_SERVER['PHP_SELF'] . "?download_me=true" 
        ); 
?>

I've disabled the file download initation bit so that I can view the query and I get the following:

DELETE FROM userstest WHERE id=' '

Over and over...

[tbach2]

you'll laugh when you see it... 😉

   $query = "DELETE FROM userstest WHERE id=' ".$row['id']." ' ";

   while($row = mysql_fetch_row($result)) { 
       print $query;
       //$line = implode(",", $row) ."\n";
       //print $line;
   } 

$query is not in the loop, hence it never gets changed :p

Say, another idea (since the whole deletion business has me so bothered :rolleyes: ) Have a boolean field that you update to show whether a name has been used or not. That way if they want to reuse that list, its still there: all you have to do is reset the one field to show that all names are available again.
But, you know your customer better than I do. I'll quit sticking my nose where it doesn't belong 😉

[darthmahon]

😃 hehe I noticed that last night, I couldn't believe it...

What do you mean by a boolean field?

Anyway I've had to alter my code slightly and I've got it all working now. What I'm doing is after the row has been used I am updating a field called registered and entering "2" into that. Then when the while loop is finished it deletes all rows where registered=2.

I've told them to keep the data but they said it won't be used again so I think why waste the space on the db.

This is the code as it stands for the while loop:

while($row = mysql_fetch_object($result)) { 
       //$query = "DELETE FROM userstest WHERE id=' ".$row->id." ' ";
	   //print $query;
	   $array = array(" $row->email, $row->firstname, $row->lastname ");
	   $line = implode(",", $array) ."\n"; 
       print $line;
	   mysql_query ("UPDATE userstest SET registered='2' WHERE id=' ".$row->id." ' ");echo mysql_error();
   }

Thanx for your help! 😃

[tbach2]

boolean field means a field that contains either true or false. IOW, the field that you've created called 'registered' 😉

well, the customer's always right, even when they're downright goofy...

please be sure and mark this thread resolved.