Query Syntax

I have a script that updates a database.  It updates about 100 field from information that is being parsed from a feed.  What type of query code would I need to write so that the UPDATE that ran would report to me the fields that were updated?

I want it to echo each field_id that was actually affected/updated:

$q = "UPDATE exp_weblog_data SET field_id_29='$field_id_29', field_id_30='$field_id_30', field_id_31='$field_id_31'  WHERE field_id_215='$field_id_215'";

if ($return = mysql_query($q, $yourconnection))
{
      echo $q;
)
LVL 7
rgranlundAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ray PaseurConnect With a Mentor Commented:
You can see the code in action here:
http://www.laprbass.com/RAY_temp_rgranlund.php

Starting on line 40 we create a test data base table.

On line 60 we create three sets of data that will drive our updates.

Lines 65 through 113 is our class definition for the objects that will hold the records of our updates.  This class definition could be placed anywhere in the code.  It might be externally defined and brought in through an include() or an autoloader method.

On line 117 we create an array that will be an array of objects.  Each object in the array will have information about one update to one row of the table.

Beginning on line 120 we process the three sets of updates.  First we select the row to be updated and copy its data into local variables.  Then we create an object for the changes and (conditionally) use the setters to add the changes to the object.  On line 138 we add the object to the changelog array.

On line 140 we update the row, and the code loops through each of the update data sets.

When the updates are complete we check our work.  Lines 146 through 153 dump the updated table rows so we can inspect the data.  Lines 157 through the end print out the changelog object message fields.

HTH, ~Ray
<?php // RAY_temp_rgranlund.php
error_reporting(E_ALL);


// DEMONSTRATE THE USE OF COLUMN-LEVEL UPDATE LOGGING


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// LIVE DATABASE CREDENTIALS
require_once('RAY_live_data.php');


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// CREATING THE TEST DATA TABLE
$sql = "CREATE TEMPORARY TABLE myTable (
        nom   VARCHAR(4)    NOT NULL DEFAULT ''      COMMENT 'Just some test data strings',
        siz   INT           NOT NULL DEFAULT 0,
        qty   INT           NOT NULL DEFAULT 0  )";
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );

// LOADING SOME DATA INTO THE TEST DATA TABLE
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Joe', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Ray', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Tom', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Sue', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Pat', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Tib', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Kat', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Amy', 1, 10 )");
$res = mysql_query("INSERT INTO myTable ( nom, siz, qty ) VALUES ( 'Rob', 1, 10 )");


// THE DATA FOR SOME UPDATE QUERIES
$usqls   = array();
$usqls[] = array('nom' => 'Ray', 'siz' => 3, 'qty' => 12); // CHANGE TWO FIELDS
$usqls[] = array('nom' => 'Pat', 'siz' => 3, 'qty' => 10); // CHANGE ONE FIELD
$usqls[] = array('nom' => 'Amy', 'siz' => 1, 'qty' => 10); // CHANGE NO FIELDS


// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS
class Changes
{
    protected $nom, $old_siz, $old_qty, $new_siz, $new_qty;

    public function __construct($nom)
    {
        $this->nom     = $nom;
        $this->old_siz = NULL;
        $this->old_qty = NULL;
        $this->new_siz = NULL;
        $this->new_qty = NULL;
    }

    public function set_siz($old_siz, $new_siz)
    {
        $this->old_siz = $old_siz;
        $this->new_siz = $new_siz;
    }

    public function set_qty($old_qty, $new_qty)
    {
        $this->old_qty = $old_qty;
        $this->new_qty = $new_qty;
    }

    public function get_change_message()
    {
        // SET THE MESSAGE TO THE OBJECT'S nom ONLY
        $msg = $this->nom;

        // ADD TO THE MESSAGE FOR ANY CHANGES
        if ($this->old_siz)
        {
            $msg .= " SIZ CHANGED FROM $this->old_siz TO $this->new_siz";
        }
        if ($this->old_qty)
        {
            $msg .= " QTY CHANGED FROM $this->old_qty TO $this->new_qty";
        }

        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
        if ($msg == $this->nom)
        {
            $msg .= " NO CHANGES";
        }
        return $msg;
    }
}


// OUR CHANGELOG IS AN ARRAY OF OBJECTS
$changelog = array();


// ITERATE OVER THE UPDATE QUERY DATA
foreach ($usqls as $udata)
{
    $nom = mysql_real_escape_string($udata["nom"]);
    $new_siz = $udata["siz"];
    $new_qty = $udata["qty"];

    // SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
    $sql = "SELECT siz, qty FROM myTable WHERE nom = '$nom' LIMIT 1";
    $res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
    $row = mysql_fetch_assoc($res);
    $old_siz = $row["siz"];
    $old_qty = $row["qty"];

    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    $c = new Changes($nom);
    if ($new_siz != $old_siz) $c->set_siz($old_siz, $new_siz);
    if ($new_qty != $old_qty) $c->set_qty($old_qty, $new_qty);
    $changelog[] = $c;

    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
    $sql = "UPDATE myTable SET siz = $new_siz, qty = $new_qty WHERE nom = '$nom' LIMIT 1";
    $res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
}


