Solved

sorting data from folder with addition of a database table

Posted on 2008-06-18
11
262 Views
Last Modified: 2013-12-12
My webpage pulls PDF"s from a folder on my desktop and disply's them by the selected drop down boxes on my page.  I have a drop down for month and year.  When a selection is made the PDF's that meet that criteria are displyed along with a last modifed date.

What I would like to added to the table being displyed is the description from a table in my database.  So when the selection is shown on my webpage I would like it to show
"PDFName"  NEW "Desription" and "modified date"

Pdf name and modifed date I can get to already show I just don't know how to include Desription.

Description is located in my database table Tams under Description.

Here is my code to disply the PDF's
<form id="form1" name="form1" method="post" action="Tams1.php">
      <table width="63%">
        <tr>
          <td width="37%"><strong>Month:</strong>
            <select name="month" id="month">
              <option value="Select" selected="selected">Select</option>
              <option value ="1" > January</option>
              <option value ="2" > February</option>
              <option value ="3" >March</option>
              <option value ="4" >April</option>
              <option value ="5" >May</option>
              <option value ="6" >June</option>
              <option value ="7" >July</option>
              <option value ="8" >August</option>
              <option value ="9" >September</option>
              <option value ="10" >October</option>
              <option value ="11" >November</option>
              <option value ="12" >December</option>
            </select></td>
          <td width="22%"><strong>Year:
              <select name="year" id="year">
                <option value="0" selected="selected">ALL</option>
                <option value="2008">2008</option>
                <option value="2007">2007</option>
                <option value="2006">2006</option>
                <option value="2005">2005</option>
                <option value="2004">2004</option>
<option value="2003">2003</option>
              </select>
          </strong></td>
          <td width="29%"><input type="submit" name="submit" id="submit" value="Submit" /></td>
        </tr>
      </table>
    </form>
    <p align="left" class="style9">&nbsp;</p>
    <table width="900" border="6" align="center">
        <tr>
          <td width="220" height="34" bgcolor="#CCCCCC"><table width="900">
              <tr>
                <td width="522"><span class="style16">File Name</span></td>
                <td width="366"><div align="right" class="style12">
                    <div align="left" class="style17">Last Modified</div>
                </div></td>
              </tr>
          </table></td>
        </tr>
        <tr>
          <td>
              <?php  
    $dir="C:/xampp/htdocs/MGCWEB/files/tams";
    if ($handle = opendir($dir))
    {    
    echo "<table>";}
    $files = array();
//Get all the files
$month = (isset($_POST['month'])?(integer)$_POST['month']:0);
$year = (isset($_POST['year'])?(integer)$_POST['year']:0);

while (($file = readdir($handle)) !== false) {
      $path = $dir . DIRECTORY_SEPARATOR . $file;
      $fileinfo = pathinfo($path);
      if(is_file($path) && $file != "." && $file != '..' && $fileinfo['extension'] == 'pdf') {
            $modified = stat($path);
if ($year == 0 or date('Y', $modified['mtime']) == $year) {
if ($month == 0 or date('m', $modified['mtime']) == $month) {
  $files[] = array('name' => $file, 'mod' => date('m/d/Y h:i:s A', $modified['mtime']), 'sort' => date('Y-m-d H:i:s', $modified['mtime']));
}
};
}

      }

 
//Sort them by modification month
usort($files, 'datesort');
 
//Display them
foreach($files as $file) {
      echo "<tr>
      <td ><font face=\"Arial\" style=\"font-size: 10pt;\"><a href=\"/MGCWEB/files/tams/".rawurlencode($file['name']). "\">" .htmlentities($file['name']). "</a></font></td>
      <td><p align=\"center\" style=\"margin: 2px 4px\"><font face=\"Arial\" style=\"font-size: 10pt;\">{$file['mod']}</p></font></td>
      </tr>";
}
 
