Member Avatar for stephen_UK

For reason I won't bore you with, I need to update an archive database periodically with typically blocks of 100-300 records.
Each record has 6 visible fields in the database.
Frequently 4 of the 6 fields have the same information in them for the whole block of data.
For a good reason I do this by creating the required number of empty records in the database for the required block of data then manually editing each record.
My intension was to complete the first record of the block, and then create a program that would read that record and then duplicate the data for those 4 fields in all the records in the block.

I would then only have to manually edit a couple of fields for each record.

I have programmed the first part and obtain the data to be duplicated in 4 session variables but for some reason none of my attempts to update those the block of records from unique_id =$first to $last (in the primary ID field) appears to work. If you could suggest where I am going wrong as simply as possible I would be grateful. Many thanks for your time.

$x=$first;
whilst ($x <= $last) {
$query = ("UPDATE $tn SET 
source  =  $_SESSION['source'],
type  =  '$_SESSION['type'],  
size  =  '$_SESSION['size'],
comments  =  $_SESSION['comments']  WHERE unique_id = '$x' ");
$result = mysql_query($query);
$x=$x+1;
}

Recommended Answers

All 8 Replies

whilst should be while. If the column types are chars, you must use single quotes. You may want to add error checking to see if the query failed.

Member Avatar for stephen_UK

Whilst was just my typo here, it is while in the code. I am not sure what you mean re single quotes as all the field names have single quotes.

Try this:

$query = "UPDATE `$tn` SET 
    `source` = '{$_SESSION['source']}',
    `type` = '{$_SESSION['type']}',  
    `size` = '{$_SESSION['size']}',
    `comments` = '{$_SESSION['comments']}'  
    WHERE `unique_id` = '$x'";

Note that if any of those session variables can contain a single quote, it has to be escaped or the query will fail.

Try echoing the query before you execute it. If it looks fine then try adding or die(mysql_error()); after the mysql_query().

Member Avatar for stephen_UK

Still no joy. I have pasted the actual code this time. It runs error free echoing the correct values for $x or scan_id. The $_SESSION['type'] echos the correct data to update the record with but still nothing is getting saved in the table.

$x=$sta;
while ($x <= $fin) {
$query = "UPDATE `$tn` SET 
    `source` = '{$_SESSION['source']}',
    `type` = '{$_SESSION['type']}',  
    `comments` = '{$_SESSION['comments']}'  
    WHERE `scan_id` = '$x'"; 
$result = mysql_query($query);
echo $x."  type = ".$_SESSION['type'];
?><br><br><?php
$x=$x+1;
}

Having a break now and try again in an hour.

Stephen

Do this:

$result = mysql_query($query) or die(mysql_error() . '<br/>' . $query);

$result = mysql_query($query) or die (mysql_error ());

Append the or die (mysql_error ()) clause to your query. This will print any errors stemming from MySQL. If you receive any then post what it is here. If you do not receive any errors then it's a logic error within your application flow or variable definitions.

Member Avatar for stephen_UK

Thanks to both of you for your advice. It looks like the script was falling over as it doesn't like the embedded apostrophes in the text being copied from one record to the other.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.