...

View Full Version : Mysql



44stephenv
04-11-2011, 06:05 PM
Hi,
I want to add data into a field that already has data, but I want to check to see if the data already exists in the field and if so dont add it.

Basically its a approval field. So when an admin person clicks on to the account it puts the name of the admin in the field, and if another admin also approves it as them too.

Thanks

Old Pedant
04-11-2011, 08:55 PM
You mean you want BOTH names to end up in the same field???

Bad database design!!! Create a separate table just for approvals. One approval per record.

Go read up on NORMALIZATION.

gooney0
04-12-2011, 03:57 AM
Old Pedant is right on this. You need a one-to-many relationship.

Example:

Approvals table:

ID
Document_ID
Admin_ID or Name

Then to show approvals you can query:

SELECT * FROM approvals WHERE Document_ID=1

You'll need to check for existing entries before inserting new ones.

This sounds like more work but in the end it will save you a great deal of effort.

Old Pedant
04-12-2011, 08:24 PM
Approvals table:

ID
Document_ID
Admin_ID or Name

You'll need to check for existing entries before inserting new ones.



*Mild* disagreement.

(1) What's the purpose of the ID field? If it's an auto_increment field, it's useless in a table like this. You'll never use any query that needs it. If you need something for a primary key, use a *composite* primary key: The Document_ID and Admin_ID together should clearly be unique in the table and so they make a perfect primary key.

(2) You don't *really* need to check for existing entries ahead of time. You can simply use INSERT IGNORE:

INSERT IGNORE INTO Approvals (Document_ID,Admin_ID) VALUES( 33, 171 );

or equivalent. If the INSERT would cause a duplicate (you *did* create the composite primary key I recommended, right?), then it is simply IGNOREd. A nice feature of MySQL.

gooney0
04-13-2011, 06:41 PM
Old Pedant,

Very good suggestions. I'll have to read up on those a bit more and see how I can work them into the mix.

That would seem a much better way of adding data, but processing "selected" data would seem less straightforward. (i would think?)

With ID field: I would store the results into an array or object and use the ID field to uniquely point to that data. Something like:

$people[1]["First_Name"]="John";

Now it is easy to update the table using WHERE `ID` = "1"

Thanks again. That INSERT IGNORE feature is a nice tip.

Old Pedant
04-13-2011, 10:10 PM
You've lost me. How would you ensure that you'd get the same ID from one UPDATE to the next???

And if ID is an auto_increment field, it is likely to be something like 1718 for a given "John" anyway.

******

Re processing SELECTed data. I don't see the issue.


SELECT Project.projectname, Users.username
FROM Project, Approvals, Users
WHERE Project.projectid = Approvals.projectid
AND User.userid = Approvals.userid

Or even


SELECT Project.projectname, GROUP_CONCAT( Users.username, ', ' ) AS allApprovers
FROM Project, Approvals, Users
WHERE Project.projectid = Approvals.projectid
AND User.userid = Approvals.userid
GROUP BY Project.projectname


And so on. I truly can't find any value at all in an ID field for the Approvals table. There almost never is any reason to have one in a many-to-many table, because the many-to-many table is never used in any "stand-alone" fashion. It pretty much has to be used in linking the two tables it is related to.

What am I misunderstanding??



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum