将列表中的数据插入到mysql数据库中

Question

I am using a custom web crawler to pull information from a site (with permission) and storing all of the information into separate lists. I then want to store that information on a table I have created in mysql. I have successfully connected to the DB, and I know that the table name and the places I am INSERT INTO are correct.

Whenever I see examples of inserting data from php it is only showing one or two insertions, but i have 5 lists of info with up to 50 items each. How would I go about inserting the data store in my lists? This is the first program I have ever written in PHP so keep in mind I am not familiar with all the ins and outs of it.

<?php
// connect to mysql
include('connect_mysql.php');
// make sure the webservice returns plain text
header( 'Content-Type: text/plain; charset=utf-8' );
// include 3rd party parsing utility
require './vendor/querypath/qp.php';
$gidURL = $_SERVER['REQUEST_URI'];

// figure out which guild to scrape info for
// MAKE SURE TO CHANGE TO EXACT URLS ONCE PAGES ARE COMPLETE
if(strpos($gidURL, 'guild_members') !== false) {
    $url = 'https://swgoh.gg/g/21284/gid-1-800-druidia/';
}else if(strpos($gidURL, 'liquid-schwartz') !== false) {
    $url = 'https://swgoh.gg/g/19528/gid-liquid-schwartz/';
}else if(strpos($gidURL, 'spaceballsdeep') !== false) {
    $url = 'https://swgoh.gg/g/14280/gid-spaceballsdeep/';
}else if(strpos($gidURL, 'chapter-eleven') !== false) {
    $url = 'https://swgoh.gg/g/41958/gid-chapter-eleven/';
}else if(strpos($gidURL, 'my-own-best-friend') !== false) {
    $url = 'https://swgoh.gg/g/4736/gid-my-own-best-friend/';
}else if(strpos($gidURL, 'gone-to-plaid') !== false) {
    $url = 'https://swgoh.gg/g/363/gid-gone-to-plaid/';
}else if(strpos($gidURL, 'my-schwartz-is-bigger') !== false) {
    $url = 'https://swgoh.gg/g/27827/gid-my-schwartz-is-bigger/';
}else if(strpos($gidURL, 'ludicrousspeed') !== false) {
    $url = 'https://swgoh.gg/g/19538/gid-ludicrousspeed/';
}else if(strpos($gidURL, 'lone-starr') !== false) {
    $url = 'https://swgoh.gg/g/32650/gid-lone-starr/';
}else {
    echo "No guild was found in the URL.
";
}

// get the HTML page into the parser
$guild_page = htmlqp( $url );
// $guild_page = htmlqp( './test-druidia.html' ); // for testing if you don't want to hit swgoh.gg
function guild_info_data() {
    // init empty array that will hold our data later
    $guild_members = [];
    global $guild_page;
    // loop through table rows
    foreach ( $guild_page -> find( '.character-list tbody tr' ) as $row ) {
        // init empty array for individual player
        $guild_member = [];
        $member_name = [];
        $member_gp = [];
        $member_cs = [];
        $member_rank = [];
        $member_rank_avg = [];

        // store data in temporary array
        $guild_member[ 'name' ] = preg_replace( "/|
/", "", trim( $row -> branch( 'td' ) -> eq( 0 ) -> text() ) );
        $guild_member[ 'gp' ] = $row -> branch( 'td' ) -> eq( 1 ) -> text();
        $guild_member[ 'collection_score' ] = $row -> branch( 'td' ) -> eq( 2 ) -> text();
        $guild_member[ 'arena_rank' ] = $row -> branch('td') -> eq(3) -> text();
        $guild_member[ 'arena_average' ] = $row -> branch( 'td' ) -> eq( 4 ) -> text();

        // add player array to full response array
        $guild_members[] = $guild_member;
    }

    foreach ($guild_members as $guild_member) {
        $member_name[] = $guild_member['name'];
    }
    foreach ($guild_members as $guild_member) {
        $member_gp[] = $guild_member['gp'];
    }
    foreach ($guild_members as $guild_member) {
        $member_cs[] = $guild_member['collection_score'];
    }
    foreach ($guild_members as $guild_member) {
        $member_rank[] = $guild_member['arena_rank'];
    }
    foreach ($guild_members as $guild_member) {
        $member_rank_avg[] = $guild_member['arena_average'];
    }

}


// check what page is loaded 
if(strpos($gidURL, 'guild_members') !== false) {
    $name_insert = "INSERT INTO gid-1-800-druidia (name, galactic_power, collection_score, arena_rank, arena_average) VALUES ('$member_name', '$member_gp', '$member_cs', '$member_rank', '$member_rank_avg')";

    if(!mysqli_query($dbcon, $name_insert)) {
        die('error inserting new record');
    }else {
        echo 'successfully added info to DB';
    }

}else if(strpos($gidURL, 'liquid-schwartz') !== false) {
    // insert LSD data
}else if(strpos($gidURL, 'spaceballsdeep') !== false) {
    // insert SBD data
}else if(strpos($gidURL, 'chapter-eleven') !== false) {
    // insert C11 data
}else if(strpos($gidURL, 'my-own-best-friend') !== false) {
    // insert MOBF data
}else if(strpos($gidURL, 'gone-to-plaid') !== false) {
    // insert GtP data
}else if(strpos($gidURL, 'my-schwartz-is-bigger') !== false) {
    // insert SCH data
}else if(strpos($gidURL, 'ludicrousspeed') !== false) {
    // insert LS data
}else if(strpos($gidURL, 'lone-starr') !== false) {
    // insert LST data
}else {
    echo "No guild info was pulled.";
}


// let's get out of here
exit;

?>

I was also told that mysqli is better to use so if anyone can go on to explain what is done differently it would also be much appreciated.

<?php

// Connect to DB
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PSWD', 'root');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'guild_info');

$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PSWD, DB_NAME);

?>

Answer 1

I had to add the variable $dbcon in this line:

if(!mysqli_query($dbcon, $name_insert)) {
    die('error inserting new record');
}

as well as put the name of the table in backticks like user3783243 such as:

$name_insert = "INSERT INTO `gid-1-800-druidia` (name, galactic_power, collection_score, arena_rank, arena_average) VALUES ('$member_name', '$member_gp', '$member_cs', '$member_rank', '$member_rank_avg')";