asai Posted January 21, 2015 Share Posted 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". Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/ Share on other sites More sharing options...
Barand Posted January 21, 2015 Share Posted January 21, 2015 Output an HTML anchor tag with a link to the file. echo "<a href='path/to/file'>View</a>"; Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503597 Share on other sites More sharing options...
asai Posted January 21, 2015 Author Share Posted January 21, 2015 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? Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503623 Share on other sites More sharing options...
cyberRobot Posted January 21, 2015 Share Posted January 21, 2015 It looks like you're missing the variable name. Try something like this: echo "<a href='alldaymovies/{$row['FileName']}'>View</a>"; Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503625 Share on other sites More sharing options...
asai Posted January 21, 2015 Author Share Posted January 21, 2015 (edited) 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 January 21, 2015 by asai Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503632 Share on other sites More sharing options...
Barand Posted January 21, 2015 Share Posted 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'])); Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503634 Share on other sites More sharing options...
asai Posted January 21, 2015 Author Share Posted January 21, 2015 Perfect!! Thank you. One small detail: How can I adjust the colum alignment? Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503643 Share on other sites More sharing options...
Barand Posted January 21, 2015 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1503645 Share on other sites More sharing options...
asai Posted January 25, 2015 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504134 Share on other sites More sharing options...
Barand Posted January 25, 2015 Share Posted January 25, 2015 Sorry, I don't understand what you are asking. Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504135 Share on other sites More sharing options...
asai Posted January 26, 2015 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504212 Share on other sites More sharing options...
Barand Posted January 26, 2015 Share Posted January 26, 2015 Like this? SELECT * FROM DayMovie WHERE YEAR(FileDate) = 2015 AND MONTH(FileDate) = 1 ORDER BY FileDate DESC Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504225 Share on other sites More sharing options...
asai Posted January 26, 2015 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504227 Share on other sites More sharing options...
asai Posted January 26, 2015 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504237 Share on other sites More sharing options...
asai Posted January 31, 2015 Author Share Posted January 31, 2015 Is there any suggestions to my last question? Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504441 Share on other sites More sharing options...
Barand Posted January 31, 2015 Share Posted 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"]; } Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504448 Share on other sites More sharing options...
asai Posted February 1, 2015 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504497 Share on other sites More sharing options...
asai Posted February 2, 2015 Author Share Posted February 2, 2015 (edited) 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 February 2, 2015 by asai Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504554 Share on other sites More sharing options...
Barand Posted February 2, 2015 Share Posted 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) Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504560 Share on other sites More sharing options...
asai Posted February 2, 2015 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504568 Share on other sites More sharing options...
Barand Posted February 2, 2015 Share Posted 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 <option value='2015-01'>Jan 2015</option> Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504576 Share on other sites More sharing options...
asai Posted February 2, 2015 Author Share Posted 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... Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504577 Share on other sites More sharing options...
Barand Posted February 2, 2015 Share Posted 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"]; Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504582 Share on other sites More sharing options...
asai Posted February 2, 2015 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504583 Share on other sites More sharing options...
cyberRobot Posted February 2, 2015 Share Posted February 2, 2015 Try moving the ORDER BY clause after the WHERE clause. Quote Link to comment https://forums.phpfreaks.com/topic/294097-data-from-a-table/#findComment-1504584 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.