Pulling data from SQL, and writing to a text file.

Question

I am trying to pull data from SQL, and then write it to a text file. This does that, to an extent, but it only pulls 1 from the table, which reads test:test<br> on the text file.

I want to be able to pull all the data from the table, and then post to the text file in a list format such as this...

    test:test
    test2:test2
    test3:test3

I need to find out what I am doing wrong.

<?php
$sql = mysql_query("SELECT * FROM _$setprofile ORDER BY fc DESC");
while($row = mysql_fetch_array($sql)){
$user = $row['user'];
$pass = $row['pass'];

$accounts = "$user:$pass<br>";

//Functionsss!
$file = "backups/$newcode.txt";
file_put_contents($file, $accounts);
}

echo "<a href=backups/$newcode.txt>TEST!</a>";
?>

Answer 1

The file_put_contents() function overwrites the whole file - that's why you end up with only the last record each time.

You can use fopen() and fwrite() instead.

While a little more complicated than building a large string and using a single call to file_put_contents, this won't run out of memory if you have a lot of records.

<?php

$file = "backups/$newcode.txt";
$f = fopen($file, 'w'); // Open in write mode

$sql = mysql_query("SELECT * FROM _$setprofile ORDER BY fc DESC");
while($row = mysql_fetch_array($sql))
{
    $user = $row['user'];
    $pass = $row['pass'];

    $accounts = "$user:$pass<br>";
    // Or "$user:$pass\n" as @Benjamin Cox points out

    fwrite($f, $accounts);
}

fclose($f);

echo "<a href=backups/$newcode.txt>TEST!</a>";
?>

Answer 2

file_put_contents overwrites the existing file. With the above code, you'll only ever get one line.

Try this instead:

<?php

$sql = mysql_query("SELECT * FROM _$setprofile ORDER BY fc DESC");
$content = "";

while($row = mysql_fetch_array($sql)){
  $user = $row['user'];
  $pass = $row['pass'];

  $accounts = "$user:$pass<br>";
  $content .= $accounts;

}

$file = "backups/$newcode.txt";
file_put_contents($file, $content);

echo "<a href=backups/$newcode.txt>TEST!</a>";
?>

Answer 3

You're not appending your database results to the $accounts string; you're creating it from scratch each time. Try something like this:

<?php
$accounts = "";
$sql = mysql_query("SELECT * FROM _$setprofile ORDER BY fc DESC");
while($row = mysql_fetch_array($sql)) {
  $user = $row['user'];
  $pass = $row['pass'];

  $accounts .= "$user:$pass<br>";
}

//Functionsss!
$file = "backups/$newcode.txt";
file_put_contents($file, $accounts);

echo "<a href=backups/$newcode.txt>TEST!</a>";
?>

so you append it, and then once you've got all your results in the $accounts string, you write it out to the file.

Answer 4

It looks like you are reopening and rewriting the entire contents of the file with every pass through your while loop.

Try this:

<?php
$sql = mysql_query("SELECT * FROM _$setprofile ORDER BY fc DESC");
$file = "backups/$newcode.txt";
$fh = fopen($file, 'a') or die("can't open file");

while($row = mysql_fetch_array($sql)){
  $user = $row['user'];
  $pass = $row['pass'];

  $accounts = "$user:$pass<br>";

  fwrite($fh, $accounts);
}

fclose($fh);

echo "<a href=backups/$newcode.txt>TEST!</a>";
?>

Also, if you don't want the < br >, but a real line break, use:

  $accounts = "$user:$pass\n";

Answer 5

Others have answered the why it is only writing one content into the file, this is more of a suggestion but if you want to record the actual user and password, instead of:

$accounts = "$user:$pass<br>";

use

$accounts = $user . ":" . $pass . "\n";

but if you already knew that and were using that for debugging purposes then disregard this.

Good luck

Answer 6

If it's a text file, the <br> tag will not be particularly useful to you, as well. You'll need to use \n to cause a newline to happen in a text file. If it was html, then we'd have a different situation.

$accounts = "$user:$pass<br>";

should be

$accounts .= "$user:$pass\n";

and you definitely should pull the file_put_contents out of the loop or you'll overwrite the file every time you go through the loop.

Answer 7

echo "select concat(user,':',pass,'<br>') from table order by fc desc" | mysql <database>

I prefer to do this kind of thing in bash ;)