How to Insert Data In MYSQL Database From A For Loop As a Single Query Using Simple HTML Dom

Question

I have this codes below how can i insert them as a single query it really confuse me because some data are supposed to be fetched from a For Loop any idea how to accomplish this? It really confuse me by now it only insert to database one last data only.

<?php
$hostname="localhost";
$username="root";
$password="";
$dbname="tz-arb";

  $connect=mysql_connect($hostname,$username,$password)or die("Error cannot connect to 

  database".mysql_error());
  mysql_select_db($dbname,$connect)or die("Failed To Select Database".mysql_error());



  include('advanced_html_dom.php'); 
  $html = file_get_html('yoururl');

   //Finding Numbers of 1x2 Game
  $numcounter = count($gameone = $html->find('div[data-gamename="1X2"]'));


   foreach($html->find(".event-date") as $evdate)
   echo "Event TIME ".$evdate."<br>";

   foreach($html->find(".event-name") as $evname)
   echo "Event NAME ".$evname."<br>";

   for ($i = 0; $i < $numcounter; $i += 3){
   // Set up variables to be inserted
   $home = $html->find('div[data-gamename="1X2"]', $i);
    $draw = $html->find('div[data-gamename="1X2"]', $i + 1);
   $away = $html->find('div[data-gamename="1X2"]', $i + 2);
   echo "1 HOME ".$home."<br>";
   echo "2 DRAW ".$draw."<br>";
   echo "3 AWAY ".$away."<br>";

   // Create unique queries for each INSERT
     $query = "INSERT INTO premier_bet_football 
        (TIME, TEAMS, HOME, DRAW, AWAY) 
 VALUES ('$evdate->plaintext', '$evname->plaintext', '$home->plaintext', '$draw->plaintext','$away->plaintext')";

  // Actually execute each unique INSERT (and log output)
   if (mysql_query($query)) {
    echo "Time And Match Added To Database";
     }
    else {
     echo "RECORD NOT ENTERED SOMETHING WENT WRONG";
     echo mysql_error();
     }

     }


    $html->clear();
     ?>

These are the codes.

Answer 1

You appear to be inserting information directly from the HTML DOM itself. Considering your insert is outside of your loops, only the last value for $home, $draw and $away will get inserted. To remedy this, you need to insert into the database inside a loop.

In your three loops, you are essentially simply offsetting the index. This can be done inside a single loop by using an addition at the end of each variable assignment instead:

for ($i = 0; $i < $numcounter; $i += 3){

  // Event name and date
  $evdate = $html->find(".event-date")[$i]
  echo "Event TIME ".$evdate."<br>";
  $evname = $html->find(".event-name")[$i]
  echo "Event NAME ".$evname."<br>";

  // Set up variables to be inserted
  $home = $html->find('div[data-gamename="1X2"]', $i);
  $draw = $html->find('div[data-gamename="1X2"]', $i + 1);
  $away = $html->find('div[data-gamename="1X2"]', $i + 2);

  // Create unique queries for each INSERT
  $query = "INSERT INTO premier_bet_football 
            (time, teams, home, draw, away) 
     VALUES ('$time->plaintext', '$eventname->plaintext', '$home->plaintext', '$draw->plaintext', '$away->plaintext')";

  // Actually execute each unique INSERT (and log output)
  if (mysql_query($query)) {
    echo "Time And Match Added To Database";
  }
  else {
    echo "RECORD NOT ENTERED SOMETHING WENT WRONG";
    echo mysql_error();
  }

}

Also, I strongly recommend switching to either MySQLi or PDO, as the mysql_ constructor is deprecated as of PHP 5.5, and outright removed as of PHP 7. You should also look into prepared statements to prevent against MySQL Injection.

Hope this helps! :)