//Function for comparing mod dates
function datesort($a, $b) {
      return ($a['sort'] < $b['sort']);
}

    echo "</table>";
?>        

Thanks
0
Comment
Question by:Football33Football
  • 6
  • 5
11 Comments
 
LVL 29

Expert Comment

by:fibo
ID: 21815029
As far as I understand, your problem is to display additional information for each file. This description cannot come from the file itself (as can size, name, dates) but from some special sou re, eg, a table in a database.

Assuming you can get this description and put it in the variable$my_description, then displaying it would be just a change:
//Display them
foreach($files as $file) {
      echo "<tr>
      <td ><font face=\"Arial\" style=\"font-size: 10pt;\"><a href=\"/MGCWEB/files/tams/".rawurlencode($file['name']). "\">" .htmlentities($file['name']). "</a></font></td>
<td valign="top"><font face=\"Arial\" style=\"font-size:10pt;\">{$file['description']}</font></td>
      <td><p align=\"center\" style=\"margin: 2px 4px\"><font face=\"Arial\" style=\"font-size: 10pt;\">{$file['mod']}</p></font></td>
      </tr>";
}
 
Now the more difficult problem: how was placed a value in 'description'?
you will probably need to build a table which would have the filename as the primary key (full directory + filename), and some additional information of which the description.
You get the filename --> you check if the description exists.
YES? then place the value, your done
NO? then the data needs to be captured from user: display complete filename, size, date, ask for text in the description...
0
 

Author Comment

by:Football33Football
ID: 21817134
I'm new to php so would you be able to help me through this.
I'm using dreamweaver so would I be able to connect to my  database and grab the description table in my tams database.

mysql_select_db($database_Tams, $Tams);
$query_Recordset1 = "SELECT `Description` FROM tams ORDER BY `Description` ASC";
$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>   but I don't know if I could use this with what I already have.

I also created another page that is connected to my database and displys all my data by what ever year is selected from my drop down box, but I don't know how I could get the user to disply all the data by selecting month since month is not a feild in my database.  I'm not sure which route to even start with. They both seem pretty complex for my begginer knowledge.  any help would be appreciated.  Here is my other test page.

$colname_Recordset1 = "-1";
if (isset($_GET['d2'])) {
  $colname_Recordset1 = $_GET['d2'];
}
mysql_select_db($database_Tams, $Tams);
$query_Recordset1 = sprintf("SELECT `Date`, Tams, `Description` FROM tams WHERE `Date` LIKE %s ORDER BY `Date` DESC", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
 <form id="form1" name="form1" method="get" action="testTams1.php">
      <table width="63%">
        <tr>
          <td width="37%"><strong>Month:</strong>
            <select name="month" id="month">
              <option value="Select" selected="selected">Select</option>
              <option value ="1" > January</option>
              <option value ="2" > February</option>
              <option value ="3" >March</option>
              <option value ="4" >April</option>
              <option value ="5" >May</option>
              <option value ="6" >June</option>
              <option value ="7" >July</option>
              <option value ="8" >August</option>
              <option value ="9" >September</option>
              <option value ="10" >October</option>
              <option value ="11" >November</option>
              <option value ="12" >December</option>
            </select></td>
          <td width="22%"><strong>Year:
              <select name="d2" id="d2">
                <option value="0" selected="selected">ALL</option>
                <option value="2008">2008</option>
                <option value="2007">2007</option>
                <option value="2006">2006</option>
                <option value="2005">2005</option>
                <option value="2004">2004</option>
<option value="2003">2003</option>
              </select>
          </strong></td>
          <td width="29%"><input type="submit" name="submit" id="submit" value="Submit" /></td>
        </tr>
      </table>
    </form>
    <p align="left" class="style9">&nbsp;</p>
   
    <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
      <table width="783" border="2">
        <tr>
          <td width="243"><strong>Tams</strong></td>
            <td width="383"><strong>Description </strong></td>
            <td width="133"><strong>Date</strong></td>
          </tr>
        <?php do { ?>
          <tr>
            <td height="23"><a href="/MGCWEB/files/tams/<?php echo $row_Recordset1['Tams'];?>"><?php echo $row_Recordset1['Tams']; ?></a></span></td>
            <td><?php echo $row_Recordset1['Description']; ?></td>
            <td><?php echo $row_Recordset1['Date']; ?></td>
          </tr>
          <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
Im just not sure how to get this to work. Thanks
0
 
LVL 29

Expert Comment

by:fibo
ID: 21819659
Hi,
You are currently working with one single step that will use html and php.
I would think that the final touch with html is the last one, the final one. You should work on the php/ MySQL part first.
Once your page will work fine as far as logic and data are concerned, then you will be in a better position to finalize it.

You might start with a very simple thing: display for a given year all the files with dates in that year. For each file display name, description, complete date, year of the date, month of the date.
Just display them using some simple echo instruction.

Once this works OK, now you can tackle the second problem, the html part.

Working separately on these 2 problems will make it easier to see / feel how/that you are progressing towards your goal, and a the same time will lower the range of problems you have to solve at any given time.
0
 

Author Comment

by:Football33Football
ID: 21821045
OK so I can get this page to display for a given year from the drop down box the PDF name, description, complete date.  What do I do at this point to get the month to disply since I can't include month from my database becase I don't have a feild for month?

Here is my code to get all the data I want from my database when the user selects the year.
$colname_Recordset1 = "-1";
if (isset($_GET['d2'])) {
  $colname_Recordset1 = $_GET['d2'];
}
mysql_select_db($database_Tams, $Tams);
$query_Recordset1 = sprintf("SELECT `Date`, Tams, `Description` FROM tams WHERE `Date` LIKE %s ORDER BY `Date` DESC", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
 <form id="form1" name="form1" method="get" action="testTams1.php">
      <table width="63%">
        <tr>
          <td width="37%"><strong>Month:</strong>
            <select name="month" id="month">
              <option value="Select" selected="selected">Select</option>
              <option value ="1" > January</option>
              <option value ="2" > February</option>
              <option value ="3" >March</option>
              <option value ="4" >April</option>
              <option value ="5" >May</option>
              <option value ="6" >June</option>
              <option value ="7" >July</option>
              <option value ="8" >August</option>
              <option value ="9" >September</option>
              <option value ="10" >October</option>
              <option value ="11" >November</option>
              <option value ="12" >December</option>
            </select></td>
          <td width="22%"><strong>Year:
              <select name="d2" id="d2">
                <option value="0" selected="selected">ALL</option>
                <option value="2008">2008</option>
                <option value="2007">2007</option>
                <option value="2006">2006</option>
                <option value="2005">2005</option>
                <option value="2004">2004</option>
<option value="2003">2003</option>
              </select>
          </strong></td>
          <td width="29%"><input type="submit" name="submit" id="submit" value="Submit" /></td>
        </tr>
      </table>
    </form>
    <p align="left" class="style9">&nbsp;</p>
   
    <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
      <table width="783" border="2">
        <tr>
          <td width="243"><strong>Tams</strong></td>
            <td width="383"><strong>Description </strong></td>
            <td width="133"><strong>Date</strong></td>
          </tr>
        <?php do { ?>
          <tr>
            <td height="23"><a href="/MGCWEB/files/tams/<?php echo $row_Recordset1['Tams'];?>"><?php echo $row_Recordset1['Tams']; ?></a></span></td>
            <td><?php echo $row_Recordset1['Description']; ?></td>
            <td><?php echo $row_Recordset1['Date']; ?></td>
          </tr>
          <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>

0
 

Author Comment

by:Football33Football
ID: 21832963
just checking back
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Expert Comment

by:fibo
ID: 21833172
since you have in your table a field for dates, you can retrieve year and month from this.

Depending on the format you uise to store your dates, formulas for this might change.
What would a a typical date format in your table?

To make interaction easier with your vistors, you might also consider displayin a table with years as comuns and montks as row, where the cell of month m and year y would hold the number of files with correspondning date...

0
 

Author Comment

by:Football33Football
ID: 21834430
my dates are stored in a sql database like yyyy-mm-dd

How would I be able to retrieve month from this if i'm using a database that month is not in the database.
I'm new at sql but dreamweaver made this code for me when I choose what I wanted to disply when year is selected.
$colname_Recordset1 = "-1";
if (isset($_GET['d2'])) {
  $colname_Recordset1 = $_GET['d2'];
}
mysql_select_db($database_Tams, $Tams);
$query_Recordset1 = sprintf("SELECT `Date`, Tams, `Description` FROM tams WHERE `Date` LIKE %s ORDER BY `Date` DESC", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>


0
 
LVL 29

Expert Comment

by:fibo
ID: 21835197
$query_Recordset1 = sprintf("SELECT `Date`, Tams, `Description` FROM tams WHERE `Date` LIKE %s ORDER BY `Date` DESC", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());

while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {
 $my_date=$row-Recordset1['Date'];
 $my_year = substr($my_date, 0,4);
 $my_month= substr ($my_date, 5,2);
....

};
0
 

Author Comment

by:Football33Football
ID: 21836310
fibo,
I tired the code above and nothing gets displyed in my table.  What's also weird is dreamweaver shows a binding connection in the application window but when I added the code aboe and compiled it's not showing the binding data.  Did I do something wrong, I didn't think I did.

$colname_Recordset1 = "-1";
if (isset($_GET['d2'])) {
  $colname_Recordset1 = $_GET['d2'];
}
mysql_select_db($database_Tams, $Tams);
$query_Recordset1 = sprintf("SELECT `Date`, Tams, `Description` FROM tams WHERE `Date` LIKE %s  ORDER BY `Date` DESC", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());

while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {
 $my_date=$row_Recordset1['Date'];
 $my_year = substr($my_date, 0,4);
 $my_month= substr ($my_date, 5,2);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
};?>
0
 
LVL 29

Accepted Solution

by:
fibo earned 250 total points
ID: 21836997
I was not giving you the complete code, the ... was meant for you.
I think also you have an error at the query text: ' are missing around %S.
A more complete example, but without working on the output, appears atached


<?php

$colname_Recordset1 = ''; // initially set to get all answers

if (isset($_GET['d2'])) {

  $colname_Recordset1 = $_GET['d2'];

}
 

mysql_select_db($database_Tams, $Tams);
 

$query_Recordset1 = sprintf("SELECT `Date`, Tams, `Description` FROM tams WHERE `Date` LIKE '%s'  ORDER BY `Date` DESC", GetSQLValueString("%$colname_Recordset1%", "text"));
 

$debug=true; //easy to change...

if ($debug) {echo "the SQL query will be [$query_Recordset1] *** <br>";};
 

$Recordset1 = mysql_query($query_Recordset1, $Tams) or die(mysql_error());

$nb_rows = 0;
 

while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {

	 ++$nb_rows;

	 $my_date=$row_Recordset1['Date'];

	 $my_year = substr($my_date, 0,4);

	 $my_month= substr ($my_date, 5,2);

	 echo "Rec.# $nb_rows - <a href='/MGCWEB/files/tams/". $row_Recordset1['Tams'] . "'>" 

	  . $row_Recordset1['Tams'] ."</a> - Month $my_month - Year $my_year - Date $my_date - Description [" 

	  . $row_Recordset1['Description'] ."] **</p>";

};

// $nb_rows now hold the number of rows successfully read

echo "$nb_rows matched the pattern searched";

?>

Open in new window

0
 
LVL 29

Expert Comment

by:fibo
ID: 21864310
Thx for the points, glad I helped somehow
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now