display total links from database

[Jack.C]

Hello all, Happy Holidays!

 

I have a question here, and I'm sure it has been answered before, but I couldn't find it via the search. So sorry if it has been answered before.

 

What I have is a script that displays full episodes of tv shows, where each episode is a category added to a tv show, or in this case a "movie".

 

What I would like to accomplish is show the total number of episodes (every category on the site.) and the total number of tv shows (every movie added.)

 

To display something like this:

 

 

Total TV Shows: 1,000 (where the total comes from the amount of Movies added to the database.)

 

Total Episodes: 1,000 (where the total comes from the amount of categories added to the database.)

 

And also if possible. a way to show the last five items added in the category table.

 

Thanks,

Jack

[MadTechie]

Hello all, Happy Holidays!

Hi Happy Holidays

 

I'll assume this in a SQL Database, and you have the following fields

ID = an autonumber (likely primary)

Added = timestamp for when the reocrds was created (recommeded)

Name = Movie Name

Categories = Movie categories  (i'll also assume you have data with this field containing TV an Episodes

and i'll guess the table is called Movies

 

Total TV Shows: 1,000 (where the total comes from the amount of Movies added to the database.)

SELECT count(*) as TotalMovies  FROM Movies

$Row['TotalMovies']

 

Total Episodes: 1,000 (where the total comes from the amount of categories added to the database.)

SELECT count(*) as TotalCats FROM Movies GROUP BY Categories

$Row['TotalCats']

 

And also if possible. a way to show the last five items added in the category table.

SELECT * FROM Movies ORDER BY Added DESC Limit 5

you could used ID instead of Added but i recommend you have a timestamp

 

Hope that helps

 

i assumed alot so it probably wrong lol

[Jack.C]

Hi, thanks. I get the folllowing error:

 

Parse error: syntax error, unexpected T_STRING in /home/camvixsi/public_html/watch/stats.php on line 3

[wildteen88]

And line 3 is? You'll need to post the code you're using here.

[Jack.C]

Line three is the code he posted above. I created the page stats.php to test it.

 

SELECT count(*) as TotalMovies  FROM movies

 

I changed Movies to movies to reflect the table name of the db.

[wildteen88]

PHP doesn't understand SQL. You can just dump SQL code into a PHP script and expect it to work. You'll first need to connect to mysql server using mysql_connect, followed by mysql_select_db to use a MySQL database.

 

To run your queries you'll use mysql_query. In order to retireve results from your query you'll use mysql_fetch_assoc.

 

Altogether

<?php

mysql_connect('localhost', 'your_mysql_username', 'your_mysql_password') or die(mysql_error());
mysql_select_db('your_mysql_database') or die(mysql_error());
// your query
$sql = 'SELECT count(*) as TotalMovies  FROM movies';
$result = mysql_query($sql or die(mysql_error());

// get the total number of movies
$row = mysql_fetch_assoc($result);
echo $row['count(*)'];

?>

[Jack.C]

Parse error: syntax error, unexpected ';' in /home/camvixsi/public_html/watch/stats.php on line 7

 

line 7 is:

 

$result = mysql_query($sql or die(mysql_error());

[LemonInflux]

Should it not be:

$result = mysql_query($sql) or die(mysql_error());

?

[Jack.C]

Should it not be:

$result = mysql_query($sql) or die(mysql_error());

?

 

That just displays a blank page.

[wildteen88]

echo $row['count(*)']; should of been $row['TotalMovies'];

[Jack.C]

Thanks.

 

One last question, how do I style the results? Like in a table or something.

 

Right now the results show as just numbers 5,24. How can I make it like

 

Total TV Shows : 5.

Total Episodes: 24

 

With some style around them? Like in a table or something.

[wildteen88]

You'd use an HTML table for that.

[Jack.C]

You'd use an HTML table for that.

 

Yeah, I know. But do I build the table around just the echo statement, or the entire query?

[wildteen88]

Post your full code here.

[Jack.C]

Post your full code here.

 

The code right now is just those queries you posted above. I want to style them before I put them somewhere. But anytime I try to add text before the echo $row['TotalMovies']; I get an error. So where do I begin styling, and adding text etc?

[MadTechie]

A little example

This is HTML<br>
Total TV Shows : <?php echo $row['TotalMovies']; ?><br>
Total Episodes : <?php echo $row['TotalCats']; ?><br>

 

Not the HTML is outside the <?php tags

 

you could also do this

 

<?php
echo "Total TV Shows : ".$row['TotalMovies'];
?>

[wildteen88]

To output text/HTML/JS/CSS etc you'll need to put it within an echo statement. Example

 

<?php

mysql_connect('localhost', 'your_mysql_username', 'your_mysql_password') or die(mysql_error());
mysql_select_db('your_mysql_database') or die(mysql_error());
// your query
$sql = 'SELECT count(*) as TotalMovies  FROM movies';
$result = mysql_query($sql or die(mysql_error());

// start HTML table
echo '<table border="1" cellpadding="5" cellspacing="0">
  <tr>
    <th>Total Movies</th>
';

// get the total number of movies
$row = mysql_fetch_assoc($result);

// display TotalMovies within a table cell
echo '    <td>'.$row['TotalMovies'].'</td>';

// end HTML table
echo '  </tr>
</table>';

?>

[Jack.C]

Thanks a lot. Sorry one last question, is there any way to display php code in tpl files? I have been trying to figure that out forever.

[wildteen88]

By display do you mean have PHP code visible on the page? Or have PHP code run within a .tpl

 

For the latter you can just use include to parse PHP code within a .tpl eg

 

include 'file_with_php_code.tpl';

 

To display actual PHP code you'll need to convert it to HTML special chars, eg

$php = '<?php echo "hello world"; ?>';

echo htmlentities($php, ENT_QUOTES);

[Jack.C]

What I have is a script that was built in/around .tpl files. http://watch.thetvstop.com/

 

I want to display the results of total links, and total episodes in the right sidebar of the above page, within <div> tags like the rest of the sidebar.

 

So would I just create a .tpl file with the php code to display the results from the db, and with the style of the box for the right sidebar?

 

I have tried many times to insert php into a .tpl and had no success. =/ So I assume creating a .tpl with just php would work?

[MadTechie]

it depends on how your calling the .tpl file.. if you include it it should work fine..

if your getting its contents then you could use str_replace() to replace a marker/tag

 

ie

Hello world We have <!--MovieCount--> Movies

 

$movies = 100;
$str = file_get_contents("myfile.tpl");
$str = str_replace("<!--MovieCount-->", $movies, $str);

echo $str;

 

Hello world We have 100 Movies

 

 

If your using ITX then you could try this

add {Movies} to the tpl flie

 

look in your code for something like this

$template = new HTML_Template_ITX

its the part that loads in your template..

Note the Red part and use the same below

Now add this after the SQL query

$MoveCount = 999;

$template->setVariable("Movies", $MoveCount);

[Jack.C]

The .tpl files are in a templates directory. Where movies_list.tpl serves as the index. As I have not written this script myself, somebody else did, I have no clue as to how the tpl files are being called. I just know how to edit them, and what they edit. I have never worked with tpl files before.

 

I have searched all the files that are not in the admin directory, template files and php files and did not find "$template = new HTML_Template_ITX" anywhere. So I would assume they are not being included?

 

The template files structure are like:

 

movies.tpl (this is the template that styles/displays) the content of a single movie.

movies_list.tpl (this file serves as the index page.)

links.tpl (this file serves as the way to style each link once "view link" is clicked.

overall.tpl (I would assume this is the main file, as it has the html doctype etc.)

 

 

 

 

[Jack.C]

By display do you mean have PHP code visible on the page? Or have PHP code run within a .tpl

 

For the latter you can just use include to parse PHP code within a .tpl eg

 

include 'file_with_php_code.tpl';

 

The include doesn't work. see the rightsidebar "stats" here: http://watch.thetvstop.com/

 

If you go directly to the .tpl file however it works. http://watch.thetvstop.com/templates/stats.tpl

 

The problem lies within including, how do I fix that?

[wildteen88]

When using PHP code ensure you place it within PHP tags (<?php ?>)

 

Also when using include you'll need to use the absolute/relative path to the file you're including, not just state the filename. In your case the path will be templates/stats.tpl

 

<?php include 'templates/stats.tpl'; ?>

[Jack.C]

When using PHP code ensure you place it within PHP tags (<?php ?>)

 

Also when using include you'll need to use the absolute/relative path to the file you're including, not just state the filename. In your case the path will be templates/stats.tpl

 

<?php include 'templates/stats.tpl'; ?>

 

I have done this, and it doesn't display anything from the stats file where it is included. It's supposed to show on http://watch.thetvstop.com in the right sidebar, under "stats" and as you can see it's blank.

 

<?php include 'templates/stats.tpl'; ?>

 

Is the code.