Some major help for a n00bie

[DieSucker]

So I wrote a PHP script, works fine, but I would rather store the output in a mySQL database, rather then in another text file. The script works but I just need some help,first seeing if it is possible and secondly the output into html

So here is the script
[code]<?
/////////////////Server Info
$ftp_server = "****"
$ftp_user = "*****"
$ftp_pass = "*****"
$local_file = 'records.txt';
$server_file = 'BTRecords.ini';

// set up a connection or die
$conn_id = ftp_connect($ftp_server) or die("Couldn't connect to $ftp_server");

// try to login
ftp_login($conn_id, $ftp_user, $ftp_pass);

ftp_chdir($conn_id, "System");


// try to download $server_file and save to $local_file
if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY))

// close the connection
ftp_close($conn_id);


/////////////////////////DURATION FUNCTION
function duration($timestamp) {

$years=floor($timestamp / (60*60*24*365));
$timestamp%=60*60*24*365;

$weeks=floor($timestamp / (60*60*24*7));
$timestamp%=60*60*24*7;

$days=floor($timestamp / (60*60*24));
$timestamp%=60*60*24;
   
$hrs=floor($timestamp / (60*60));
$timestamp%=60*60;
   
$mins=floor($timestamp / 60);
$secs=$timestamp % 60;
 
  $str="";

if ($years >= 1) { $str.="{$years} years "; }
  if ($weeks >= 1) { $str.="{$weeks} weeks "; }
  if ($days >= 1) { $str.="{$days} days "; }
  if ($hrs >= 1) { $str.="{$hrs} hours "; }
  if ($mins >= 1) { $str.="{$mins}"; }

  // Checks if there are only seconds.
  if ($years < 1 && $weeks < 1 && $days < 1 && $hrs < 1 && $mins < 1)
      {$str = "0";}
  if ($secs >= 10) { $str.=":{$secs}"; }
  if ($secs < 10) { $str.=":0{$secs}"; }

 
  return $str;
}
////////////////////////Removes Firstline from $local_file

function cutline($filename,$line_no=-1) { 

$strip_return=FALSE; 

$data=file("records.txt"); 
$pipe=fopen("records.txt",'w'); 
$size=count($data); 

if($line_no==-1) $skip=$size-1; 
else $skip=$line_no-1; 

for($line=0;$line<$size;$line++) 
if($line!=$skip) 
fputs($pipe,$data[$line]); 
else 
$strip_return=TRUE; 

return $strip_return; 
} 

cutline('records.txt',1); // deletes line 6 in foo.txt


//open records file and parse

