Creating databases or tables for images?

[menre]

Hi,

Could somebody help me out please? I am in a situation that I need to create two databases for different kind of images on one website. I would like to do this with PHP and MYSQL. One of the databases will hold images for full-time students, while the other one will hold images for part-time students.

I know that I can also achieve this by creating just one database with two tables. One table for each group of students.

My first problem is, I do not know how to create a database or table that holds images. The students will not be asked to upload the images. I will be doing that myself. However, the students will be writing articles and their images will be displayed alongside each article they write. If I were to create a database with a table for normal data I wouldn't have much trouble with that.

I will use PHP to display the article and the image on a web page. Is it possible to create a database or table that holds images and text?

Could someone give me a hand or point me to available resources (eg code or tutorial) somewhere to start from please?

Thanks in advance.

Menre

[kfcSmitty]

You can have a table that holds images and text if you want, and I am sure someone here would be willing to help you with that, but why not simply store the path to the image in the database?

Converting the image to a blob and back is extra processing you really don't need to do.

[techgnome]

especially in a web environment where that would have to take place for each and every view of the pages... that puts a lot of strain on the database and server. I'd just have the images in a folder, then have a field that points to the file that way.

-tg

[Nightwalker83]

You can create the databases via phpmyadmin and just fill in the details, database name, tables, etc. I think I still have php script to do what you are asking burt I would do what techgnome is suggesting above. This will greatly reduce the size of the database because all you are storing is the name, id and link to the image instead of the name, id and the image. The image would be in the images on the server.

Edit:

Here are some scripts that will create the database then store the image url in the database then load the image.

createShoppingCart:

<?php
// Database connection variables
$dbDatabase = "Your database";

//connect to db
$conn = @mysql_connect("localhost", "admin", "");
if (!$conn) {
die("Connection failed: " .mysql_error());
}

//create database
$query = "CREATE DATABASE IF NOT EXISTS yourdatabase";
if (mysql_query($query, $conn)) {
echo ("<p>Database create query successful</p>");
}else {
die ("Database query failed: " .mysql_error());
}
//select database
if (mysql_select_db($dbDatabase, $conn)) {
echo ("<p>Database selection successful</p>");
}else {
die ("Could not locate test database" .mysql_error());
}
//create tables
$query = "CREATE TABLE users
(username varchar(40) not null primary key,
password varchar(20))";
if (mysql_query($query, $conn)) {
echo ("<p>table users query successful</p>");
}else {
die ("Database query failed: " .mysql_error());
}
$query = "CREATE TABLE products
(productid varchar(20) not null primary key,
pPrice decimal (8,2), pImagePath varchar(100), pImageType varchar(100))";
if (mysql_query($query, $conn)) {
echo ("<p>Database products query successful</p>");
}else {
die ("Database query failed: " .mysql_error());
}
//insert data into tables
$insert = "insert into users (username, password) values ('','')";
if (mysql_query($insert, $conn)) {
echo ("<p>insert query successful</p>");
}else {
die ("Database query failed: " .mysql_error());
}
?>


Display the product:


<?php
// Database connection variables
$dbDatabase = "your database";
// Make sure a file has been entered
if (!($conn = mysql_connect("localhost", "admin", "") )){
//echo '&result=db+connection+failed&';
exit;
}
if (!(mysql_select_db($dbDatabase, $conn))){
// echo '&result=db+selection+failed&';
exit;
}
$pName = $_POST['productId'];
$query = "select * from products where productid='$pName'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$pPrice = $row['pPrice'];
$pImage =$row['pImagePath'];
echo "pPrice=$pPrice&pImage=$pImage";
?>


You just need to replace "Your database" with the name you want for your database.

I think I still have the code to store the image in the actual database if you want I could post it if I find it?

[menre]

Hello,

Thanks everyone for your suggestions and replies. I have been working on this issue for some days now as I attempt to master using PHP and MYSQL for some tasks as I learn it further. After reading your suggestions, some books and tutorials, I decided to put eveything aside and give it a go without refering to any resources again. I was able to come up with something that I can confidently do on my own (no looking at books) next time and the code below shows what I came up with.

<?php
//the connection details to mysql

$username = "root";
$password = "";
$hostname = "localhost";

//making the real connection to the database
$dbhandle = mysql_connect ($hostname, $username, $password)
or die ("I am unable to connect: " . mysql_error());
echo "Connected to MYSQL <br />";

?>

<?php
//select a database to work with
$selected = mysql_select_db("StudentEssay", $dbhandle)
or die("Could not select StudentEssay");
?>

<?php

//create the articles table
$sql = "CREATE TABLE IF NOT EXISTS articles(
article_id INT(4) NOT NULL AUTO_INCREMENT,
article_writer VARCHAR(20) NOT NULL,
article_title VARCHAR(50) NOT NULL,
article_intro VARCHAR(220) NOT NULL,
article_date DATE NOT NULL,
PRIMARY KEY (article_id)
)";

