sorting data from folder with addition of a database table

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
Football33FootballAsked:
Who is Participating?
 
Bernard S.Connect With a Mentor CTOCommented:
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
 
Bernard S.CTOCommented:
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
 
Football33FootballAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Bernard S.CTOCommented:
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
 
Football33FootballAuthor Commented:
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
 
Football33FootballAuthor Commented:
just checking back
0
 
Bernard S.CTOCommented:
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
 
Football33FootballAuthor Commented:
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
 
Bernard S.CTOCommented:
$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
 
Football33FootballAuthor Commented:
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
 
Bernard S.CTOCommented:
Thx for the points, glad I helped somehow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.