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

insert/update arrays into db for later retrieval to table

P: 3
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

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $url = 'http://sports.yahoo.com/nascar/standings';
  3. $html = file_get_contents($url);
  4.  
  5. // First, get that one single table.
  6. preg_match('~(<table width="100%" border="0" cellspacing="0" cellpadding="0">.*<tr.*class="ysptblthbody1".*>.*</table>)~iUs', $html, $matches);
  7.  
  8. // $matches[0] is now the full match (e.g. the whole $html content)
  9. // $matches[1] is just the table we need
  10.  
  11. // We'll split it up between those in the contention and those not
  12. preg_match('~(<tr class="ysprow(?:1|2)">.*)<tr><td.*><img.*></td></tr>(<tr class="ysprow(?:1|2)".*>.*</tr>.*)</table>~iUs', $matches[1], $parts);
  13.  
  14. // $parts[1] are those in contention
  15. // $parts[2] are the other drivers in the field
  16.  
  17. // Look for all the information for those in contention, save to $matches
  18. 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);
  19.  
  20. // Look for all the information for the other drivers, store in $matches2
  21. 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);
  22.  
  23. // Prepare our array for population
  24. $drivers = array(
  25.     'contention' => array(),
  26.     'field' => array()
  27. );
  28.  
  29. // Add the drivers in contention
  30. foreach($matches as $match)
  31. {
  32.     $drivers['contention'][$match[1]] = array(
  33.         'rank' => $match[1],
  34.         'rank_delta' => $match[2],
  35.         'driver' => $match[3],
  36.         'points' => $match[4],
  37.         'behind' => $match[5],
  38.         'start' => $match[6],
  39.         'poles' => $match[7],
  40.         'wins' => $match[8],
  41.         'top5' => $match[9],
  42.         'top10' => $match[10],
  43.         'dnf' => $match[11],
  44.         'winnings' => $match[12]
  45.     );
  46. }
  47.  
  48. // Add those drivers that are in the rest of the field
  49. foreach($matches2 as $match)
  50. {
  51.     $drivers['field'][$match[1]] = array(
  52.         'rank' => $match[1],
  53.         'rank_delta' => $match[2],
  54.         'driver' => $match[3],
  55.         'points' => $match[4],
  56.         'behind' => $match[5],
  57.         'start' => $match[6],
  58.         'poles' => $match[7],
  59.         'wins' => $match[8],
  60.         'top5' => $match[9],
  61.         'top10' => $match[10],
  62.         'dnf' => $match[11],
  63.         'winnings' => $match[12]
  64.     );
  65. }
  66.  
  67. echo '<pre>'.print_r($drivers, 1).'</pre>'; 



this prints out arrays (approx 65) like the following