$results = mysql_query($sql)
or die (mysql_error());

echo "article table successfully created.";

echo "<img src='full_time_students/images/sammy.jpg' />";
?>


With the code above, when I preview my page I can see that it is working and the image for sammy is displayed. Now, I chose not to use a database for the images instead as someone suggested, I just used an images folder.

1) But how do I get the text for example, the article_intro to display on my page?

2) Is there a limit on how much text size I can put in the database table for each article?

3) Looking at my database table design, is there a better way to do it?


I think I still have the code to store the image in the actual database if you want I could post it if I find it?

Could you post the code please? I didn't know what to do when I got to that point.

…but why not simply store the path to the image in the database?
How do I do that please?

Thanks,
Menre

[Nightwalker83]

Could you post the code please? I didn't know what to do when I got to that point.


I will check whether I still have the code.


Edit:

Sorry, I was confused! The other code I have also save the images path to the database instead of the image itself.


How do I do that please?



<?php

/*check if form fields are blank. If they are, let the customer know and
provide a link to return to the original customer details form*/
if ($_POST["productname"] ==""){
echo "you need to enter the product name";
echo "<br><a href='getProductDetails.php'>Return to form</a>";

} elseif ($_POST["productdesc"] =="") {
echo "you need to enter the product description.";
echo "<br><a href='getProductDetails.php'>Return to form</a>";

}elseif($_POST["colour"] == ""){
echo "you need to enter a colour";
echo "<br><a href='getProductDetails.php'>Return to form</a>";

}elseif($_POST["price"] == ""){
echo "you need to enter a price";
echo "<br><a href='getProductDetails.php'>Return to form</a>";

}elseif($_POST["imagepath"] == ""){
echo "you need to enter a correct image path";
echo "<br><a href='getProductDetails.php'>Return to form</a>";

}else {

$conn = @mysql_connect("localhost", "user", "");
if (!$conn) {
die("Connection failed: " .mysql_error());
}

mysql_select_db("your database", $conn);

$query = "INSERT INTO products (productname, productdesc, colour, price, imagepath)
VALUES ('$_POST[productname]', '$_POST[productdesc]', '$_POST[colour]', '$_POST[price]', '$_POST[imagepath]')";

if (mysql_query($query, $conn)){
echo ("<br><b>The following product has been added to the database: </b>" .$_POST[productname]);
echo "<br><br><a href=getProductDetails.php>Add another product?</a>";
}else {
die(mysql_error());
}
}
?>

[kows]

you should just forget the idea of storing images in your database. it's a terrible idea, even if someone gives you the code to do so.

to answer your first question, displaying your text just requires a simple query. but, because I'm not sure which article you'd like to display, I'll just give you a generic way of displaying the last 5 articles instead. if you need help building a query in the first place, you could take a look at this post (http://www.vbforums.com/showpost.php?p=2645433&postcount=2) that I made quite a while ago.
<?php

//our query. we want the last 5 articles.
$sql = "SELECT * FROM articles ORDER BY article_date DESC LIMIT 5";
$query = mysql_query($sql);
while($article = mysql_fetch_assoc($query)){
//we now have an associative array stored in $article of our records.

//dump all of the data
print_r($article);

//we can refer to these values like so:
// $article['article_id'] => the article id
// $article['article_intro'] => the article introduction

echo "Article #{$article['article_id']} was posted on {$article['article_date']}!";
}

?>

to answer your second and third, your database looks fine. I don't see a place for you to store your actual article, though. it should be a text field, and text fields have no limit to the amount of data they can store. article_intro, for example, is a varchar and can hold up to 220 characters.

and lastly, you can store the image path to an image in your database by simply adding another field, and storing a value in it like any other field! if you are letting a user upload a file, then you can get started (if you haven't already) by looking at these examples on how to upload a file (http://ca.php.net/manual/en/features.file-upload.post-method.php). you can store the final path into the database (the path you choose with move_uploaded_file()). if you're not doing file uploads, you can just let a user enter in an image path and then save it like a regular field.

hope that helps.

oh, and I wouldn't suggest using any of the code that NightHawk posted. it's messy and has a lot of repetitive code, and the only feature of it is that it has an INSERT query at the end of it. you should not get into the habit of echoing HTML, as PHP is an embedded language and should be treated like one. instead, you could make a loop and do something like this instead:
<p>There were a few errors with your submission:</p>
<ul>
<?php
foreach($_POST as $key => $value):
if($value == ""):
?>
<li><?php echo $key; ?> had an empty value.</li>
<?php endif; endforeach; ?>
</ul>
if you are still really unfamiliar with PHP, though, you might not want to jump head first into that ;)

