Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts

    File names in SQL database

    Just a quick question. I know it is possible to store a file name in a SQL database and use PHP to recall it to a web page, I'm currently doing it with members avatars.

    What I want to know is can you add multiple file names to the same box and recall multiple files to create a gallery?
    Last edited by Fou-Lu; 09-26-2013 at 03:00 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Sure, but that's simply a bad design. Use a many to many relationship by using a many to many or a many to one in order to flatten it into a normalized structure.
    If an image can belong to multiple gallery, you'd use three tables, image, gallery and imagegallery (or whatever names you want). If an image can belong to one and only one gallery, than you'd add a property on the image table to relate it to the gallery.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by Fou-Lu View Post
    Sure, but that's simply a bad design. Use a many to many relationship by using a many to many or a many to one in order to flatten it into a normalized structure.
    If an image can belong to multiple gallery, you'd use three tables, image, gallery and imagegallery (or whatever names you want). If an image can belong to one and only one gallery, than you'd add a property on the image table to relate it to the gallery.
    I'm not sure what you mean, my knowledge of SQL is limited but growing.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Many to many:
    Code:
    +-------------+            +-----------------+              +-------------------+
    | image       |            | galleryimage    |              | gallery           |
    +-------------+            +-----------------+              +-------------------+
    | id [pk]     |>o--------+<| imageid [pk][fk]|>+----------o<| id [pk]           |
    | name [uk]   |            | gallid [pk][fk] |              | name [uk]         |
    | path [uk]   |            +-----------------+              | otherthings       |
    | otherthings |                                             +-------------------+
    +-------------+
    Many to one:
    Code:
    +-------------+         +---------------+
    | image       |         | gallery       |
    +-------------+         +---------------+
    | id [pk]     |>o-----+<| id [pk]       |
    | name [uk]   |         | name [uk]     |
    | path [uk]   |         | otherthings   |
    | gallid [fk] |         +---------------+
    | otherthings |
    +-------------+
    The first lets you use images in as many galleries as you want, while the second attaches an image to a gallery and is required.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Example of a many-to-one table structure:
    Code:
    CREATE TABLE members (
        memberid INT AUTO_INCREMENT PRIMARY KEY,
        membername VARCHAR(50),
        password VARCHAR(30),
        ... other fields as needed for your site ..
    ) ENGINE INNODB;
    
    CREATE TABLE memberImages (
        memberid INT,
        imagefilename VARCHAR(255),
        CONSTRAINT FOREIGN KEY (memberid) REFERENCES members(memberid)
    ) ENGINE INNODB;
    If you think you need many-to-many, ask again.

    *****
    EDIT: Slow on the uptake again, aren't I? <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    It would be many to one. Each member will have their own gallery and therefore will only ever appear in one gallery.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    If a member can have multiple galleries, than you'll want to use similar to the one to many I have posted above, and then use the memberid to provide ownership to the gallery.
    If you only allow one gallery per member, you could assign the image directly to a member exactly as old pedant has.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #8
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    It will be one gallery per member.

  • #9
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    How would I then upload multiple files to the table? Or rather how should they appear once in the table?

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Code:
    memberid | imagefilename
    -------------------------
    1 | /path/to/image
    1 | /path/to/image
    2 | /path/to/image
    4 | /path/to/image
    4 | /path/to/image
    4 | /path/to/image
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #11
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    I have managed to get it to upload files to the server but it doesn't put the file names in to the database. This is my code below.

    PHP Code:
    <?php 

    include("config.php"); 

    // connect to the mysql server
    $link mysql_connect($server$db_user$db_pass)
    or die (
    "Could not connect to mysql because ".mysql_error());

    // select the database
    mysql_select_db($database)
    or die (
    "Could not select database because ".mysql_error());

    $insert mysql_query("insert into `Images` values ('".$_FILES['file']['name']."' WHERE regname = '".$_POST['regname']."')");

    ?>

    <?php
    $id 
    $_POST['regname'];
    $target"pictures/".$id."/";
            if(
    $target[strlen($target)-1]!='/')
                    
    $target=$target.'/';
                
    $count=0;
                foreach (
    $_FILES['file']['name'] as $filename
                {
                    
    $temp=$target;
                    
    $tmp=$_FILES['file']['tmp_name'][$count];
                    
    $count=$count 1;
                    
    $temp=$temp.basename($filename);
                    
    move_uploaded_file($tmp,$temp);
                    
    $temp='';
                    
    $tmp='';
                }
        
    header("location:gallery.html");
    ?>

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    You must have one INSERT for each file, so the INSERT must be *INSIDE* the loop that gets the files one at a time.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    How would I do that then?

  • #14
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    On the back of your advice I have done a little research and come up with the below. How ever it is still not working correctly.

    PHP Code:
    <?php 

    include("config.php"); 

    // connect to the mysql server
    $link mysql_connect($server$db_user$db_pass)
    or die (
    "Could not connect to mysql because ".mysql_error());

    // select the database
    mysql_select_db($database)
    or die (
    "Could not select database because ".mysql_error());

    $id $_POST['regname'];
    $target"pictures/".$id."/";
            if(
    $target[strlen($target)-1]!='/')
                    
    $target=$target.'/';
                
    $count=0;
                foreach (
    $_FILES['file']['name'] as $filename
                {
                    
    $query1 "insert into `Images` values ('".$filename."' WHERE regname = '".$id."')";
                    
    $temp=$target;
                    
    $tmp=$_FILES['file']['tmp_name'][$count];
                    
    $count=$count 1;
                    
    $temp=$temp.basename($filename);
                    
    move_uploaded_file($tmp,$temp);
                    
    $temp='';
                    
    $tmp='';
                    
    $q mysql_query($query1) or die ('Error posting data');
                                }
        
    header("location:gallery.html");
    ?>
    Last edited by Foster; 09-29-2013 at 02:58 PM.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    You can't use WHERE in an INSERT query. Where did you come up with that very wrong syntax???

    Where is the } that is needed to end your for ( ... ) { loop??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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