Expand|Select|Wrap|Line Numbers
  1. Array
  2. (
  3.     [contention] => Array
  4.         (
  5.             [1] => Array
  6.                 (
  7.                     [rank] => 1
  8.                     [rank_delta] => -
  9.                     [driver] => Tony Stewart
  10.                     [points] => 3383
  11.                     [behind] => Leader
  12.                     [start] => 22
  13.                     [poles] => 4
  14.                     [wins] => 3
  15.                     [top5] => 13
  16.                     [top10] => 18
  17.                     [dnf] => 0
  18.                     [winnings] => $5,084,829
  19.                 )
  20.  
  21.             [2] => Array
  22.                 (
  23.                     [rank] => 2
  24.                     [rank_delta] => -
  25.                     [driver] => Jimmie Johnson
  26.                     [points] => 3123
  27.                     [behind] => 260
  28.                     [start] => 22
  29.                     [poles] => 1
  30.                     [wins] => 3
  31.                     [top5] => 9
  32.                     [top10] => 14
  33.                     [dnf] => 1
  34.                     [winnings] => $4,607,420
  35.                 )
  36.  
  37.                 ....
  38.  
  39.     [field] => Array
  40.         (
  41.             [13] => Array
  42.                 (
  43.                     [rank] => 13
  44.                     [rank_delta] => -
  45.                     [driver] => Kyle Busch
  46.                     [points] => 2627
  47.                     [behind] => 756
  48.                     [start] => 22
  49.                     [poles] => 1
  50.                     [wins] => 3
  51.                     [top5] => 5
  52.                     [top10] => 7
  53.                     [dnf] => 2
  54.                     [winnings] => $4,044,855
  55.                 )
  56.  
  57.             [14] => Array
  58.                 (
  59.                     [rank] => 14
  60.                     [rank_delta] => -
  61.                     [driver] => Brian Vickers
  62.                     [points] => 2589
  63.                     [behind] => 794
  64.                     [start] => 22
  65.                     [poles] => 5
  66.                     [wins] => 0
  67.                     [top5] => 3
  68.                     [top10] => 10
  69.                     [dnf] => 3
  70.                     [winnings] => $3,070,173
  71.                 )
  72.         )
  73. )

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? )

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `standings` (
  2.    `rank` INT,
  3.    `rank_delta` INT,
  4.    `driver` TEXT,
  5.    `points` INT,
  6.    `behind` INT,
  7.    `start` INT,
  8.    `poles` INT,
  9.    `wins` INT,
  10.    `top5` INT,
  11.    `top10` INT,
  12.    `dnf` INT,
  13.    `winnings` BIGINT
Aug 12 '09 #1
Share this Question
Share on Google+
3 Replies


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

Am I close though?

Expand|Select|Wrap|Line Numbers
  1. // Connect and select database
  2. $link = mysqli_connect("localhost", "user", "password", "dbname") or die('Could not connect');
  3.  
  4. // now, you either update in a loop like so:
  5.  
  6. foreach ($drivers) {
  7.     $result = mysqli_query("UPDATE table SET Build = 'standings' WHERE Area='$drivers'");
  8.     echo "$drivers updated. <";
  9. }
Aug 12 '09 #2

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

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $url = 'http://sports.yahoo.com/nascar/standings';
  3. $html = file_get_contents($url);
  4.  
  5. // First, get that one single table.
  6. preg_match('~(<table width="100%" border="0" cellspacing="0" cellpadding="0">.*<tr.*class="ysptblthbody1".*>.*</table>)~iUs', $html, $matches);
  7.  
  8. // $matches[0] is now the full match (e.g. the whole $html content)
  9. // $matches[1] is just the table we need
  10.  
  11. // We'll split it up between those in the contention and those not
  12. preg_match('~(<tr class="ysprow(?:1|2)">.*)<tr><td.*><img.*></td></tr>(<tr class="ysprow(?:1|2)".*>.*</tr>.*)</table>~iUs', $matches[1], $parts);
  13.  
  14. // $parts[1] are those in contention
  15. // $parts[2] are the other drivers in the field
  16.  
  17. // Look for all the information for those in contention, save to $matches
  18. 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);
  19.  
  20. // Look for all the information for the other drivers, store in $matches2
  21. 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);
  22.  
  23. // Prepare our array for population
  24. $drivers = array(); 
  25.  
  26. // Add the drivers in contention
  27. foreach($matches as $match)
  28. {
  29.     $drivers[$match[1]] = array(
  30.         'rank' => $match[1],
  31.         'rank_delta' => $match[2],
  32.         'driver' => $match[3],
  33.         'points' => $match[4],
  34.         'behind' => $match[5],
  35.         'start' => $match[6],
  36.         'poles' => $match[7],
  37.         'wins' => $match[8],
  38.         'top5' => $match[9],
  39.         'top10' => $match[10],
  40.         'dnf' => $match[11],
  41.         'winnings' => $match[12],
  42.         'in_contention' => 1
  43.     );
  44. }
  45.  
  46. foreach($matches2 as $match)
  47. {
  48.     $drivers[$match[1]] = array(
  49.         'rank' => $match[1],
  50.         'rank_delta' => $match[2],
  51.         'driver' => $match[3],
  52.         'points' => $match[4],
  53.         'behind' => $match[5],
  54.         'start' => $match[6],
  55.         'poles' => $match[7],
  56.         'wins' => $match[8],
  57.         'top5' => $match[9],
  58.         'top10' => $match[10],
  59.         'dnf' => $match[11],
  60.         'winnings' => $match[12],
  61.         'in_contention' => 0
  62.     );
  63. echo '<pre>'.print_r($drivers, 1).'</pre>'; 
  64.  
  65. $link = mysqli_connect('localhost', 'ninfouser', '********', 'ninfo');
  66. if (!$link) {
  67.     die('Could not connect: ' . mysql_error());
  68. }
  69. echo 'Connected successfully';
  70.  
  71.  
  72. foreach($drivers as $driver)
  73. {
  74.     $query = sprintf("INSERT INTO `nascar_standings`
  75.                     (`race_week`, `rank`, `rank_delta`, `driver`, `points`, `behind`, `start`, `poles`, `wins`, `top5`, `top10`, `dnf`, `winnings`, `in_contention`)
  76.                     VALUES (%d, %d, '%s', %d, '%s', %d, %d, %d, %d, %d, '%s', %d)",
  77.             intval($race_week),
  78.             intval($driver['rank']),
  79.             mysql_real_escape_string($driver['driver']),
  80.             intval($driver['points']),
  81.             mysql_real_escape_string($driver['behind']),
  82.             intval($driver['start']),
  83.             intval($driver['poles']),
  84.             intval($driver['wins']),
  85.             intval($driver['top5']),
  86.             intval($driver['top10']),
  87.             intval($driver['dnf']),
  88.             mysql_real_escape_string($driver['winnings']),
  89.             intval($driver['in_contention'])
  90. );
  91.     $result = mysql_query($query);
  92.     if(!$result)
  93.     {
  94.         die('Unable to make the full update!<br />'.mysql_error());
  95.     }
  96. }
  97.  
  98. echo 'Completed update.'; 
  99.  
Aug 13 '09 #3

Dormilich
Expert Mod 5K+
P: 8,639
side note: you can simplify the driver array creation by using array_combine()
Expand|Select|Wrap|Line Numbers
  1. // because the original $match[0] is not used in array creation, 
  2. // the only constant value is used to overwrite it so that there is no offset
  3. $keys = array('in_contention', 'rank', 'rank_delta', );
  4.  
  5. foreach($matches as $match)
  6. {
  7.    $match[0] = 1; // or whatever value is currently needed
  8.    $drivers[$match[1]] = array_combine($keys, $match);
  9. }
  10.  
Aug 13 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.