Jump to content

Help with PHP/MySQL Please!

Saraht

By Saraht
in PHP Coding Help

 Share

Recommended Posts

Saraht Newbie

Saraht

Posted
Posted

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

 

 

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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.

Link to comment
Share on other sites
HuggieBear Member

HuggieBear

Posted
Posted

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

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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!

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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!

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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!

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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)

Link to comment
Share on other sites
Saraht Newbie

Saraht

Posted
  • Author
Posted

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

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

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?

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.