PHP and mysql table join

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.


$address = mysql_query(
	'SELECT * FROM address, filestore WHERE fileid=file.id');
$result = mysql_query($address);
	
$address = @mysql_query($sql);
	if (!$address) {
	exit('Database error storing file: ' . mysql_error());
	}
	header('location: ' . $_SERVER['PHP_SELF']);
	exit();

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&amp;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&amp;id=<?php echo $f['id']; ?>"
                        >Download</a> | 
                        <a href="<?php echo $_SERVER['PHP_SELF'];
						?>?action=del&amp;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.

You have to learn to divide and conquer.

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!).

Images should be stored on the file system.

The Image meta data can be stored in a table.

People stored in another table.

Rules:
Images belong to owners.
Owners can have many images.
Images can only have one owner.

people <- table

=====
id - int <- each row
name - str

image_data

========
address str UNIQUE
owner - (foreign key to people)

Sample DATA in those tables:

people

=====
1 - Bob
2 - Ted

image_data

=========
upload1.jpg | 1
upload2.jpg | 2
upload3.jpg | 1

Get all images belonging to a person


Select address 
FROM image_data
LEFT JOIN people 
ON owner = id
WHERE name="Bob"

Ought to get you
image1.jpg
image3.jpg

and you display them by assembling the/path/to/images and appending the image1.jpg

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&amp;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&amp;id=<?php echo $f['id']; ?>"
                        >Download</a> | 
                        <a href="<?php echo $_SERVER['PHP_SELF'];
						?>?action=del&amp;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.

not too much data of a file? :confused:

i like the design Cups suggested, it’s as simple as it gets and it doesn’t need to be denormalised

:slight_smile:

WHERE id = 'id'

there’s your problem right there, your variable isn’t being properly substituted – ‘id’ is a character string that does not contain a valid id value

presumably you want something like this –

WHERE id = 937

where you actually go looking for a specific id value

note that if id is a numeric column, the value you compare it to should not be in quotes

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.

By the logic of looking at the very first part of the if statement change strops to strpos

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’”

Where o where did I go wrong…
Thanks again.

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.


If( empty( $row['photo_url'] ) ){
$row['photo_url'] = "xmas_version.jpg" ;
}

$sql = “SELECT filename, mimetype, filedata
FROM filestore WHERE id = ‘$id’”;

And if id is a numerical field you don’t need to quote it.

$sql = “SELECT filename, mimetype, filedata
FROM filestore WHERE id = $id” ;

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. :wink: