storing images in database

Discussion in 'MySQL' started by ravi951, Aug 20, 2011.

  1. ravi951

    ravi951 New Member

    Joined:
    Aug 9, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    web developer
    Location:
    hyderabad
    hi all,
    i want to store an image in MYSQL database.please tell me whether the below sql syntax i have written is correct or not.
    Code:
    CREATE TABLE IF NOT EXISTS `products`(
      `serial` int(11) NOT NULL auto_increment,
      `name` varchar(20) collate latin1_general_ci NOT NULL,
      `description` varchar(255) collate latin1_general_ci NOT NULL,
      `price` float NOT NULL,
      `picture` varchar(80) collate latin1_general_ci NOT NULL,
      PRIMARY KEY(`serial`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7;
    
    and i have inserted the values for the above table also.
    below is the one.....
    Code:
    INSERT INTO `products` (`serial`, `name`, `description`, `price`, `picture`) VALUES
    (1, 'View Sonic LCD', '19" View Sonic Black LCD, with 10 months warranty', 250, 'images/lcd.jpg'),
    (2, 'IBM CDROM Drive', 'IBM CDROM Drive', 80, 'images/cdrom-drive.jpg'),
    (3, 'Laptop Charger', 'Dell Laptop Charger with 6 months warranty', 50, 'images/charger.jpg'),
    (4, 'Seagate Hard Drive', '80 GB Seagate Hard Drive in 10 months warranty', 40, 'images/hard-drive.jpg'),
    (5, 'Atech Mouse', 'Black colored laser mouse. No warranty', 5, 'images/mouse.jpg'),
    (6, 'Nokia 5800', 'Nokia 5800 XpressMusic is a mobile device with 3.2" widescreen display brings photos, video clips and web content to life', 299, 'images/mobile.jpg');
    
    the "picture" field stores pictures in database.i have given there VARCHAR(80)
    i know for storing pictures we should give BLOB data type.
    but tell me whether the above syntax i have written will work or not.
    if not what would be the syntax...
     
  2. pein87

    pein87 Active Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    28
    Occupation:
    Web Dev
    Location:
    Limbo
    No varchar(80) expects a character length of only 80. If the images character length is only 80 then yes but in most cases it will not be and will not work. Instead go with BLOB if you absolutely need to store them in the database. Its a best practice to store them in the filesystem so your not using up resources when your performing the query. A query for a fixed text length is far less on memory and the processor then one for an image files. I'd suggest that you store the link to the image in file or the directory and the file name is different columns.
     
  3. mountainman

    mountainman Banned

    Joined:
    Feb 6, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    I agree with pein87.
     
  4. c_user

    c_user New Member

    Joined:
    Aug 23, 2009
    Messages:
    86
    Likes Received:
    8
    Trophy Points:
    0
    Occupation:
    Php dev
    Location:
    Bhubaneswar
    @ravi951 u can do as per ur wish .. using the varchar datatype for storing the image is not a prob.
    But as Pein87 said it should not exceed the bound.

    here is the code :

    image.php
    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Image</title>
    </head>
    
    <body>
    <FORM action="nexttpage.php" METHOD="POST" ENCTYPE="multipart/form-data">
      <P>Please browse and double-click the file to upload. Your filename must have one of the following extensions: .doc, .pdf, .ppt, .pps, .xls, .csv, .rtf, .txt, .htm, .html, .jpg, .gif, .png</p>
      <P>File name: <br><INPUT TYPE="file" name="userfile" style="width:250px;"></p>
      <P><INPUT  TYPE="submit" VALUE="Upload File"></p>
    
    <INPUT class="inputform" TYPE=hidden name="MAX_FILE_SIZE" value="513024">
    
    </FORM>
    
    <div >
    <form action="retrieve.php" method="post" enctype="multipart/form-data">
    <h4>Enter the serial no to open the image </h4><input type="text" id="txt1" name="txt1" /><input type="submit" value="View" name="btn" />
    </form>
    </div>
    
    </body>
    </html>

    nexttpage.php
    Code:
    <?php
    include_once('connection.php');
    ob_start();
    // In PHP versions earlier than 4.1.0, $HTTP_POST_FILES should be used instead
    // of $_FILES.
    
    $uploaddir = 'C:\xampp\htdocs\sir\imagess/';
    $uploadfile = $uploaddir . basename($_FILES['userfile']['name']);
    
    echo '<pre>';
    if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) 
    	{
    	
    		$sqlst= "INSERT INTO  `college`.`images` (`img`)VALUES( '$uploadfile')";
    		$chk=mysql_query($sqlst);
    		if($chk)
    			{	
    				echo "data inserted";
    				echo "File is valid, and was successfully uploaded.\n";	
    				
    				header('location:image.php');
    			}
    } else 
        echo "Possible file upload attack!\n";
    
    
    //echo 'Here is some more debugging info:';
    //print_r($_FILES);
    
    //print "</pre>";
    
    ?>
    
    Write the connection.php for ur connection
    Hope i was able to help u out .
    enjoy coding .. :)
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice