Jump to content

Data from a table


asai

Recommended Posts

Hi,

 

A little new to php programming... I have a question:

I have written this code:

<html>
<head>
<title>Test</title>
</head>
<FONT FACE="Verdana, sans-serif">
<H3>Movies</h3>
<?php

include 'config.php';
include 'opendb.php';

$result = mysql_query("SELECT * FROM DayMovie ORDER BY FileDate") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Filename</th><th>Path</th><th>Date</th><th>Filetype</th> <th>Size</th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['FileName'];
	echo "</td><td>";
	echo $row['FilePath'];
	echo "</td><td>"; 
	echo $row['FileDate'];
	echo "</td><td>";
	echo $row['FileType'];
	echo "</td><td>";
	echo $row['FileSize'];
	echo "</td></tr>";        
} 

echo "</table>";

include 'closedb.php';

?>

I would like to have a link to these files on all the rows to open the files. How can I do that?

I would also like that the link from the mysql row is not shown in the table. Only a text with the text "link"  or "view".

 

Link to comment
Share on other sites

Output an HTML anchor tag with a link to the file.

 

echo "<a href='path/to/file'>View</a>";

 

Thanks for the reply! 

Almost there:

echo "<a href='alldaymovies/'FileName''>View</a>";

As you can see I would like to put in the filename from one of the rows in the table. The path works great, but I didn't get the filename with this code.

How can i do that?

Link to comment
Share on other sites

Fantastic! Work perfect! Thanks for good help. :)

One last question: The date field has sql date format: yyyy-mm-dd

Is there a way to display this in another format, i.e. dd.mm.yyyy instead?

Edited by asai
Link to comment
Share on other sites

Sorry for my bad explaining. I am norwegian, så my english isn't too good. :)

Heres my php:

<html>
<link href="css/style.css" rel="stylesheet" type="text/css" media="screen" />

<?php

include 'config.php';
include 'opendb.php';

$result = mysql_query("SELECT * FROM DayMovie WHERE YEAR(FileDate) = 2015 ORDER BY FileDate DESC") 
or die(mysql_error());  
echo "<table border='0'>";
while($row = mysql_fetch_array( $result )) {
	echo "<tr><td>"; 
	echo date('d.m.Y', strtotime($row['FileDate']));
	echo "</td><td>";
	echo "<a href='alldaymovies/{$row['FileName']}'>Watch movie</a>";
	echo "</td></tr>";        
} 
echo "</table>";

include 'closedb.php';

?>

</html>

This lists the content of the table very nice.

However this list is going to be very long. What I could use is a way to choose which year and month i would like to view.

Link to comment
Share on other sites

Yes something like that. :)

Is it possible to do this choice from the webpage? i.e. from a dropdown box? The dropdown should only have available choices from the available data. If there is no data from february 2015 then it not an option in the dropdown. Is this possible?

Link to comment
Share on other sites

I have tried this:

<?php

include 'config.php';
include 'opendb.php';

$query_disp="SELECT * FROM DayMovie ORDER BY FileDate";
$result_disp = mysql_query($query_disp, $conn);
$options = array();
while ($query_data = mysql_fetch_array($result_disp)) {
    $options[$query_data["Id"]] = $query_data["FileDate"];
}
?>

<select name="Id" onClick="submitCboSemester();">
<?php foreach ($options as $key => $value) : ?>
    <?php $selected = ($key == $_POST['Id']) ? 'selected="selected"' : ''; ?>
    <option value="<?php echo $key ?>" <?php echo $selected ?>>
    <?php echo $value ?>
    </option>
<?php endforeach; ?>
</select>

Can be viewed here: http://81.166.2.19/combo.php

However, I would like it to look a little different. Instead of all the dates, I would like it to only show december 2014 and january 2015

After that the result of this choice displays the content of this table accordingly: http://81.166.2.19/movies.php

Is that possible?

Link to comment
Share on other sites

I'd use a query like this to build the option array

$query_disp="SELECT DISTINCT 
                DATE_FORMAT(FileDate, '%b %Y') as adate
               ,DATE_FORMAT(FileDate, '%Y-%m') as ndate 
            FROM DayMovie 
            ORDER BY FileDate";
