I've cust_name and install_no columns in table, each customer's name repeated 12 times as rows how to insert numbers 1 to 12 in install_no column for all rows.

table sample:
-----------------------------
cust_name  | install_no
----------------------------
ROGER      | 1
----------------------------
ROGER      | 2
----------------------------
ROGER      | 3
----------------------------
ROGER      | 4
----------------------------
ROGER      | 5
----------------------------
ROGER      | 6
----------------------------
ROGER      | 7
----------------------------
ROGER      | 8
----------------------------
ROGER      | 9
----------------------------
ROGER      | 10
----------------------------
ROGER      | 11
----------------------------
ROGER      | 12
----------------------------
JAMES      | 1
----------------------------
JAMES      | 2
----------------------------
JAMES      | 3
----------------------------
JAMES      | 4
----------------------------
JAMES      | 5
----------------------------
JAMES      | 6
----------------------------
JAMES      | 7
----------------------------
JAMES      | 8
----------------------------
JAMES      | 9
----------------------------
JAMES      | 10
----------------------------
JAMES      | 11
----------------------------
JAMES      | 12
----------------------------



       $b = array(0 => '1', 1 => '2', 2 => '3', 3 => '4', 4 => '5', 5 => '6', 6 => '7', 7 => '8', 8 => '9', 9 => '10', 10 => '11', 11 => '12'); //pre-defined installment numbers
        $string = "";
        foreach($b as $k => $v)
        {
        $string = "UPDATE bijapur_slabpay SET install_no = $v WHERE install_no=''";
        mysql_query($string);
        }

Recommended Answers

All 2 Replies

What is the PK in that table? SQL databases don't really have a defined order of rows, unless that order is defined somehow. Usually with a PK integer column, that acts in many ways like a row counter.

If the order doesn't really matter, and all you want is any of the cust_name fields with the same name to have the next install_no, then you could just loop through the rows and update them one at at time, each time with the next number. That way you'd end up with the order you are looking for; each name having and install_no from 1 to 12.

In pseudo code, it might look like this.

Fetch all the unique names in the table.
FOR each unique name:
    FOR 1 through 12:
        Find the next row for this name with an empty isntall_no
        and update it to the current number.

Note that you can UPDATE one row by adding a LIMIT 1 clause at the very end.

Member Avatar for diafol

Unclear what you're trying to do. From your loop you're updating a blank install_no field with a number. However, these records need to exist in the first place. So why not set them to 1...12 when you create the records in the first place? As Atli says, I'd expect a PK here. Also cust_name seems wasteful - why not have a customer integer id instead? Use integers(or tinyint) where possible.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.