Data from a table

asai · February 2, 2015

YES! One small issue remains: Theres nothing happening when I select anything in the combo. It only show me the first value. (Dec 2014)

I have separated the code into 2 php files.

combo.php:

<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["ndate"]] = $query_data["adate"];
}
include 'closedb.php';

?>

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

</html>

movies.php:

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

<?php

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

$result = mysql_query("SELECT *
            FROM DayMovie 
            WHERE FileDate LIKE '$key%'") 
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']}'>Se film</a>";
	echo "</td></tr>";        
} 
echo "</table>";

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

I miss a small thing here... What is it?

Edited February 2, 2015 by asai

cyberRobot · February 2, 2015

In your movies.php file, are you trying to query DayMovie based on the selection made in the drop-down menu? If so, you'll need to use the corresponding $_POST (or $_GET if your form uses the GET method) variable instead of $key in the following line:

WHERE FileDate LIKE '$key%'")

Also, remember to protect your queries from SQL injection attacks by using something like mysql_real_escape_string().

http://php.net/manual/en/function.mysql-real-escape-string.php

asai · February 2, 2015

Yes, this is what I try to do.

Any example on how to do this?

CroNiX · February 2, 2015

It'd either be:

$key = $_GET['ndate']

or

$key = $_POST['ndate']

depending on how you are submitting your form (POST or GET). Your code is using a javascript function here, but we don't see that code so not sure how your form is being submitted:

<select name="ndate" onClick="submitCboSemester();">

asai · February 2, 2015

OK, I see that this is the cause of the problem... The javascript is from a copy paste, so this function isn't existing. The onclick should the be POST? Like this:

<select name="ndate" onClick="POST;">

And then $key = $_POST['ndate'] ??

CroNiX · February 2, 2015

Not exactly. Why do you need javascript there anyway, especially if you don't know what it's doing? Just use a regular form with a submit button. You don't have a complete form right now...just a single form element with no form open/close tag.

The form open tag should contain the action attribute, where the form gets submitted to (your movies.php script), along with the method, which would be POST.

There are tons of tutorials on HTML forms on the net.

cyberRobot · February 2, 2015

For what it's worth, here are a couple of resources to help with building forms:

CroNiX · February 2, 2015

<form action="http://yoursite.com/movies.php" method="POST">
  <select name="ndate">
    <option></option> //your options
  </select>
  <input type="submit">
</form>

Clicking submit will send all form data to http://yoursite.com/movies.php via the POST method. So all values in movies.php would retrieve from the $_POST superglobal

Then in your movies.php script, get the form value for the form element named 'ndate', which is your select form element.

$ndate = $_POST['ndate'];

asai · February 2, 2015

Fantastic! It works great!

Thanks all for the good help.

asai · February 3, 2015

One more idea I have:

Now I have this link to open the movies from the SQL table:

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

Is it possible to make the same link do 2 things: First of all open the movie as it does now. The second thing I would like it to do is update a row in the table.

This is for counting how many times the movie is played (the link is clicked). Is this possible?

cyberRobot · February 3, 2015

The second thing I would like it to do is update a row in the table.

This is for counting how many times the movie is played (the link is clicked). Is this possible?

In the script which opens the movie, you just need to add a query which updates the database.

asai · February 3, 2015

This is just a hyperlink. How do I write a script to do this job and put it into the table instead of the hyperlink?

cyberRobot · February 3, 2015

This is just a hyperlink. How do I write a script to do this job and put it into the table instead of the hyperlink?

From what I understand about the code so far, the hyperlink is fine as is. It just needs to direct visitors to the script which opens the movie. The script that opens the movie is where you would add the extra query to update the counter.

cyberRobot · February 3, 2015

For the update query, you could try something like the following example:

http://www.tech-recipes.com/rx/2139/mysql_increment_an_exisitng_value/

asai · February 3, 2015

Well, theres no script that opens the file. It is a MP4 file which is opened directly and played within the browser.

I tried this:

echo "<a href='alldaymovies/{$row['FileName']}' onclick='window.open(this.href); return false;' onkeypress='window.open(this.href); return false;'>Se film</a>";

And then I want to run this command:

UPDATE DayMovie SET counter=counter+1 WHERE id=xx

CroNiX · February 3, 2015

Since you're just linking to the actual file in a dir instead of a script that process the request and plays the video, probably the best way would be using ajax that sends the $row['FileName'] value to a script on the server, which will increment the counter for that movie.

