[alatum.angelus]

UPDATE HALF SOLVED.

I put the php components of the webpage into a script that is run from bash using php each time the xbmc library is updated. the php populated a table then outputs its html into 2 text files named "movies and tvshows" these are then linked into the webpage via phpinclude, this way the time consuming part of the script only needs to be run once each time a new movie is loaded and not each time the webpage is visited. its reduced load times from 5-10 seconds down to milliseconds. its also stoped the server bogging down due to multiple sql requests for the same webpage to different users.

code update is

PHP Code:

<?php
// Create connection
$con=mysqli_connect("localhost","###","###","###");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


$result = mysqli_query($con,"SELECT movie.idFile, movie.c00, movie.c07, movie.c14, genre.strGenre, genre.idGenre, genrelinkmovie.idGenre, genrelinkmovie.idMovie
                                                        FROM movie, genre, genrelinkmovie
                                                        WHERE genrelinkmovie.idGenre = genre.idGenre
                                                        GROUP BY idFile
                                                        order by `c00`");

ob_start();
echo "<table class='center' border='0'>
<tr>
<th>Movie</th>
<th>Genre</th>
<th>Year</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td class='movienametable1'>" . $row['c00'] . "</td>";
  echo "<td class='moviegenre'>" . $row['c14'] . "</td>";
  echo "<td class='movieyeartable'>" . $row['c07'] . "</td>";
  echo "</tr>";
}

echo "</table>";

$page = ob_get_contents();
ob_end_flush();
$fp = fopen("######scripts/media/kodi/kodiphpscript_out/movies","w");
fwrite($fp,$page);
fclose($fp);

ob_start();
$result = mysqli_query($con,"SELECT tvshow.idshow, tvshow.c00, tvshowcounts.idshow, tvshowcounts.totalSeasons, tvshowcounts.totalCount, episodeview.c05, episodeview.idFile, episodeview.idShow, episodeview.c12, episodeview.c13
                                                        FROM tvshow, tvshowcounts, episodeview
                                                        WHERE tvshow.idshow = tvshowcounts.idshow
                                                        AND episodeview.idfile = (SELECT MAX(idfile)
                                                        FROM episodeview
                                                        WHERE tvshow.idshow = episodeview.idshow
                                                        GROUP BY idshow LIMIT 0, 1
                                                        order by 'c05')
                                                        GROUP BY `c05`
                                                        order by `c00`"
                                                        );


echo "<table class='center' border='0'>
<tr>
<th>TV Show</th>
<th>Seasons</th>
<th>Airdate</th>
<th>Latest</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td class='movienametable'>" . $row['c00'] . "</td>";
  echo "<td class='movieyeartable'>" . $row['totalSeasons'] . "</td>";
  echo "<td class='tvshowdate'>" . $row['c05'] . "</td>";
  echo "<td class='tvshowdate'>S" . sprintf("%02d", $row['c12']) ."E" .  sprintf("%02d", $row['c13']) . "</td>";
  echo "</tr>";
 echo "</div>";
}

echo "</table>";

$page = ob_get_contents();
ob_end_flush();
$fp = fopen("#######scripts/media/kodi/kodiphpscript_out/tvshows","w");
fwrite($fp,$page);
fclose($fp);

mysqli_close($con);
?>

Now I just have to workout how to shrink the query for same results.

--------------------------------------------------------------------------------------------------------------------


Hello there, really struggled to find correct place for this question, Please move if see fit,
Also the structure isn't optimal of my post, I shall try to do my best.

I have spent some time programming a webpage for family and friends to connect to and see a list of my kodi movies and tv shows.

The movies sections shows the shows in alphabetical order and the year the movie was released and the genres in which the movie fall under.
The tv show section shows the tv show name, amount of seasons and the latest season in the list again in alphabetical order.

recently i noticed this running slow so decided to make a script on server.

