Help me using PHP CURL and SQL insert

[mathieu89]

Hi All,

This has become a very tedious process and I am hoping someone can help me simplify things.

I run the below php command to retreive all the stock prices and then it prints it to the screen like this:

stockcode price
AAA $1
BBB $2
CCC $3

It takes about 5 minutes to get the 2000 results and then I have to paste it into excel and use cancatenate strings to generate my sql insert query. It all works perfectly but I would love to somehow do the sql insert automatically after running the script below.

<?php
$url = "http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=ONT"; 
$ch = curl_init($url); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); 
$var = curl_exec($ch); 
curl_close($ch); 
$first = stripos($var, '<th scope="row" class="row"><a href="/asx/research/companyInfo.do?by=asxCode&asxCode=', 0) ; 
$second = stripos($var,'<td class="change indicator" nowrap>', 0); 
echo substr($var, $first, $second - $first); 
echo "<br />";

$url ...

$url ...
?>

Ideally, I just want to open the php page and it will retreive the codes/prices and then automatically inserts it into the DB. My current insert query is below.

INSERT INTO ASX_PRICES (ASX_CODE, ASX_PRICE, DATE) VALUES ('ONT', '5.43' '2013-01-03')
INSERT INTO ...
INSERT INTO ...

Thanks.

Matt.

[pritaeas]

('ONT', '5.43' '2013-01-03')

If you specify where these values are defined (or coming from), then you can just build your query and execute it. I can only see ONT because it is used in the URL you call.

[broj1]

WHere do you keep the list of URL's? If it is a file or an array then do a while or foreach or for loop reading the urls, getting the value and add values to the insert query:

// your URLs (in an array in this example)
$url_list = array(
    'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=ONT', 
    'http://...',
    'http://...',
);

// count of URLs
$url_count = count($url_list);

// temporary counter
$i = 0;

// start the query
$query = 'INSERT INTO ASX_PRICES (ASX_CODE, ASX_PRICE, DATE) VALUES ';

foreach($url_list as $url) {

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $var = curl_exec($ch);
    curl_close($ch);
    $first = stripos($var, '<th scope="row" class="row"><a href="' . $url . '"', 0) ;
    $second = stripos($var,'<td class="change indicator" nowrap>', 0);
    echo substr($var, $first, $second - $first);
    echo "<br />";

    // get the ASX_CODE and ASX_PRICE from the substring above (maybe using a regex)
    // and add to insert query
    $ASX_CODE = ...
    $ASX_PRICE = ...

    // get the date (wherever it comes from)
    $date = ...

    // add to the query
    $query .= "('$ASX_CODE', '$ASX_PRICE', $date)";

    // increase the counter
    $i++;

    // if it is not the last value, add the comma
    if($i < $count - 1) {

        $query = ', ';
    }
}

Not sure if I got what is your intention but I hope it helps.

[mathieu89]

Thanks Broj1. That was a massive help! I ended up with this and its working ok

<?
$url_list = array(

'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=BHP',
'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=LNC',
'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=SUL',
'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=mhm',
'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=hdg',
'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=csl',
'http://asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=cgh',
);

$url_count = count($url_list);

$i = 0;
$ch = '';
$var = '';
$first = '';
$second = '';
$code = '';
$price = '';
$date = '';
$query = '';
$url = '';

$query = 'INSERT INTO `ASX_PRICES` (`ASX_CODE`, `ASX_PRICE`, `DATE`) VALUES ';

foreach($url_list as $url) {


$ch = curl_init($url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); 
$var= curl_exec($ch); 
curl_close($ch); 
$first= stripos($var,'<th scope="row" class="row"><a href="/asx/research/companyInfo.do?by=asxCode&asxCode=',0); 
$second= stripos($var,'<td class="change indicator" nowrap>',0); 


$code = substr($var,14995,100);
$price = substr($var,15199,7);
$date = "'15/03/2013'";

$code2 = str_replace (" ", "", trim($code));
$price2 = trim($price, "<> ");

$query .= "('$code2', '$price2', $date)";
echo $query;
echo "<br />";

$i++;

if($i <= $url_count - 1) {
$query = 'INSERT INTO `ASX_PRICES` (`ASX_CODE`, `ASX_PRICE`, `DATE`) VALUES ';


}
}

?>

[broj1]

The code looks nice and clean. The only question I have is what is the purpose of code on line 52:

if($i <= $url_count - 1) {
    $query = 'INSERT INTO `ASX_PRICES` (`ASX_CODE`, `ASX_PRICE`, `DATE`) VALUES ';
}

The original purpose was to add comma between each set of values (but not at the last set of values, so you do not get an sql error). In your case it should throw an error in sql.