[SOLVED] Displaying Most Viewed Image

[RAH]

Hi,

 

I'm new here!     Looks like a great forum - hope to learn lots here.

 

I'm currently trying to get a media script to display the most viewed image.

 

I have worked out a query to do this:

 

'SELECT * FROM `files` WHERE `category_id` = 5 ORDER BY `files` . `views` DESC LIMIT 0, 1 ';

 

However I'm not sure how to get the appropriate values I need into variables.  The above query returns 1 entire row and I have to grab several values from that row (eg. filename).

 

When I get the necessary data into variables I can do things like the following:

 

<p>Top Viewed Image</p>
<p>
<img src="http://mydomain.com/' . $top_viewed_image . 'jpg '" width="276" height="110" /></p>

 

 

I'm just sort of lost a bit, would appreciate any help.

 

Thanks!

[suttercain]

Hello,

 

Just a suggestion and maybe not the most efficient, but you could store the page views (that contains the image) into a database. Each time a user visits the page view is incremented by one. You could also add functionality using the $_SERVER array to ensure that the same person who visits that same image only counts as one.

 

Then in your MYSQL select statement, you can order by page view.

 

Just a thought.

[RAH]

The page views are stored in the database, see my query above.  I can find what I'm looking for via SQL query but then I need to turn the result values into variables and so on.

[emehrkay]

are you limiting your display to only one image? that is what your query is doing...

 

if not, you could run your qurey then loop through the results displaying an image on each loop

 

$query = "SELECT * FROM files WHERE catageory_id = 5 ORDER BY views DESC";

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

$count = mysql_num_rows($result);

 

$images = '';

for($i = 0; $i < $count; $i++){

$row = mysql_fetch_assoc($result);

$images .= "<img src =\"". $row['columnWithSrc'] ."\" ... />";

}

 

echo "<p>Top Viewed images</p>". $images;

 

something like that should work

[RAH]

Hi,

 

I just need the most viewed image displayed (a single image).  Thanks.

[emehrkay]

$query = "SELECT * FROM files WHERE catageory_id = 5 ORDER BY views DESC LIMIT 0, 1";

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

$count = mysql_num_rows($result);

$row = mysql_fetch_assoc($result);

$images = "<img src =\"". $row['columnWithSrc'] ."\" ... />";

 

 

same code, no loop

[RAH]

OK, that makes sense for the most part - how do I go about displaying the actual image in the browser?

[suttercain]

This is the image

$images = "<img src =\"". $row['columnWithSrc'] ."\" ... />";

 

you can now echo the variable that contains that string:

<?php echo $images; ?>

 

Obviously you need to replace columnWithSrc' with the actual column name that contains the image name. So which ever column has image.jpg is the column you put.

 

SC

[RAH]

OK, here's the current code:

 

$query = "SELECT * FROM files WHERE category_id = 5 ORDER BY views DESC LIMIT 0, 1";
$result = mysql_query($query) or die (mysql_error());
$count = mysql_num_rows($result);
$row = mysql_fetch_assoc($result);
$images = "<img src =\thumbnails\"". $row['filename'] .png."\" ... />";

echo $images

 

This is outputting the following HTML:

<img src = humbnails"91b440ebfbda475a3fd198d608adb449png" ... />

 

This needs to be thumbnails/91b440ebfbda475a3fd198d608adb449.png

[lightningstrike]

$query = "SELECT * FROM files WHERE category_id = 5 ORDER BY views DESC LIMIT 0, 1";
$result = mysql_query($query) or die (mysql_error());
$count = mysql_num_rows($result);
$row = mysql_fetch_assoc($result);
$images = "<img src =\"thumbnails/". $row['filename'] .png."\" ... />";
echo $images;

[RAH]

That is outputting the following HTML which is still not quite right:

 

<img src ="thumbnails/91b440ebfbda475a3fd198d608adb449png" ... />

[AndyB]

Should be:

 

$images = "<img src='thumbnails/". $row['filename']. ".png'/>";

[RAH]

Thanks very much!  You're all very helpful here.

 

How would I go about hyperlinking this image to the appropriate file in the database?  Would I do another query/result/row combo and then edit the $images variable, adding a hyperlink?

 

I have a rough idea...

[RAH]

Anyone?

[suttercain]

Rah,

 

Do you know HTML or XHTML?

[RAH]

Hi,

 

I can do both, just not sure about this particular PHP task.

[RAH]

Hi,

 

I'm using the following code:

 

$image = "<a href='http://domain.com/files/". $row['id']. "'/". $row['permalink']. "''><img src='thumbnails/". $row['thumbnail']. "'/></a>";

 

For some reason all the row variables are being correctly output apart from permalink - why would this be?

 

Thanks.

[itsmeArry]

this is because you are closing ' i.e. quote

 

use this

 

$image = "<a href='http://domain.com/files/". $row['id']. "/". $row['permalink']. "'><img src='thumbnails/". $row['thumbnail']. "'/></a>";