beginner question: primary and foreign keys

[kaplanyo]

I would like to know if it's a bad idea to not use the primary key from one table as the foreign key in another. What if I use a different field as the foreign key. Is it still a foreign key?

Maybe I can explain more... if I have a table called 'projects' with a field called 'project_id' (the primary key) and 'name'... is it a bad idea to use 'name' as my foreign key in another table, i'm calling project_images. Project_images has a field called 'item_id' (the primary key) and 'project_name'. I want to use 'name' from 'projects' and put it into 'project_name' in the 'project_images'.

Thanks. Any advice would be really great. I can also post a link or take some screen shots to show what I mean.
Dave

I'm using:
MySQL client version: 5.0.22
Used PHP extensions: mysql

[kaplanyo]

I apologize, PHP and mySQL are kicking my butt. This question is making less sense as I try and work through my bug. Maybe some code will help.

Here's where I'm getting the list of projects to choose from. What I'm trying to do is insert the name into a field in the project_images table in addition to setting the foreign key of project_images from the primary key of projects (project_id).

<label for="project_id">Project:</label>
<select name="project_id" id="project_id">
<option value="">Select Project</option>
<?php
// get details of projects
$getProjects = 'SELECT * FROM projects ORDER BY name';
$projectList = mysql_query($getProjects) or die (mysql_error());
while ($project = mysql_fetch_assoc($projectList)) {
?>
<option value="<?php echo $project['project_id']; ?>"
<?php
if ($project['project_id'] == $row['project_id']) {
echo ' selected="selected"';
}
?>><?php echo $project['name']; ?>
</option>
<?php } ?>
</select>

Here's my PHP:

if (empty($missing)) {
$sql = "INSERT INTO project_images (project_id, project_name, image_order, filename, caption )
VALUES('$project_id', '$project_name', '$order', '$file', '$caption')";
// process the query
$result = mysql_query($sql) or die(mysql_error());
// if successful, redirect to list of existing records
if ($result) {
header('Location: ... ');
}
}

I was looking to delete this posting, but don't see the option. maybe we can work through this.

[Voetsjoeba]

A foreign key by definition can only reference candidate keys, ie. primary or unique keys. So technically you could use the project's name as a foreign key if you have a unique index on it, although I don't see why in the world you wouldn't want to use the project's id.

From what I can see, the way you're trying to do it introduces redundancy in your database. The project name is already stored in the projects table and you now appear to be using the project_id after all to link project_images to projects, so you're effectively recording the project's name twice.

The proper way to do this is to make project_id in project_images a foreign key that references the primary key of the projects table, and drop the project_name column from project_images.

I'd also like to point out that you're currently vulnerable to both SQL injection and XSS attacks. Read up on these subjects to learn how to prevent them from happening; you'll find plenty on Google.

[kaplanyo]

Thanks for replying.

Yes, yes. I think I see what you're saying!


The proper way to do this is to make project_id in project_images a foreign key that references the primary key of the projects table, and drop the project_name column from project_images.

I've got that. I just want to be able to reference the name that's already in projects using the project_id that's in project_images. I was trying to insert the same info in 2 places, I see... it epiphany.

I've got a page that lists everything in the project_images including the project_id, just haven't figured out how to get the 'name' from projects through the _id.

I've managed to get one record and display it... I'm cobbling this together from stuff I've been learning/using in PHP Solutions Dynamic Web Design Made Easy by David Powers. I've managed to get some stuff built using a 1-to-1 relationship, this is a tougher.

I've attached a screenshot what my listing looks like without the name. I don't get how I can get the name from projects yet.


$conn = dbConnect('query');
$sql = 'SELECT * FROM projectimages
ORDER BY project_id ASC, image_order ASC';
$result = mysql_query($sql) or die(mysql_error());

Here's the table code

<!-- Dynamic Table Data -->
<table width="900" height="45">
<tr>
<th width="80" class="tableheading" scope="col">PREVIEW</th>
<th width="20" class="tableheading" scope="col">ID</th>
<th width="80" class="tableheading" scope="col">PROJECT</th>
<th width="20" class="tableheading" scope="col">DISPLAY</th>
<th width="200" class="tableheading" scope="col">FILENAME</th>
<th width="300" class="tableheading" scope="col">CAPTION / CREDIT</th>
<th width="50" class="tableheading">&nbsp;</th>
<th width="50" class="tableheading">&nbsp;</th>
</tr>
<?php
while($row = mysql_fetch_assoc($result)) {
?>
<tr valign="top" bgcolor="#EFEFEF">
<td><img src="../project_images/<?php echo $row['filename']; ?>" width="80" height="50" /></td>
<td style="text-align: center;"><?php echo $row['project_id']; ?></td>
<td style="text-align: center;"><?php echo $row['name']; ?></td>
<td style="text-align: center;"><?php echo $row['image_order']; ?></td>
<td><?php echo $row['filename']; ?></td>
<td><?php echo $row['caption']; ?></td>
<td style="text-align: center;"><a href="projectimage_update.php?item_id=<?php echo $row['item_id']; ?>">EDIT</a></td>
<td style="text-align: center;"><a href="projectimage_delete.php?item_id=<?php echo $row['item_id']; ?>">DELETE</a></td>
</tr>
<?php } ?>
</table>
<!-- End Dynamic Table Data -->

I can get the code for one by assigning the item_id, but what i'd like to do is loop through and get all the records.

// check for item_id in query string
if (isset($_GET['item_id']) && is_numeric($_GET['item_id'])) {
$item_id = $_GET['item_id'];
}
else {
$item_id = 74;
}

// prepare SQL query
$sql = "SELECT filename, image_order, caption, name
FROM projectimages, projects
WHERE projectimages.item_id = $item_id
AND projectimages.project_id = projects.project_id";


I'm chipping away, if I'm anything other than a stubborn thickhead, I'm persistent.
Thanks for the tip on security. If you can give any additional advice, that would really be appreciated.

[kaplanyo]

It only took me all day, but it feels so good when something works! Like pressing on a paper cut so it hurts more just so that when the pressure is released a nice sense of pain-freeness washes over. Ok.

Here's my cobbly works.

$sql = "SELECT projectimages.item_id, projectimages.filename, projectimages.image_order, projectimages.caption, projects.name, projects.project_id
FROM projectimages, projects
WHERE projectimages.project_id = projects.project_id
ORDER BY projectimages.project_id ASC, projectimages.image_order ASC";

I found some great info here: http://www.tizag.com/mysqlTutorial/mysqljoins.php
always forget about the awesome tizag for schooling! the other great site along with kirupa!

I've attached some screenshots of them working together. Now I can hide the id and use the name as the informational communicator it is. As they say where the people work hard, It's MillerTime™!

[Voetsjoeba]

Glad you got it working :)