By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,979 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,979 IT Pros & Developers. It's quick & easy.

Help with this code - parse insert into Mysql

P: n/a
Can someone help me with this code - I'm trying to retrieve updated
product information by pulling 3 fields and inserting values into my
MYSQL db. In my code below I'm getting the page but I can't get the
date inserted into the DB what am I doing wrong.

Second Can I suppress output to screen. I'm planning on running this
in cron if I'm inserting into db do I have to output to screen
This is the method my host company told me to use. they gave me the
Curl portion of the code which I modified.

Thanks
Steve
<?PHP

#$URL="https://shop.xxxxxxx.com/feeds/productdownload.php?
#fields=sku,qty,price&delim=csv&ohtime=1212278400& invtype=st#ock&id=MPB_MzUxxxxxU4MzQyNzk5";
(Hidden for security #reasons)
#Output looks like this
10001,0,822.51 10002,1,320.86 10003,1,498.91 10004,0,288.77
10005,1,573.91 10006,19,52.50 10007,1,822.51 10008,2,129.89
10010,0,15.63 10011,35,64.50 10012,5,445.65 10013,1,572.99
10014,13,23.53 10015,1,50.00 10016,5,12.99 10017,30,15.60 10018,9,8.24
10019,2,795.46 10020,222,2.40 10021,6,24.38 10024,4,24.38
10025,0,82.50 10026,4,14.38 10027,1,141.25 10030,1,214.12
10036,0,352.02 10043,4,27.50 10046,60,104.51 10047,4,955.37
10054,4,193.80 10122,6,409.50 10123,6,507.50 10127,6,483.00
10141,10,199.50 10147,0,7.50 10149,2,37.10 10156,8,63.75 10157,1,7.64
10158,0,29.83 10167,66,21.73 10169,8,36.47 10171,0,182.06
10177,23,12.00 10178,0,52.50 10179,1,16.50 10183,2,16.00 10195,0,27.00
10207,90,20.17 10245,0,55.29 10278,2,25.60 10284,3,16.98
10288,4,100.00 10333,2,84.99 10341,6,21.08 10342,3,125.31

if (isset($_GET["site"])) { $URL = $_GET["site"]; }
$ch = curl_init();
#"echo "URL = $URL <br>n";
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt ($ch, CURLOPT_HTTPPROXYTUNNEL, TRUE);
curl_setopt ($ch, CURLOPT_PROXYTYPE, CURLPROXY_HTTP);
curl_setopt ($ch, CURLOPT_PROXY,"http://proxy.shr.secureserver.net:
3128");
curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt ($ch, CURLOPT_URL, $URL);
curl_setopt ($ch, CURLOPT_TIMEOUT, 120);

$result = curl_exec ($ch);
# Variables that need to be set by you.
#$file = "https://shop.cwrelectronics.com/feeds/productdownload.php?
fields=sku,qty,price&delim=csv&ohtime=1212278400&i nvtype=stock&id=MPB_MzU4MzQyMzU4MzQyNzk5";
$hostname = "xxxx.secureserver.net";
$username = "xxxxxxx";
$password = "xxxxxx";
$dbname = "xxxxxxxx";
$table = "Inv_Refresh";
$field1 = "Field1";
$field2 = "Field2";
$field3 = "Field3";
$field4 = "Field4";
$field4m = 0.1777;

# No need to change anything here
mysql_connect($hostname,$username,$password);
mysql_select_db($dbname);
$result = str_replace("\r","",$result);
$lines = explode("\n",$result);

foreach ($lines as $line) {
$values = explode(",",$line);
$sql = "REPLACE INTO $table ($field1,$field2,$field3,$field4) VALUES
('$values[0]','$values[1]','$values[2]','".($values[2] *
$field4m)."');";
mysql_query($sql);
}
curl_close ($ch);
?>
Jul 26 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Jul 26, 1:02 pm, Steve <dafella...@yahoo.comwrote:
Can someone help me with this code - I'm trying to retrieve updated
product information by pulling 3 fields and inserting values into my
MYSQL db. In my code below I'm getting the page but I can't get the
date inserted into the DB what am I doing wrong.

Second Can I suppress output to screen. I'm planning on running this
in cron if I'm inserting into db do I have to output to screen
This is the method my host company told me to use. they gave me the
Curl portion of the code which I modified.

Thanks
Steve
Yes, I'm sure lots of people can solve the problem - but you need to
take a look at why you're not able to work this out for yourself.

You should read this:

http://www.php.net/errorfunc

then this:

http://www.catb.org/~esr/faqs/smart-questions.html

Then if you're still stuck, apply what you've learnt to a new usenet
post.

C.
Jul 27 '08 #2

P: n/a
Steve wrote:
Can someone help me with this code - I'm trying to retrieve updated
product information by pulling 3 fields and inserting values into my
MYSQL db. In my code below I'm getting the page but I can't get the
date inserted into the DB what am I doing wrong.
"I can't get the date inserted" isn't very clear. What's the SQL
statement you're trying to use? What's the error you get (from MySQL or
PHP)?

Is this even a PHP problem? It looks more like a SQL problem - in which
case you should be asking on comp.databases.mysql.
Second Can I suppress output to screen. I'm planning on running this
in cron if I'm inserting into db do I have to output to screen
This is the method my host company told me to use. they gave me the
Curl portion of the code which I modified.
You never "have" to output anything to the screen.
Thanks
Steve
<?PHP

#$URL="https://shop.xxxxxxx.com/feeds/productdownload.php?
#fields=sku,qty,price&delim=csv&ohtime=1212278400& invtype=st#ock&id=MPB_MzUxxxxxU4MzQyNzk5";
(Hidden for security #reasons)
#Output looks like this
10001,0,822.51 10002,1,320.86 10003,1,498.91 10004,0,288.77
10005,1,573.91 10006,19,52.50 10007,1,822.51 10008,2,129.89
10010,0,15.63 10011,35,64.50 10012,5,445.65 10013,1,572.99
10014,13,23.53 10015,1,50.00 10016,5,12.99 10017,30,15.60 10018,9,8.24
10019,2,795.46 10020,222,2.40 10021,6,24.38 10024,4,24.38
10025,0,82.50 10026,4,14.38 10027,1,141.25 10030,1,214.12
10036,0,352.02 10043,4,27.50 10046,60,104.51 10047,4,955.37
10054,4,193.80 10122,6,409.50 10123,6,507.50 10127,6,483.00
10141,10,199.50 10147,0,7.50 10149,2,37.10 10156,8,63.75 10157,1,7.64
10158,0,29.83 10167,66,21.73 10169,8,36.47 10171,0,182.06
10177,23,12.00 10178,0,52.50 10179,1,16.50 10183,2,16.00 10195,0,27.00
10207,90,20.17 10245,0,55.29 10278,2,25.60 10284,3,16.98
10288,4,100.00 10333,2,84.99 10341,6,21.08 10342,3,125.31

if (isset($_GET["site"])) { $URL = $_GET["site"]; }
$ch = curl_init();
#"echo "URL = $URL <br>n";
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt ($ch, CURLOPT_HTTPPROXYTUNNEL, TRUE);
curl_setopt ($ch, CURLOPT_PROXYTYPE, CURLPROXY_HTTP);
curl_setopt ($ch, CURLOPT_PROXY,"http://proxy.shr.secureserver.net:
3128");
curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt ($ch, CURLOPT_URL, $URL);
curl_setopt ($ch, CURLOPT_TIMEOUT, 120);

$result = curl_exec ($ch);
# Variables that need to be set by you.
#$file = "https://shop.cwrelectronics.com/feeds/productdownload.php?
fields=sku,qty,price&delim=csv&ohtime=1212278400&i nvtype=stock&id=MPB_MzU4MzQyMzU4MzQyNzk5";
$hostname = "xxxx.secureserver.net";
$username = "xxxxxxx";
$password = "xxxxxx";
$dbname = "xxxxxxxx";
$table = "Inv_Refresh";
$field1 = "Field1";
$field2 = "Field2";
$field3 = "Field3";
$field4 = "Field4";
$field4m = 0.1777;

# No need to change anything here
mysql_connect($hostname,$username,$password);
mysql_select_db($dbname);
$result = str_replace("\r","",$result);
$lines = explode("\n",$result);

foreach ($lines as $line) {
$values = explode(",",$line);
$sql = "REPLACE INTO $table ($field1,$field2,$field3,$field4) VALUES
('$values[0]','$values[1]','$values[2]','".($values[2] *
$field4m)."');";
mysql_query($sql);
}
curl_close ($ch);
?>

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jul 27 '08 #3

P: n/a
Steve wrote:
Can someone help me with this code - I'm trying to retrieve updated
product information by pulling 3 fields and inserting values into my
MYSQL db. In my code below I'm getting the page but I can't get the
date inserted into the DB what am I doing wrong.

Second Can I suppress output to screen. I'm planning on running this
in cron if I'm inserting into db do I have to output to screen
This is the method my host company told me to use. they gave me the
Curl portion of the code which I modified.

Thanks
Steve
<?PHP

#$URL="https://shop.xxxxxxx.com/feeds/productdownload.php?
#fields=sku,qty,price&delim=csv&ohtime=1212278400& invtype=st#ock&id=MPB_MzUxxxxxU4MzQyNzk5";
(Hidden for security #reasons)
#Output looks like this
10001,0,822.51 10002,1,320.86 10003,1,498.91 10004,0,288.77
10005,1,573.91 10006,19,52.50 10007,1,822.51 10008,2,129.89
10010,0,15.63 10011,35,64.50 10012,5,445.65 10013,1,572.99
10014,13,23.53 10015,1,50.00 10016,5,12.99 10017,30,15.60 10018,9,8.24
10019,2,795.46 10020,222,2.40 10021,6,24.38 10024,4,24.38
10025,0,82.50 10026,4,14.38 10027,1,141.25 10030,1,214.12
10036,0,352.02 10043,4,27.50 10046,60,104.51 10047,4,955.37
10054,4,193.80 10122,6,409.50 10123,6,507.50 10127,6,483.00
10141,10,199.50 10147,0,7.50 10149,2,37.10 10156,8,63.75 10157,1,7.64
10158,0,29.83 10167,66,21.73 10169,8,36.47 10171,0,182.06
10177,23,12.00 10178,0,52.50 10179,1,16.50 10183,2,16.00 10195,0,27.00
10207,90,20.17 10245,0,55.29 10278,2,25.60 10284,3,16.98
10288,4,100.00 10333,2,84.99 10341,6,21.08 10342,3,125.31

if (isset($_GET["site"])) { $URL = $_GET["site"]; }
$ch = curl_init();
#"echo "URL = $URL <br>n";
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt ($ch, CURLOPT_HTTPPROXYTUNNEL, TRUE);
curl_setopt ($ch, CURLOPT_PROXYTYPE, CURLPROXY_HTTP);
curl_setopt ($ch, CURLOPT_PROXY,"http://proxy.shr.secureserver.net:
3128");
curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt ($ch, CURLOPT_URL, $URL);
curl_setopt ($ch, CURLOPT_TIMEOUT, 120);

$result = curl_exec ($ch);
# Variables that need to be set by you.
#$file = "https://shop.cwrelectronics.com/feeds/productdownload.php?
fields=sku,qty,price&delim=csv&ohtime=1212278400&i nvtype=stock&id=MPB_MzU4MzQyMzU4MzQyNzk5";
$hostname = "xxxx.secureserver.net";
$username = "xxxxxxx";
$password = "xxxxxx";
$dbname = "xxxxxxxx";
$table = "Inv_Refresh";
$field1 = "Field1";
$field2 = "Field2";
$field3 = "Field3";
$field4 = "Field4";
$field4m = 0.1777;

# No need to change anything here
mysql_connect($hostname,$username,$password);
mysql_select_db($dbname);
$result = str_replace("\r","",$result);
$lines = explode("\n",$result);

foreach ($lines as $line) {
$values = explode(",",$line);
$sql = "REPLACE INTO $table ($field1,$field2,$field3,$field4) VALUES
('$values[0]','$values[1]','$values[2]','".($values[2] *
$field4m)."');";
mysql_query($sql);
}
curl_close ($ch);
?>
My suggestion is to break up your code into smaller pieces (processes)
and debug each piece one at a time. Your code is rather hard to follow
in what you're trying to accomplish so with each piece comment the code
with the function (purpose) of the code and the desired result.

Also, you need to explain (in a repost) from what this code is being
invoked (shell/Command-Line, webpage, Windows, Unix, ect...) as it will
react differently in each case. It seems to me from reading your post
that you're invoking this from the command-Line since you are going to
use cron to invoke it.

As hard as the devel team has tried the command-line functionality of
PHP is, in my opinion, not fully finished and leaves me wanting in most
cases when I use it solely as command-line script. As a result of this I
will generally use is as a "shell-out" within ksh or bash. Using the two
in concert with each other (using ksh as the main script and then
shelling-out to php) compliments the others short comings.

One more suggestion, if you can clarify the purpose of the script and
the desired result there maybe a possibility that there is a better
solution to using MySQL. There are better databases out there that
handle command-line applications better than MySQL. SQLite comes to my
mind right away.

Try these suggestions and repost.
Carl
--

Carl Lindgren
C. R. Lindgren Consulting | Business on the Desktop

****
To Reply Remove the (*) from the "reply to"
Jul 28 '08 #4

P: n/a
Steve wrote:
Can someone help me with this code - I'm trying to retrieve updated
product information by pulling 3 fields and inserting values into my
MYSQL db. In my code below I'm getting the page but I can't get the
date inserted into the DB what am I doing wrong.

Second Can I suppress output to screen. I'm planning on running this
in cron if I'm inserting into db do I have to output to screen
This is the method my host company told me to use. they gave me the
Curl portion of the code which I modified.

Thanks
Steve
<?PHP

#$URL="https://shop.xxxxxxx.com/feeds/productdownload.php?
#fields=sku,qty,price&delim=csv&ohtime=1212278400& invtype=st#ock&id=MPB_MzUxxxxxU4MzQyNzk5";
(Hidden for security #reasons)
#Output looks like this
10001,0,822.51 10002,1,320.86 10003,1,498.91 10004,0,288.77
10005,1,573.91 10006,19,52.50 10007,1,822.51 10008,2,129.89
10010,0,15.63 10011,35,64.50 10012,5,445.65 10013,1,572.99
10014,13,23.53 10015,1,50.00 10016,5,12.99 10017,30,15.60 10018,9,8.24
10019,2,795.46 10020,222,2.40 10021,6,24.38 10024,4,24.38
10025,0,82.50 10026,4,14.38 10027,1,141.25 10030,1,214.12
10036,0,352.02 10043,4,27.50 10046,60,104.51 10047,4,955.37
10054,4,193.80 10122,6,409.50 10123,6,507.50 10127,6,483.00
10141,10,199.50 10147,0,7.50 10149,2,37.10 10156,8,63.75 10157,1,7.64
10158,0,29.83 10167,66,21.73 10169,8,36.47 10171,0,182.06
10177,23,12.00 10178,0,52.50 10179,1,16.50 10183,2,16.00 10195,0,27.00
10207,90,20.17 10245,0,55.29 10278,2,25.60 10284,3,16.98
10288,4,100.00 10333,2,84.99 10341,6,21.08 10342,3,125.31

if (isset($_GET["site"])) { $URL = $_GET["site"]; }
$ch = curl_init();
#"echo "URL = $URL <br>n";
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt ($ch, CURLOPT_HTTPPROXYTUNNEL, TRUE);
curl_setopt ($ch, CURLOPT_PROXYTYPE, CURLPROXY_HTTP);
curl_setopt ($ch, CURLOPT_PROXY,"http://proxy.shr.secureserver.net:
3128");
curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt ($ch, CURLOPT_URL, $URL);
curl_setopt ($ch, CURLOPT_TIMEOUT, 120);

$result = curl_exec ($ch);
# Variables that need to be set by you.
#$file = "https://shop.cwrelectronics.com/feeds/productdownload.php?
fields=sku,qty,price&delim=csv&ohtime=1212278400&i nvtype=stock&id=MPB_MzU4MzQyMzU4MzQyNzk5";
$hostname = "xxxx.secureserver.net";
$username = "xxxxxxx";
$password = "xxxxxx";
$dbname = "xxxxxxxx";
$table = "Inv_Refresh";
$field1 = "Field1";
$field2 = "Field2";
$field3 = "Field3";
$field4 = "Field4";
$field4m = 0.1777;

# No need to change anything here
mysql_connect($hostname,$username,$password);
mysql_select_db($dbname);
$result = str_replace("\r","",$result);
$lines = explode("\n",$result);

foreach ($lines as $line) {
$values = explode(",",$line);
$sql = "REPLACE INTO $table ($field1,$field2,$field3,$field4) VALUES
('$values[0]','$values[1]','$values[2]','".($values[2] *
$field4m)."');";
mysql_query($sql);
}
curl_close ($ch);
?>
My suggestion is to break up your code into smaller pieces (processes)
and debug each piece one at a time. Your code is rather hard to follow
in what you're trying to accomplish so with each piece comment the code
with the function (purpose) of the code and the desired result.

Also, you need to explain (in a repost) from what this code is being
invoked (shell/Command-Line, webpage, Windows, Unix, ect...) as it will
react differently in each case. It seems to me from reading your post
that you're invoking this from the command-Line since you are going to
use cron to invoke it.

As hard as the devel team has tried the command-line functionality of
PHP is, in my opinion, not fully finished and leaves me wanting in most
cases when I use it solely as command-line script. As a result of this I
will generally use is as a "shell-out" within ksh or bash. Using the two
in concert with each other (using ksh as the main script and then
shelling-out to php) compliments the others short comings.

One more suggestion, if you can clarify the purpose of the script and
the desired result there maybe a possibility that there is a better
solution to using MySQL. There are better databases out there that
handle command-line applications better than MySQL. SQLite comes to my
mind right away.

Try these suggestions and repost.
Carl
--

Carl Lindgren
C. R. Lindgren Consulting | Business on the Desktop

****
To Reply Remove the (*) from the "reply to"
Jul 28 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.