Hey guys I was hoping someone could point me in the right direction for this join. I would like to have two tables one called “filestore” that stores uploaded files(from Kevin’s book) and one that gets the name and address of the person who uploaded the file called “address”. I added a filestoreid column to the address table but when the database stores the information of the person the filestore.id column shows “0” instead of the id of the filestore row.
This is what I have so far. It stores the info but returns a database error!!??
I need to be able to mail out a copy(printed) of the image on the uploaded file so I need the correlation between address and image to be correct. Thanks Bob
Cups here is the code I am working with so far. It seems to be placing 2 blank entries in the “filestore” table and one entry with the file, description, mimetype but not the firstname, lastname etc or the value of the radio buttons. Thanks so much for helping me through my code induced haze!
<?php
$dbcnx = @mysql_connect('localhost', 'fileupload', 'print01');
if (!$dbcnx) {
exit('<p>Unable to connect to the' . 'database server at this time.</p>');
}
if (!@mysql_select_db('lsufileupload')) {
exit('<p>Unable to locate the ' . 'database at this time.</p>');
}
if (isset($_GET['action'])) {
$action = $_GET['action'];
}else{
$action = '';
}
if (($action == 'view' or $action == 'dnld') and
isset($_GET['id'])) {
$id = $_GET['id'];
//user is retrieving file
$sql = "SELECT filename, mimetype, filedata
FROM filestore WHERE id = $id";
$result = @mysql_query($sql);
if (!$result) {
exit('Database error: ' . mysql_error());
}
$file = mysql_fetch_array($result);
if (!$file) {
exit ('File with given ID not found in database!' . $sql);
}
$filename = $file['filename'];
$mimetype = $file['mimetype'];
$filedata = $file['filedata'];
$disposition = 'inline';
if ($action == 'dnld') {
$disposition = 'attachment';
if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5') or
strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 7')) {
$mimetype = 'application/x-download';
}
}
header("content-disposition: $disposition; filename=$filename");
header("content-type: $mimetype");
header('content-length: ' . strlen($filedata));
echo $filedata;
exit();
} elseif ($action == 'del' and isset($_GET['id'])) {
$id = $_GET['id'];
//user is deleting a file
$sql = "DELETE FROM filestore WHERE id = '$id'";
$ok = @mysql_query($sql);
if (!$ok) {
exit('Database error: ' . mysql_error());
}
header('location: ' . $_SERVER['PHP_SELF']);
exit();
} elseif (isset($_FILES['upload'])) {
//bail out if the file isn't really an upload
if (!is_uploaded_file($_FILES['upload']['tmp_name'])) {
exit('There was no file uploaded!');
}
$uploadfile = $_FILES['upload']['tmp_name'];
$uploadname = $_FILES['upload']['name'];
$uploadtype = $_FILES['upload']['type'];
$uploaddesc = $_POST['desc'];
//$uploadfirstname = $_POST['firstname'];
//open file for binary reading ('rb')
$tempfile = fopen($uploadfile, 'rb');
//read the entire file into memory using PHP's
//filesize function to get the file size
$filedata = fread($tempfile, filesize($uploadfile));
//prepare for database insert by adding backslashes
//before special characters.
$filedata = addslashes($filedata);
//copy file into upload directory.
$tempfile = copy($tmp_name, $filename);
//create the sql query
$sql = "INSERT INTO filestore SET
filename = '$uploadname',
mimetype = '$uploadtype',
description = '$uploaddesc',
filedata = '$filedata',
firstname = '$firstname'";
//perform the insert
$ok = @mysql_query($sql);
if (!$ok) {
exit('Database error storing file: ' . mysql_error());
}
header('location: ' . $_SERVER['PHP_SELF']);
exit();
}
//default page view: list stored files
$sql = 'SELECT id, filename, mimetype, description
FROM filestore';
$filelist = @mysql_query($sql);
if (!$filelist) {
exit('Database error: ' . mysql_error());
}
$selected_radio = (isset($_POST['stock']));
$wild_status = 'unchecked';
$cat_status = 'unchecked';
$dog_status = 'unchecked';
$horse_status = 'unchecked';
$mike_status = 'unchecked';
$bird_status = 'unchecked';
//if (isset($_POST['address'])) {
//$selected_radio = $_POST['stock'];
if ($selected_radio == 'wild') {
$wild_status = 'checked';
}
else if($selected_radio == 'cat') {
$cat_status = 'checked';
}
else if($selected_radio == 'dog') {
$dog_status = 'checked';
}
else if($selected_radio == 'horse') {
$horse_status = 'checked';
}
else if($selected_radio == 'mike') {
$mike_status = 'checked';
}
else if($selected_radio == 'bird') {
$bird_status = 'checked';
}
$sql = "INSERT INTO filestore SET
selected_radio = '$selected_radio'";
$stock = mysql_query($sql);
if(isset($_POST['address'])) {
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$street = $_POST['street'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$sql = "INSERT INTO filestore SET
firstname='$firstname',
lastname='$lastname',
street='$street',
city='$city',
state='$state',
zip='$zip'";
}
$address = mysql_query($sql);
//if (!$sql) {
//exit('Database error storing file: ' . mysql_error());
//}
//header('location: ' . $_SERVER['PHP_SELF']);
//exit();
//$sql = 'SELECT * FROM address, filestore WHERE address.fileid=filestore.id';
//$address = mysql_query( $sql );
?>
<body>
<table>
<thead>
<tr>
<th>File name</th>
<th>Type</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<?php
if (mysql_num_rows($filelist) > 0) {
while ($f = mysql_fetch_array($filelist)) {
?>
<tr valign="top">
<td>
<a href="<?php echo $_SERVER['PHP_SELF'];
?>?action=view&id=<?php echo $f['id']; ?>">
<?php echo $f['filename']; ?></a>
</td>
<td><?php echo $f['mimetype']; ?></td>
<td><?php echo $f['description']; ?></td>
<td>
[<a href="<?php echo $_SERVER['PHP_SELF'];
?>?action=dnld&id=<?php echo $f['id']; ?>"
>Download</a> |
<a href="<?php echo $_SERVER['PHP_SELF'];
?>?action=del&id=<?php echo $f['id']; ?>"
onclick="return confirm('Delete this file?');"
>Delete</a>]
</td>
</tr>
<?php
}
} else {
?>
<tr><td colspan="3">No Files!</td></tr>
<?php
}
?>
</tbody>
</table>
</body>
</html>
$address = mysql_query(
'SELECT * FROM address, filestore WHERE address.fileid=filestore.id');
One trick you could pick up when starting out, is to separate you SQL from your PHP.
$sql = 'SELECT * FROM address, filestore WHERE address.fileid=filestore.id' ;
$address = mysql_query( $sql );
// now you can optionally print out your sql
echo $sql ;
Doing the above instead offers you the chance of investigating whether the SQL statement actually returns any results.
Echo the sql out, copy it, paste it into PhpMyAdmin or whatever you use and make sure your sql statement is a) valid b) returns the result you expect from your test data
Where do the WHERE clause parameters come from? You said your table name was filestore, but that table doesn’t seem to be referenced in the WHERE clause. I would expect to see something like this:
$address = mysql_query(
'SELECT * FROM address, filestore WHERE address.fileid=filestore.id');
Also, this should probably have been posted in the MySQL forum.
Echo out your $sql and check what PHP is telling Mysql to do.
Separate blocks of code into their own mini-tests pages and check they work on their own, remove all the other conditional checks, get a simple version working, then add the code back in - checking all the time that it is still working.
ps you could also remove the error suppression @ chars from in front of mysql_* commands.
Thanks Cups for the feedback. The stock photo really isn’t an actual image it is just a placeholder for a photo we will insert when we print the cards. The image displayed in the html file is just for the participant to view as a representative sample so the radio button doesn’t need to contain an image just a value so I know which version of the card they want.
I will need an “if then” statement to determine whether or not they want to send a custom photo because right now the script exits if an image is not downloaded. I think I can probably get that to work myself but the jury is still out on the rest. Fortunately I have so time to get this to work (good thing too!).
Cups you are a genius.
That was the problem, no $. Now I can view the file and delete the file but when I try to download the file I get this error “Fatal error: Call to undefined function strops() in C:\wamp\www\filestoredwnld.php on line 37” This is the code it is referring to <code>if ($action == ‘dnld’) {
$disposition = ‘attachment’;
if (strpos($_SERVER[‘HTTP_USER_AGENT’], ‘MSIE 5’) or
strops($_SERVER[‘HTTP_USER_AGENT’], ‘Opera 7’)) {
$mimetype = ‘application/x-download’;
}</code>
Line 37 in particular is “strops($_SERVER[‘HTTP_USER_AGENT’], ‘Opera 7’)) {”
I think I may not be using “copy” correctly to copy the file into an upload directory. If you could help me along those lines as well that would be great!
Thanks so much for the help. I am a serious newbie and I am starting to think I am kinda thick too. I still am sure the update is not being done on the address table. I have two scripts running currently that both work as far as inserting data into the two tables “address” and “filestore”. The script list the file and I can delete it but when I try to download it is says no file with that id exist. Weird huh? Also when I check the “filestoreid” row in the “address” table it shows “0”. Here is the code for the two scripts if you can see where I am off base I would really appreciate the help. Thanks again ya’ll are the best.
<?php
$dbcnx = @mysql_connect('localhost', 'fileupload', 'print01');
if (!$dbcnx) {
exit('<p>Unable to connect to the' . 'database server at this time.</p>');
}
if (!@mysql_select_db('lsufileupload')) {
exit('<p>Unable to locate the ' . 'database at this time.</p>');
}
if(isset($_POST['address'])) {
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$street = $_POST['street'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$sql = "INSERT INTO address SET
firstname='$firstname',
lastname='$lastname',
street='$street',
city='$city',
state='$state',
zip='$zip'";
}
$address = mysql_query($sql);
if (!$sql) {
exit('Database error storing file: ' . mysql_error());
}
header('location: ' . $_SERVER['PHP_SELF']);
exit();
$sql = 'SELECT * FROM address, filestore WHERE address.filestoreid=filestore.id';
$address = mysql_query( $sql );
echo $sql;
echo "Firstname: $firstname<br>
Lastname: $lastname<br>
Address: $street<br>
City: $city<br>
State: $state<br>
Zipcode: $zip<br>";
?>
</body>
</html>
<?php
$dbcnx = @mysql_connect('localhost', 'fileupload', 'print01');
if (!$dbcnx) {
exit('<p>Unable to connect to the' . 'database server at this time.</p>');
}
if (!@mysql_select_db('lsufileupload')) {
exit('<p>Unable to locate the ' . 'database at this time.</p>');
}
if (isset($_GET['action'])) {
$action = $_GET['action'];
}else{
$action = '';
}
if (($action == 'view' or $action == 'dnld') and
isset($_GET['id'])) {
$id = $_GET['id'];
//user is retrieving file
$sql = "SELECT filename, mimetype, filedata
FROM filestore WHERE id = 'id'";
$result = @mysql_query($sql);
if (!$result) {
exit('Database error: ' . mysql_error());
}
$file = mysql_fetch_array($result);
if (!$file) {
exit ('File with given ID not found in database!');
}
$filename = $file['filename'];
$mimetype = $file['mimetype'];
$filedata = $file['filedata'];
$disposition = 'inline';
if ($action == 'dnld') {
$disposition = 'attachment';
if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5') or
strops($_SERVER['HTTP_USER_AGENT'], 'Opera 7')) {
$mimetype = 'application/x-download';
}
}
header("content-disposition: $disposition; filename=$filename");
header("content-type: $mimetype");
header('content-length: ' . strlen($filedata));
echo $filedata;
exit();
} elseif ($action == 'del' and isset($_GET['id'])) {
$id = $_GET['id'];
//user is deleting a file
$sql = "DELETE FROM filestore WHERE id = '$id'";
$ok = @mysql_query($sql);
if (!$ok) {
exit('Database error: ' . mysql_error());
}
header('location: ' . $_SERVER['PHP_SELF']);
exit();
} elseif (isset($_FILES['upload'])) {
//bail out if the file isn't really an upload
if (!is_uploaded_file($_FILES['upload']['tmp_name'])) {
exit('There was no file uploaded!');
}
$uploadfile = $_FILES['upload']['tmp_name'];
$uploadname = $_FILES['upload']['name'];
$uploadtype = $_FILES['upload']['type'];
$uploaddesc = $_POST['desc'];
//open file for binary reading ('rb')
$tempfile = fopen($uploadfile, 'rb');
//read the entire file into memory using PHP's
//filesize function to get the file size
$filedata = fread($tempfile, filesize($uploadfile));
//prepare for database insert by adding backslashes
//before special characters.
$filedata = addslashes($filedata);
//create the sql query
$sql = "INSERT INTO filestore SET
filename = '$uploadname',
mimetype = '$uploadtype',
description = '$uploaddesc',
filedata = '$filedata'";
//perform the insert
$ok = @mysql_query($sql);
if (!$ok) {
exit('Database error storing file: ' . mysql_error());
}
header('location: ' . $_SERVER['PHP_SELF']);
exit();
}
//default page view: list stored files
$sql = 'SELECT id, filename, mimetype, description
FROM filestore';
$filelist = @mysql_query($sql);
if (!$filelist) {
exit('Database error: ' . mysql_error());
}
?>
<body>
<link rel="stylesheet" href="scripts/upload_form.css" type="text/css" />
<script src="../jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="../jquery.metadata.js" type="text/javascript"></script>
<script src="../jquery.validate.js" type="text/javascript"></script>
<!--<script src="js/cmxforms.js" type="text/javascript"></script>-->
<script type="text/javascript"></script>
<div class="container">
<div class="formcontainer">
<!--</fieldset>-->
<!--<fieldset class="shipto"><legend>Ship to address here</legend>-->
<form action="http://localhost/address.php" method="post" name="address">
<p><label for="firstname">First name</label>
<input type="text" name="firstname" id="firstname" /></p>
<p><label for="lastname">Last name</label>
<input type="text" name="lastname" id="lastname" /></p>
<p><label for="street">Street/P.O. Box</label>
<textarea name="street" id="street" cols="17" rows="2"></textarea></p>
<p class="rightside"><label for="city">City</label>
<input type="text" name="city" id="city" /></p>
<p class="rightside2"><label for="state">State</label>
<input type="text" name="state" id="state" /></p>
<p class="rightside3"><label for="zip">Zipcode</label>
<input type="text" name="zip" id="zip" /></p>
<input type="submit" value="Enter" name="address" class="enterbtn"/>
<input type="hidden" name="action" value="upload"/>
</form><!--</fieldset>--></div></div>
<table>
<thead>
<tr>
<th>File name</th>
<th>Type</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<?php
if (mysql_num_rows($filelist) > 0) {
while ($f = mysql_fetch_array($filelist)) {
?>
<tr valign="top">
<td>
<a href="<?php echo $_SERVER['PHP_SELF'];
?>?action=view&id=<?php echo $f['id']; ?>">
<?php echo $f['filename']; ?></a>
</td>
<td><?php echo $f['mimetype']; ?></td>
<td><?php echo $f['description']; ?></td>
<td>
[<a href="<?php echo $_SERVER['PHP_SELF'];
?>?action=dnld&id=<?php echo $f['id']; ?>"
>Download</a> |
<a href="<?php echo $_SERVER['PHP_SELF'];
?>?action=del&id=<?php echo $f['id']; ?>"
onclick="return confirm('Delete this file?');"
>Delete</a>]
</td>
</tr>
<?php
}
} else {
?>
<tr><td colspan="3">No Files!</td></tr>
<?php
}
?>
</tbody>
</table>
</body>
</html>
With the understanding from your original post, if you are supposed to have two tables and relations, then the following query should work for you to get all files and related addresses information.
SELECT
f.*,
a.*
FROM
filestore AS f
LEFT JOIN address AS a ON f.id=a.filestoreid
WHERE
1;
Though you may need to denormlise the tables if there are not too much data of a file.
Maybe I am going at this wrong. Should I make one table and put all the info such as name, address, filename, filedata etc in one table so I don’t need the join. This seems to be simpler and has a less likely chance of getting messed up. I don’t want to send some one who has a dog a picture of someone elses cat! Let me know what way to go.
Adamcoppard, great catch. The script works great now. Shesshsh I wish I could type.
I have changed my thinking on this project based on Rajug’s comment. One person - one file - use one table. Now I just have to figure out how to get the radio selection included in the “INSERT” if they want a stock photo instead of the upload. If anyone can point me in the right direction I would appreciate it greatly. Thanks again everyone for all the help.
Thanks Cups for the kind words, now I get this message after adding $sql to the code
“File with given ID not found in database!SELECT filename, mimetype, filedata FROM filestore WHERE id = ‘id’”
Divide and conquer sounds like great advice. I am going to break my script into three separate files and scripts. The file upload one is working great (I simply copied Kevin’s script so of course it would work!). Now I will work on the address one because it is simpler and I have examples in the book to work from, then the radio button last but not least. Thank you so much for your time and patience with me on this. I’m confident with all of your help I can get it to work. Thank you, Thank you.
Can you just detect if an image was uploaded, and if it isnt then either
a) leave it blank
b) insert the stock
a) might be better because when you display records you can just conditionally check for the absence of an image and put the url to the stock photo into the html stream there.
That way, the day you decide to change your stock photo, you only change it in one place, rather than editing all the database records.
I mean to say that if there are not much columns to be used for file information like (3/4 columns in each table) and if it is the case one-to-one relation then there is no need to use two tables.