$handle = @fopen("records.txt", "r");
$rectxt1 = "recs.txt";
$handle2 = @fopen("recs.txt", "w+");
$begintable = "<table width=100% border=0 cellspacing=0 cellpadding=0 class=sample>"."<tr>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."When"."</th>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."Map"."</th>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."Who"."</th>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."Time"."</th>"."</tr>";
$endtable = "</table>";
fwrite($handle2, $begintable);
if ($handle) {
  while (!feof($handle)) {
      $buffer = fgets($handle, 4096);
  $patterns = '/Record/';
  $replacement = '';
  $buffer = preg_replace($patterns, $replacement, $buffer);
  list($buffer2, $buffer3, $buffer4, $buffer5, $buffer6) = split("]=",$buffer);
 
  list($buffer2, $buffer3, $buffer4, $buffer5, $buffer6) = split("
",$buffer3);
  list($rec1, $rec2, $rec4, $rec14) = split("\t",$buffer2);
  list($rec5, $rec6, $rec7, $rec15) = split("\t",$buffer3);
  list($rec8, $rec9, $rec10, $rec16) = split("\t",$buffer4);
  list($rec11, $rec12, $rec13, $rec17) = split("\t",$buffer5);
  $poop = (2000-$rec2);
  $poop1 = (2000-$rec6);
  $poop2 = (2000-$rec9);
  $poop3 = (2000-$rec12);
  $time = $poop;
    $time1 = $poop1;
$time2 = $poop2;
  $time3 = $poop3;

if (strlen($rec5) >= 1) {
$re1 = "<tr>"."<td>"."<font size=2 color=#FFFFFF>".date("m/d/Y", $rec14)."</td>"."<td>"."<img src=/btrecords/images/$rec1.jpg width=40 heigth=40>"."<font size=2 color=#FFFFFF>".$rec1."<td>"."<font size=2 color=#FFFFFF>".$rec4."</td>"."<td>"."<font size=3 color=#597B96>"."<b>".duration($time)."</b>"."</td>"."</tr>";
fwrite($handle2, $re1);
}  
if (strlen($rec5) >= 3) {
$re2 = "<tr>"."<td>"."<font size=2 color=#FFFFFF>".date("m/d/Y", $rec15)."</td>"."<td>"."<img src=/btrecords/images/$rec5.jpg width=40 heigth=40>"."<font size=2 color=#FFFFFF>".$rec5."<td>"."<font size=2 color=#FFFFFF>".$rec7."</td>"."<td>"."<font size=3 color=#597B96>"."<b>".duration($time1)."</b>"."</td>"."</tr>";
fwrite($handle2, $re2);
}
if (strlen($rec8) >= 3) {
$re3 = "<tr>"."<td>"."<font size=2 color=#FFFFFF>".date("m/d/Y", $rec16)."</td>"."<td>"."<img src=/btrecords/images/$rec8.jpg width=40 heigth=40>"."<font size=2 color=#FFFFFF>".$rec8."<td>"."<font size=2 color=#FFFFFF>".$rec10."</td>"."<td>"."<font size=3 color=#597B96>"."<b>".duration($time2)."</b>"."</td>"."</tr>";
fwrite($handle2, $re3);
}
if (strlen($rec11) >= 3) {
$re4 = "<tr>"."<td>"."<font size=2 color=#FFFFFF>".date("m/d/Y", $rec17)."</td>"."<td>"."<img src=/btrecords/images/$rec11.jpg width=40 heigth=40>"."<font size=2 color=#FFFFFF>".$rec11."<td>"."<font size=2 color=#FFFFFF>".$rec13."</td>"."<td>"."<font size=3 color=#597B96>"."<b>".duration($time3)."</b>"."</td>"."</tr>";
fwrite($handle2, $re4);
}
  }
  fwrite($handle2, $endtable);
      fclose($handle);
  fclose($handle2);
}
[/code]
What I would like is to be able to get each out put above to go to the database then output it  with pagination and a search function. I can handle the search function and I think i can do the pagination feature, just need help with the mySQL

Thanks all ;D

[DieSucker]

someone's gotta be able to help:P

[.josh]

do you have your dabase setup with a table and columns for each of the values you wish to store? if not, then make a list of each value you are storing, figure out which variable type they need to be, and a name for them (example, list of usernames might be called "user_name") then create a database and a table and the columns. have you done all that yet?

[DieSucker]

ok so yeah I have a database setup, btrecords, and a user and pass, this is where I get lost
so I need four values, "date" "user" "map" "time_completed" per line, so i dunno how to do it, just give me a base script on inserting these into tables and i think i can figure it out.
only thing is I need the output to look like
http://www.evilbunnytracks.net/btrecords/107.php
which is how the script outputs now

[tfoster]

Try something like this:

$insertBTR = "insert into [i]tablename[/i] (date, user, map, time_completed) values ('" . $inputDate . "', '" . $inputUser . "', '" . $inputMap . "', '" . $input_time_completed . "') where BTR_no=[Btr]";

$resultBTR = mysql_query($insertBTR);

if(mysql_affected_rows()>0)
  echo "BTR inserted successfully";
else
  echo "Failed";

A couple of notes about this:
Between the first set of parentheses in the insert statement, use the field names from your database.
Between the second set of parentheses, make sure you do single quote, double quote [input element] double quote, single quote.
Also the inputs are whatever value you are wanting to insert into the database.

When you go to get it out of the database, use a loop to go through all of them:
$selectBTR = "select * from [table_name]";
$resultBTR = mysql_query($selectBTR);
echo "<table>\n";
while($rowBTR = mysql_fetch_array($resultBTR)){
  echo "  <tr>\n";
  echo "     <td>" . $rowBTR[date] . "</td>\n";
  echo "     <td>" . $rowBTR[map] . "</td>\n";
  echo "     <td>" . $rowBTR[user] . "</td>\n";
  echo "     <td>" . $rowBTR[time_completed] . "</td>\n";
  echo "  </tr>\n";
}
echo "</table>\n";

This should output it back into your table nicely.

[DieSucker]

I am coming up with an error though, and I am prety sure I know why, I just dunno how to fix it, the reason for the error would be the players name its trying to input "DoN*']['r0p$" and the error i get is


Quote
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 ']['r0p$ ', 'CTF-BT-PsychoJungle', '4:16', '1150447172')' at line 1

I have my table set up as

Code:
$sql = "CREATE TABLE information(
player_name CHAR(30) NOT NULL,
map_name CHAR(60) NOT NULL,
map_time CHAR(60) NOT NULL,
rec_date CHAR(30) NOT NULL
)"; any help would be greatful

