Displaying images from database

[PJStew]

Hi every one, i'm using bokehs upload image script (this) to upload the images to a database called images (table call uploaded_images) this part works fine, but I'd like to be able to diplay the images from the database on a seporate page, one at a time idealy in a random order with a next buton to take you to the next image. I would also like to be able to vote on the images if possable a bit like hot or not, this is for my unicycle site, I want to have a winner for the best photo every month.

Thanks to you all!

[bokeh]

Could you post your table structure?

[PJStew]

Could you post your table structure?

Yep sure, i'l be able to post up everything i've done when i get home tonight, but the table looks like this.

mysql> use images;
Database changed
mysql> show tables;
+------------------+
| Tables_in_images |
+------------------+
| uploaded_images  |
+------------------+
1 row in set (0.00 sec)

mysql> explain uploaded_images;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| user_name| varchar(50) | YES  |     | NULL    |       |
| filename | varchar(150)| YES  |     | NULL    |       |
| DATE     |    date     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

[bokeh]

Just a couple of thoughts for the time being... random and order are a contradiction; you can't have both. If you want a next/prev navigation system you want order rather than random.

I would have used a datetime column rather than just date. This could then be used as a useful ordering facility.

It would be nice to have an auto incrementing ID column as well for easy reference.

You need to think how the voting will work! You need a system that recognizes the clients and does not allow repeat voting on any particular picure by the same user. Any idea how to differenciate between your users?

You will need another table to keep track of the voting.

[PJStew]

Just a couple of thoughts for the time being... random and order are a contradiction; you can't have both. If you want a next/prev navigation system you want order rather than random.

I would have used a datetime column rather than just date. This could then be used as a useful ordering facility.

It would be nice to have an auto incrementing ID column as well for easy reference.

You need to think how the voting will work! You need a system that recognizes the clients and does not allow repeat voting on any particular picure by the same user. Any idea how to differenciate between your users?

You will need another table to keep track of the voting.

Thanks for replying bokeh!
Sorry for the contradiction, I think the way you suggest would be good. I don't think i'll need a prev nav, infact if i could make it so it just displays the next image when they vote that would be amazing!!
I will change/add everything you mentioned tonight when i get home( then post it all up for you to see). as for differenciate between users, i'm using a session with the user name in it for the access to the upload page. will that do?

Thanks again!!

[PJStew]

Ok, I've made some chainged, now database (images) table (uploaded_images) looks like this.

