Help with PHP/MySQL Please!

[Saraht]

Hi,

 

I have a project which involves an image gallery with a user comments system (a logged into user can post comments underneath an image similar to deviantart.com).

 

I have all the php code working (image upload, user comments, user registration etc) but the problem is that the mysql tables in the database are not linked, therefore the user comments cannot be posted to a particular image (obviously lol  )

 

So as MySql is not my strong point I would REALLY appreciate if someone could help me out with the statement to create the tables.

 

This is what I want:

 

table username:

 

username

password

email

USERID

 

 

table image:

 

imageTitle

imageURL

USERID

COMMENTID

 

 

table comment:

 

commentBODY

COMMENTID

 

 

So, when a user is logged in and uploads an image this is what happens:

 

1. add image detail in the image table and add the userid in the image table too

so when I call for that user you could select * from table image where userID == the user loggedin

 

2. same with comment when a user adds a comment you make a new commentID and add it to the comment table plus the userID of the logged in user

 

Any help would be gratefully appreciated! 

 

 

[Guest]

Lets settle the structure before writing the SQL. I'm going to suggest:

 

(users)

id

username

password

email

 

(images)

id

imageTitle

imageURL

 

(comments)

id

user_id        REFERENCES users.id

image_id      REFERENCES images.id

commentBODY

 

We normalize it pretty well (not completely, but enough), this way, Images and Users don't need to know about each other, and hte PHP connects comments to Images and Users.

 

If you have no objections, I can move on and write theSQL statments for them.

[HuggieBear]

Change the image table and comment table setup slightly so it's like this:

 

Table Image:

ImageID <- unique

ImageTitle

ImageURL

UserID <- unique id from the user table

 

Table Comment

CommentID <- unique

ImageID <- unique id from the image table

CommentBody

 

Regards

Huggie

 

EDIT: Beaten to it

[Saraht]

Hi 8Ball

 

I have no objections. Thanks

[Guest]

Heya, took me awhile to get back, sorry! But here you go!

 

Also, I shortened the names in the Images table, that's just personal preference, you may change it back if you like.

 

CREATE TABLE Users
(
    id        INT    AUTO_INCREMENT,
    username    VARCHAR(40)    NOT NULL,
    password    VARCHAR(40)    NOT NULL,
    email        VARCHAR(60)    NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(username)
)

CREATE TABLE Images
(
    id    INT        AUTO_INCREMENT,
    title    VARCHAR(60)    NOT NULL,
    url    VARCHAR(80)    NOT NULL,
    PRIMARY KEY(id)
)

CREATE TABLE Comments
(
    id        INT    AUTO_INCREMENT,
    user_id        INT    NOT NULL,
    image_id    INT    NOT NULL,
    comment    TEXT   NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES Users(id)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    FOREIGN KEY(image_id) REFERENCES Images(id)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
)

 

EDIT:

I forgot the comment field in the Comments table! Fixed!

[Saraht]

Ok I'm going to have to change the table names slightly as I have a users and comments table already (just incase this doesnt work for me!)

 

I will be back with how it went! Thanks x

[Guest]

Now here's a statement you could use to get the comments:

 

SELECT c.comment 
FROM Comments c 
INNER JOIN Images i 
       ON c.image_id=i.id
INNER JOIN Users u
       ON c.user_id=u.id
WHERE i.id="{selected id}"
-- OR
WHERE i.title="{selected title}"

 

 

