Multiple update.

[muffinresearch]

I am starting with a datbase structure like this:

ID | filename | info1fromfile | info2fromfile
-------------------------------------------------------
1 file1
2 file2

My query loops through the filename column and then opens each file listed in turn.

Whilst open it extracts strings from the text files.
These are to be entered into the rows relative to that filename. So I should end up with this:

ID | filename | info1fromfile | info2fromfile
-------------------------------------------------------
1 file1 someinfo1 someinfo2
2 file2 someinfo3 someinfo4


To be able to run the UPDATE I need to know which row I am currently on. Is there a way to do that as the filename QUERY pulls out the filenames?

I guess Ideally I need to know what ID we are on.

Also is there an easy way to count how many filenames have been looked at so I can use LIMIT so that the UPDATE statement doesn't try to UPDATE the whole database every time. (The number of files will get larger over time so I only want to update the rows necessary!)

 

[sleipnir214]

If you pull the ID out of the row, open the file and process the file's contents, aren't you going to be updating only one row at at time?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[muffinresearch]

Thanks for your swift answer!

Fair enough so it should work without an ID reference - Why am I getting an error:

Error adding data into database:You have an error in your SQL syntax near 'Infofromfile='', Infromfile2=''' at line 1

The update statement code is (this all goes on one line):

$tofromsubdate = "UPDATE test SET Infofromfile= $Infofromfile', Infofromfile2='$Infofromfile2'";

 

[sleipnir214]

It looks to me like you have a missing open singlequote just before $Infofromfile

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

But what I would do, if I know the ID of the row to be updated, is to use an UPDATE query like:

UPDATE tablename SET columnname1 = value1, columnname2 = value2 [red]WHERE id = idcolumnnumber[/red]

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[muffinresearch]

Sorry that missing ' is a typo, I'll try your idea about the UPDATE query.

Cheers for your help.

 

[muffinresearch]

Sorry to be a pain but I don't understand your reference to idcolumnnumber what should be in place of this?

 

[sleipnir214]

From the column named "ID" from your table.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[muffinresearch]

I've fixed the UPDATE the problem was due to using the Column name "TO" in my actual database (which is a dumb thing to do when TO exists in SQL!) If you hadn't noticed I've only been doing this for a couple of days!

Any way I still have a problem which is that whilst now the update runs it clearly doesn't know the ID number to update.

I have set up the SQL UPDATE followed by a load of lines echoing the variables updated. The database shows only the variables from the last file looked at on every row. Whereas the echo lines print the correct information to the screen!

Any ideas?

 

[sleipnir214]

Why not fetch the row ID at the same time as your filename, store the row ID and use that row ID in the where clause of your UPDATE query?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[muffinresearch]

I fixed it a slightly simpler way.

I just used WHERE filename=$filenamevar as this was information I had already taken from the database.

Cheers for your guidance and pointers.

Mocha.