number of entries

Coruba67

Hi guys,

Have one I am stuck on. I have a CSV file, which contains stock levels. Basically the way that the database I am working with works with stock levels is each item needs to be its on record. So the CSV has two columns, part number and quantity.

Based on the quantity I need to put X amount of records into the database. What kind of query can I use to repeat itself X amount of times in MySQL or is there a way to loop it considering its only 1 record on the CSV, or is there a way I can just get the query to loop itself X amount of times?

Piranha

You can (and should) do it all in one query:

insert into table (column1, column2) values (v1, v2), (v3, v4);

Let PHP loop x times to insert the values, then just run the query.

Coruba67

how do I get PHP to loop?

Piranha

SearchSearch the internet and do a tutorial.

Roger_Ramjet

Coruba67;10879871 wrote:
Based on the quantity I need to put X amount of records into the database. What kind of query can I use to repeat itself X amount of times in MySQL or is there a way to loop it considering its only 1 record on the CSV, or is there a way I can just get the query to loop itself X amount of times?

No there is not.

So you have to get a bit creative.

Upload the csv into a work table.

Now, you need a number sequence. You can do this by creating a table with 0 to the max integer you need. Personally I like to just create a digits table with just 0-9 and then join it to itself to generate the numbers.

SELECT CONCAT(digits.digit, digits1.digit, digits2.digit) FROM digits, digits AS digits1, digits AS digits2;

That exploits the Cartesian join and silent type conversion to return a series of string in the sequence 000 to 999.

Now you can use CAST to then convert the sequence back into INTEGERS

SELECT cast(concat(digits.digit,digits1.digit,digits2.digit) as unsigned) as seq
FROM digits, digits AS digits1, digits AS digits2 order by seq

Now, you can use this as a table-type subquery in an insert query to add the number of rows you want. Assuming you have loaded you csv into a table called work with 2 columns partno, quantity

INSERT INTO stock(partno) SELECT partno FROM work, (SELECT cast(concat(digits.digit,digits1.digit,digits2.digit) as unsigned) as seq
FROM digits, digits AS digits1, digits AS digits2 order by seq) AS counter
WHERE counter.seq < work.quantity

And there it is all in 1 query. What it does is join each part no to the 0-999 sequence, so you would get each part no repeated 1000 times. The where clause then limits the repetition of each part to its quantity.

Seems a bit complex. Well so does a php code loop that is going to run umpteen queries in a loop to insert 1 row at a time: and the php code would take longer to type as well. Imagine how long it would take to process if you had 100 parts with 50 items each in stock, a modest inventory to my mind, but that would be 5000 queries.

Any time someone suggests you run a query in a loop ignore them - they do not know anything about SQL. In 25 years of using it I have come across maybe 5 occasions where running a query in a loop was the right answer - because it was the only answer.

SQL is a very elegant and powerful programming language designed to work on large numbers of records. Explore it, it can do a great many things very well and deserves as much effort to master as any other programming language such as C or PHP or VB.

Having said all this, I now ask the question - Why do you need to do it? Normal DB design would say that you just need 1 row per partno, with the quantity field for stock on hand. Please explain your rational for this design.

Piranha

Roger Ramjet;10880042 wrote:
Seems a bit complex. Well so does a php code loop that is going to run umpteen queries in a loop to insert 1 row at a time: and the php code would take longer to type as well. Imagine how long it would take to process if you had 100 parts with 50 items each in stock, a modest inventory to my mind, but that would be 5000 queries.

Any time someone suggests you run a query in a loop ignore them - they do not know anything about SQL. In 25 years of using it I have come across maybe 5 occasions where running a query in a loop was the right answer - because it was the only answer.

First I have to agree that you shouldn't do queries in a loop. But it is perfectly ok to build queries in a loop. Of course, using your way would be even better.

Second I must say that you misunderstood what I intended. I didn't intend to use 5000 queries in your example above, but 100 queries. Or even one query. Instead of doing something like this (5000 queries)

for (each partno) {
  for (each item) {
    //insert one row into the database
  }
}

I would do something like this (100 queries)

for (each partno) {
  $sql = "insert into table (cola, colb) values";
  for (each item) {
    $sql = $sql + "($valuea, $valueb),";
  }
  // trim the last comma from the string
  // insert one row into the database
}

Or even something like this (1 query)

$sql = "insert into table (cola, colb) values";
for (each partno) {
  for (each item) {
    $sql = $sql + "($valuea, $valueb),";
  }
}
// trim the last comma from the string
// insert one row into the database

I hope I made my idea clear this time 🙂

Roger_Ramjet

Of course, Piranha, of course. Did not read your example properly.

Coruba67

Cool, thanks guys! She's all up and running now!