Solved

JSON Data Inserted To MySQL

Posted on 2013-11-26
50
3,962 Views
Last Modified: 2013-11-27
I have a json file that has 2 variables in it that I want to populate in a MySQL table.

I have put this code together by assembling bits and pieces over the internet because I am not an accomplished PHP programmer.

<?php
$jsonData = file_get_contents("http://www.abc.com/abc.json");
$phpArray = json_decode($jsonData, true);

foreach ($phpArray as $key => $value) {
    echo "<h2>$key</h2>";
    foreach ($value as $k => $v) {
        echo "$k | $v <br />";
    }
}

// @todo prepare your data, whether you harvest from text files or however.
// @todo Then set your data to the $data variable here.
 
$phpArray = array_filter($phpArray);
 
// @todo edit below with your own database details
$con = mysqli_connect("localhost","username","password","databasename");
 
// Check connection
if (mysqli_connect_errno()) {
    echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
    exit();
}
 
unset($success, $fail);
 
foreach ($phpArray as $row) {
    // @todo edit your table name, field names, and the number of $row elements
   $result = mysqli_query($con,"INSERT INTO tablename(id_product, price, wholesale_price) VALUES ('$row[2]', '$row.PriceList.RETAIL', '$row.PriceList.COST')");
        // REPLACE INTO when needed
     
    if($result){
        $success .= sprintf("%s has been entered in database.<br />", $row[0]);
     
    }else{
     
        $fail .= sprintf("%s insert failed: %s\n<br />", $row[0], mysqli_error($con));
    }
}
 
print $success;
print $fail;
 
echo '<p>End of script.</p>';
 
mysqli_close($con);
?>

Open in new window


The code runs and shows the data but in the mysql table there are only zeros in the price fields.

My JSON File that I am converting to an associative array looks like this

