Solved

sorting data from folder with addition of a database table

Posted on 2008-06-18
11
263 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use md5 hashing 3 25
WordPress Themes 10 34
how to use a function in heredoc 5 21
is this a cms? 8 33
Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

919 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

18 Experts available now in Live!

Get 1:1 Help Now