You'd just do something like:

echo "<a href='alldaymovies/{$row['FileName']}' onclick='playVideo(this.href, {$row['FileName']});' onkeypress='playVideo(this.href, {$row['FileName']});'>Se film</a>";

and then create a js function

function playVideo(url, filename)
{
  //code to send the ajax request, POSTing "filename" to the script
  //..
  //in the oncomplete of the ajax event
  window.open(url);
  return false;
}

It would probably be best to send them to a script, which will output the video, rather than linking to the raw file. Then you could put the update code in there whenever a video is requested. That would be more sure-fire.

Edited February 3, 2015 by CroNiX

Barand · February 3, 2015

You could call a js function when the link is clicked which then uses AJAX to update the db table

asai · February 3, 2015

You could call a js function when the link is clicked which then uses AJAX to update the db table

I am not familiar with AJAX. Could you give me a hint from the information earlier in this thread? :sweat:

cyberRobot · February 3, 2015

Have you considered using something like Google Analytics to track link clicks?

asai · February 3, 2015

Have you considered using something like Google Analytics to track link clicks?

No, this i new to me.

How could I implement something like that? Take into consideration that this list is updated with a new movie, with link every day.

I want to count how many times these movies are shown. The idea is to use the link clicks as a counter.

asai · February 4, 2015

I would like to use my update to the table.

I have made a php file for updating the table:

<?php

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

$filename = $_POST['FileName'];

$result = mysql_query("UPDATE DayMovie SET Counter=Counter+1 WHERE FileName='$filename'") 
or die(mysql_error());

include 'closedb.php';
?>

I only need a way to get the filename from the movie page and that this page is called when the link is clicked.

Any suggestions for a solution?

Barand · February 4, 2015

Any suggestions for a solution?

None that haven't already been suggested

asai · February 5, 2015

You could call a js function when the link is clicked which then uses AJAX to update the db table

I know, but I don't know how to make a js function and how to use AJAX. I am sure this is the solution, but I need an example on how to do this. :confused:

Barand · February 5, 2015

Following on from Cronix's reply (#41)

Easiest way to do an AJAX call is with jquery

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script type="text/javascript">

function playVideo(url, filename)
{
    // AJAX call to post filename to your update script
    $.post(
        "my_update.php",
        {"filename":filename},
        function(data) {
            // do whatever with whatever you send back in "data"
            // eg if you send back an error message
            if (data != '') {
                alert(data);
            }
            else {
                 // show video
                window.open(url);
                return false;
            }
        },
        "text"
    );
    
   
}
</script>

Whatever you echo in the update script will be sent back as the response in the "data" variable above.

asai · February 5, 2015

It is possible that I am a bit slow... But I try my best. I think... ::)

Heres my movie.php file:

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

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script type="text/javascript">

function playVideo(url, filename)
{
    $POST(
        "update.php",
        {"filename":filename},
        function(data) {
            if (data != '') {
                alert(data);
            }
            else {
                window.open(url);
                return false;
            }
        },
        "Hva skjer her?"
    );
    
   
}
</script>

<?php

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

$ndate = $_POST['ndate'];

$result = mysql_query("SELECT *
            FROM DayMovie 
            WHERE FileDate LIKE '$ndate%' ORDER BY FileDate DESC") 
or die(mysql_error());  
echo "<table border='0'>";
echo "<tr> <th>Dato</th><th>Visninger</th><th>Handling</th></tr>";
while($row = mysql_fetch_array( $result )) {
	echo "<tr><td>";
	echo date('d.m.Y', strtotime($row['FileDate']));
	echo "</td><td>";
	echo $row['Counter'];
	echo "</td><td>";
	echo "<a href='alldaymovies/{$row['FileName']}' onclick='playVideo(this.href, {$row['FileName']});' onkeypress='playVideo(this.href, {$row['FileName']});'>Se film</a>";
	echo "</td></tr>";        
} 
echo "</table>";

include 'closedb.php';
?>

</html>

And this is my update.php:

<?php

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

$filename = $_POST['filename'];

$result = mysql_query("UPDATE DayMovie SET Counter=Counter+1 WHERE FileName='$filename'") 
or die(mysql_error());

include 'closedb.php';
?>

The link opens the video but no update is run.

Sign In

Data from a table

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Barand

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

Join the conversation

Important Information