[Orio]

Time to meet:
[url=http://www.php.net/manual/en/function.addslashes.php]addslashes()[/url] and [url=http://www.php.net/manual/en/function.stripslashes.php]stripslashes()[/url]

Orio.

[DieSucker]

ok so I tried using the code that foster proved for me to output the DB, but it isnt working to well, its repeating itself every so often and I dunno wth it is doing this
here is the page
http://www.evilbunnytracks.net/btrecords/107a.php
and here is the code now
[code]<?
////////////////mySQL Info///////////
$DBhost = "localhost";
$DBuser = "btrecords";
$DBpass = "*****";
$DBName = "btrecords";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
@mysql_select_db("$DBName") or die("Unable to select
database $DBName");

//////////////////////////

//////////////////Creating Tables for recs
$drop = 'DROP TABLE IF EXISTS `information`';
mysql_query( $drop );

$sql = "CREATE TABLE information(
player_name CHAR(60) NOT NULL,
map_name CHAR(60) NOT NULL,
map_time CHAR(60) NOT NULL,
rec_date CHAR(30) NOT NULL
)";

$res = mysql_query( $sql );


/////////////////Server Info
$ftp_server = "65.99.211.107";
$ftp_user = "211.107";
$ftp_pass = "*****";
$local_file = 'records.txt';
$server_file = 'BTRecords.ini';

// set up a connection or die
$conn_id = ftp_connect($ftp_server) or die("Couldn't connect to $ftp_server");

// try to login
ftp_login($conn_id, $ftp_user, $ftp_pass);

ftp_chdir($conn_id, "System");


// try to download $server_file and save to $local_file
if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY))

// close the connection
ftp_close($conn_id);


/////////////////////////
function duration($timestamp) {

$years=floor($timestamp / (60*60*24*365));
$timestamp%=60*60*24*365;

$weeks=floor($timestamp / (60*60*24*7));
$timestamp%=60*60*24*7;

$days=floor($timestamp / (60*60*24));
$timestamp%=60*60*24;
   
$hrs=floor($timestamp / (60*60));
$timestamp%=60*60;
   
$mins=floor($timestamp / 60);
$secs=$timestamp % 60;
 
  $str="";

if ($years >= 1) { $str.="{$years} years "; }
  if ($weeks >= 1) { $str.="{$weeks} weeks "; }
  if ($days >= 1) { $str.="{$days} days "; }
  if ($hrs >= 1) { $str.="{$hrs} hours "; }
  if ($mins >= 1) { $str.="{$mins}"; }

  // Checks if there are only seconds.
  if ($years < 1 && $weeks < 1 && $days < 1 && $hrs < 1 && $mins < 1)
      {$str = "0";}
  if ($secs >= 10) { $str.=":{$secs}"; }
  if ($secs < 10) { $str.=":0{$secs}"; }

 
  return $str;
}
////////////////////////



function cutline($filename,$line_no=-1) { 

$strip_return=FALSE; 

$data=file("records.txt"); 
$pipe=fopen("records.txt",'w'); 
$size=count($data); 

if($line_no==-1) $skip=$size-1; 
else $skip=$line_no-1; 

for($line=0;$line<$size;$line++) 
if($line!=$skip) 
fputs($pipe,$data[$line]); 
else 
$strip_return=TRUE; 

return $strip_return; 
} 

