CODEWITHSUNDEEP
mysqldatabaseauto-increment
SoldierCorp
SoldierCorp

Reputation: 7700

MySQL: How to insert multiple records with same ID autoincrement

I have a table with 1 column that stores the ID auto_increment and with that instruction:

$query = "SELECT first_data, second_data, third_data
            FROM table_data
                WHERE condition = 'value_condition'";
$result = mysql_query($query) or die(mysql_error());

$no_of_rows = mysql_num_rows($result);

    if ($no_of_rows > 0) {
        while($row = mysql_fetch_assoc($result)) {
            $valueone = $row['first_data'];
            $valuetwo = $row['second_data'];
            $valuethree = $row['third_data'];

            $queryTwo = "INSERT INTO historial_ventas (reg1, reg2, reg3) 
                        VALUES('$ivalueone','$valuetwo','$valuethree')";
            $resultTwol = mysql_query($queryTwo) or die(mysql_error());
        }
    } else {
        return false;
}

Information is stored as follows:

ID(auto_increment)  reg1      reg2      reg3
______________________________________________
1                   value1    value2    value3
2                   value1    value2    value3
3                   value1    value2    value3
4                   value1    value2    value3

But I want the records maintained in the while loop, stored with the same ID as in a purchase online, ie you save the products and their features but with the same ID to buy, as this way:

ID(auto_increment)  reg1      reg2      reg3
______________________________________________
1                   value1    value2    value3
1                   value1    value2    value3
1                   value1    value2    value3
1                   value1    value2    value3

Upvotes: 2

Views: 15436

Answers (3)

mauris
mauris

Reputation: 43619

The normalized way is to use a joint table like this:

Purchase System

Both PurchaseId and ItemId are set to auto-increment. The purpose of a Primary Key is to uniquely identify each row.

Updated: Customer

Read up on Database Normalization on how you can design your database to be effective.

enter image description here

Upvotes: 2

Kyra
Kyra

Reputation: 5407

Remove the auto-increment and manually enter the id number. Also you need to check if it is set to a primary key (ie. unique). Make sure this is what you want to do because removing the uniqueness may cause issues with duplicity later.

To do this before you enter the while loop you can get the maximum number (select max(id) from tablename), increment it by one and then insert it within the while loop.

$query = "SELECT max(id)
            FROM historial_ventas";
$result = mysql_query($query) or die(mysql_error());

//increase you resulting number
$result = $result + 1

    if ($no_of_rows > 0) {
        while($row = mysql_fetch_assoc($result)) {
            $valueone = $row['first_data'];
            $valuetwo = $row['second_data'];
            $valuethree = $row['third_data'];

            $queryTwo = "INSERT INTO historial_ventas (id, reg1, reg2, reg3) 
                        VALUES('$result', '$ivalueone','$valuetwo','$valuethree')";
            $resultTwol = mysql_query($queryTwo) or die(mysql_error());
        }
    } else {
        return false;
}

Upvotes: 0

Shoaib
Shoaib

Reputation: 561

I'm assuming the ID column is defined as a primary key, in which case its entire purpose is supplying a unique piece of data to identify each record by - it's impossible to have two records in the same table with the same primary key. What exactly are you trying to accomplish? If you want to store the purchase ID, you should create a separate column for it and store it explicitly - an auto increment column isn't what you're looking for.

Upvotes: 5

Related Questions