$result_disp = mysql_query($query_disp, $conn);
$options = array();
while ($query_data = mysql_fetch_array($result_disp)) {
    $options[$query_data["ndate"]] = $query_data["adate"];
}

Link to comment
Share on other sites

Now I think we are getting closer. :)

However when I put this code together with the rest of my code I did something wrong. With the result that the the webpage is empty.

I would like that only the combobox shows on the page and when I select a value, the page is updated with the result in the table. ;)

Link to comment
Share on other sites

Heres were I am at right now:

<html>
<link href="css/style.css" rel="stylesheet" type="text/css" media="screen" />

<?php

include 'config.php';
include 'opendb.php';

$result = mysql_query("SELECT DISTINCT 
			   DATE_FORMAT(FileDate, '%b %Y') as adate,
               DATE_FORMAT(FileDate, '%d.%m.%Y') as ndate,
			   FileName as filename
            FROM DayMovie 
            ORDER BY FileDate DESC") 
or die(mysql_error());  
echo "<table border='0'>";
while($row = mysql_fetch_array( $result )) {
	echo "<tr><td>";
	echo $row['adate'];
	echo "</td><td>";	
	echo date($row['ndate']);
	echo "</td><td>";
	echo "<a href='alldaymovies/{$row['filename']}'>Se film</a>";
	echo "</td></tr>";        
} 
echo "</table>";

include 'closedb.php';

?>

</html>

This generates a table with 3 columns. In the first column it is the adate value (Month and year).

What I would like is to select this value from a combobox and then only show the content that has this value in this column.

 

The result can be viewed here: http://81.166.2.19/movies.php

Edited by asai
Link to comment
Share on other sites

Yes I understand that, but I can see how I would put this together with the combobox. I would like the combobox to show the value Jan 2015, and when it is selected i would like the value 2015-01 be put into the query so I get a table with the movies from January 2015.

I am very close, but haven't nailed it just yet. :)

Link to comment
Share on other sites

Very close, but a little something wrong:

<html>
<link href="css/style.css" rel="stylesheet" type="text/css" media="screen" />

<?php

include 'config.php';
include 'opendb.php';

$query_disp="SELECT DISTINCT 
            DATE_FORMAT(FileDate, '%b %Y') as adate,
            DATE_FORMAT(FileDate, '%Y-%m') as ndate
            FROM DayMovie 
            ORDER BY FileDate DESC";
$result_disp = mysql_query($query_disp, $conn);
$options = array();
while ($query_data = mysql_fetch_array($result_disp)) {
    $options[$query_data["adate"]] = $query_data["adate"];
}
?>

<select name="ndate" onClick="submitCboSemester();">
<?php foreach ($options as $key => $value) : ?>
    <?php $selected = ($key == $_POST['ndate']) ? 'selected="selected"' : ''; ?>
    <option value="<?php echo $key ?>" <?php echo $selected ?>>
    <?php echo $value ?>
    </option>
<?php endforeach; ?>
</select>

<?php
$result = mysql_query("SELECT DISTINCT 
	    DATE_FORMAT(FileDate, '%d.%m.%Y') as ndate,
	    FileName as filename
            FROM DayMovie 
            ORDER BY FileDate DESC WHERE FileDate LIKE '$value%'") 
or die(mysql_error());  
echo "<table border='0'>";
while($row = mysql_fetch_array( $result )) {
	echo "<tr><td>";
	echo date($row['ndate']);
	echo "</td><td>";
	echo "<a href='alldaymovies/{$row['filename']}'>Se film</a>";
	echo "</td></tr>";        
} 
echo "</table>";

include 'closedb.php';
?>
</html>

I get the combobox correct, but the the value in my WHERE is the adate value and not the ndate...

 

Link to comment
Share on other sites

Really getting close now :D

 

However I get this error:

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 'WHERE FileDate LIKE '2014-12%'' at line 5

But when I try this command in my MySQL Workbench, the command works great. What could cause this?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.