Solved

sorting data from folder with addition of a database table

Posted on 2008-06-18
11
265 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

828 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