// UPDATES COMPLETE, DUMP THE TABLE TO SHOW ALL THE NEW VALUES
$sql = "SELECT nom, siz, qty FROM myTable";
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
while ($row = mysql_fetch_assoc($res))
{
    echo PHP_EOL . "<br/>";
    var_dump($row);
}


// NOW SHOW THE CHANGELOG
foreach ($changelog as $change_object)
{
    $msg = $change_object->get_change_message();
    echo "<br/>$msg" . PHP_EOL;
}

Open in new window

0
 
Ray PaseurCommented:
To be clear about the terminology, are you saying that you want to know what COLUMNS are changed?
0
 
rgranlundAuthor Commented:
@ray

Yes, I want to know exactly which columns update.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ropennerCommented:
from:  http://dev.mysql.com/doc/refman/5.0/en/update.html
"If you set a column to the value it currently has, MySQL notices this and does not update it."

"UPDATE returns the number of rows that were actually changed."

I believe you will have to read the row yourself and do the comparison as to whether values have changed.  I've seen no indication mysql will report this for you.
0
 
Ray PaseurCommented:
I do not think MySQL has native functionality to do what you want.  Here is the general design I would make.

SELECT the row you want to update.
Iterate over the columns in this row comparing the old value to the new value.
Where they differ, record the column name, the old value and the new value (a small object is useful for this kind of thing).
After the comparison has generated the object, perform the update.
0
 
ropennerCommented:
you could do a separate update for each field and get the return value.
0
 
rgranlundAuthor Commented:
Is there a way to compare and report different values in a column prior to an up date?
0
 
Ray PaseurCommented:
Yes.  See ID:35012060
0
 
rgranlundAuthor Commented:
@ropenner: can you show me a brief example?  I'm a newbie and a little slow sometimes.
0
 
rgranlundAuthor Commented:
@Ray can you point me to a tutorial that explains what you are suggesting?  I'm not 100% clear on how to do what you are suggesting.
0
 
Ray PaseurCommented:
I've never seen or written a tutorial about it.  Do you understand OOP - like how to create classes with getters and setters?  If not, we might want to go another way and use two associative arrays.  The keys would be the column name and the values would be the old and new data.
0
 
rgranlundAuthor Commented:
We better go the two associative arrays way.  Is there a tutorials on how to write that?
0
 
Ray PaseurCommented:
No, but I can write something that would illustrate the principles.  And as I think about it, two associative arrays will not really do the job .  I'll show you the OOP notation to make an array of objects - it is a VERY useful technique.  While I am working on the code for a teaching example, buy this book.
http://www.sitepoint.com/books/phpmysql4/
0
 
rgranlundAuthor Commented:
Thank you in advance.  I will order the book, asap.
0
 
ropennerCommented:
This tell you how many rows had a certain field updated.

if the table holds the data
1      2      4      8
1      3      3      8
1      3      9      8

for fields field_id_215, field_id_29, field_id_30, andn field_id_31 respectively then the output of the code below is:

UPDATE exp_weblog SET field_id_29=3 WHERE field_id_215=1<BR>
field_id_29 changed (1 times)<BR>
UPDATE exp_weblog SET field_id_30=4 WHERE field_id_215=1<BR>
field_id_30 changed (2 times)<BR>
UPDATE exp_weblog SET field_id_31=2 WHERE field_id_215=1<BR>
field_id_31 changed (3 times)<BR>

replacing the **** with your username and password and database name

<?PHP
$database = mysql_connect('localhost', '****','****') or die (mysql_error());
mysql_select_db("****",$database) or die( "Unable to select database");

$field_id_215= 1;
$field_id_29 = 3;
$field_id_30 = 4;
$field_id_31 = 2;
foreach (array('field_id_29','field_id_30','field_id_31') as $field) {
      eval ("\$name=\$$field;");
      $query = "UPDATE exp_weblog_data SET $field=$name WHERE field_id_215=$field_id_215";      print "$q<BR>\n";
      $statement = mysql_query($query);
      $rows_changed = mysql_affected_rows();
      print "$field changed ($rows_changed times)<BR>\n";
}
?>
0
 
ropennerCommented:
NOTE:  I used integer fields for testing but if you have TEXT fields then the query changes to one with single quotes like below

      $query = "UPDATE exp_weblog_data SET $field='$name' WHERE field_id_215='$field_id_215'";

