Member Avatar for Member #859548

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;
}
Edited by Member #859548
  • 4 Contributors
  • 8 Replies
  • 263 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by Member #859548

Dani AI

Generated

The real cause here is the unescaped apostrophes in the session text: building SQL with plain string concatenation will break when the data contains single quotes. 's symptom ("script falling over" when copying text with apostrophes) is exactly that. Escaping fixes the immediate problem, but parameterized queries are a much safer, long‑term solution. (php.net)

Quick options (choose one):

  • Fast patch for legacy mysql_* code: escape every value with the appropriate escape function (for MySQL: use mysqli_real_escape_string or mysql_real_escape_string if you cannot migrate immediately).
  • Recommended: migrate to PDO or MySQLi and use prepared statements so the driver handles quoting for you. Example (PDO) that updates a whole block in one call (replace the connection/config as needed):
$stmt = $pdo->prepare(
  "UPDATE `$tn` 
     SET source = :source, type = :type, size = :size, comments = :comments
   WHERE scan_id BETWEEN :start AND :end"
);

$stmt->execute([
  ':source'   => $_SESSION['source'],
  ':type'     => $_SESSION['type'],
  ':size'     => $_SESSION['size'],
  ':comments' => $_SESSION['comments'],
  ':start'    => (int)$sta,
  ':end'      => (int)$fin,
]);

PDO/mysqli prepared statements prevent the quoting problem altogether and are the modern approach; the old ext/mysql was deprecated/removed in later PHP versions so migration is recommended. (php.net)

Efficiency and safety tips:

  • If every row in the block gets the same 4 fields, one UPDATE with a range (BETWEEN) or an IN list is simpler and faster than looping. If per-row differences remain, prepare once and execute in a loop (bind new params each time). Always test the WHERE with a SELECT first. (dev.mysql.com)

Debugging and best practice:

  • Follow and : print the SQL you are about to run and check the DB error/exception output when it fails. After an UPDATE, verify changes with affected rows / rowCount and use transactions and backups for bulk edits. For security, prefer parameterized queries per OWASP guidance. (cheatsheetseries.owasp.org)

Recommended Answers

All 8 Replies

Member Avatar for pritaeas

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 Member #859548

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.

Member Avatar for pritaeas

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.

Edited by pritaeas
Member Avatar for bops

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 Member #859548

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

Member Avatar for pritaeas

Do this:

$result = mysql_query($query) or die(mysql_error() . '<br/>' . $query);
Member Avatar for Bob Hensley

$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 Member #859548

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.