MySQL PHP Insert Limit

Question

I'm trying to insert a bunch of data into a database, but it's getting hung up on inserting, it gets to 5000 entries and then stops. Problem is at least one table has 44,000 entries.

I'm using PHP to gather and the info that is going into the database. I'd like to enter the data using a loop, just submit 5000 entries at a time, but I'm not sure how to write that.

Is there a way without editing the initial query that I can loop through results, 5000 at a time? It would have to stop inserting after 5000 entries and then start up again, but at the same spot it was at when it stopped.

$listings = $rets->SearchQuery("Property","Listing",$query);
echo "Total Listings found: {$rets->TotalRecordsFound()}<br>\n";   

if ($listings) {
            echo "'Listings' Success.<br><br />";
} else {
            echo "'Listings' - Error!<br><br />";
            print_r($rets->Error());
            exit;

}   
while ($record = $rets->FetchRow($listings)) {
    $mysqli->query("INSERT INTO Property VALUES (

...
}

What do you mean with "it's getting hung"? Do you get an error message or does it just not proceed? — Dan, Aug 01 '17 at 20:49
Just doesn't proceed. Stops the insert and continues on with the rest of the code on the page. I have 2 tables, one that has 5,700 results and another with 44,000 results and both of them are capping at 5,000 entered. I can still enter more into the database after, it just seems to be a per insert session limit. — Nicole, Aug 01 '17 at 20:52
Which part of `Please provide the code you're currently using` was unclear? — N.B., Aug 01 '17 at 20:52
Try breaking it up. You don't provide any code, so we can't give any guidance on how to do that. — aynber, Aug 01 '17 at 20:53
If they are capped of both times at exactly 5000 entries, its most likely that `SearchQuery` has a limit how many rows it returns. Since it is unclear (at least to me) what kind of framework you are using, its hard to answer the question how to change that limit or how to change the offset at which row to start. — Peter van der Wal, Aug 01 '17 at 21:10
I'm not rude. You also don't need to do this with PHP at all since you can write an `INSERT INTO ... SELECT` query (paste exactly that into google for actual copy paste examples. — N.B., Aug 01 '17 at 21:20
@Peter van der Wal - The "$rets->TotalRecordsFound()" is showing me how many rows are in the query, that's how I know how many are supposed to be there. If there was a limit on "SearchQuery", wouldn't it limit that return? The framework I'm using is RETS. Usually within a query I could use LIMIT, and loop the query? But RETS seems to have a lot of issues with normal MySQL functions, so I'm trying to do it after I have the results. — Nicole, Aug 01 '17 at 21:26
@N.B. - It is already inserting entries, it's just getting stuck after 5,000. I'm looking for a way to submit in batches. It is a cron file that pulls data from one site to store in this database, it's not something I will be doing by hand. — Nicole, Aug 01 '17 at 21:29
I'm unfamilliary whith that framework (and documentation is partly offline right now), but you could try either of the following: 1) arbitrary high limit `SearchQuery("..","..", $query, array('Limit' => 99999999))` or 2) set an offset to continue at row 5000, then 10000 etc `SearchQuery("..","..", $query, array('Offset' => 5000))` -- And `TotalRecordsFound` may return the [total number of rows in db disregarding limit](https://stackoverflow.com/a/12887293/2903251) — Peter van der Wal, Aug 01 '17 at 21:48
I don't know how to politely explain it seems, so I'll go step by step - you're doing a `SELECT` query, then you pull data into PHP, then you're doing `INSERT` query. What I suggested was to avoid the whole PHP data pulling, simply trigger your PHP script with cron and have your PHP script issue an `INSERT INTO ... SELECT` query - for the details what that is, I suggested that you google. Now, what happens is that everything happens on MySQL, there's no data roundtrip to PHP, insert is many orders of magnitude faster etc etc, it's just up to you if you are willing to *read* what's written. — N.B., Aug 01 '17 at 22:30
@N.B - It's not a linked server that I am grabbing the information from. I need to use the RETS framework to grab the information in the first place. — Nicole, Aug 01 '17 at 22:41

score 0 · Answer 1 · answered Aug 01 '17 at 20:53

0

There is a limit on each server. 5000 a time is lot. Use your php script to insert about 500-1000 a time let's say each 10 minutes or so. You can use cron jobs in your server to automate your script run. It might take a day or so but you won't run out of bandwidth.

answered Aug 01 '17 at 20:53

SG_Rowin

622
3
19

It doesn't seem to be a limit on the whole file, just each input session. I have 3 queries submitting in the file, two of them are over 5,000 entries, and both of them are still submitting, just getting stopped/capped at 5,000 entries each. Just looking on how to loop the entries to submit in batches, within the initial query. – Nicole Aug 01 '17 at 21:11

MySQL PHP Insert Limit

1 Answers1