How to Limit an insert?

[dodgebill]

I have a question that I know is easy but it's not for me... How do I put a limit on an insert command? I am trying to limit the size of the datafeed users can upload. Here is what I have been trying. Please don't laugh if it's really bad...

 
$limter = "SELECT level FROM ".$glob['dbprefix']."DBNAME_members_users WHERE memberId=".$_SESSION['shlMember'];

                       if ($limiter[0]['level']==1) { $limit= 250;}
                       
                       if ($limiter[0]['level']==2) { $limit= 500;}
                       if ($limiter[0]['level']==3) { $limit= 5000;}
                       if ($limiter[0]['level']==4) { $limit= 25000000;}

                        
                        $query = sprintf("INSERT INTO %sDBNAME_inventory (%s) VALUES (%s) LIMIT%s;", $glob['dbprefix'], implode(',', $fields), implode(',', $values),"LIMIT".$limit);

So, what am I doing wrong? It still inserts but does not limit the number of lines in the text file.

Thanks

Bill

[Old Pedant]

Ummm...WHAT "text file"???

You are creating a SINGLE RECORD in the database.

Assuming that one of the fields is a group of lines, then the entire group of lines is plopped into a single field.

The LIMIT keyword in MySQL is used to limit the number of *RECORDS*. It has no impact whatsoever on the content of a single field in a single record.

(Beyond that, I'm pretty sure that the LIMIT keyword can't be used with an INSERT, in any case. It's used to limit the number of records *selected*. But it's all irrelevant here, since you only have one record.)

I'm a little mystified as to how that code works, since you are implode-ing an array of values by only separating them with a comma. If the values include any text values--and they must, given that you say you are saving a batch of lines--then those particular values should be wrapped in apostrophes. But you could obviously do that as you put the values into the array, so I can see how it *could* work.

ANYWAY...

If you want to limit the number of lines in that text blob, you'll have to do it with string manipulation. It would be a lot easier to simply limit the number of characters, if that's acceptable.

[dodgebill]

This script is uploading a datafeed of products. It's for a googlebase or shopzilla type site. You have a datafeed with say 1000 seperate products in it. Each one get's it's own row in the table and all the tab delimited entries go into the correct fields within that row. I want to be able to limit a members ability to upload products to say 250 or 500 or so rows. Based on the level of their membership.

I did an archive search of this site and back in 2002 someone posted that it was easy to limit an insert to a total number of rows inserted into a table. That is what I'm after. They left no details however as that was not the question being asked at the time.

This script works great with no limits. Obviously I didn't post the whole nuts and bolts of it. I was just after the insert limit part.

I can get it to acknowledge the limit call but it can't find a value for it. It says "Limit (limit)" in the error. Is it just in the lines - ($limiter[0]['level']=250) that I am having a problem?

Bill

[dodgebill]

It was Spookster that posted that comment about easily limiting inserts but I'm new here and not sure the policy about bugging people with stupid questions. Is it OK to PM people you don't know? On some sites it's not!

Bill

[Old Pedant]

Well, if you say it really is inserting multiple records, then I believe you.

I know that MySQL syntax allows that; just didn't think you'd use it with a sprintf like this. But never mind...

Assuming that post from 2002 is legit, then maybe it's just the syntax of the INSERT that you have wrong?

Maybe it should be:

$query = sprintf("INSERT INTO %sDBNAME_inventory (%s) VALUES (%s) LIMIT %s;", 
               $glob['dbprefix'], 
               implode(',', $fields), 
               implode(',', $values),
               $limit
               );

As you coded it, you had the LIMIT as a statement that was *separate* from the INSERT. This now makes it part of the INSERT.

No idea if it works. Stab in the dark.

[Old Pedant]

Dodgebill asked:

Is it OK to PM people you don't know? On some sites it's not!

I dunno if it's okay or not, but people to it to me at least twice a week. <grin/>

(Okay, to be fair, it's usually after we've had a "conversation" in the threads.)

But is somebody who posted in 2002 still hanging around?

[dodgebill]

Last night it showed he was last logged in on that day

[dodgebill]

OK, I have been playing with this all night. If I change the script like old pedant said and manually feed it the number 250 it shows that it is trying to seeing the command but has no idea what to do with it.

So, how can this be done? I have seen it done on some websites. There is always a way, it may not be easy or obvious but there is always a way.

The script is putting the data in a cache and then seperating it into fields and values before inserting it into the table. Is that where I need to be looking to limit the number of rows?

Thanks for your help

Bill

[dodgebill]

Near as I can tell you are right about not being able to limit an insert. At least not that way. How about writing a loop and using $limit as the iterate? I have no idea how to do that though. Anyone know what I'm talking about? Here is my code. How can I change it to make this work?

