[RESOLVED] Series of simple newbie questions about mysql...

[middleman666]

your probly not going to like me asking all these questions, but im hoping someone will be able to give me some simple bits of code to get me started.

basically, ive created a guestbook using a flat file system and am looking to now create it using a mysql database. my flat file guestbook had general text fields, file upload, thumbnail upload and a 'add comments' script in every entry.

I have a very basic mysql guestbook at the moment, simple name and message.
the fields it has are: id guest message date

so here some the questions:

Using the ID, how can I make it so the latest entry displays first?

Using ID, how can I add a link to delete an entire entry?

With file upload - what should I be writing to the database to use as a link to an uploaded file? i.e. should it simple be 'files/File[name]' or should I write the whole link tag?

I think thats it for now...
Thankyou for anyone that helps.

[leatherback]

Hi,

How much do you know? THere is some advice on how to start, get going and formulate basic queries in the FAQ thread of this forum. Start there, and give it a try.

The file upload qeustion: THat is a personal choice. A full link is more secure, since it will give the full path to the fiu. SO when you start using rewritten URL's you do not need to worry about your images missing. It is however very sensitive to you moving a section of your website. If everything is relative, you have less problems in those cases.

J.

[middleman666]

The Code I have done at the moment is this:

<?          

if ($action=="Upload") 
mysql_query("INSERT INTO UniLoad SET 
				filetype='$type', 
				userfile='$userfile', 
				Thumbnail='$Thumbnail', 
				rating='$rating', 
				title='$title', 
				description='$description', 
				user='$user', 
				email='$email', 
				date=date('jS F, H:i')
			"); 

$result=mysql_query("SELECT * FROM UniLoad"); 
while($row=mysql_fetch_array($result)) 
print 	$row['filetype'].'<br>'.
		$row['userfile'].'<br>'.
		$row['Thumbnail'].'<br>'.
		$row['rating'].'<br>'.
		$row['title'].'<br>'.
		$row['description'].'<br>'.
		'<a href="mailto:'.$row['email'].'">'.$row['user'].'</a><br>'.
		$row['date'].'<br><br>'
		; 

// USERFILE VARIABLES ------
	$UserFilePath = "files/"; 
	$UserFileMax_size = 20000000; //20MB
	$UserFileDomain = $_SERVER["HTTP_HOST"];
    $FILE_EXTS  = array('.zip','.gif','.jpg','.jpeg','.png','.mov','.avi','.mpg','.mpeg','.wmv','.wma','.mp3','.wav'); 

// THUMBNAIL VARIABLE ------
$ThumbnailPath = "Thumbnails/"; 
$ThumbnailMax_size = 1000000; //1000KB
$ThumbnailDomain = $_SERVER["HTTP_HOST"];
$THUMBNAIL_EXTS  = array('.gif','.jpg','.jpeg','.png'); 

// Display Form Start -----
echo '<form action="' . "$PHP_SELF" . '" method="post" enctype="multipart/form-data" name="upload">
	<b>: Contents of File :</b><br>
	<select name="type" id="type" width="40">
	  <option value="non">Select Type</option>
	  <option value="photo">Photo/s</option>
	  <option value="video">Video</option>
	  <option value="other">Other</option>
	</select>
	<br>
	<b>: Location of File :</b><br>
	<input name="userfile" type="file" id="userfile" size="37">
	<br>
	<b>: Thumbnail Image : </b><br>
	<input name="Thumbnail" type="file" id="Thumbnail" size="37">
	<br>
	<b>: Rating of File :</b><br>
	&nbsp;<img src="starDull.jpg" width="20" height="20"> 
	<img src="starBright.jpg" width="20" height="20"> 
	<img src="starBright.jpg" width="20" height="20"> 
	<img src="starBright.jpg" width="20" height="20"> 
	<img src="starBright.jpg" width="20" height="20"> 
	<img src="starBright.jpg" width="20" height="20"><br>
	<label><input name="rating" type="radio" value="non" checked></label>
	<label><input type="radio" name="rating" value="one"></label>
	<label><input type="radio" name="rating" value="two"></label>
	<label><input type="radio" name="rating" value="three"></label>
	<label><input type="radio" name="rating" value="four"></label>
	<label><input type="radio" name="rating" value="five"></label>
	<br>
	<br>
	<b>: Title of Uploaded File :</b><br>
	<input name="title" type="text" id="title" size="50" maxlength="40">
	<br>
	<b>: Description of Uploaded File :</b><br>
	<textarea name="description" cols="50" rows="5" id="description"></textarea>
	<br>
	<b>: Your Name :</b><br>
	<input name="user" type="text" id="user" size="50" maxlength="40">
	<br>
	<b>: Your E-mail Address :</b><br>
	<input name="email" type="text" id="email" size="50" maxlength="40">
	<br>
	<input type="submit" name="action" id="action" value="Upload"> 
	<input name="clear" type="reset" id="clear" value="Clear">
	<br>
	</form>';
// Display Form End ------------------------------------------------

// Combined Validation start ---------------------------------------
// IF file and thumbnail is_uploaded_file
if (is_uploaded_file($HTTP_POST_FILES['userfile']['tmp_name']) && is_uploaded_file($HTTP_POST_FILES['Thumbnail']['tmp_name'])) {

		// Check IF file or thumbnail size is too big
		if ($HTTP_POST_FILES['userfile']['size']>$UserFileMax_size || $HTTP_POST_FILES['Thumbnail']['size']>$ThumbnailMax_size) {
			$message = "- Either your File is too big or your Thumbnail File is too big -"; 
			print "<script>window.location.href='$url_this?message=$message'</script>";
			exit; 
			}
		else {}

		// Set Variables to check file extension
		$file_name = $_FILES['userfile']['name'];
		$file_ext = strtolower(substr($file_name,strrpos($file_name,".")));
		$thumbnail_name = $_FILES['Thumbnail']['name'];
		$thumbnail_ext = strtolower(substr($thumbnail_name,strrpos($thumbnail_name,".")));

		// Check IF file and thumbnail has extension
		if (in_array($file_ext, $FILE_EXTS) && in_array($thumbnail_ext, $THUMBNAIL_EXTS)) {

				// Check IF file or thumbnail already exsists
				if (file_exists($UserfilePath . $HTTP_POST_FILES['userfile']['name']) || file_exists($ThumbnailPath . $HTTP_POST_FILES['Thumbnail']['name'])) {
					$message = "- Please rename either your File or Thumbnail file as they already exist -";
					print "<script>window.location.href='$url_this?message=$message'</script>";
					exit;
					}
				else {}

					// Change UserFileSize From Bytes to MB
					$UserFileSize = 0.00;
					$UserFileSize = number_format($HTTP_POST_FILES['userfile']['size']/1000000, 2);

					// Replace Spaces in UserFileName With Underscores
					$ReplaceThese = array(' ');
					$UserFileName = $HTTP_POST_FILES['userfile']['name'];
					$UserFileName = str_replace($ReplaceThese, '_', $UserFileName);

					// Upload Files
					$UploadFiles = copy($HTTP_POST_FILES['userfile']['tmp_name'], $UserFilePath .$UserFileName) &&
										copy($HTTP_POST_FILES['Thumbnail']['tmp_name'], $ThumbnailPath .$HTTP_POST_FILES['Thumbnail']['name']);

				// IF file and thumbnail didnt upload display message
				if (!$UploadFiles) { 
					$message = "- Upload didn't work, please try again -"; 
					print "<script>window.location.href='$url_this?message=$message'</script>";
					exit; 
					} 
				// IF file and thumbnail did upload display message
				else { 
					$message = "- Upload Succesful -";
					print "<script>window.location.href='$url_this?message=$message'</script>";
					}

				// Create Download link for file
				$UniLoadDownloadLink = '<a href=\"' . $UserFilePath.$UserFileName . '\">Download</a>';

				// Create IMG tag for file
				if ( $thumbnail_ext == '.jpg' ) {
				$Thumbnail = '<img alt=' . 'File:&nbsp;' . $UserFileName . '&nbsp;&nbsp;Size:&nbsp;' 
								. $UserFileSize . '&nbsp;MB' .  '
								src=\"'.$ThumbnailPath.$HTTP_POST_FILES['Thumbnail']['name'].'\">';
				}
				else {
				$Thumbnail = '<img width=120 alt=' . 'File:&nbsp;' . $UserFileName . '&nbsp;&nbsp;Size:&nbsp;' 
								. $HTTP_POST_FILES['userfile']['size'] . '&nbsp;MB' .  '
								src=\"'.$ThumbnailPath.$HTTP_POST_FILES['Thumbnail']['name'].'\">';
				}

				}
		// IF file or thumbnail is wrong type then display message
		else { 
				$message = "- Sorry, either your File or Thumbnail File is not the correct file type -";  
				print "<script>window.location.href='$url_this?message=$message'</script>";
				exit;
				}

		}
else {}
// Combined Validation end -----------------------------------------

// IF function to decide which image goes with which file type choosen
if ($_POST["type"] == "non") { 
	$type = '<img src="Type-non.jpg" width="50" height="50">';
}
if ($_POST["type"] == "photo") { 
	$type = '<img src="Type-photo.jpg" width="50" height="50">';
}
if ($_POST["type"] == "video") { 
	$type = '<img src="Type-video.jpg" width="50" height="50">';
}
if ($_POST["type"] == "other") { 
	$type = '<img src="Type-other.jpg" width="50" height="50">';
}
else {}

// IF rating is equal to the following display images acordingly
if ($_POST["rating"] == "non") { 
	$rating = '<img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20">';
}
if ($_POST["rating"] == "one") { 
	$rating = '<img src="starBright.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20">';
}
if ($_POST["rating"] == "two") { 
	$rating = '<img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20">';
}
if ($_POST["rating"] == "three") { 
	$rating = '<img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20">';
}
if ($_POST["rating"] == "four") { 
	$rating = '<img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starDull.jpg" width="20" height="20">';
}
if ($_POST["rating"] == "five") { 
	$rating = '<img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20"><img src="starBright.jpg" width="20" height="20">';
}

// Define Post Variables
$title = $_POST['title'];
$description = $_POST['description'];
$user = $_POST['user'];
$email = $_POST['email'];

// Dont Execute HTML or PHP
$title  = htmlspecialchars($title);
$user  = htmlspecialchars($user);
$description  = htmlspecialchars($description);
$email  = htmlspecialchars($email);

// Get Rid of Slashes (\)
$title  = stripslashes($title);
$user  = stripslashes($user);
$description  = stripslashes($description);
$email  = stripslashes($email);
?>

continued in nxt post...

[middleman666]

my database has this:

CREATE TABLE UniLoad (
id int(10) unsigned NOT NULL auto_increment,
user varchar(100) NOT NULL default '',
email varchar(100) NOT NULL default '',
title varchar(100) NOT NULL default '',
description varchar(250) NOT NULL default '',
rating varchar(100) NOT NULL default '',
Thumbnail varchar(100) NOT NULL default '',
userfile varchar(100) NOT NULL default '',
filetype varchar(100) NOT NULL default '',
date datetime default NULL,
PRIMARY KEY (id)
) ;

The output I get is:

video
/tmp/phpMroAsq
/tmp/phpZggraG
three
Title sdfsdf
Des sfgsdfs
name fsdfs

Am I right in thinking that the data types in my Create Table are wrong if I want to display images instead of text? what should I be using for?:

rating varchar(100) NOT NULL default '',
Thumbnail varchar(100) NOT NULL default '',
userfile varchar(100) NOT NULL default '',
filetype varchar(100) NOT NULL default '',

Thanks.

[middleman666]

Actually... Forget the above lol. I know its all completely in the wrong order.

Anyway... Ive got it to do what I want now I think.

Ill post later with more questions if i get stuck.

thanks for your help.