cutline('records.txt',1); // deletes line 6 in foo.txt


//open records file and parse

$handle = @fopen("records.txt", "r");
$rectxt1 = "recs.txt";
$handle2 = @fopen("recs.txt", "w+");
$begintable = "<table width=100% border=0 cellspacing=0 cellpadding=0 class=sample>"."<tr>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."When"."</th>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."Map"."</th>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."Who"."</th>"."<th scope=col>"."<font size=2 color=#FFFFFF>"."Time"."</th>"."</tr>";
$endtable = "</table>";
fwrite($handle2, $begintable);
if ($handle) {
  while (!feof($handle)) {
      $buffer = fgets($handle, 4096);
  $patterns = '/Record/';
  $replacement = '';
  $buffer = preg_replace($patterns, $replacement, $buffer);
  list($buffer2, $buffer3, $buffer4, $buffer5, $buffer6) = split("]=",$buffer);
 
  list($buffer2, $buffer3, $buffer4, $buffer5, $buffer6) = split("",$buffer3);
  list($rec1, $rec2, $rec4, $rec14) = split("\t",$buffer2);
  list($rec5, $rec6, $rec7, $rec15) = split("\t",$buffer3);
  list($rec8, $rec9, $rec10, $rec16) = split("\t",$buffer4);
  list($rec11, $rec12, $rec13, $rec17) = split("\t",$buffer5);
  $poop = (2000-$rec2);
  $poop1 = (2000-$rec6);
  $poop2 = (2000-$rec9);
  $poop3 = (2000-$rec12);
  $time = duration($poop);
    $time1 = duration($poop1);
$time2 = duration($poop2);
  $time3 = duration($poop3);
 
  $rec4 = addslashes($rec4);
  $rec7 = addslashes($rec7);
  $rec5 = addslashes($rec5);
  $rec8 = addslashes($rec8);
  $rec11 = addslashes($rec11);
  $rec13 = addslashes($rec13);
  $rec10 = addslashes($rec10);
  //////////////////

   
  $insertBTR = "insert into information(player_name, map_name, map_time, rec_date) values ('" . $rec4 . "', '" . $rec1 . "', '" . $time . "', '" . date("m/d/Y", $rec14) . "')";

  $insertBTR1 = "insert into information(player_name, map_name, map_time, rec_date) values ('" . $rec7 . "', '" . $rec5 . "', '" . $time1 . "', '" . date("m/d/Y", $rec15) . "')";


  $insertBTR2 = "insert into information(player_name, map_name, map_time, rec_date) values ('" . $rec10 . "', '" . $rec8 . "', '" . $time2 . "', '" . date("m/d/Y", $rec16) . "')";


  $insertBTR3 = "insert into information(player_name, map_name, map_time, rec_date) values ('" . $rec13 . "', '" . $rec11 . "', '" . $time2 . "', '" . date("m/d/Y", $rec17) . "')";

if($rec4 != NULL) {
$resultBTR = mysql_query($insertBTR);
}
if($rec7 != NULL) {
$resultBTR = mysql_query($insertBTR1);
}
if($rec10 != NULL) {
$resultBTR = mysql_query($insertBTR2);
}
if($rec13 != NULL) {
$resultBTR = mysql_query($insertBTR3);
}
if (!$resultBTR){
print mysql_error();
exit;
}



$selectBTR = 'SELECT * FROM `information` LIMIT 0, 30 ';
$resultBTR = mysql_query($selectBTR);
echo "<table>\n";
while($rowBTR = mysql_fetch_array($resultBTR)){
  echo "  <tr>\n";
  echo "    <td>" . $rowBTR[rec_date] . "</td>\n";
  echo "    <td>" . $rowBTR[map_name] . "</td>\n";
  echo "    <td>" . $rowBTR[player_name] . "</td>\n";
  echo "    <td>" . $rowBTR[map_time] . "</td>\n";
  echo "  </tr>\n";
}
echo "</table>\n";



////////////////////////
}
}


?>
[/code]

[DieSucker]

ne help on this?? the database shows everything correctly bu twhen i output it it doubles