Page 1 of 1

Newb Database Woes ! HELP !!!

Posted: Tue Nov 17, 2009 4:16 pm
by MiniMonty
Hi all,

pretty new to this so be gentle...

I'm trying to build a gallery site with a LAMP set up. (steep learning curve but having lots of fun).
Users upload images to their own directories and the filepath to the image is stored on the db.

I've got this 99% working except for one glaring problem.

If I (as member 1) upload an image it's placed my images dir and renamed "1.jpg"
If I (as member 2) do the same thing I get this error: Duplicate entry '1.jpg' for key 2
I'm pretty sure this is to do with the foreign key and I'd really appreciate some newb style advice on what
I'm doing wrong and how (if) I can fix it.

OK this is how the db is set up: (pictures speak loudest)...
Made a table "myMembers" to hold the basics on members.
Image

Made a table "pictures" with UID as the unique and created an index on this of "images:member_fk".
Image

Tried to link the UID field of "pictures" to the "id" field of "myMembers" I'm presented with four drop downs.
If I set the first to "myMembers-> id" and the last to to "CASCADE" when I hit "GO" the page refreshes and the cascade is not set.
If I set the first to "myMembers-> id" and the second to "pictures -> UID" the cascade IS set but I still get the same error
of Duplicate entry '1.jpg' for key 2 if I try to upload an image.
Image

Some advice on this would be very welcome as I've spent all day reading and trying to solve it with no joy.
I'm happy to start from scratch if that's what's needed but I have to get it solved !

Best wishes
Monty

Re: Newb Database Woes ! HELP !!!

Posted: Tue Nov 17, 2009 5:47 pm
by califdon
Let's start with what primary and foreign keys are for, and what a unique index is. In a relational table, if you want to relate anything else to it, you must have a unique primary key. This means that whatever value that field has can occur only once in that table. Another way to say the same thing is that every row in the table can be uniquely identified by its primary key. This is essential if you want to join another table, because the foreign key of the other table must have one and only one row to match in this table. Primary keys must always be unique. This is done by creating a unique index which will reject any attempt to add a row with a primary key value that already exists in that table. A foreign key is one that matches a primary key value in a different table, and is the basis for all table joins. A foreign key should never have a unique index, because typically there WILL be duplicate values of the foreign key.

So you must remove the unique index on the UID field in your "pictures" table. The unique index was doing exactly what you told it to do, refuse to insert a new row where a row already existed with the same value in that field. That should resolve the issue.

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 6:25 am
by MiniMonty
That's beautiful - really nicely explained.
Thanks mate.

Maybe you can help with something else... ?
I'm making Flash front end galleries work like this:

Code: Select all

 
$sql = mysql_query("SELECT * FROM pictures WHERE gallery='portraits'");
//WRITE A STRING (ARRAY) THAT FLASH CAN READ
$portraits="";
// LOOP THROUGH THE DB AND MAKE A LIST
while (($row = mysql_fetch_assoc($sql)) !== false) {
    $imagelist=  $row["dirpath"]. ",";
    $portraits=$portraits.$imagelist;
    }
// NOW COUNT THE COMMAS IN THE STRING TO GIVE "allpics" TO FLASH.
$temp_string=$portraits;
$allportraits= substr_count($temp_string,","); 
 
The pictures table has a row "dtadded" (date added).
What do I need to add to the query to list the pictures in date order ?

Best wishes
Monty

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 6:27 am
by papa
"SELECT * FROM pictures WHERE gallery='portraits' ORDER BY dtadded"

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 7:14 am
by GimbaL
califdon: sorry if it's is not relevant in this topic, but what exactly is the difference between Index, Unique, and Primary Key?

This is what I understood so far: an Index field can be used for sorting but does not have to be unique, a Unique field has to be unique (and can also be NULL, well only one row, if more rows were NULL they wouldn't be unique), and a Primary Key field has to be unique and can not be NULL). Is that all correct?

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 8:44 am
by MiniMonty
papa wrote:"SELECT * FROM pictures WHERE gallery='portraits' ORDER BY dtadded"
Thanks for that.

Just for anyone who's interested to display most recently added first ad the "DESC" to the line above.

Code: Select all

 
$sql = mysql_query("SELECT * FROM pictures WHERE gallery='portraits' ORDER BY dtadded DESC");
 
Best wishes
Monty

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 12:10 pm
by califdon
GimbaL wrote:califdon: sorry if it's is not relevant in this topic, but what exactly is the difference between Index, Unique, and Primary Key?

This is what I understood so far: an Index field can be used for sorting but does not have to be unique, a Unique field has to be unique (and can also be NULL, well only one row, if more rows were NULL they wouldn't be unique), and a Primary Key field has to be unique and can not be NULL). Is that all correct?
That's substantially correct. I'm not sure if a unique index accepts one occurrence of a NULL or not, it probably does if the column doesn't also have a NO NULLS constraint on it. You can create an index on any column and you can specify whether an index is also a unique index. An index is a separate structure, a separate file in most database engines. "Unique" is an attribute or constraint on how that index operates. "Primary key" is a column or combination of columns that has/have a unique index built on it/them AND that you designate as the Primary Key. You could possibly have other columns with unique indexes, but if you don't use them as the Primary Key in a relationship, they wouldn't be called the Primary Key.

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 12:53 pm
by MiniMonty
califdon wrote:So you must remove the unique index on the UID field in your "pictures" table. The unique index was doing exactly what you told it to do, refuse to insert a new row where a row already existed with the same value in that field. That should resolve the issue.
Hi Califdon, your solution solved the duplicate entry issue but the relation view still won't
allow me to set the cascades - any ideas ?

Best wishes
Monty

PS - the site's almost finished if you want to take another look and maybe post up some
shots of sunny California !
http://www.shutterbugclub.com/main.php

Re: Newb Database Woes ! HELP !!!

Posted: Wed Nov 18, 2009 4:13 pm
by califdon
I've never used cascades from phpmyadmin, so I don't recognize that screenshot and I don't think I will be able to help you. I assume that this is similar to referential integrity in, for example, Microsoft Access. The concept is not to allow a deletion or change in primary key value unless the user authorizes the deletion of any related rows in other tables, or makes the key value change in any related rows in other tables, then those deletions or changes will be made automatically for all affected records. But what's in those dropdown lists and how you use them is new to me.