We help IT Professionals succeed at work.

How do I go through a MySQL table in a database and add records using some information stored in existing records in that table?

ramsait
ramsait asked
on
255 Views
Last Modified: 2010-03-19
Hello!

I think I need some expert help here.

So I've been trying to figure out how to do this and I'm stumped, hopefully I make sense here.

We have a table in a MySQL database that has 3 columns. It stores information about images in an image database.

Field_ID, Record_ID, String Value

Field_ID - is the type of field that we use for data retreival purpoes (city state country, image name etc)
Record_ID - is unique number for that image (can be used more than once, but the field_ID must be different)
StringValue - is the name of the city, state, country, image name etc.

Example:

10102, 2989455, Spain
10104, 2989455, Madrid
10207, 2989455, madrid.jpg

Now I need a new Field_ID where the StringValue is the same as the Record_ID value from another row. Essentially adding more information about that image.

So search for any of these:

10102, 2989455, Spain
10104, 2989455, Madrid
10207, 2989455, city.jpg

and then making one of these if it doesn't already exist (without duplicates):

10105, 298455, 298455

I'm not sure how to do this, I'm not very good with writing SQL statements, would php be good for this? I don't know where to begin. Any help would be greatly appreciated.
Comment
Watch Question

The question is a little confusing but:

" I need a new Field_ID where the StringValue is the same as the Record_ID value from another row."

Would be something like this:

Select * from tablename where StringValue in (select Record_ID from tablename)

That would give you a list of rows where the string value is the same as a record_ID from any row.

You could then loop through them and update data to your liking.

is that what you needed?

Commented:
Hello!
 the Field_ID column usually populated with a sequence. Sequence is an object in MySQL. When you create a table you can aasign a sequence toa column, so everytime you add a row into a table the column with sequence will be populated automatically with next number.
 In your case you need to fill in missed number which I'm not sure how to do without getting it VERY complicated.
 All I can help you with is to continue to add rows after the maximum Field_Id found.
Ex:
select max(Field_ID) from  
insert into   (field_ID, record_ID, string_value) select max(Field_ID), ,  from  


Kind regards,
t.

Cedric Obinna A.Full Stack Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
In addition to Anakinjay's post, what is the criteria for choosing what record_ID value to use for the new record? What guides the decision to add an existing record_ID as a new record?

Author

Commented:
Sorry for any confusion. What I meant was I need to create new rows, but in the new rows I need the value in the Field_ID column to be always be 10105, and for the values in StringValue and Record_ID to be equal to the value of a Record_ID value from another row already in the table.

The record_ID is just an increment of a number that is stored elsewhere in the database. Each time a new image is added, the number is incremented. Then we use that number in THIS table to assign additional information about it, like where the photo was taken, a description, file name etc.

(These always stay the same)

Field_ID = What it is.
10102 = Country
10104 = City
10207 = Original name of the image file.
10105 = Image ID# (for web searching by ID# rather than city, or country)

So this is what we have for example (there are actually over a 100,000 rows like this) in the table.

10102, 2989455, Spain
10104, 2989455, Madrid
10207, 2989455, madrid.jpg

10102, 2989456, France
10104, 2989456, Paris
10207, 2989456, image001.jpg

10102, 2989457, United Kingdom
10104, 2989457, London
10207, 2989457, city.jpg

I need to do this:

10102, 2989455, Spain
10104, 2989455, Madrid
10207, 2989455, madrid.jpg
10105, 2989455, 2989455

10102, 2989456, France
10104, 2989456, Paris
10207, 2989456, image001.jpg
10105, 2989456, 2989456

10102, 2989457, United Kingdom
10104, 2989457, London
10207, 2989457, city.jpg
10105, 2989456, 2989456

So these are going to be added to the table.

10105, 2989455, 2989455
10105, 2989456, 2989456
10105, 2989457, 2989457

Am I making better sense now?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.