A newbie MySQL question...

[moomin52]

Hi. I have a database table which currently has 21 fields - id and 20 fields which hold text links to the location of download items.
I've set up a series of "admin" pages for this table so that the user can create new records and update existing records - this works OK, but there's a small problem which I need some help with.
If the user were to make all 20 uploads in one go, that would be fine and all the data would be written into one row in the table. However, if he uploads (say) 10 one day and 4 the next, the latter uploads get written into a second row - ideally what I need is for the table to be locked down somehow so there's only one row which I'm updating each time.
So... my question, basically, is can this be done, or do I need to set my table up in a different way? I've got id as primary key, and wondered whether I ought to have set this to something different like unique, maybe?
Any assistance appreciated with this one - thanks in advance.

[Atli]

Hey.

Could you post the CREATE TABLE statement for your table? (Just do SHOW CREATE TABLE.)

If your user uploads the data for the 20 fields in smaller batches, then you need to find a way to tie those batches to the same row. - That is done by passing the PK (or any other unique field) of that row with each batch of data and using UPDATE instead of INSERT for all but the first batch. The first batch creates the row, and the following batches UPDATE the same row.

Consider this basic PHP example. It uses a session to store the ID of the row created int he first request, and then it uses that ID to update the same row in the following requests.

(It probably doesn't work as-is... didn't test it, but you get the idea.)
[code=php]<?php
// Start the session.
session_start() ;

// Assumign the data is a file.
$filename = "uploads/" . time() . ".txt";
if(move_uploade d_file($_FILES['upload']['tmp_name'], $filename))
{
// INSERT if no row ID was saved in session.
if(!isset($_SES SION['rowID']))
{
mysql_query("IN SERT INTO myTable(field1)
VALUES '{$filename}'") ;

// Set the row ID and the field number
// in the session.
$_SESSION['rowID'] = mysql_insert_id ();
$_SESSOIN['fieldNum'] = 2;
}

// UPDATE the row stored in the session.
else
{
$fieldname = "field" . $_SESSION['fieldNum'];
mysql_query("UP DATE myTable
SET {$fieldname} = '{$filename}'
WHERE rowID = {$_SESSION['rowID']}");

// Increment the row and field numbers.
$_SESSION['rowID']++;
$_SESSION['fieldNum']++;
}

// If 20 files have been uploaded, destroy
// the session, making the next request
// create a new row.
if($_SESSION['fieldNum'] > 20)
{
session_destroy ();
}
}
?>[/code]