[Nightwalker83]

oh, and I wouldn't suggest using any of the code that NightHawk posted. it's messy and has a lot of repetitive code, and the only feature of it is that it has an INSERT query at the end of it. you should not get into the habit of echoing HTML, as PHP is an embedded language and should be treated like one.

I should point out that the code I posted above is code from my php class last year and we were taught to code like that. However, the code we were give this year does not use html within the echo statement but like so:


echo 'authenticated=queryFailed';


The above just being the echo then message in single quotes!

[menre]

Hello,

Thanks all. I have tried your suggestions and worked with the code you posted. I have posted below what I have made out of it so far.

<?php
//the connection details to mysql

$username = "root";
$password = "";
$hostname = "localhost";


//making the real connection to the database
$dbhandle = mysql_connect ($hostname, $username, $password)
or die ("I am unable to connect: " . mysql_error());

echo "Connected to MYSQL <br />";

?>


<?php
//select a database to work with
$selected = mysql_select_db("StudentEssay", $dbhandle)
or die("Could not select StudentEssay");

?>


<?php

//create the articles table
$sql = "CREATE TABLE IF NOT EXISTS articles(
article_id INT(4) NOT NULL AUTO_INCREMENT,
article_writer VARCHAR(20) NOT NULL,
article_title VARCHAR(100) NOT NULL,
article_intro VARCHAR(220) NOT NULL,
article_body LONGTEXT NOT NULL,
article_date DATE NOT NULL,
PRIMARY KEY (article_id)
)";

$results = mysql_query($sql)
or die (mysql_error());

echo "article table successfully created.";


//the code below is one of the two articles that I have inserted
//into the articles table through phpmyadmin

/***
INSERT INTO `studentessay`.`articles` (`article_id`, `article_writer`,

`article_title`, `article_intro`, `article_body`, `article_date`) VALUES

(NULL, 'Sammy King', 'If you were to write an essay on a web scripting

language, what would it be? PHP or JavaScript?', 'The popularity of PHP is

making it the scripting language for all. Most dynamic websites today rely

heavily on it.', 'Ever since the release of PHP in the early 1990s, the power

and uniqueness of the language has been obvious to all.

', NULL);

*******/


echo "<img src='full_time_students/images/sammy.jpg' />";
?>


<?php

//our query. we want the last 5 articles.
$sql = "SELECT * FROM articles ORDER BY article_date DESC LIMIT 5";
$query = mysql_query($sql);
while($articles = mysql_fetch_assoc($query)){
//we now have an associative array stored in $article of our records.

//dump all of the data
print_r($articles);

//we can refer to these values like so:
// $article['article_id'] => the article id
// $article['article_intro'] => the article introduction

echo "Article #{$articles['article_id']} was posted on {$articles

['article_date']}!";
}

?>


When I run the code above, I get the output below.

Array ( [article_id] => 2 [article_writer] => Eva Dickenson [article_title] => The Beauty, the Wonder and the Joy of PHP and MYSQL [article_intro] => Many web programmers have come to accept PHP and MYSQL as the ulimate tools for creating dynamic web pages. [article_body] => For some years now, web developers have been using both PHP and MYSQl to create dynamic and database-driven websites. [article_date] => 2009-09-24 ) Article #2 was posted on 2009-09-24!Array ( [article_id] => 1 [article_writer] => Sammy King [article_title] => If you were to write an essay on a web scripting language, what would it be? PHP or JavaScript? [article_intro] => The popularity of PHP is making it the scripting language for all. Most dynamic websites today rely heavily on it. [article_body] => Ever since the release of PHP in the early 1990s, the power and uniqueness of the language has been obvious to all. [article_date] => 2009-09-23 ) Article #1 was posted on 2009-09-23!

But I have one more question please. Why am I getting these pointers such as: [article_intro] =>?

if you need help building a query in the first place, you could take a look at this post that I made quite a while ago.

I have read and saved that post somewhere for future reference. It was really useful and I am going to read it all again.

don't see a place for you to store your actual article, though. it should be a text field, and text fields have no limit to the amount of data they can store.

I have now added a field for the full article (article_body and I set it to LONGTEXT) and you can see it from the code. I hope I have not done it wrongly.

Thanks.

[techgnome]

$articles is an array.... so when you echo $articles .... it did exactly what you told it to do... it echoed the contents of $articles. Part of that also means it printed out the field names for each element - that's what "[article_intro] =>" is telling you. Oddly, that's commented right in your code...

-tg

[kows]

techgnome, he copied my code verbatim. guess he didn't read the comments ;)

print_r() dumps the contents of an array. it prints the keys (fields in the case of a database table) and their values, like stated in techgnome's post. make sure you read the comments I've made in the post; they explain what's going on pretty well, I'd think. but if you don't get it, ask questions.