mysql> create table uploaded_images
    -> (
    -> id int auto_increment not null,
    -> user_name varchar(50),
    -> filename varchar(150),
    -> date DATETIME,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> explain uploaded_images;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(50)  | YES  |     | NULL    |                |
| filename  | varchar(150) | YES  |     | NULL    |                |
| date      | datetime     | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

I've also had a go at the voting script.
// vote.php

<body>
<table align="center" width="400px" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#E8E8E8">
    <td width="400px" colspan="2" rowspan="2" nowrap="nowrap"><div align="center"><h4>Please vote </h4>
<form action="votecheck.php" method="post">
<input type="radio" name="1" value="$1">1
<input type="radio" name="2"  value="$2">2
<input type="radio" name="3" value="$3">3
<input type="radio" name="4" value="$4">4
<input type="radio" name="5" value="$5">5
<input type="radio" name="6" value="$6">6
<input type="radio" name="7" value="$7">7
<input type="radio" name="8" value="$8">8
<input type="radio" name="9" value="$9">9
<input type="radio" name="10" value="$10">10<br />
<input type="submit" value="Vote!">
</form></div></td>
  </tr>
</table>

</body>

// votecheck.php

<?php
if( ($_POST['1']) or ($_POST['2']) or ($_POST['3']) or ($_POST['4']) or ($_POST['5']) or ($_POST['6']) or ($_POST['7']) or ($_POST['8'])
or ($_POST['9']) or ($_POST['10']) )
{
 $conn = mysql_connect("localhost","Username","password")
    or die("Could not connect to MySQL");
  $db = mysql_select_db("images",$conn)
    or die("Could not select database");
  $sql = "insert into `votes`
    (`1`,`2`,`3`,`4`,`5`,`6`,`7`,`8`,`9`,`10`) values
    (\"$1\",\"$2\",\"$3\",\"$4\",\"$5\",\"$6\",\"$7\",\"$8\",\"$9\",\"$10\",) )";
  $result = mysql_query($sql,$conn)
    or die(mysql_error());
if($result) { echo("vote added"); }
}
else { header("Location:http://localhost/Dinamic%20uni/Unicycle/vote2.php"); }
?>

This doesn't work unfortunatly.
I only started PHP 2 weeks ago, and it's taking me a while to get the hang of it.

[PJStew]

My vote table looks like this.

mysql> use images;
Database changed
mysql> create table vote
    -> (
    -> id int auto_increment not null,
    -> user_name varchar(50),
    -> filename varchar(150),
    -> vote varchar(2),
    -> date DATETIME,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> explain vote;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(50)  | YES  |     | NULL    |                |
| filename  | varchar(150) | YES  |     | NULL    |                |
| vote      | varchar(2)   | YES  |     | NULL    |                |
| date      | datetime     | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

mysql>

Thanks in advance!

[bokeh]

Well I can't wait to see the pictures. I've got a unicycle too but haven't used it for ages.

I know you want to get stuck straight into the coding but you need to think about the logic first. You need to think of a system to stop double voting. Are the users logged in or unknown?

[PJStew]

Well I can't wait to see the pictures. I've got a unicycle too but haven't used it for ages.

I know you want to get stuck straight into the coding but you need to think about the logic first. You need to think of a system to stop double voting. Are the users logged in or unknown?

That's a very good point, I think I would want it so the viewers don't have to be loged in, I've got it set so you have to be loged in to upload the images though.

I not to sure exactly how the display image function work, but could you stop double voting by creating a session with the filename in it when they vote? eg.

{ session_start();
  session_register("uploadFilename"); 
  $uploadFilename = <?php session_start();?>;
}

and then have something like..

<?php session_start();?>
if( ($uploadFilename) == (uploadFilename) )
{ do some thing... }
else
{ do some thing else...}

Thanks again for all your help.

p.s. keep up the unicycling!!
you can see some pics on the site i have up at the mo. unicyclepics it's in a bit of a state! as I started redoing it, in xhtml, then started playing whith PHP..

[bokeh]

I haven't really given this much thought but... maybe I would add two columns to the image table and store the votes in that. one column that counts the number of votes and another that contains the total vote score. This would let you count the total number of votes and to display the average.

Give each voter an id stored in a cookie for longevity and have a table that keeps track of the pictures they have voted on. This isn't foolproof but would probably be best if they don't need to be logged in.

[PJStew]

one quick question, what is the PHP script for datetime? for date I was using..

$q = "INSERT INTO uploaded_images (`filename`,`date`) VALUES ('$uploadFilename','".date(dMy)."')"; 

But now that coming up with this error.

Incorrect datetime value: '29Jul06' for column 'date' at row 1
Warning: Cannot add header information - headers already sent by (output started at c:\Apache\htdocs\Dinamic uni\Unicycle\new.upload.processor.php:70) in c:\Apache\htdocs\Dinamic uni\Unicycle\new.upload.processor.php on line 78

That idea sound perfect! For identifying the viewer, would it work if I use the date and time the viewer fist goes onto the page in a cookie, and then had a table with the time and date to identify and ether the date and time for the file, or the filename.
Thanks again!

[PJStew]

I've change my uploaded_images table so it looks like this.

mysql> use images;
Database changed
mysql> create table uploaded_images
    -> (
    -> id int auto_increment not null,
    -> user_name varchar(50),
    -> filename varchar(150),
    -> date DATETIME,
    -> votes int,
    -> score int,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> explain uploaded_images;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(50)  | YES  |     | NULL    |                |
| filename  | varchar(150) | YES  |     | NULL    |                |
| date      | datetime     | YES  |     | NULL    |                |
| votes     | int(11)      | YES  |     | NULL    |                |
| score     | int(11)      | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> 

one other idea, is the away of getting the view ip? If so could i use that in the cookie to identify?

[PJStew]

If i had a cookie that stored the viewers IP, and used the variable $filename the the file that is trying to be displayed, would a script like this...

<?php
if( (!$filename) or (!$ip) )
{ header("Location:http://localhost/Dinamic%20uni/Unicycle/index.php"); exit(); }
$conn=@mysql_connect("localhost", "ussername", "password")
    or die("Could not connect");
$rs = @mysql_select_db("images", $conn)
    or die("could not select database");
$sql="select * from vote_track where filename=\"$filename\" and ip=\"$ip\")";
$rs=mysql_query($sql,$conn)
    or die("Could not execute query");
$num = mysql_numrows($rs);
if($num != 0)
{ display differant image...
}

else
{ display this image and uploaded it to vote_take under that ip. }
?>

and a database like this.

mysql> create table vote_track
    -> (
    -> id int auto_increment not null,
    -> filename varchar(150),
    -> ip varchar(50),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> explain vote_track;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| filename | varchar(150) | YES  |     | NULL    |                |
| ip       | varchar(50)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

would that work to stop people voting on the same image?

[bokeh]

No need to worry about IPs. I recommend 2 tables to go with your uploaded_images table.

CREATE TABLE `vote_records` (
  `voter_id` int(11) NOT NULL,
  `image_id` int(11) NOT NULL
)

CREATE TABLE `voters` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
)

The following code will handle the voting.

<?php

$cookiename = 'voter_id';
$image_id = $_POST['image_id'];
$image_score = $_POST['image_score'];

mysql_connect('localhost', $user, $pass);
mysql_select_db('test');

// if voter doesn't have an id allocate one
if(!isset($_COOKIE[$cookiename]) or (isset($_COOKIE[$cookiename]) and !is_numeric($_COOKIE[$cookiename])))
{
    $query = "INSERT INTO `voters` VALUES(NULL)";
    mysql_query($query) or die(mysql_error());
    $_COOKIE[$cookiename] = mysql_insert_id();
}

// set or update cookie
setcookie($cookiename, $_COOKIE[$cookiename], time()+(60*60*24*365), '/', '', 0);

// check if already voted
$query = "SELECT * FROM `vote_records` WHERE `voter_id` = {$_COOKIE[$cookiename]} AND `image_id` = $image_id";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result))
{
    $output = 'No double voting allowed.';
}
else
{
    // update records and images tables
    $query = "INSERT INTO `vote_records` VALUES({$_COOKIE[$cookiename]}, $image_id)";
    mysql_query($query) or die(mysql_error());
    $query = "UPDATE `uploaded_images` SET votes = votes + 1, score = score + $image_score";
    mysql_query($query) or die(mysql_error());
    $output = 'Your vote on that photo has been added.';
}

echo $output;

?>

Don't forget to sanitize your variables.

There's probably a more more efficient way but I suck at SQL and no one else is replying.

As far as displaying the score I would display this under each image and work it out when displayoing the image page.

[PJStew]

Thanks for replying.

i've added all of that now, but still don't know how to display the images from the database, and how the vote form and image diplay would work together?

Thanks again!!