insert/update arrays into db for later retrieval to table

[planethax]

I have multiple arrays that I need to insert/update database and I am not sure how to start, I think I need to use the "foreach" statement and also not sure whether or not to serialize and unserialize?

this info is then going to be used to populate a table.

This is what I have so far

<?php
$url = 'http://sports.yahoo.com/nascar/standings';
$html = file_get_contents($url);
 
// First, get that one single table.
preg_match('~(<table width="100%" border="0" cellspacing="0" cellpadding="0">.*<tr.*class="ysptblthbody1".*>.*</table>)~iUs', $html, $matches);
 
// $matches[0] is now the full match (e.g. the whole $html content)
// $matches[1] is just the table we need
 
// We'll split it up between those in the contention and those not
preg_match('~(<tr class="ysprow(?:1|2)">.*)<tr><td.*><img.*></td></tr>(<tr class="ysprow(?:1|2)".*>.*</tr>.*)</table>~iUs', $matches[1], $parts);
 
// $parts[1] are those in contention
// $parts[2] are the other drivers in the field
 
// Look for all the information for those in contention, save to $matches
preg_match_all('~<tr class="ysprow.*">.*<td.*>.*([\d]*)</td>.*<td.*>([+|-][\d]*)</td><td class=".*padded2px.*".*>.*<a.*>(.*)</a></td>.*<td class=".*ysptblclbg6.*".*>(.*)</td>.*<td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td>.*</tr>~iUs', $parts[1], $matches, PREG_SET_ORDER);
 
// Look for all the information for the other drivers, store in $matches2
preg_match_all('~<tr class="ysprow.*">.*<td.*>.*([\d]*)</td>.*<td.*>([+|-][\d]*)</td><td class=".*padded2px.*".*>.*<a.*>(.*)</a></td>.*<td class=".*ysptblclbg6.*".*>(.*)</td>.*<td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td>.*</tr>~iUs', $parts[2], $matches2, PREG_SET_ORDER);
 
// Prepare our array for population
$drivers = array(
    'contention' => array(),
    'field' => array()
);
 
// Add the drivers in contention
foreach($matches as $match)
{
    $drivers['contention'][$match[1]] = array(
        'rank' => $match[1],
        'rank_delta' => $match[2],
        'driver' => $match[3],
        'points' => $match[4],
        'behind' => $match[5],
        'start' => $match[6],
        'poles' => $match[7],
        'wins' => $match[8],
        'top5' => $match[9],
        'top10' => $match[10],
        'dnf' => $match[11],
        'winnings' => $match[12]
    );
}
 
// Add those drivers that are in the rest of the field
foreach($matches2 as $match)
{
    $drivers['field'][$match[1]] = array(
        'rank' => $match[1],
        'rank_delta' => $match[2],
        'driver' => $match[3],
        'points' => $match[4],
        'behind' => $match[5],
        'start' => $match[6],
        'poles' => $match[7],
        'wins' => $match[8],
        'top5' => $match[9],
        'top10' => $match[10],
        'dnf' => $match[11],
        'winnings' => $match[12]
    );
}
 
echo '<pre>'.print_r($drivers, 1).'</pre>'; 

this prints out arrays (approx 65) like the following