the script will detect a new movie source, be it dvd/blu ray backup or download, it will scrape and rename the files, copy them to the correct location, tell kodi to update library then run my webpage code in the background which will query the database and output the results into a table on a html file for faster loading in the webpage (doesn't need to run query on each page load)

now id like to streamline this further, the way i get it to run my webpage is using a wget command on the webpage and outputting its results into another webpage which is view able to the user. but to make this better i need to clean up the original code also and i'm new to mysql. I will show you my basic scripts here. any advice would be wonderful.

Code:

filebot -script fn:amc --output "/media/48TB-POOL/Media/" --log-file /scripts/transmission/amc.log --action copy --conflict override -non-strict --def music=y --def artwork=y --def subtitles=en  "ut_dir=$TR_TORRENT_DIR/$TR_TORRENT_NAME" "ut_kind=multi" "ut_title=$TR_TORRENT_NAME" --def "seriesFormat=TV Shows/{n}/Season {s.pad(2)}/{n} - {sxe} - {t}"

. /scripts/media/kodi/config/kodi-host.conf

{
xbmc-send --host=$SERVER --port=$PORT --action="UpdateLibrary(video)"

} &> /dev/null
wget -O /var/www/****************_com/kodi/index.html *************.com/kodi/php/index.php

Also to check for rips etc

Code:

do
{
inotifywait -r -e attrib,move,create,delete /media/48TB-POOL/Media/ripped &&
cp -r /media/48TB-POOL/Media/ripped /media/48TB-POOL/Media/
xbmc-send --host=$SERVER --port=$PORT --action="UpdateLibrary(video)"

} &> /dev/null
done

And finally the the php part of the webpage itself..

PHP Code:

<?php
$result = mysqli_query($con,"SELECT tvshow.idshow, tvshow.c00, tvshowcounts.idshow, tvshowcounts.totalSeasons, tvshowcounts.totalCount, episodeview.c05, episodeview.idFile, episodeview.idShow, episodeview.c12, episodeview.c13
                            FROM tvshow, tvshowcounts, episodeview 
                            WHERE tvshow.idshow = tvshowcounts.idshow 
                            AND episodeview.idfile = (SELECT MAX(idfile)
                            FROM episodeview 
                            WHERE tvshow.idshow = episodeview.idshow 
                            GROUP BY idshow LIMIT 0, 1
                            order by 'c05')
                            GROUP BY `c05`
                            order by `c00`"
                            );


echo "<table class='center' border='0'>
<tr>
<!-- <th>ID</th> -->
<th>TV Show</th>
<th>Seasons</th>
<!-- <th>Episodes</th> -->
<th>Airdate</th>
<th>Latest</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td class='movienametable'>" . $row['c00'] . "</td>";
  echo "<td class='movieyeartable'>" . $row['totalSeasons'] . "</td>";
  echo "<td class='tvshowdate'>" . $row['c05'] . "</td>";
  echo "<td class='tvshowdate'>S" . sprintf("%02d", $row['c12']) ."E" .  sprintf("%02d", $row['c13']) . "</td>";
  echo "</tr>";
 echo "</div>";
}

echo "</table>";

mysqli_close($con);
?>

and

PHP Code:

<?php

$result = mysqli_query($con,"SELECT movie.idFile, movie.c00, movie.c07, movie.c14, genre.strGenre, genre.idGenre, genrelinkmovie.idGenre, genrelinkmovie.idMovie
                            FROM movie, genre, genrelinkmovie 
                            WHERE genrelinkmovie.idGenre = genre.idGenre                    
                            GROUP BY idFile
                            order by `c00`");

echo "<table class='center' border='0'>
<tr>
<th>Movie</th>
<th>Genre</th>
<th>Year</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td class='movienametable1'>" . $row['c00'] . "</td>";
  echo "<td class='moviegenre'>" . $row['c14'] . "</td>"; 
  echo "<td class='movieyeartable'>" . $row['c07'] . "</td>";
  echo "</tr>";
}

echo "</table>";



mysqli_close($con);
?>

Realistically id like the query to be shorter with same results. id also like to remove the wget part and have a bash script to do the same result.


Any advice would be great. thanks