[
  {
    "ID": "199a2f68-66c5-4a4b-9789-3435bbb6fcb3",
    "PublishDate": "2013-08-05T22:22:01.6041246-04:00",
    "SKU": "100",
    "CheckDigit": 1,
    "Description": "Blessed Thistle (100 caps)",
    "Benifits": [
      "Supports the female reproductive system.",
      "May help improve digestion as it supports liver function.",
      "Supports the glandular system."
    ],
    "HowItWorks": "Blessed thistle supports digestion by stimulating secretions of gastric juices and saliva, and stimulating the flow of bile.",
    "Ingredients": "Blessed thistle aerial parts.",
    "RecommendedUse": "Take 2 capsules with a meal twice daily.",
    "Title": "Blessed Thistle (100 caps)",
    "Status": "Valid",
    "PriceList": [
      {
        "Type": "QV",
        "Value": "14.25"
      },
      {
        "Type": "COST",
        "Value": "14.25"
      },
      {
        "Type": "RETAIL",
        "Value": "21.40"
      }
    ],
    "ImagePath": "\\images\\100.jpg",
    "ImageLable": "\\labels\\100.jpg",
    "PDF": "\\pdfs\\100-1.pdf",
    "Category": null,
    "SubCategory": null
  },
  {
    "ID": "f84f0ff6-1399-42bc-aae9-c323e683d6c7",
    "PublishDate": "2013-08-05T22:22:05.1297307-04:00",
    "SKU": "1004",
    "CheckDigit": 3,
    "Description": "Lung Support TCM Conc. (30 caps)",
    "Benifits": [
      "May act as a tonic for the respiratory system.",
      "Maintains tissue surfaces.",
      "Supports the lungs and sinuses.",
      "Helps promote mucous membrane moisture.",
      "Offers additional benefits to the circulatory and urinary systems."
    ],
    "HowItWorks": "<P>This formula contains the same herbs as Lung Support, but in a highly concentrated blend. This Chinese combination was specially formulated to supplement the needs of a weakened metal constitution. The Chinese call this formula fu lei, which can be translated to mean “strengthen the weak and thin.” Lung Support is considered a lung tonic formula. Its primary herbs—anemarrhena, aster, bupleurum and astragalus—have been used in Traditional Chinese Medicine to boost the immune system, build energy and support the respiratory system. </P><P>This formula may be beneficial as a nutritional aid for people experiencing occasional fatigue or loss of weight. </P>",
    "Ingredients": "Concentrated extract of astragalus root, aster root, qingjiao root, platycodon root, anemarrhena rhizome, bupleurum root, lycium fruit, ophiopogon root tuber, ginseng root, tang-kuei root, atractylodes rhizome, blue citrus peel, citrus peel, schizandra fruit, typhonium rhizome and licorice root.",
    "RecommendedUse": "Take 1 capsule with a meal daily. NOTE: Pregnant or lactating women should consult their health care provider prior to taking this supplement.",
    "Title": "Lung Support TCM Conc. (30 caps)",
    "Status": "Valid",
    "PriceList": [
      {
        "Type": "QV",
        "Value": "22.15"
      },
      {
        "Type": "COST",
        "Value": "22.15"
      },
      {
        "Type": "RETAIL",
        "Value": "33.25"
      }
    ],
    "ImagePath": "\\images\\1004.jpg",
    "ImageLable": "\\labels\\1004.jpg",
    "PDF": "\\pdfs\\1004-3.pdf",
    "Category": null,
    "SubCategory": null
  },
  {
    "ID": "d3260833-80e3-4648-8a6f-ab48fce38b35",
    "PublishDate": "2013-08-05T22:22:07.4853349-04:00",
    "SKU": "1005",
    "CheckDigit": 9,
    "Description": "Blood Stimulator TCM Conc. (30 caps)",
    "Benifits": [
      "Enhances bodily function by improving blood quality and circulation.",
      "Helps strengthen the immune system.",
      "Soothes the nerves and muscles.",
      "Encourages an overall feeling of well-being.",
      "Helps maintain healthy skin complexion.",
      "Supports the circulatory system."
    ],
    "HowItWorks": "<P>This formula contains the same herbs found in Blood Stimulator but in a highly concentrated blend. In addition to the kidneys, the liver is a major facilitator of blood purity. But an effective blood cleanser must deal with all of the cleansing organs, including the colon and skin. This formula is designed to nutritionally support the blood, liver, glands and general circulation. </P><P>The Chinese call this formula <EM>bu xue</EM>, which translates as “to nurture the blood.” It is designed to strengthen a weakened wood constitution. </P><P>Blood Stimulator may improve blood quality and purity and help strengthen the immune system. Not only does Blood Stimulator help the body get rid of irritating toxins, it works to prevent more toxic buildup. This herbal combination is “adaptogenic,” meaning it has a tonic effect on the glandular system. This helps normalize hormone production to relieve the stress that comes from hormone imbalance. </P>",
    "Ingredients": "Concentrated extract of Ganoderma mushroom, lycium fruit, peony root without bark, tang-kuei root, bupleurum root, cornus fruit without seeds, curcuma root tuber, salvia root and rhizome, achyranthes root, alisma rhizome, astragalus root, atractylodes rhizome, cnidium rhizome, he shou wu root tuber, ligustrum fruit, rehmannia root tuber, cyperus rhizome and ginseng root.",
    "RecommendedUse": "Take 1 capsule with a meal daily. NOTE: Pregnant or lactating women should consult their health care provider prior to taking this supplement.",
    "Title": "Blood Stimulator TCM Conc. (30 caps)",
    "Status": "Valid",
    "PriceList": [
      {
        "Type": "QV",
        "Value": "29.80"
      },
      {
        "Type": "COST",
        "Value": "29.80"
      },
      {
        "Type": "RETAIL",
        "Value": "44.70"
      }
    ],
    "ImagePath": "\\images\\1005.jpg",
    "ImageLable": "\\labels\\1005.jpg",
    "PDF": "\\pdfs\\1005-9.pdf",
    "Category": null,
    "SubCategory": null
  },

Open in new window


It seems I am just not referencing the values correctly which is why they are zeroes.  Please fix this script so I can populate the table with the 2 prices.

Thanks,

Randal
0
Comment
Question by:sharingsunshine
  • 20
  • 17
  • 9
  • +1
50 Comments
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
replace line 30 by theses two lines of code :
       $query = "INSERT INTO tablename(id_product, price, wholesale_price) VALUES ('" . $row["ID"] . "', '" . $row["PriceList"][2]["Value"] . "', '" . $row["PriceList"][1]["Value"] . "')";
       $result = mysqli_query($con, $query);

Open in new window

0
 

Author Comment

by:sharingsunshine
Comment Utility
Made the changes but it still doesn't work.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
I'd create the SQL statement as a variable so you can examine it. You can then at least use var_dump to make sure it is exactly what you need.

$sqlStr = sprintf("INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('%s', '%g', '%g')", $row['ID'], $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);
	
//check the SQL statement to make sure it's correct:
var_dump($sqlStr); 
$result = mysqli_query($con, $sqlStr);

Open in new window

You haven't told us your datatype for the price and wholesale_price, so don't know whether you need quotes or not.

You might also want to look into prepared statements for this kind of thing
0
 

Author Comment

by:sharingsunshine
Comment Utility
the data type on both price and wholesale_price is decimal(20,6)

adding in the code you suggested I get the following warnings

string(123) "INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('199a2f68-66c5-4a4b-9789-3435bbb6fcb3', '21.4', '14.25')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 37
string(124) "INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('f84f0ff6-1399-42bc-aae9-c323e683d6c7', '33.25', '22.15')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 37
string(122) "INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('d3260833-80e3-4648-8a6f-ab48fce38b35', '44.7', '29.8')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 37
string(123) "INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('87d03c9a-c8b3-4569-9fef-370b80bd6b11', '34.3', '22.85')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 37
string(124) "INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('4dc18837-fcc6-48c8-bd17-246c2a75d029', '34.15', '22.75')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 37
string(120) "INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('fd6f1292-a983-494f-8fa4-162806494249', '28.5', '19')" 

Open in new window


looks like it is pulling in the first field in the json instead of the sku but the prices look correct.

I don't know what a prepared statement actually is.  This isn't something that will be done continually.  I am populating a DB in a new shopping cart and then once I cut over I won't need this anymore.

Still no update to the mysql table with respect to all 3 variables.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Right. For the SKU, just change the sprintf statement:

$sqlStr = sprintf("INSERT INTO tablename (id_product, price, wholesale_price) VALUES ('%s', '%g', '%g')", $row['SKU'], $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);

Open in new window

Your SQL statement is correct, so something else is wrong.

Is your table really called 'tablename'!!!!
0
 
LVL 82

Assisted Solution

by:leakim971
leakim971 earned 100 total points
Comment Utility
Work fine for me :
work
0
 

Author Comment

by:sharingsunshine
Comment Utility
I change the name of the table when I run the query.  So no, it is called something different.  However, I posted it in the last set of warning statements so its name is ps_product_test.

I changed the code because I noticed the id_product is an auto increment so no need to put the sku in that field.

$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES ('%g', '%g')", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);	
//check the SQL statement to make sure it's correct:
var_dump($sqlStr); 
$result = mysqli_query($con, $sqlStr);
       $result = mysqli_query($con, $query); 

Open in new window


When I run this code I get the following:

tring(77) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('21.4', '14.25')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 36
string(78) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('33.25', '22.15')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 36
string(76) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('44.7', '29.8')"
Warning: mysqli_query() [function.mysqli-query]: Empty query in /home/theherbs/public_html/test2_json.php on line 36
string(77) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('34.3', '22.85')"

Open in new window


so that looks real good.

however, this is what seems to be wrong

nsert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1
insert failed: Duplicate entry '4294967295' for key 1

Open in new window


and still no update to the price fields.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
could you post the schema of the table?
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
When writing code, and also when showing us code, it's vitally important that you show us accurate information. In the code you've just posted, you are running 2 different querys - one stored in $sqlStr and one stored in $query. What is stored in $query??

$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES ('%g', '%g')", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);	
//check the SQL statement to make sure it's correct:
var_dump($sqlStr); 
$result = mysqli_query($con, $sqlStr);
       $result = mysqli_query($con, $query); 

Open in new window

And when we see an error on Line 36, it means nothing to us if you only post 5 lines of code.
0
 

Author Comment

by:sharingsunshine
Comment Utility
not sure if this is the best form to send it or not

ps_product_test
Table comments: InnoDB free: 7168 kB

Column	Type	Null	Default 	Comments
id_product 	int(10)	No 	  	 
id_supplier 	int(10)	Yes 	NULL  	 
id_manufacturer 	int(10)	Yes 	NULL  	 
id_category_default 	int(10)	Yes 	NULL  	 
id_shop_default 	int(10)	No 	1  	 
id_tax_rules_group 	int(11)	No 	  	 
on_sale 	tinyint(1)	No 	0  	 
online_only 	tinyint(1)	No 	0  	 
ean13 	varchar(13)	Yes 	NULL  	 
upc 	varchar(12)	Yes 	NULL  	 
ecotax 	decimal(17,6)	No 	0.000000  	 
quantity 	int(10)	No 	0  	 
minimal_quantity 	int(10)	No 	1  	 
price 	decimal(20,6)	No 	0.000000  	 
wholesale_price 	decimal(20,6)	No 	0.000000  	 
unity 	varchar(255)	Yes 	NULL  	 
unit_price_ratio 	decimal(20,6)	No 	0.000000  	 
additional_shipping_cost 	decimal(20,2)	No 	0.00  	 
reference 	varchar(32)	Yes 	NULL  	 
supplier_reference 	varchar(32)	Yes 	NULL  	 
location 	varchar(64)	Yes 	NULL  	 
width 	decimal(20,6)	No 	0.000000  	 
height 	decimal(20,6)	No 	0.000000  	 
depth 	decimal(20,6)	No 	0.000000  	 
weight 	decimal(20,6)	No 	0.000000  	 
out_of_stock 	int(10)	No 	2  	 
quantity_discount 	tinyint(1)	Yes 	0  	 
customizable 	tinyint(2)	No 	0  	 
uploadable_files 	tinyint(4)	No 	0  	 
text_fields 	tinyint(4)	No 	0  	 
active 	tinyint(1)	No 	0  	 
redirect_type 	enum('', '404', '301', '302')	No 	  	 
id_product_redirected 	int(10)	No 	0  	 
available_for_order 	tinyint(1)	No 	1  	 
available_date 	date	No 	  	 
condition 	enum('new', 'used', 'refurbished')	No 	new  	 
show_price 	tinyint(1)	No 	1  	 
indexed 	tinyint(1)	No 	0  	 
visibility 	enum('both', 'catalog', 'search', 'none')	No 	both  	 
cache_is_pack 	tinyint(1)	No 	0  	 
cache_has_attachments 	tinyint(1)	No 	0  	 
is_virtual 	tinyint(1)	No 	0  	 
cache_default_attribute 	int(10)	Yes 	NULL  	 
date_add 	datetime	No 	  	 
date_upd 	datetime	No 	  	 
advanced_stock_management 	tinyint(1)	No 	0  	 
Indexes
Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null
PRIMARY	BTREE	Yes	No	id_product	4141	A	No
product_supplier	BTREE	No	No	id_supplier	4	A	Yes
product_manufacturer	BTREE	No	No	id_manufacturer	4	A	Yes
id_category_default	BTREE	No	No	id_category_default	4	A	Yes
indexed	BTREE	No	No	indexed	4	A	No
date_add	BTREE	No	No	date_add	13	A	No

Open new phpMyAdmin window

Open in new window


if there is a better way let me know.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Right. I think you need to show us your full page code again, with any changes you've made. If your id_product column really is auto-increment then you should never get a duplication error.

To see the table structure in full run this query:

SHOW CREATE TABLE ps_product_test

Open in new window

0
 

Author Comment

by:sharingsunshine
Comment Utility
ps_product_test 	CREATE TABLE `ps_product_test` (
 `id_product` int(10) unsigned NOT NULL auto_increment,
 `id_supplier` int(10) unsigned default NULL,
 `id_manufacturer` int(10) unsigned default NULL,
 `id_category_default` int(10) unsigned default NULL,
 `id_shop_default` int(10) unsigned NOT NULL default '1',
 `id_tax_rules_group` int(11) unsigned NOT NULL,
 `on_sale` tinyint(1) unsigned NOT NULL default '0',
 `online_only` tinyint(1) unsigned NOT NULL default '0',
 `ean13` varchar(13) default NULL,
 `upc` varchar(12) default NULL,
 `ecotax` decimal(17,6) NOT NULL default '0.000000',
 `quantity` int(10) NOT NULL default '0',
 `minimal_quantity` int(10) unsigned NOT NULL default '1',
 `price` decimal(20,6) NOT NULL default '0.000000',
 `wholesale_price` decimal(20,6) NOT NULL default '0.000000',
 `unity` varchar(255) default NULL,
 `unit_price_ratio` decimal(20,6) NOT NULL default '0.000000',
 `additional_shipping_cost` decimal(20,2) NOT NULL default '0.00',
 `reference` varchar(32) default NULL,
 `supplier_reference` varchar(32) default NULL,
 `location` varchar(64) default NULL,
 `width` decimal(20,6) NOT NULL default '0.000000',
 `height` decimal(20,6) NOT NULL default '0.000000',
 `depth` decimal(20,6) NOT NULL default '0.000000',
 `weight` decimal(20,6) NOT NULL default '0.000000',
 `out_of_stock` int(10) unsigned NOT NULL default '2',
 `quantity_discount` tinyint(1) default '0',
 `customizable` tinyint(2) NOT NULL default '0',
 `uploadable_files` tinyint(4) NOT NULL default '0',
 `text_fields` tinyint(4) NOT NULL default '0',
 `active` tinyint(1) unsigned NOT NULL default '0',
 `redirect_type` enum('','404','301','302') NOT NULL default '',
 `id_product_redirected` int(10) unsigned NOT NULL default '0',
 `available_for_order` tinyint(1) NOT NULL default '1',
 `available_date` date NOT NULL,
 `condition` enum('new','used','refurbished') NOT NULL default 'new',
 `show_price` tinyint(1) NOT NULL default '1',
 `indexed` tinyint(1) NOT NULL default '0',
 `visibility` enum('both','catalog','search','none') NOT NULL default 'both',
 `cache_is_pack` tinyint(1) NOT NULL default '0',
 `cache_has_attachments` tinyint(1) NOT NULL default '0',
 `is_virtual` tinyint(1) NOT NULL default '0',
 `cache_default_attribute` int(10) unsigned default NULL,
 `date_add` datetime NOT NULL,
 `date_upd` datetime NOT NULL,
 `advanced_stock_management` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id_product`),
 KEY `product_supplier` (`id_supplier`),
 KEY `product_manufacturer` (`id_manufacturer`),
 KEY `id_category_default` (`id_category_default`),
 KEY `indexed` (`indexed`),
 KEY `date_add` (`date_add`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8

Open in new window

0
 

Author Comment

by:sharingsunshine
Comment Utility
OK, code in full is this

<?php
$jsonData = file_get_contents("http://www.abc.com/somedata.json");
$phpArray = json_decode($jsonData, true);

foreach ($phpArray as $key => $value) {
    echo "<h2>$key</h2>";
    foreach ($value as $k => $v) {
        echo "$k | $v <br />";
    }
}

// @todo prepare your data, whether you harvest from text files or however.
// @todo Then set your data to the $data variable here.
 
$phpArray = array_filter($phpArray);
 
// @todo edit below with your own database details
$con = mysqli_connect("localhost","xxx","xxx","xxx");
 
// Check connection
if (mysqli_connect_errno()) {
    echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
    exit();
}
 
unset($success, $fail);
 
foreach ($phpArray as $row) {
    // @todo edit your table name, field names, and the number of $row elements
 //  $result = mysqli_query($con,"INSERT INTO ps_product_test (price, wholesale_price) VALUES ('$row.PriceList.RETAIL', '$row.PriceList.COST')");
//$query = "INSERT INTO ps_product_test(id_product, price, wholesale_price) VALUES ('" . $row["ID"] . "', '" . $row["PriceList"][2]["Value"] . "', '" . $row["PriceList"][1]["Value"] . "')";
$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES ('%g', '%g')", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);	
//check the SQL statement to make sure it's correct:
var_dump($sqlStr); 
$result = mysqli_query($con, $sqlStr);
       $result = mysqli_query($con, $query); 
	   // REPLACE INTO when needed
     
    if($result){
        $success .= sprintf("%s has been entered in database.<br />", $row[0]);
     
    }else{
     
        $fail .= sprintf("%s insert failed: %s\n<br />", $row[0], mysqli_error($con));
    }
}
 
print $success;
print $fail;
 
echo '<p>End of script.</p>';
 
mysqli_close($con);
?>

Open in new window

0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 400 total points
Comment Utility
OK. As per my previous comment - you're trying to run 2 queries. Remove the second one (line 36!!).

$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES ('%g', '%g')", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);	
//check the SQL statement to make sure it's correct:
var_dump($sqlStr); 
$result = mysqli_query($con, $sqlStr);
$result = mysqli_query($con, $query);  <!-- this line needs removing -->

Open in new window

As a heads up, whenever you are working on PHP scripts, always add this at the very start:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window

You will then see the errors clearly!!
0
 

Author Comment

by:sharingsunshine
Comment Utility
Ok, thanks for that tip.  I commented out that line and added the two lines at the top and now it is showing this error

string(77) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('21.4', '14.25')"
Notice: Undefined offset: 0 in /home/theherbs/public_html/test2_json.php on line 46

Notice: Undefined variable: fail in /home/theherbs/public_html/test2_json.php on line 46
string(78) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('33.25', '22.15')"
Notice: Undefined offset: 0 in /home/theherbs/public_html/test2_json.php on line 46
string(76) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('44.7', '29.8')"
Notice: Undefined offset: 0 in /home/theherbs/public_html/test2_json.php on line 46
string(77) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('34.3', '22.85')"
Notice: Undefined offset: 0 in /home/theherbs/public_html/test2_json.php on line 46
string(78) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('34.15', '22.75')"
Notice: Undefined offset: 0 in /home/theherbs/public_html/test2_json.php on line 46
string(74) "INSERT INTO ps_product_test (price, wholesale_price) VALUES ('28.5', '19')"

Open in new window


this is line 46

$fail .= sprintf("%s insert failed: %s\n<br />", $row[0], mysqli_error($con));

still get the same duplicate key error and no update on the price  fields.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
$row is an associative array, so you can't access the data like so: $row[0]

If it's the ID you're wanting to report on then it should be:

$fail .= sprintf("%s insert failed: %s\n<br />", $row['ID'], mysqli_error($con));

You'll need to do the same on line 40 and 44.

The other error you're getting is Undefined variable: fail

This is because you are trying to echo out $fail without it ever being set.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
The issue is the AUTO_INCREMENT value set with 4294967296
Use a lower number of course...

ALTER TABLE ps_product_test AUTO_INCREMENT = 5;

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I think it will be easier to eat this elephant one bite at a time, instead of trying to do everything at once when you're new to PHP.  Consider using some of the learning resources here.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

You might try deconstructing the problem a little bit.  Perhaps just these steps at first.

1. Read the JSON string
2. Decode the JSON into an object
3. Use var_dump() to look at the object
4. Use echo to print some of the strings in the object
5. Try creating a query string that inserts one of the object properties into the data base table.
6. Once you know you can insert one column into the data base, try adding another column or two.

If you build this up a little at a time, you will make fewer errors and enjoy shorter debugging paths.  Plus, you can use EE for questions along the way and learn as you go.

Best of luck with the project, ~Ray
0
 

Author Comment

by:sharingsunshine
Comment Utility
Hi Ray,

Thanks for your suggestion but I have done exactly like you suggested.  Albeit, items 1 -3 were done away from Experts-exchange. I always try to break it down into steps in most everything I do.

#4-5 is the problem and that is what I am needing direction on.  As you can read above, the var dump is showing the correct values they just aren't writing to the DB.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
Did you read my last comment?
Try to run the query in an SQL editor, it will fail until you change the AUTO_INCREMENT value
... With some warning because you have NOT NULL columns
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
I've already explained why you get undefined offsets and undefined variables! Have you read my post and fixed your problem??
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please post the json string, thanks.
0
 

Author Comment

by:sharingsunshine
Comment Utility
to leakim971

this line is commented out
$query = "INSERT INTO ps_product_test(id_product, price, wholesale_price) VALUES ('" . $row["ID"] . "', '" . $row["PriceList"][2]["Value"] . "', '" . $row["PriceList"][1]["Value"] . "')";

these are the only 2 variables I need

$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES ('%g', '%g')", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);      

so don't see need for auto increment.  Am I wrong?

To Chris

You said "The other error you're getting is Undefined variable: fail

This is because you are trying to echo out $fail without it ever being set. "

I here what you are saying but this doesn't really tell me how to fix it.  

Here is what I tried but I get an error

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
$jsonData = file_get_contents("http://www.abc.com/prestashop/nsp/vitamins.json");
$phpArray = json_decode($jsonData, true);

foreach ($phpArray as $key => $value) {
    echo "<h2>$key</h2>";
    foreach ($value as $k => $v) {
        echo "$k | $v <br />";
    }
}

// @todo prepare your data, whether you harvest from text files or however.
// @todo Then set your data to the $data variable here.
 
$phpArray = array_filter($phpArray);
 
// @todo edit below with your own database details
$con = mysqli_connect("xxx","xx","xxx","xxxx");
 
// Check connection
if (mysqli_connect_errno()) {
    echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
    exit();
}
 
unset($success, $fail);
 
foreach ($phpArray as $row) {
    // @todo edit your table name, field names, and the number of $row elements
 //  $result = mysqli_query($con,"INSERT INTO ps_product_test (price, wholesale_price) VALUES ('$row.PriceList.RETAIL', '$row.PriceList.COST')");
//$query = "INSERT INTO ps_product_test(id_product, price, wholesale_price) VALUES ('" . $row["ID"] . "', '" . $row["PriceList"][2]["Value"] . "', '" . $row["PriceList"][1]["Value"] . "')";
$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES ('%g', '%g')", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);	
//check the SQL statement to make sure it's correct:
var_dump($sqlStr); 
$result = mysqli_query($con, $sqlStr);
       //$result = mysqli_query($con, $query); 
	   // REPLACE INTO when needed
     
   /* if($result){
        $success .= sprintf("%s has been entered in database.<br />", $row[0]);
     
    }else{
     
        $fail .= sprintf("%s insert failed: %s\n<br />", $row[0], mysqli_error($con));
    }
}*/
 
//print $success;
//print $fail;
 
echo '<p>End of script.</p>';
 
mysqli_close($con);
?>

Open in new window


This is the error I am getting
Parse error: syntax error, unexpected $end in /home/theherbs/public_html/test2_json.php on line 56

line 56 is the closing tag

?>
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
@ray - JSON is in the OP
0
 

Author Comment

by:sharingsunshine
Comment Utility
the JSON string is posted above it is the second set of code I posted.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Line 48 - you've commented out the closing curly bracket for the foreach() loop.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
so don't see need for auto increment.  Am I wrong?

Yes, your ID and primary key have the auto increment column

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28304890.html#a39680644
Line 54 :
) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8

That's why it insert the first time, but not the next time.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
The whole point of an auto-increment field is that a value is automatically set. Your database is currently set to make the next auto ID 4294967296. This is not possible because the largest value you can store in an INT column is 4294967295

leakim's code was aimed at resetting the next available ID and prevent even more errors from creeping in. If you don't run it, you can't insert any more records.
0
 

Author Comment

by:sharingsunshine
Comment Utility
I ran the sql as you suggested but still no update to the DB.  

By the way, the duplicate key error went away, before the sql, when I commented out $fail and fixed the extra bracket as Chris pointed out.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Just for fun, run this code in it's entirety. The only changes you should make are to the database connection string and the JSON file location - NOTHING ELSE!!!

<?php
//turn error reporting on
error_reporting(E_ALL);
ini_set('display_errors', 1);

//get the JSON file
$jsonData = file_get_contents("data.json");
$phpArray = json_decode($jsonData, true);

//connect to the database
$con = mysqli_connect("localhost","username","password","database");
if (mysqli_connect_errno()) {
	echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
	exit();
};

//loop through the JSON data
foreach ($phpArray as $row) {
	//prepare the SQL Statement
	$sqlStr = sprintf("INSERT INTO ps_product_test (price, wholesale_price) VALUES (%g, %g)", $row['PriceList'][2]['Value'], $row['PriceList'][1]['Value']);
	
	//check the sqlStatement to see if it's correct:
	var_dump($sqlStr); 

	//run the query
	$result = mysqli_query($con, $sqlStr);
	
	//check the outcome
	if ($result):
		printf("<p>Insert Done, ID: %d</p>", mysqli_insert_id($con));
	else:
		printf("<p>Insert failed: %s</p>", mysqli_error($con));
	endif;
}
 
//sign off
echo '<p>End of script.</p>';
?>

Open in new window

Run it and report back...
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
This is why I asked for the JSON string.
http://www.laprbass.com/RAY_temp_sharingsubshine.php

Outputs: NULL

<?php // RAY_temp_sharingsunshine.php
error_reporting(E_ALL);


// DEMONSTRATE JSON TO DB QUERY
// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28304890.html

// COPY / PASTE FROM EXPERTS-EXCHANGE
$jso = <<<EOD
[
  {
    "ID": "199a2f68-66c5-4a4b-9789-3435bbb6fcb3",
    "PublishDate": "2013-08-05T22:22:01.6041246-04:00",
    "SKU": "100",
    "CheckDigit": 1,
    "Description": "Blessed Thistle (100 caps)",
    "Benifits": [
      "Supports the female reproductive system.",
      "May help improve digestion as it supports liver function.",
      "Supports the glandular system."
    ],
    "HowItWorks": "Blessed thistle supports digestion by stimulating secretions of gastric juices and saliva, and stimulating the flow of bile.",
    "Ingredients": "Blessed thistle aerial parts.",
    "RecommendedUse": "Take 2 capsules with a meal twice daily.",
    "Title": "Blessed Thistle (100 caps)",
    "Status": "Valid",
    "PriceList": [
      {
        "Type": "QV",
        "Value": "14.25"
      },
      {
        "Type": "COST",
        "Value": "14.25"
      },
      {
        "Type": "RETAIL",
        "Value": "21.40"
      }
    ],
    "ImagePath": "\\images\\100.jpg",
    "ImageLable": "\\labels\\100.jpg",
    "PDF": "\\pdfs\\100-1.pdf",
    "Category": null,
    "SubCategory": null
  },
  {
    "ID": "f84f0ff6-1399-42bc-aae9-c323e683d6c7",
    "PublishDate": "2013-08-05T22:22:05.1297307-04:00",
    "SKU": "1004",
    "CheckDigit": 3,
    "Description": "Lung Support TCM Conc. (30 caps)",
    "Benifits": [
      "May act as a tonic for the respiratory system.",
      "Maintains tissue surfaces.",
      "Supports the lungs and sinuses.",
      "Helps promote mucous membrane moisture.",
      "Offers additional benefits to the circulatory and urinary systems."
    ],
    "HowItWorks": "<P>This formula contains the same herbs as Lung Support, but in a highly concentrated blend. This Chinese combination was specially formulated to supplement the needs of a weakened metal constitution. The Chinese call this formula fu lei, which can be translated to mean “strengthen the weak and thin.” Lung Support is considered a lung tonic formula. Its primary herbs—anemarrhena, aster, bupleurum and astragalus—have been used in Traditional Chinese Medicine to boost the immune system, build energy and support the respiratory system. </P><P>This formula may be beneficial as a nutritional aid for people experiencing occasional fatigue or loss of weight. </P>",
    "Ingredients": "Concentrated extract of astragalus root, aster root, qingjiao root, platycodon root, anemarrhena rhizome, bupleurum root, lycium fruit, ophiopogon root tuber, ginseng root, tang-kuei root, atractylodes rhizome, blue citrus peel, citrus peel, schizandra fruit, typhonium rhizome and licorice root.",
    "RecommendedUse": "Take 1 capsule with a meal daily. NOTE: Pregnant or lactating women should consult their health care provider prior to taking this supplement.",
    "Title": "Lung Support TCM Conc. (30 caps)",
    "Status": "Valid",
    "PriceList": [
      {
        "Type": "QV",
        "Value": "22.15"
      },
      {
        "Type": "COST",
        "Value": "22.15"
      },
      {
        "Type": "RETAIL",
        "Value": "33.25"
      }
    ],
    "ImagePath": "\\images\\1004.jpg",
    "ImageLable": "\\labels\\1004.jpg",
    "PDF": "\\pdfs\\1004-3.pdf",
    "Category": null,
    "SubCategory": null
  },
  {
    "ID": "d3260833-80e3-4648-8a6f-ab48fce38b35",
    "PublishDate": "2013-08-05T22:22:07.4853349-04:00",
    "SKU": "1005",
    "CheckDigit": 9,
    "Description": "Blood Stimulator TCM Conc. (30 caps)",
    "Benifits": [
      "Enhances bodily function by improving blood quality and circulation.",
      "Helps strengthen the immune system.",
      "Soothes the nerves and muscles.",
      "Encourages an overall feeling of well-being.",
      "Helps maintain healthy skin complexion.",
      "Supports the circulatory system."
    ],
    "HowItWorks": "<P>This formula contains the same herbs found in Blood Stimulator but in a highly concentrated blend. In addition to the kidneys, the liver is a major facilitator of blood purity. But an effective blood cleanser must deal with all of the cleansing organs, including the colon and skin. This formula is designed to nutritionally support the blood, liver, glands and general circulation. </P><P>The Chinese call this formula <EM>bu xue</EM>, which translates as “to nurture the blood.” It is designed to strengthen a weakened wood constitution. </P><P>Blood Stimulator may improve blood quality and purity and help strengthen the immune system. Not only does Blood Stimulator help the body get rid of irritating toxins, it works to prevent more toxic buildup. This herbal combination is “adaptogenic,” meaning it has a tonic effect on the glandular system. This helps normalize hormone production to relieve the stress that comes from hormone imbalance. </P>",
    "Ingredients": "Concentrated extract of Ganoderma mushroom, lycium fruit, peony root without bark, tang-kuei root, bupleurum root, cornus fruit without seeds, curcuma root tuber, salvia root and rhizome, achyranthes root, alisma rhizome, astragalus root, atractylodes rhizome, cnidium rhizome, he shou wu root tuber, ligustrum fruit, rehmannia root tuber, cyperus rhizome and ginseng root.",
    "RecommendedUse": "Take 1 capsule with a meal daily. NOTE: Pregnant or lactating women should consult their health care provider prior to taking this supplement.",
    "Title": "Blood Stimulator TCM Conc. (30 caps)",
    "Status": "Valid",
    "PriceList": [
      {
        "Type": "QV",
        "Value": "29.80"
      },
      {
        "Type": "COST",
        "Value": "29.80"
      },
      {
        "Type": "RETAIL",
        "Value": "44.70"
      }
    ],
    "ImagePath": "\\images\\1005.jpg",
    "ImageLable": "\\labels\\1005.jpg",
    "PDF": "\\pdfs\\1005-9.pdf",
    "Category": null,
    "SubCategory": null
  },
EOD;

$obj = json_decode($jso);
var_dump($obj);

Open in new window

0
 

Author Comment

by:sharingsunshine
Comment Utility
that's all I changed and I get this

string(73) "INSERT INTO ps_product_test (price, wholesale_price) VALUES (21.4, 14.25)"

Insert failed: Duplicate entry '4294967295' for key 1
string(74) "INSERT INTO ps_product_test (price, wholesale_price) VALUES (33.25, 22.15)"

Insert failed: Duplicate entry '4294967295' for key 1
string(72) "INSERT INTO ps_product_test (price, wholesale_price) VALUES (44.7, 29.8)"

Insert failed: Duplicate entry '4294967295' for key 1
string(73) "INSERT INTO ps_product_test (price, wholesale_price) VALUES (34.3, 22.85)"

Insert failed: Duplicate entry '4294967295' for key 1
0
 

Author Comment

by:sharingsunshine
Comment Utility
Hi Ray,

I am not sure what you have realized.
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
The error is : Insert failed: Duplicate entry '4294967295' for key 1

'4294967295', the AUTO_INCREMENT

I'm not sure your successfully run the script :
ALTER TABLE ps_product_test AUTO_INCREMENT = 5;

Open in new window


double check the schema of the table to be sure
SHOW CREATE TABLE ps_product_test;

Open in new window

0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Follow leakim's advice, and read my previous comments about the maximum value for an INT column:

Your auto-increment column is set to make the next ID too big for the column, so instead of inserting a value that's too big it tries to insert the maximum value - 4294967295 - over and over again, creating duplicate keys - run leakims code and then run my code and you'll be fine.

Leakim's code is assuming you only have records in your table with ID of 1-4 (therweby setting the next auto value to 5) - check your table and adjust accordingly

@Ray - the JSON posted was missing the closing square bracket (and had a extra comma) but that's obviously not the problem as the loop over the JSON data is working fine!
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@Chris:  That looked obvious to me and I tried that already.  It's still not valid JSON.  I think if I have some test data I can be more productive than when I have to guess at what's contained in the test data!

I'll leave it to you guys.  Best of luck, ~Ray
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
Leakim's code is assuming you only have records in your table with ID of 1-4 (therweby setting the next auto value to 5) - check your table and adjust accordingly

Open in new window


You right.
As it's a test table, truncate it !
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
@Ray - changing the trailing comma to a closing square bracket does give valid JSON!
0
 
LVL 82

Expert Comment

by:leakim971
Comment Utility
My file is attached. I added the missing bracket at the end.
abc.json.txt
0
 

Author Comment

by:sharingsunshine
Comment Utility
I ran the sql again and the schema shows it isn't taking.  Could the syntax be wrong?

CREATE TABLE `ps_product_test` (
 `id_product` int(10) unsigned NOT NULL auto_increment,
 `id_supplier` int(10) unsigned default NULL,
 `id_manufacturer` int(10) unsigned default NULL,
 `id_category_default` int(10) unsigned default NULL,
 `id_shop_default` int(10) unsigned NOT NULL default '1',
 `id_tax_rules_group` int(11) unsigned NOT NULL,
 `on_sale` tinyint(1) unsigned NOT NULL default '0',
 `online_only` tinyint(1) unsigned NOT NULL default '0',
 `ean13` varchar(13) default NULL,
 `upc` varchar(12) default NULL,
 `ecotax` decimal(17,6) NOT NULL default '0.000000',
 `quantity` int(10) NOT NULL default '0',
 `minimal_quantity` int(10) unsigned NOT NULL default '1',
 `price` decimal(20,6) NOT NULL default '0.000000',
 `wholesale_price` decimal(20,6) NOT NULL default '0.000000',
 `unity` varchar(255) default NULL,
 `unit_price_ratio` decimal(20,6) NOT NULL default '0.000000',
 `additional_shipping_cost` decimal(20,2) NOT NULL default '0.00',
 `reference` varchar(32) default NULL,
 `supplier_reference` varchar(32) default NULL,
 `location` varchar(64) default NULL,
 `width` decimal(20,6) NOT NULL default '0.000000',
 `height` decimal(20,6) NOT NULL default '0.000000',
 `depth` decimal(20,6) NOT NULL default '0.000000',
 `weight` decimal(20,6) NOT NULL default '0.000000',
 `out_of_stock` int(10) unsigned NOT NULL default '2',
 `quantity_discount` tinyint(1) default '0',
 `customizable` tinyint(2) NOT NULL default '0',
 `uploadable_files` tinyint(4) NOT NULL default '0',
 `text_fields` tinyint(4) NOT NULL default '0',
 `active` tinyint(1) unsigned NOT NULL default '0',
 `redirect_type` enum('','404','301','302') NOT NULL default '',
 `id_product_redirected` int(10) unsigned NOT NULL default '0',
 `available_for_order` tinyint(1) NOT NULL default '1',
 `available_date` date NOT NULL,
 `condition` enum('new','used','refurbished') NOT NULL default 'new',
 `show_price` tinyint(1) NOT NULL default '1',
 `indexed` tinyint(1) NOT NULL default '0',
 `visibility` enum('both','catalog','search','none') NOT NULL default 'both',
 `cache_is_pack` tinyint(1) NOT NULL default '0',
 `cache_has_attachments` tinyint(1) NOT NULL default '0',
 `is_virtual` tinyint(1) NOT NULL default '0',
 `cache_default_attribute` int(10) unsigned default NULL,
 `date_add` datetime NOT NULL,
 `date_upd` datetime NOT NULL,
 `advanced_stock_management` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id_product`),
 KEY `product_supplier` (`id_supplier`),
 KEY `product_manufacturer` (`id_manufacturer`),
 KEY `id_category_default` (`id_category_default`),
 KEY `indexed` (`indexed`),
 KEY `date_add` (`date_add`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8

Open in new window

0
 

Author Comment

by:sharingsunshine
Comment Utility
when the auto_increment sql is inserted only the ALTER TABLE command is colored the AUTO_INCREMENT is in black.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
if you're using PHPMyAdmin, select your table, and then click on the Operations tab across the top. You should see a panel called Table Options - change the AUTO INCREMENT value and click on the Go button.

You still haven't confirmed whether you already have records in your table. You may want to delete them first and start with an empty table
0
 

Author Comment

by:sharingsunshine
Comment Utility
all I have is sample data that is in this table from when the shopping cart was installed.  Only 7 records.

Tell me how to get rid of them and I can do that.

I don't see table options as a panel.  I have enclosed a screenshot to show you what I am seeing.table-options.tiff
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
You need to select your Table from the left panel and then you'll see the options.

To delete all the records from your table, run this query:

DELETE FROM ps_product_test;
0
 

Author Comment

by:sharingsunshine
Comment Utility
using the code you supplied one record only is updated and the id_product = 4294967295

and the two prices are updated correctly.

Where did all the other records go?
0
 

Author Comment

by:sharingsunshine
Comment Utility
here is the schema
CREATE TABLE `ps_product_test` (
 `id_product` int(10) unsigned NOT NULL auto_increment,
 `id_supplier` int(10) unsigned default NULL,
 `id_manufacturer` int(10) unsigned default NULL,
 `id_category_default` int(10) unsigned default NULL,
 `id_shop_default` int(10) unsigned NOT NULL default '1',
 `id_tax_rules_group` int(11) unsigned NOT NULL,
 `on_sale` tinyint(1) unsigned NOT NULL default '0',
 `online_only` tinyint(1) unsigned NOT NULL default '0',
 `ean13` varchar(13) default NULL,
 `upc` varchar(12) default NULL,
 `ecotax` decimal(17,6) NOT NULL default '0.000000',
 `quantity` int(10) NOT NULL default '0',
 `minimal_quantity` int(10) unsigned NOT NULL default '1',
 `price` decimal(20,6) NOT NULL default '0.000000',
 `wholesale_price` decimal(20,6) NOT NULL default '0.000000',
 `unity` varchar(255) default NULL,
 `unit_price_ratio` decimal(20,6) NOT NULL default '0.000000',
 `additional_shipping_cost` decimal(20,2) NOT NULL default '0.00',
 `reference` varchar(32) default NULL,
 `supplier_reference` varchar(32) default NULL,
 `location` varchar(64) default NULL,
 `width` decimal(20,6) NOT NULL default '0.000000',
 `height` decimal(20,6) NOT NULL default '0.000000',
 `depth` decimal(20,6) NOT NULL default '0.000000',
 `weight` decimal(20,6) NOT NULL default '0.000000',
 `out_of_stock` int(10) unsigned NOT NULL default '2',
 `quantity_discount` tinyint(1) default '0',
 `customizable` tinyint(2) NOT NULL default '0',
 `uploadable_files` tinyint(4) NOT NULL default '0',
 `text_fields` tinyint(4) NOT NULL default '0',
 `active` tinyint(1) unsigned NOT NULL default '0',
 `redirect_type` enum('','404','301','302') NOT NULL default '',
 `id_product_redirected` int(10) unsigned NOT NULL default '0',
 `available_for_order` tinyint(1) NOT NULL default '1',
 `available_date` date NOT NULL,
 `condition` enum('new','used','refurbished') NOT NULL default 'new',
 `show_price` tinyint(1) NOT NULL default '1',
 `indexed` tinyint(1) NOT NULL default '0',
 `visibility` enum('both','catalog','search','none') NOT NULL default 'both',
 `cache_is_pack` tinyint(1) NOT NULL default '0',
 `cache_has_attachments` tinyint(1) NOT NULL default '0',
 `is_virtual` tinyint(1) NOT NULL default '0',
 `cache_default_attribute` int(10) unsigned default NULL,
 `date_add` datetime NOT NULL,
 `date_upd` datetime NOT NULL,
 `advanced_stock_management` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id_product`),
 KEY `product_supplier` (`id_supplier`),
 KEY `product_manufacturer` (`id_manufacturer`),
 KEY `id_category_default` (`id_category_default`),
 KEY `indexed` (`indexed`),
 KEY `date_add` (`date_add`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8

Open in new window


It is still showing the auto increment didn't stick.  I am checking to see if I have the latest phpMYAdmin since that could be causing the increment issues.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
No disrespect intended here - but have you considered hiring a professional web developer to work on this. We're 46 comments in and no closer to a solution. Even the very basics seem beyond you.

A professional with access to your system would have nailed this in 10 minutes flat, probably costing you a couple of beers and a cake :)

The fact that you're stuggling with your table, for the sake of testing your PHP script (and our sanity) just create a new table containing 3 fields - id, price, wholesale price and work with that:

CREATE TABLE `ps_product_test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(20,6) DEFAULT NULL,
  `wholesale_price` decimal(20,6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Open in new window

0
 

Author Comment

by:sharingsunshine
Comment Utility
Chris,

Sometimes the most obvious solutions are the ones that are overlooked.  I created another table with the following commands:

CREATE TABLE new_table_name LIKE old_table_name;

INSERT INTO new_table_name SELECT * FROM `old_table_name`;

This put the sample data in and set the auto increment to 8.  I ran the original code that I started with and ti updated the price fields without any problems.   For some reason the code misfires we ran against the table caused the auto increment to be 4294967296.

For your information, I do have a web programmer I use but he is out of pocket right now and that is why I signed up with Experts-Exchange.

Anyway, this is solved and I appreciate your help and the others.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Excellent - glad we got you there in the end :)
0
 

Author Comment

by:sharingsunshine
Comment Utility
me too
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

There are a couple ways to attach a JavaScript function to dynamically created elements. You can make a new script for each element as it’s created or you can use delegation. Delegation allows a single script that is added at page creation to mat…
How to build a simple, quick and effective accordion menu using just 15 lines of jQuery and 2 css classes
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now