ERROR:  print "$q   should be print "$query
0
 
rgranlundAuthor Commented:
This is really great.  It will take me sometime to "own it"  but this is a really great path to start on.  Thank you very much.
0
 
Ray PaseurCommented:
Glad to help!  Thanks for the points, ~Ray
0
 
rgranlundAuthor Commented:
I am still working on this and putting it together.  As soon as I have something, I will post it.  The one thing that I did not mention is that the new data is coming in via a feed.
0
 
rgranlundAuthor Commented:
@Ray:

Could you please have a look at this and tell me if I am on the correct path:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style>
body	{
	background-color:#D9DCF0;
}

fieldste	{
	border:double 3px #333333;
}

#wrapper	{
	width:750px;
	padding:20px;
	margin:0px auto 0px auto;
}

.content	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:13px;
	font-weight:600;
	color:#333333;
}
</style>
</head>

<body>

<!--  WRAPPER  -->
<div id="wrapper" class="content">
<?php 
error_reporting(E_ALL);


include('connect/script/');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
echo "<fieldset>";
// GET THE XML FEED IN A STRING
$xml = file_get_contents($url);
 
// MAKE AN OBJECT OUT OF THE XML
$obj = SimpleXML_Load_String($xml);
 

foreach ($obj->model as $model)
{  //  FOR EACH
	
	$field_id_103 = (string)$model->prodName;
	$field_id_104 = (string)$model->prodNo;

	
	$field_id_103 = mysqli_real_escape_string ($dbc, $field_id_103);
	$field_id_104 = mysqli_real_escape_string ($dbc, $field_id_104);
	
//  END OF PARSE AND PREP


// THE DATA FOR SOME UPDATE QUERIES
$usqls   = array();
$usqls[] = array('field_id_104' => '$field_id_104', 'field_id_103' => '$field_id_103'); // CHANGE TWO FIELDS


// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS
class Changes
{
    protected $field_id_104, $old103, $new103;

    public function __construct($field_id_104)
    {
        $this->field_id_104 = $field_id_104;
        $this->old103 = NULL;
        $this->new103 = NULL;

    }

    public function set_103($old103, $new103)
    {
        $this->old103 = $old103;
        $this->new103 = $new103;
    }


    public function get_change_message()
    {
        // SET THE MESSAGE TO THE OBJECT'S nom ONLY
        $msg = $this->field_id_104;

        // ADD TO THE MESSAGE FOR ANY CHANGES
        if ($this->old103)
        {
            $msg .= "Product Name CHANGED FROM $this->old103 TO $this->new103<br />";
        }
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
        if ($msg == $this->field_id_104)
        {
            $msg .= " NO CHANGES to $this->field_id_104";
        }
        return $msg;
    }
}

// OUR CHANGELOG IS AN ARRAY OF OBJECTS
$changelog = array();


// ITERATE OVER THE UPDATE QUERY DATA
foreach ($usqls as $udata)  //  FOREACH  //
{
    $field_id_104 = mysqli_real_escape_string($dbc, $udata["field_id_104"]);
    $new103 = $udata["field_id_103"];

    // SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
    $q = "SELECT field_id_103 FROM exp_weblog_data WHERE field_id_104 = '$field_id_104' LIMIT 1";
    $r = mysqli_query($dbc, $q) or die( "$dbc<br/>" . mysql_error() );
    $row = mysqli_fetch_assoc($dbc, $r);
    $old103 = $row["field_id_103"];
echo"<h2>Hello World</h2><br />";
echo $old103;
echo "<br /><br />";
    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    $c = new Changes($field_id_104);
    if ($new103 != $old103) $c->set_103($old103, $new103);
    $changelog[] = $c;

    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
    $q = "UPDATE exp_weblog_data SET field_id_103 = $new103 WHERE field_id_104='$field_id_104' LIMIT 1";
    $r = mysqli_query($dbc, $q) or die( "$dbc<br/>" . mysql_error() );
}  //  ITERATE OVER THE UPDATE QUERY DATA  //


// UPDATES COMPLETE, DUMP THE TABLE TO SHOW ALL THE NEW VALUES
$q = "SELECT field_id_104, field_id_103 FROM exp_weblog_data";
$r = mysqli_query($dbc, $q) or die( "$q<br/>" . mysql_error() );
while ($row = mysqli_fetch_assoc($r))
{

    echo PHP_EOL . "<br/>";
    var_dump($row);
}


// NOW SHOW THE CHANGELOG
foreach ($changelog as $change_object)
{
    $msg = $change_object->get_change_message();
    echo "<br/>$msg" . PHP_EOL;
}



}  //  END OF FOREACH  //
echo "<h2>Hello World!</h2><br />";
echo "<br /><br /></fieldset>";
?>

</div>
<!--  END WRAPPER  -->
</body>
</html>

