Data from a table

asai · January 21, 2015

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

Barand · January 21, 2015

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

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

asai · January 21, 2015

On 1/21/2015 at 9:35 AM, Barand said:

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?

cyberRobot · January 21, 2015

It looks like you're missing the variable name. Try something like this:

echo "<a href='alldaymovies/{$row['FileName']}'>View</a>";

asai · January 21, 2015

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?

Barand · January 21, 2015

You can format it in your query EG

SELECT DATE_FORMAT(datecol, '%d.%m.%Y') as formatted_date

or you can do it in php

echo date('d.m.Y', strtotime($row['datecol']));

asai · January 21, 2015

Perfect!! Thank you.

One small detail: How can I adjust the colum alignment?

Barand · January 21, 2015

By using the "text-align" attribute in the cells style definition.

https://developer.mozilla.org/en-US/docs/Web/CSS/text-align

asai · January 25, 2015

I have one more question: In the table theres a row with date. Is there a way to use this date and create a row heading with the months?

ie: All the rows with date in january, have a heading with January, and so on.

Barand · January 25, 2015

Sorry, I don't understand what you are asking.

asai · January 26, 2015

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.

Barand · January 26, 2015

Like this?

SELECT * FROM DayMovie 
WHERE YEAR(FileDate) = 2015 AND MONTH(FileDate) = 1
ORDER BY FileDate DESC

asai · January 26, 2015

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?

asai · January 26, 2015

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?

asai · January 31, 2015

Is there any suggestions to my last question?

Barand · January 31, 2015

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"];
}

asai · February 1, 2015

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.

asai · February 2, 2015

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

Barand · February 2, 2015

The query I gave you was for the date options, so selecting would give a value like "2015-02" (yy-mm).

You would then query your films for those WHERE fileDate LIKE '$date%' (assuming fileDate is a DATE field in format yyyy-mm-dd)

asai · February 2, 2015

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.

Barand · February 2, 2015

If you use my query to get the options and then process those options as did in your earlier post

foreach ($options as $key => $val)

then you should get eg

asai · February 2, 2015

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

Barand · February 2, 2015

That's because you have

$options[$query_data["adate"]] = $query_data["adate"];

when you should have

$options[$query_data["ndate"]] = $query_data["adate"];

asai · February 2, 2015

Really getting close now

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?

cyberRobot · February 2, 2015

Try moving the ORDER BY clause after the WHERE clause.

Sign In

Data from a table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information