Member Avatar for benhowdle89

Hi,

What i have is a variable that is getting updated periodically and i've set the php script to refresh itself every 30 seconds to run a mysql query updating a db, this is good but if this value isnt changing from the source i get duplicate records in the db, how do i check the value of the variable with php and only execute the query when the variable has changed value?

Thanks

  • 3 Contributors
  • 6 Replies
  • 4K Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Member #334542

Dani AI

Generated

A small gotcha here: clearing $value in PHP after the insert (as suggested by rajarajan2017) will not prevent duplicates across page refreshes. Each HTTP request is a new PHP process, so any variable reset is lost between runs. You need either a database check or a database constraint.

Two practical patterns:

  1. Compare to the last stored value before inserting.
  • Fetch the most recent value from the table and insert only if the new reading differs.
// PDO recommended; ext/mysql is deprecated/removed in PHP 7+
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// $value comes from your source; cast if it should be numeric
$new = (float)$value;

$last = $pdo->query('SELECT data FROM arduino ORDER BY id DESC LIMIT 1')->fetchColumn();
if ($last === false || (float)$last !== $new) {
    $stmt = $pdo->prepare('INSERT INTO arduino (data) VALUES (?)');
    $stmt->execute([$new]);
}

Using prepared statements handles quoting safely and avoids SQL injection issues. See PDO prepared statements. Also note the old mysql_* API was deprecated in PHP 5.5 and removed in PHP 7; use PDO or MySQLi going forward (php.net manual note).

  1. Let MySQL enforce idempotency.
  • Add a UNIQUE index on the column that represents a reading you only want once, and turn duplicates into an update of a timestamp/counter:
ALTER TABLE arduino ADD UNIQUE KEY uniq_data (data);

INSERT INTO arduino (data, seen_at)
VALUES (?, NOW())
ON DUPLICATE KEY UPDATE seen_at = VALUES(seen_at);

This relies on MySQL’s UNIQUE constraint and INSERT ... ON DUPLICATE KEY UPDATE to avoid duplicate rows while still recording that the same value was seen again (MySQL UNIQUE/CREATE INDEX, INSERT ... ON DUPLICATE KEY UPDATE).

Bonus tip: rather than using header('Refresh: 30'), consider a cron job or queue worker to poll on a schedule; it is more reliable than a self-refreshing web page.

Recommended Answers

All 6 Replies

Member Avatar for Member #334542

Before generating the next value set your variable to clear or NULL after updating the db, and before updating check whether variable is clear or NULL skip the update process.

Member Avatar for raju_boini525

post your code.

Member Avatar for benhowdle89

That sounds perfect, would you mind putting a bit of sample code together please?

This is my code so far:

<?php
header('refresh:30; url=pachube.php');
require_once( '/pachube_php_library/pachube_functions.php' );
$api_key = "xxxxxxxxxxxxxxxxx";
$pachube = new Pachube($api_key);
$feed = xxxxx;
$data = $pachube->retrieveData ( $feed, "csv" );
$array = explode(',', $data, 5);
$value = $array[0];
echo $value;

	//Connect to database
	$opendb = mysql_connect("localhost", "root", "") or mysql_error("Could not connect to database");
    mysql_select_db("test");

    if ($opendb)
		{
     	mysql_query(" INSERT INTO arduino (data) VALUES ($value)");
		mysql_close($opendb);
		}
		
?>

So i'm guessing i would need to put some sort of "if" statement above the mysql query??

Thanks

Member Avatar for Member #334542 
<?php
header('refresh:30; url=pachube.php');
require_once( '/pachube_php_library/pachube_functions.php' );
$api_key = "xxxxxxxxxxxxxxxxx";
$pachube = new Pachube($api_key);
$feed = xxxxx;
$data = $pachube->retrieveData ( $feed, "csv" );
$array = explode(',', $data, 5);
$value = $array[0];
echo $value;
if (isset($value) && !empty($value) && $value != NULL)
{
	//Connect to database
	$opendb = mysql_connect("localhost", "root", "") or mysql_error("Could not connect to database");
    mysql_select_db("test");
    if ($opendb)
		{
     	mysql_query(" INSERT INTO arduino (data) VALUES ($value)");
		mysql_close($opendb);
		}
	$value = '';
} else echo "No value is set";
?>
Member Avatar for benhowdle89

Brilliant, Thanks so much!!!

Member Avatar for Member #334542

Always welcome! Please mark the thread as solved.

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.