Array
(
    [contention] => Array
        (
            [1] => Array
                (
                    [rank] => 1
                    [rank_delta] => -
                    [driver] => Tony Stewart
                    [points] => 3383
                    [behind] => Leader
                    [start] => 22
                    [poles] => 4
                    [wins] => 3
                    [top5] => 13
                    [top10] => 18
                    [dnf] => 0
                    [winnings] => $5,084,829
                )
 
            [2] => Array
                (
                    [rank] => 2
                    [rank_delta] => -
                    [driver] => Jimmie Johnson
                    [points] => 3123
                    [behind] => 260
                    [start] => 22
                    [poles] => 1
                    [wins] => 3
                    [top5] => 9
                    [top10] => 14
                    [dnf] => 1
                    [winnings] => $4,607,420
                )
 
                ....
 
    [field] => Array
        (
            [13] => Array
                (
                    [rank] => 13
                    [rank_delta] => -
                    [driver] => Kyle Busch
                    [points] => 2627
                    [behind] => 756
                    [start] => 22
                    [poles] => 1
                    [wins] => 3
                    [top5] => 5
                    [top10] => 7
                    [dnf] => 2
                    [winnings] => $4,044,855
                )
 
            [14] => Array
                (
                    [rank] => 14
                    [rank_delta] => -
                    [driver] => Brian Vickers
                    [points] => 2589
                    [behind] => 794
                    [start] => 22
                    [poles] => 5
                    [wins] => 0
                    [top5] => 3
                    [top10] => 10
                    [dnf] => 3
                    [winnings] => $3,070,173
                )
        )
)

So I need to update my database with this, then later get it to insert into a table on my page?


I have built the database like the following (not sure if it was the best/easiest way? )

CREATE TABLE `standings` (
   `rank` INT,
   `rank_delta` INT,
   `driver` TEXT,
   `points` INT,
   `behind` INT,
   `start` INT,
   `poles` INT,
   `wins` INT,
   `top5` INT,
   `top10` INT,
   `dnf` INT,
   `winnings` BIGINT
) 

[planethax]

I was hoping the following would do the trick, but it causes a blank page :(

Am I close though?

// Connect and select database
$link = mysqli_connect("localhost", "user", "password", "dbname") or die('Could not connect');
 
// now, you either update in a loop like so:
 
foreach ($drivers) {
    $result = mysqli_query("UPDATE table SET Build = 'standings' WHERE Area='$drivers'");
    echo "$drivers updated. <";
}

[planethax]

so far this is what I have,
it prints the array to browser, but does not fill db or give any messages

<?php
$url = 'http://sports.yahoo.com/nascar/standings';
$html = file_get_contents($url);
 
// First, get that one single table.
preg_match('~(<table width="100%" border="0" cellspacing="0" cellpadding="0">.*<tr.*class="ysptblthbody1".*>.*</table>)~iUs', $html, $matches);
 
// $matches[0] is now the full match (e.g. the whole $html content)
// $matches[1] is just the table we need
 
// We'll split it up between those in the contention and those not
preg_match('~(<tr class="ysprow(?:1|2)">.*)<tr><td.*><img.*></td></tr>(<tr class="ysprow(?:1|2)".*>.*</tr>.*)</table>~iUs', $matches[1], $parts);
 
// $parts[1] are those in contention
// $parts[2] are the other drivers in the field
 
// Look for all the information for those in contention, save to $matches
preg_match_all('~<tr class="ysprow.*">.*<td.*>.*([\d]*)</td>.*<td.*>([+|-][\d]*)</td><td class=".*padded2px.*".*>.*<a.*>(.*)</a></td>.*<td class=".*ysptblclbg6.*".*>(.*)</td>.*<td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td>.*</tr>~iUs', $parts[1], $matches, PREG_SET_ORDER);
 
// Look for all the information for the other drivers, store in $matches2
preg_match_all('~<tr class="ysprow.*">.*<td.*>.*([\d]*)</td>.*<td.*>([+|-][\d]*)</td><td class=".*padded2px.*".*>.*<a.*>(.*)</a></td>.*<td class=".*ysptblclbg6.*".*>(.*)</td>.*<td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td><td.*>(.*)</td>.*</tr>~iUs', $parts[2], $matches2, PREG_SET_ORDER);
 
// Prepare our array for population
$drivers = array(); 
 
// Add the drivers in contention
foreach($matches as $match)
{
    $drivers[$match[1]] = array(
        'rank' => $match[1],
        'rank_delta' => $match[2],
        'driver' => $match[3],
        'points' => $match[4],
        'behind' => $match[5],
        'start' => $match[6],
        'poles' => $match[7],
        'wins' => $match[8],
        'top5' => $match[9],
        'top10' => $match[10],
        'dnf' => $match[11],
        'winnings' => $match[12],
        'in_contention' => 1
    );
}
 
foreach($matches2 as $match)
{
    $drivers[$match[1]] = array(
        'rank' => $match[1],
        'rank_delta' => $match[2],
        'driver' => $match[3],
        'points' => $match[4],
        'behind' => $match[5],
        'start' => $match[6],
        'poles' => $match[7],
        'wins' => $match[8],
        'top5' => $match[9],
        'top10' => $match[10],
        'dnf' => $match[11],
        'winnings' => $match[12],
        'in_contention' => 0
    );
} 
echo '<pre>'.print_r($drivers, 1).'</pre>'; 
 
$link = mysqli_connect('localhost', 'ninfouser', '********', 'ninfo');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
 
 
foreach($drivers as $driver)
{
    $query = sprintf("INSERT INTO `nascar_standings`
                    (`race_week`, `rank`, `rank_delta`, `driver`, `points`, `behind`, `start`, `poles`, `wins`, `top5`, `top10`, `dnf`, `winnings`, `in_contention`)
                    VALUES (%d, %d, '%s', %d, '%s', %d, %d, %d, %d, %d, '%s', %d)",
            intval($race_week),
            intval($driver['rank']),
            mysql_real_escape_string($driver['driver']),
            intval($driver['points']),
            mysql_real_escape_string($driver['behind']),
            intval($driver['start']),
            intval($driver['poles']),
            intval($driver['wins']),
            intval($driver['top5']),
            intval($driver['top10']),
            intval($driver['dnf']),
            mysql_real_escape_string($driver['winnings']),
            intval($driver['in_contention'])
);
    $result = mysql_query($query);
    if(!$result)
    {
        die('Unable to make the full update!<br />'.mysql_error());
    }
}
 
echo 'Completed update.'; 
 

[Dormilich]

side note: you can simplify the driver array creation by using array_combine()

// because the original $match[0] is not used in array creation, 
// the only constant value is used to overwrite it so that there is no offset
$keys = array('in_contention', 'rank', 'rank_delta', …);
 
foreach($matches as $match)
{
   $match[0] = 1; // or whatever value is currently needed
   $drivers[$match[1]] = array_combine($keys, $match);
}