SUMMARY OF WHAT I'M TRYING TO DO:
I'm simply trying to use PHP to pull the data out of a MySQL table,
"clean" the data values, then store the "cleaned" data back into the
original table (although I'm willing to create another table with
identical structure and store the cleaned data there if necessary).
The "cleaning" that I'm trying to do consists of stripping any double
quotes in the first or last character positions and stripping any
dollar signs from the value in every field in the table (including the
primary key fields). I'd prefer to store the cleaned data back into
the original row (although I'm willing to store into a new row and
delete the original row).
===================================================================
SOME MORE DETAILS:
I've been able to successfully pull the data out of the table and
strip off the unwanted characters (with the exception that I'm
stripping double quotes in all positions rather than just those in the
leading or trailing character positions). However, I can't figure out
how to store the data back into the table. Maybe I've just got syntax
errors in the lines where I'm attempting to write the SQL query, but
if so, I can't figure it out.
By the way, I want to clean the data in numerous tables, and I will
have some tables in which the primary key values are strings and
contain double quotes, although all of my attempts so far have been
with a table that has no unwanted characters in the primary key
fields. In all of my attempts, I've been unsuccessful in storing the
data back into the table.
The MySQL version that is available to me is 4.1.14. I've been using
phpMyAdmin 2.6.3-pl1 and PHP version 4.3.11.
The PHP code that I've included below shows 4 different regions of
now-commented-out code where I've unsuccessfully tried to construct
code that stores data back into the MySQL table. These 4 regions
begin with the phrases "REGION 1", "REGION 2", "REGION 3", and "REGION
4".
===================================================================
WHAT I NEED AS AN ANSWER:
I need one of 2 things:
-- To know the proper PHP/MySQL statements that will allow me to
store, back into a MySQL table, the cleaned data that my existing code
generates.
OR
-- To be given the code for a different approach that will pull the
data from the MySQL table, clean it, then store it back into the MySQL
table.
===================================================================
HERE IS THE DEFINITION AND CONTENTS OF THE TABLE THAT I'VE BEEN WORKING WITH:
CREATE TABLE YSB_Items (
Order_ID INTEGER(10) UNSIGNED NOT NULL,
Line_ID TINYINT UNSIGNED NOT NULL,
Product_ID VARCHAR(30),
Product_Code VARCHAR(30),
Quantity SMALLINT UNSIGNED NOT NULL,
Unit_Price VARCHAR(7),
PRIMARY KEY(Order_ID,Line_ID) );
DATA IN THE TABLE:
500,1,"p010","P010",1,$9.95
500,2,"Tax","Tax",1,$0.63
500,3,"Shipping","Shipping",1,$1.00
501,1,"p010","P010",1,$9.95
501,2,"Tax","Tax",1,$0.00
501,3,"Shipping","Shipping",1,$1.00
502,1,"p005","P005",1,$7.50
502,2,"Tax","Tax",1,$0.47
502,3,"Shipping","Shipping",1,$1.00
503,1,"p005","P005",1,$7.50
503,2,"Tax","Tax",1,$0.47
503,3,"Shipping","Shipping",1,$1.00
504,1,"p005","P005",1,$7.50
504,2,"Tax","Tax",1,$0.22
504,3,"Shipping","Shipping",1,$1.00
505,1,"p005","P005",1,$7.50
505,2,"Tax","Tax",1,$0.27
505,3,"Shipping","Shipping",1,$1.00
506,1,"p005","P005",1,$7.50
506,2,"Tax","Tax",1,$0.47
506,3,"Shipping","Shipping",1,$1.00
507,1,"p005","P005",1,$7.50
507,2,"p001","P001",1,$4.50
507,3,"Tax","Tax",1,$0.76
507,4,"Shipping","Shipping",1,$1.00
===================================================================
BELOW IS THE CURRENT STATE OF MY PHP CODE (It works as I expect,
except for my inability to put the cleaned data back into the table).
The output from running this code is at the very bottom:
<!-- Program: move_and_clean_YSB_store_data.php
Desc: PHP program to move data from YSB_... tables to other
tables, while doing some cleaning-up of the data as received from the
YSB store.
-->
<html>
<head><title>Move and clean data from the YSB tables.</title></head>
<body>
<?php
$host='mysql';
$user=$_POST['user'];
$password=$_POST['password'];
if (!$connection = mysql_connect($host,$user,$password))
{
$message = mysql_error();
echo '<p>';
echo $message;
die();
}
$database = 'IFYI1';
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database.");
/* --------------------------------------------------------------------*/
/* Now connected to the IFYI1 database at this point, so start queries */
/* --------------------------------------------------------------------*/
$query = 'SELECT * FROM YSB_Items';
$result = mysql_query($query, $connection)
or die("Couldn't execute query.");
$nrows = mysql_num_rows($result);
$nfields = mysql_num_fields($result);
// Echo some values so that I'm confident that they're what I expect.
echo '<p>';
echo 'Number of rows = ';
echo $nrows;
echo '<p>';
echo 'Number of fields = ';
echo $nfields;
// Create an array with entries that are the same as the column names
in the table YSB_Items.
$field_names[0] = 'Order_ID';
$field_names[] = 'Line_ID';
$field_names[] = 'Product_ID';
$field_names[] = 'Product_Code';
$field_names[] = 'Quantity';
$field_names[] = 'Unit_Price';
// Print the array element values to verify they're what I expect.
echo "<pre>";
print_r($field_names);
echo "</pre>";
for ($row_num=0; $row_num<$nrows; $row_num++)
{
$row = mysql_fetch_array($result, MYSQL_BOTH);
echo '<p>';
echo 'Processing row ';
echo $row_num;
echo ' : ';
extract($row);
// Strip double quotes and dollar signs from all fields, then store
field back into same table.
for ($field_num=0; $field_num<$nfields; $field_num++)
{
$chars_to_strip = array("\"","\$");
$replacement_chars = array("");
$cleaned_data[$field_num] = str_replace( $chars_to_strip,
$replacement_chars, $row[$field_num]);
$cleaned_data[$field_num] = trim($cleaned_data[$field_num]);
$cleaned_data[$field_num] = htmlspecialchars($cleaned_data[$field_num]);
// Echo both the original value and the cleaned value to
verify it's what I expect
echo '<p>';
echo $row[$field_num];
echo ' ';
echo $cleaned_data[$field_num];
// REGION 1:
// THIS STUFF BELOW DOESN'T WORK AS I WANT: This is my attempt to put
the cleaned value
// back into the field from which it came in the MySQL database table.
I'd prefer to
// replace the entire row in one operation rather than replace fields
column by column.
// However, I was also having no luck with the "replace an entire row
at once" approach,
// so I tried this column by column approach, which also isn't working for me.
// $query = 'UPDATE YSB_Items SET $row[$field_num]=$cleaned_data[]
// WHERE Order_ID= $row['Order_ID'] AND Line_ID=$row['Line_ID']';
// echo '<p>';
// echo $row[$field_num];
// $result = mysql_query($query, $connection)
// or die("Couldn't execute query.");
echo '<p>';
echo $field_names[$field_num];
}
/*
echo "<pre>";
print_r($cleaned_data);
echo "</pre>";
*/
// Verify that certain values are what I expected.
echo '<p>';
echo $row[0];
echo '<p>';
echo $row[1];
// REGION 2:
// ANOTHER UNSUCCESSFUL ATTEMPT AT STORING AN ENTIRE ROW AT A TIME.
// I Still Haven't been able to figure out how to store fields back
into the table!!!!!
/*
// Store fields back into table from which they came.
for ($field_num=0; $field_num<$nfields; $field_num++)
{
// $query = 'UPDATE YSB_Items SET * = $cleaned_data[$field_num]
$query = 'UPDATE YSB_Items SET $field_names[$field_num]= $cleaned_data[$field_num]
WHERE Order_ID= $row[0] AND Line_ID = $row[1]';
$result = mysql_query($query
or die("Couldn't execute query.");
}
*/
// REGION 3:
// ANOTHER UNSUCCESSFUL ATTEMPT AT STORING AN ENTIRE ROW AT A TIME.
// Create a different table to store the data values after they've been cleaned.
// Try writing the values into a table with the same column names as
the original table.
// for ($field_num=0; $field_num<$nfields; $field_num++)
// {
// $query = "INSERT YSB_Items_Cleaned $field_names[$field_num] VALUES
$cleaned_data[$field_num]";
// $result = mysql_query($query
// or die("Couldn't execute query.");
// }
// REGION 4:
// ANOTHER UNSUCCESSFUL ATTEMPT AT STORING AN ENTIRE ROW AT A TIME.
/*
$query = 'UPDATE YSB_Items SET $field_names[$field_num]= $cleaned_data[$field_num]
WHERE Order_ID= $cleaned_data[0] AND Line_ID=$cleaned_data[1]'; //
Put cleaned row into the MySQL database.
$result = mysql_query($query, $connection)
or die("Couldn't execute query.");
*/
}
?>
</body></html>
===================================================================
HERE IS THE OUTPUT THAT RESULTS FROM RUNNING THE PROGRAM (it shows
that the double quotes and dollar signs are being stripped).
Typically, if I remove the commenting symbols and try to include code
to store data back into the table, either I get a blank window as
output or I get some sensible output in the window but the data in my
MySQL table has not been altered:
Number of rows = 25
Number of fields = 6
Array
(
[0] => Order_ID
[1] => Line_ID
[2] => Product_ID
[3] => Product_Code
[4] => Quantity
[5] => Unit_Price
)
Processing row 0 :
500 500
Order_ID
1 1
Line_ID
"p010" p010
Product_ID
"P010" P010
Product_Code
1 1
Quantity
$9.95 9.95
Unit_Price
500
1
Processing row 1 :
500 500
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.63 0.63
Unit_Price
500
2
Processing row 2 :
500 500
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
500
3
Processing row 3 :
501 501
Order_ID
1 1
Line_ID
"p010" p010
Product_ID
"P010" P010
Product_Code
1 1
Quantity
$9.95 9.95
Unit_Price
501
1
Processing row 4 :
501 501
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.00 0.00
Unit_Price
501
2
Processing row 5 :
501 501
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
501
3
Processing row 6 :
502 502
Order_ID
1 1
Line_ID
"p005" p005
Product_ID
"P005" P005
Product_Code
1 1
Quantity
$7.50 7.50
Unit_Price
502
1
Processing row 7 :
502 502
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.47 0.47
Unit_Price
502
2
Processing row 8 :
502 502
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
502
3
Processing row 9 :
503 503
Order_ID
1 1
Line_ID
"p005" p005
Product_ID
"P005" P005
Product_Code
1 1
Quantity
$7.50 7.50
Unit_Price
503
1
Processing row 10 :
503 503
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.47 0.47
Unit_Price
503
2
Processing row 11 :
503 503
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
503
3
Processing row 12 :
504 504
Order_ID
1 1
Line_ID
"p005" p005
Product_ID
"P005" P005
Product_Code
1 1
Quantity
$7.50 7.50
Unit_Price
504
1
Processing row 13 :
504 504
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.22 0.22
Unit_Price
504
2
Processing row 14 :
504 504
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
504
3
Processing row 15 :
505 505
Order_ID
1 1
Line_ID
"p005" p005
Product_ID
"P005" P005
Product_Code
1 1
Quantity
$7.50 7.50
Unit_Price
505
1
Processing row 16 :
505 505
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.27 0.27
Unit_Price
505
2
Processing row 17 :
505 505
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
505
3
Processing row 18 :
506 506
Order_ID
1 1
Line_ID
"p005" p005
Product_ID
"P005" P005
Product_Code
1 1
Quantity
$7.50 7.50
Unit_Price
506
1
Processing row 19 :
506 506
Order_ID
2 2
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.47 0.47
Unit_Price
506
2
Processing row 20 :
506 506
Order_ID
3 3
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
506
3
Processing row 21 :
507 507
Order_ID
1 1
Line_ID
"p005" p005
Product_ID
"P005" P005
Product_Code
1 1
Quantity
$7.50 7.50
Unit_Price
507
1
Processing row 22 :
507 507
Order_ID
2 2
Line_ID
"p001" p001
Product_ID
"P001" P001
Product_Code
1 1
Quantity
$4.50 4.50
Unit_Price
507
2
Processing row 23 :
507 507
Order_ID
3 3
Line_ID
"Tax" Tax
Product_ID
"Tax" Tax
Product_Code
1 1
Quantity
$0.76 0.76
Unit_Price
507
3
Processing row 24 :
507 507
Order_ID
4 4
Line_ID
"Shipping" Shipping
Product_ID
"Shipping" Shipping
Product_Code
1 1
Quantity
$1.00 1.00
Unit_Price
507
4
===================================================================
THE END. |