$fp = fopen('../../cache/importdata', 'rb');
        $i=0;
        while (($data = fgetcsv($fp, 1000, $delimiter)) !== false) {
                if ($i==0 && $_POST['ColumnHeaders']=='1') {
                        ## skip this first row
                } else {
                        foreach ($_POST['column'] as $key => $field) {
                                if (!empty($field) && $field != $data[$key]) {
                                        
                                                                                if ($field == 'price') $data[$key] = preg_replace('#[^0-9\.]#i', '', $data[$key]);
                                        

                                        $fields[] = $field;
                                        $values[] = sprintf("'%s'", addslashes($data[$key]));
                                }
                        }

[Old Pedant]

Sure, why not.

When you create that $values array that you later do the implode(',', $values) on, why not just limite how many rows you shove into it?

Of course, you could simply INSERT one row at a time, too. Unless this is something that is going to be used really really heavily, say many times a minute, I doubt you'd notice any performance hiccup.

I thought you were trying to be elegant and/or just using a $values array that somebody else is supplying.

[dodgebill]

You lost me.... remember, I'm code deaf... haha. My coding abilities are pretty mush limited to cut and paste with a few minor mods to make it functional. It gets me pretty far but it also gets me pretty stuck when I come across stuff like this.

This will get used a lot and with thousands of rows per insert possible. That's why it's important to be able to limit the numbers if I can.

I did "borrow" this part of the code from another script and it works great. I just need to figure out how to put a limit on it. The original script had nothing like that. I'm seeing that my request is not exactly normal though so I'm not surprised.

So, how do I put that limit on $value?

Thanks so much for your help. I'm learning a lot already.

Bill

[Old Pedant]

Well, I am *NOT* a PHP programmer. At all.

And there are a couple of weird things in the code you showed there. Plus the code is incomplete.

$fp = fopen('../../cache/importdata', 'rb');
$i=0;
while (($data = fgetcsv($fp, 1000, $delimiter)) !== false) 
{
    if ($i==0 && $_POST['ColumnHeaders']=='1') 
    {
        ## skip this first row
    } else {
        foreach ($_POST['column'] as $key => $field) 
        {
            if (!empty($field) && $field != $data[$key]) 
            {
                if ($field == 'price') $data[$key] = preg_replace('#[^0-9\.]#i', '', $data[$key]);
                $fields[] = $field;
                $values[] = sprintf("'%s'", addslashes($data[$key]));
            }
        }
        ???
    }
    ????
}

What's the missing code??

*******

It would appear that, in PHP code, doing
$array[ ] = $value
*appends* the given $value to contents of the given $array.

Okay, that makes sense.

So if that is true, then the answer is easy:

$fp = fopen('../../cache/importdata', 'rb');
$i=0;
while (($data = fgetcsv($fp, 1000, $delimiter)) !== false[B][COLOR="Red"] && $i < $limit[/COLOR][/B]) 
{
    if ($i==0 && $_POST['ColumnHeaders']=='1') 
    {
        ## skip this first row
    } else {
        foreach ($_POST['column'] as $key => $field) 
        {
            if (!empty($field) && $field != $data[$key]) 
            {
                if ($field == 'price') $data[$key] = preg_replace('#[^0-9\.]#i', '', $data[$key]);
                $fields[] = $field;
                $values[] = sprintf("'%s'", addslashes($data[$key]));
            }
        }
       [B][COLOR="Red"] ++$i;[/COLOR][/B]
        ???
    }
    ???
}

The ??? of course indicates "whatever code is there already". Though if the code is already doing something like ++$i or $i++ you don't need my ++$i also.

[dodgebill]

Holy Cow, it works! I did have a $i++ a little further in the code so I took out the ++i$ as you said. It works! Thanks you so much!

One other sql question

I am getting an error in this piece of code

$limiter = "SELECT level FROM ".$glob['dbprefix']."DBNAME_members_users WHERE memberId=".$_SESSION['shlMember'];

                   >>>     if (($limiter[0]['level'])=="1")   {$limit= 250;}

                       if (($limiter[0]['level'])=="2") { $limit= 500;}
                       if (($limiter[0]['level'])==3) { $limit= 5000;}
                       if (($limiter[0]['level'])==4) { $limit= 25000000;}

The error is Cannot use string offset as an array in (file location/line# >>>). What the heck does that mean? That is a new one on me.

I assume that $limiter might not be picking up "level" and then it can't complete the rest of the statement. If I take the "if" arguement out and just declare $limit = 250; it works great. I know, it's more php but it's sort of sql related, right?

Thanks again for all your help

Bill

[dodgebill]

Nevermind... I figured that one out. It works perfectly and since I now have a "delete" statement attached to the insert statement it wipes the table clean of just their rows before they can upload a new datafeed. That will keep them from adding a bunch of datafeeds on top of each other. They can only have so many rows in this table at any one time. Exactly what I needed!

This is so cool! I can't thank you enough!

Bill

[bazz]

You should look up REPLACE in case it would be more efficient for you. REPLACing a record does just that if a record exists and it creates a new record if there isn't in existence already.

bazz