Asked  7 Months ago    Answers:  5   Viewed   130 times

I've tried many ways in an attempt to insert a large amount of data parsed from a text file (200,000 lines of text gets parsed into the array in about 2 seconds on my server so I know that part isn't the issue). The mysqli_multi_query I'm using sends the queries in blocks of 5,000 (for some reason it refuses to do anything larger if I want to use multi_query more then once) OR if I split the text file down to 50,000ish rows I can insert them all at once in 2 seconds. If I do that then it refuses to run another query (I did read I need to do while (mysqli_more_results($db) && mysqli_next_result($db)); to clear the results out and I do have that for my 5,000 split one)

The 50,000 insert one runs in 2 seconds, the 5,000 insert one (of 50,000 rows) takes 40 seconds, I'm kind of tempted to just create a new mysqli connection in the foreach (for 50,000 inserts), but it doesn't always work...

I'd like some tips on how to make my function run faster since 40 seconds to insert all 50,000 takes forever when the file I'm reading from can contain sever hundred thousand possibly millions of lines

function log2db(){
$db = mysqli_connect(SQLHOST, SQLUSER, SQLPASS, SQLDB);
if($db){
    $qar = logparse("world2.chat", loglen());
    $query = "";
    $i = 0;
    foreach($qar['data'] as $q){
        $i++;
        $uid = $q['uid'];
        $type = $q['type'];
        $chldst = $q['chldst'];
        $time = $q['date'];
        $msg = $q['msg'];
        $query .= "insert into `messages` (`uid`, `type`, `chldst`, `time`, `message`) values ('$uid', '$type', '$chldst', '$time', '$msg');n";
        if(!($i % 5000)){
            $qquery[] = $query;
            $query = "";
        }
    }
    $qquery[] = $query;
    foreach($qquery as $qq){
        $q = mysqli_multi_query($db, $qq);
        if($q){
            while (mysqli_more_results($db) && mysqli_next_result($db));
        } else {
            mysqli_error($db);
            return false;
        }
    }
    if($qar !== null){
        loglen($qar['filelen']);
    }
    return true;
}
return false;

}

Is what I've come up with (always works but like I said is slow), any tips on how I can improve it?

 Answers

48

You do realize the INSERT INTO query can insert multiple rows in a single query?

$parts  = array();

foreach($qar['data'] as $q){

    $uid    = $q['uid'];
    $type   = $q['type'];
    $chldst = $q['chldst'];
    $time   = $q['date'];
    $msg    = $q['msg'];

    $parts[] = "('$uid', '$type', '$chldst', '$time', '$msg')";
 }

$query  = "INSERT INTO `messages` (`uid`, `type`, `chldst`, `time`, `message`)";
$query .= "VALUES ".implode(', ', $parts);

mysqli_query($db, $query)
Saturday, May 29, 2021
 
Puneet
answered 7 Months ago
54

Try it with

} while ($mysqli->more_results() && $mysqli->next_result());

sscce:

<?php
ini_set('display_errors', 'on');
error_reporting(E_ALL|E_STRICT);

$mysqli = new mysqli("localhost", "localonly", "localonly", "test");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %sn", mysqli_connect_error());
    exit();
}

$mysqli->query('CREATE TEMPORARY TABLE City (ID int auto_increment, `Name` varchar(32), primary key(ID))') or die($mysqli->error);

$stmt = $mysqli->prepare("INSERT INTO City (`Name`) VALUES (?)") or die($mysqli->error);
$stmt->bind_param('s', $city) or die($stmt->error);
foreach(range('A','Z') as $c) {
    $city = 'city'.$c;
    $stmt->execute() or die($stmt->error);
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (!$mysqli->multi_query($query)) {
    trigger_error('multi_query failed: '.$mysqli->error, E_USER_ERROR);
}
else {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("'%s'n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------n");
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

prints

'localonly@localhost'
-----------------
'cityU'
'cityV'
'cityW'
'cityX'
'cityY'

without warnings/notices.

Wednesday, March 31, 2021
 
saad
answered 9 Months ago
76

I don't have an answer for why the logo isn't showing on the first page header, but to stretch the height to fit the content you need to manually alter the page margins.

I'm not sure the best way to do it, whether counting newline characters or what. But once you know how many lines you have, you could do something like:

$headerHeight = ( $imageHeight / 72 ) + ( $headerLineCount * $lineHeight );
$objPHPExcel->getActiveSheet()->getPageMargins()->setHeader( $margin );
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop( $margin + $headerHeight );

** The "$imageHeight / 72" clause is just a guess at how to convert your image's screen height into inches.

Saturday, May 29, 2021
 
mario
answered 7 Months ago
37
$string="1. Tim Moltzen, 2. Joel Reddy, 3. Blake Ayshford, 4. Chris Lawrence, 5. James Tedesco, 6. Benji Marshall, 7. Braith Anasta, 8. Aaron Woods, 9. Robbie Farah, 10. Jack Buchanan, 11. Bodene Thompson, 12. Liam Fulton, 13. Adam Blair, 14. Ben Murdoch Masila, 15. Ava Seumanufagai 16. Matt Bell, 17. Eddy Pettybourne";

$string=explode(', ',$string);
foreach($string as $val)
    {
    $val=explode('. ',$val);
    mysql_query('INSERT INTO yourtable (col_number,col_name) VALUES ("'.$val[0].'.","'.$val[1].'")';
    }

I don't understand why you want to insert the period along with the number, as this would mean that the column has to unnecessarily be varchar instead of INT. Anyway, it is as you asked.

Change mysql_query to mysqli_query if you prefer.

To explode between numbers use:

$string=preg_split('/ ?[0-9]+.? /', $string, NULL, PREG_SPLIT_NO_EMPTY);

But now you don't have any numbers for each name. So you won't be able to insert it like this.

Saturday, May 29, 2021
 
Gerardo
answered 7 Months ago
52

I just found the answer in the PHP manual:

WATCH OUT: if you mix $mysqli->multi_query and $mysqli->query, the latter(s) won't be executed!

BAD CODE:

$mysqli->multi_query(" Many SQL queries ; "); // OK
$mysqli->query(" SQL statement #1 ; ") // not executed!
$mysqli->query(" SQL statement #2 ; ") // not executed!
$mysqli->query(" SQL statement #3 ; ") // not executed!
$mysqli->query(" SQL statement #4 ; ") // not executed!

The only way to do this correctly is:

WORKING CODE:

$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!

I just insert this code after mysqli_multi_query():

while(mysqli_next_result($connect)){;}
Friday, July 30, 2021
 
tim_d
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share