Open in new window

0
 
rgranlundAuthor Commented:
This is better Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style>
body	{
	background-color:#D9DCF0;
}

fieldste	{
	border:double 3px #333333;
}

#wrapper	{
	width:750px;
	padding:20px;
	margin:0px auto 0px auto;
}

.content	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:13px;
	font-weight:600;
	color:#333333;
}
</style>
</head>

<body>

<!--  WRAPPER  -->
<div id="wrapper" class="content">
<?php 
error_reporting(E_ALL);


include('connect.php');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
echo "<h1>Update and Report</h1><br /><br /><fieldset>";
// GET THE XML FEED IN A STRING
$xml = file_get_contents($url);
 
// MAKE AN OBJECT OUT OF THE XML
$obj = SimpleXML_Load_String($xml);
 

foreach ($obj->model as $model)
{  //  FOR EACH
	
	$field_id_103 = (string)$model->prodName;
	$field_id_104 = (string)$model->prodNo;

	
	$field_id_103 = mysqli_real_escape_string ($dbc, $field_id_103);
	$field_id_104 = mysqli_real_escape_string ($dbc, $field_id_104);
	
//  END OF PARSE AND PREP


// THE DATA FOR SOME UPDATE QUERIES
$usqls   = array();
$usqls[] = array('field_id_104' => '$field_id_104', 'field_id_103' => '$field_id_103'); // CHANGE TWO FIELDS


// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS
class Changes
{
    protected $field_id_104, $old103, $new103;

    public function __construct($field_id_104)
    {
        $this->field_id_104 = $field_id_104;
        $this->old103 = NULL;
        $this->new103 = NULL;

    }

    public function set_103($old103, $new103)
    {
        $this->old103 = $old103;
        $this->new103 = $new103;
    }


    public function get_change_message()
    {
        // SET THE MESSAGE TO THE OBJECT'S nom ONLY
        $msg = $this->field_id_104;

        // ADD TO THE MESSAGE FOR ANY CHANGES
        if ($this->old103)
        {
            $msg .= "Product Name CHANGED FROM $this->old103 TO $this->new103<br />";
        }
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
        if ($msg == $this->field_id_104)
        {
            $msg .= " NO CHANGES to $this->field_id_104";
        }
        return $msg;
    }
}

// OUR CHANGELOG IS AN ARRAY OF OBJECTS
$changelog = array();


// ITERATE OVER THE UPDATE QUERY DATA
foreach ($usqls as $udata)  //  FOREACH  //
{
    $field_id_104 = mysqli_real_escape_string($dbc, $udata["field_id_104"]);
    $new103 = $udata["field_id_103"];

    // SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
    $q = "SELECT field_id_103 FROM exp_weblog_data WHERE field_id_104 = '$field_id_104' LIMIT 1";
    $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
    $row = mysqli_fetch_assoc($dbc, $r);
    $old103 = $row["field_id_103"];


    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    $c = new Changes($field_id_104);
    if ($new103 != $old103) $c->set_103($old103, $new103);
    $changelog[] = $c;


    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
    $q = "UPDATE exp_weblog_data SET field_id_103 = $new103 WHERE field_id_104='$field_id_104' LIMIT 1";
    $r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
	if (mysqli_affected_rows($dbc)) {  //  IF DATA UPDATED  //
			echo '<fieldset>Data Updated<br /><strong>Product Number:</strong>&nbsp;';
			echo $field_id_104;
			echo "<br /></fieldset>";
	}  //  END IF DATA UPDATED  //
	else {
		echo "No Data was updated:<br />";
		echo $field_id_104;
		echo "<br />";
	}
}  //  ITERATE OVER THE UPDATE QUERY DATA  //


// UPDATES COMPLETE, DUMP THE TABLE TO SHOW ALL THE NEW VALUES
$q = "SELECT field_id_104, field_id_103 FROM exp_weblog_data";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
while ($row = mysqli_fetch_assoc($dbc, $r))
{

    echo PHP_EOL . "Hello<br/>";
    var_dump($row);
}


// NOW SHOW THE CHANGELOG
foreach ($changelog as $change_object)
{
    $msg = $change_object->get_change_message();
    echo "<br/>$msg" . PHP_EOL;
}



}  //  END OF FOREACH  //
echo "<br /><br /></fieldset>";
?>

</div>
<!--  END WRAPPER  -->
</body>
</html>

Open in new window

0
 
Ray PaseurCommented:
You might want to post a new question about this.  Line 50 is a foreach iterator.  You would not normally place a class definition inside an iterator.  Also, on line 64 you will wipe out whatever was in $usqls.  Not sure if that is what you want to do?
0
 
rgranlundAuthor Commented:
Ray, thanks again for all of you help.  I placed a new question.  I am learning a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.