If you are using TITLE in the above WHERE clause, you can speed up your database a little (only if you have ALOT of records--this might be overkill for small recordsets, but doesn't hurt to mention it), you just run this once:

 

CREATE INDEX cIndex ON Images (title);

 

 

Otherwise! It should all work anyway!

[Saraht]

that post made very little sense to me, what is it about?

[Guest]

Oh sorry, you can ignore the CREATE INDEX part. Chances are you won't need it at all.

 

However, the first SQL statement:

 

SELECT c.comment 
FROM Comments c 
INNER JOIN Images i 
       ON c.image_id=i.id
INNER JOIN Users u
       ON c.user_id=u.id
WHERE i.id="{$id}"
-- OR
WHERE i.title="{$title}"

 

You'll need to use that to get your comments into PHP from the database. And depending on whether you'll use the ID or TITLE, you'll have to use either:

 

  WHERE i.id="{$id}"

 

OR

 

  WHERE i.title="{$title}"

 

The rest of that post was just me going off on a tangent, hehe

[Saraht]

haha

 

Ok, so First I should create those tables as stated above and where does the c.comment statement come in?

 

Sorry if I sound stupid.

[Guest]

haha

 

Ok, so First I should create those tables as stated above and where does the c.comment statement come in?

 

Sorry if I sound stupid.

 

No at all. Ok, I'm going to assume you know some PHP (since you're making a site in PHP, after all).

 

In the PHP code, there should be a spot where it selects the comments from the database (to put it onto the page).

 

And right now, that code probably uses a SQL statement like:

 

SELECT * FROM comments WHERE IMAGESID = "$id"

 

But with this new table structure, this won't work.

 

<lecture required_learning="false" nice_to_know="questionable">

In fact, the way I've made the table, I've "normalized" the tables. What this means is that I've create two stand-alone tables, who know nothing about each other, and one table that knows about the other two.

 

To many, this is good Database design. However, what it means is it becomes PHP's responsibility to make them work together.

</lecture>

 

So, to make it work, the PHP should use this SQL statement instead:

 

SELECT c.comment 
FROM Comments c 
INNER JOIN Images i 
       ON c.image_id=i.id
INNER JOIN Users u
       ON c.user_id=u.id
WHERE i.id="{$id}"

 

Hopefully I'm making sense!

[Saraht]

ok.. so far so good, ur SQL had no problems!

[Guest]

Awesome! Well glad to help! Feel free to message me if you come across problems or need more help!

 

Take care!

[Saraht]

Ok, I have an image upload, and at the moment that is going to a new table. Images will need to be uploaded to the images table as defined in your sql statement?

[Guest]

Yea, it should be put into the Images table, with an INSERT query that should be pretty simple:

 

INSERT INTO Images(title, url) VALUES($title, $url);

 

In fact, it's only the Comments table that should be complicated to deal with.

 

When you add a comment, the insert comment will need you to get the ID of the current image, as well as the ID of the logged in user. How you do that depends on how the site was developed.

 

And then use an insert statement that should be pretty simple:

 

INSERT INTO Comments(user_id, image_id, comment) VALUES($user_id, $image_id, $comment)

[Saraht]

This is the current page where images are uploaded:

 

<?php

ob_start();

//include the db connection

require("config.php");

//show the upload file form

echo "<br><br>";

echo "<form enctype=multipart/form-data method=post action=index.php?action=upload><input name=userfile type=file> <input type=submit value=Upload></form>";

echo "<br>";

if($_GET['action'] == 'upload')

{

//get the number value from the DB

$result = mysql_query("SELECT * FROM Imagesnew")

or die(mysql_error());

$row = mysql_fetch_array( $result );

$number = $row['number'];

$number2 = $number + 1;

$uploaddir = 'pictures/';

$trim = str_replace(" ", "",basename($_FILES['userfile']['name']));

$uploadfile3 = strtolower($trim);

$uploadfile2 = $number2 . $uploadfile3;

$uploadfile = $uploaddir . $uploadfile2;

$name = $uploadfile2;

if(substr($name, -5, 5) == '.jpeg' || substr($name, -4, 4) == '.gif' || substr($name, -4, 4) == '.jpg' || substr($name, -4, 4) == '.png' || substr($name, -4, 4) == '.bmp')

{

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile))

{

  //update the number value

  $result = mysql_query("UPDATE Imagesnew SET number='{$number2}'")

  or die(mysql_error());

 

  mysql_query("INSERT INTO Imagesnew

  (name) VALUES('{$name}') ")

  or die(mysql_error());

 

  //write "uploaded successfully"

  echo func("uploaded");

}

else

{

  echo func("error");

}

}

else

{

echo "<b>Not a valid image file</b>";

}

}

function func($action)

{

if($action == 'uploaded')

{

echo "<b>Uploaded successfully</b>";

}

if($action == 'error')

{

echo "<b>The file can not be uploaded</b>";

}

}

//show all the uploaded pictures...

echo "<br>";

$result = mysql_query("SELECT * FROM Imagesnew") or die(mysql_error());

// keeps getting the next row until there are no more to get

$number4 = 15;

$i = 0;

while($row = mysql_fetch_array( $result ))

{

if($number4 == $i)

{

echo "<br>";

$i = 0;

}

echo "<a href=show.php?id=" . $row['name'] . " target=_blank><img src=pictures/" . $row['name'] . " border=0 width=50 height=50></a>";

$i++;

}

?>

 

...I really doubt that I need all of this ?!

[Guest]

No you should be fine (I went over it and cleaned up your code a little).

 

<?php

ob_start();
//include the db connection
require("config.php");
//show the upload file form
echo "\r\n\t\r\n\t<form enctype=multipart/form-data method=post action=index.php?action=upload><input name=userfile type=file> <input type=submit value=Upload></form>\r\n";
if($_GET['action'] == 'upload')
{
	//get the number value from the DB
	$result = mysql_query("SELECT * FROM Imagesnew") or die(mysql_error());
	$row 	= mysql_fetch_array( $result );

	$number 	= $row['number'];
	$number2 	= $number + 1;
	$uploaddir 	= 'pictures/';

	$trim = str_replace(" ", "",basename($_FILES['userfile']['name']));
	$uploadfile3 	= strtolower($trim);
	$uploadfile2 	= $number2 . $uploadfile3;
	$uploadfile 	= $uploaddir . $uploadfile2;
	$name = $uploadfile2;

	$ext = substr($name, strrpos($name, '.')+1);

	if( in_array($ext, array('jpeg', 'gif', 'jpg', 'png', 'bmp')) )
	{
		if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile))
		{
			//update the number value
			$result = mysql_query("UPDATE Imagesnew SET number='{$number2}'")
			or die(mysql_error());

			mysql_query("INSERT INTO Imagesnew(name) VALUES('{$name}')")
			or die(mysql_error());

			//write "uploaded successfully"
			echo func("uploaded");
		}
		else echo func("error");
	}
	else echo "Not a valid image file";
}

//show all the uploaded pictures...
echo "\r\n\t";
$result = mysql_query("SELECT * FROM Imagesnew") or die(mysql_error());
// keeps getting the next row until there are no more to get
$number4 = 15;
$i = 0;
while($row = mysql_fetch_array( $result ))
{
	if($number4 == $i)
	{
		echo "\r\n\t";
		$i = 0;
	}
	echo "<a href=show.php?id=" . $row['name'] . " target=_blank><img src=pictures/" . $row['name'] . " border=0 width=50 height=50>[/url]";
	$i++;
}

// Functions ///////////
function func($action)
{
	switch($action)
	{
		case 'uploaded':
			echo "Uploaded successfully";
			break;
		case 'error':
			echo "The file can not be uploaded";
			break;
	}
}

?>

 

But the real trouble comes when you start dealing with the comments, what about the script for listing or inserting comments?

[Saraht]

i'm just trying your version...

[Saraht]

the server is taking forever to upload an image!

[Saraht]

It says "Unknown column 'number' in 'field list' "

 

i assume that should be changed to id

[Saraht]

ok, so I know this should be simple, but it's not to me! lol

 

So.. I corrected those errors but now it says "Duplicate entry '4' for key 1"

 

Im confused.

[Saraht]

OK!

 

I'm probably talking to myself here but anyway.

 

I have sorted out the image upload, although I am having issues with the comments.

 

Any help would be appreciated!

[Guest]

Hey there! Sorry about that! I've been out for a while!

 

One sec while I sort my thoughts and read over everything.

[Saraht]

do you use msn or anything? Not that I want to harass you! I just think it will be easier to communicate

[Guest]

Sure! I'll private message you my msn email ad!