dtcrw26206
dtcrw26206
2018-07-23 03:56

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

已采纳

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

?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dsafgdafgdf45345 dsafgdafgdf45345 3年前

    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')";
    
    点赞 评论 复制链接分享
  • dragonsun00000 dragonsun00000 3年前

    If you have added mysql connection in connect_mysql.php than you can't access that MYSQL connection variable inside function without passing it as parameter.

    So you need to change your code like below,

    <?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($dbcon) {
        // 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')";
     try{
          mysqli_query($dbcon, $name_insert);
     }catch (mysqli_sql_exception $e) {
          echo $e->getMessage();
     }
    
    }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;
    
    ?>
    
    点赞 评论 复制链接分享

为你推荐