www.webdeveloper.com
Page 1 of 10 123 ... LastLast
Results 1 to 15 of 140

Thread: Displaying images from database

  1. #1
    Join Date
    Jun 2006
    Posts
    159

    Question Displaying images from database

    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!

  2. #2
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739

  3. #3
    Join Date
    Jun 2006
    Posts
    159
    Quote Originally Posted by bokeh
    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.

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

  4. #4
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739
    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.

  5. #5
    Join Date
    Jun 2006
    Posts
    159
    Quote Originally Posted by 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.
    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!!

  6. #6
    Join Date
    Jun 2006
    Posts
    159
    Ok, I've made some chainged, now database (images) table (uploaded_images) looks like this.
    Code:
    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
    HTML Code:
    <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 Code:
    <?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.

  7. #7
    Join Date
    Jun 2006
    Posts
    159
    My vote table looks like this.

    Code:
    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!

  8. #8
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739
    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?

  9. #9
    Join Date
    Jun 2006
    Posts
    159
    Quote Originally Posted by 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?
    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.
    PHP Code:
    { session_start();
      session_register("uploadFilename"); 
      $uploadFilename = <?php session_start();?>;
    }
    and then have something like..

    PHP Code:
    <?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..

  10. #10
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739
    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.

  11. #11
    Join Date
    Jun 2006
    Posts
    159
    one quick question, what is the PHP script for datetime? for date I was using..
    PHP Code:
    $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!

  12. #12
    Join Date
    Jun 2006
    Posts
    159
    I've change my uploaded_images table so it looks like this.

    PHP Code:
    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 OK0 rows affected (0.01 sec)

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

  13. #13
    Join Date
    Jun 2006
    Posts
    159
    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 Code:
    <?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.
    Code:
    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?

  14. #14
    Join Date
    Jan 2005
    Location
    Alicante (Spain)
    Posts
    7,739
    No need to worry about IPs. I recommend 2 tables to go with your uploaded_images table.
    Code:
    CREATE TABLE `vote_records` (
      `voter_id` int(11) NOT NULL,
      `image_id` int(11) NOT NULL
    )
    Code:
    CREATE TABLE `voters` (
      `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    )
    The following code will handle the voting.
    PHP Code:
    <?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.
    Last edited by bokeh; 07-30-2006 at 12:36 PM.

  15. #15
    Join Date
    Jun 2006
    Posts
    159
    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!!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles