CODEWITHSUNDEEP
mysql
Santosh Khatri
Santosh Khatri

Reputation: 487

how do i insert number series which start with 100 in mysql php?

I was trying to insert number in my database which should be start with 100. following is my query which i tried to insert in my database but it is showing every time with one not by 101.

INSERT INTO jps_final_tickets(ft_ticket_number, ft_event, ft_package_id, ft_contact_person)
                SELECT COUNT(ft_ticket_number)+1, '$eventID' AS ft_event, '$ticketID' AS ft_package_id, '$contactPerson' AS ft_contact_person,
                FROM jps_final_tickets WHERE ft_package_id = '$ticketID'

Above query showing following output, everything is fine but ticket number should be start from 100 series.

-------------------------------------------------
| Ticket Number | event ID | Ticket Id | Name    |
|     1         |   645    |   70      | Santosh |
|     2         |   645    |   70      | Sandeep |
|     1         |   645    |   71      | Sahil   |
|     1         |   645    |   72      | Jagveer |
--------------------------------------------------

Following output which i want:

-------------------------------------------------
| Ticket Number | event ID | Ticket Id | Name    |
|     101       |   645    |   70      | Santosh |
|     102       |   645    |   70      | Sandeep |
|     101       |   645    |   71      | Sahil   |
|     101       |   645    |   72      | Jagveer |
--------------------------------------------------

Please help me how can i insert above ticket number in my table.

Upvotes: 0

Views: 378

Answers (3)

Samir Selia
Samir Selia

Reputation: 7065

Just increment the counter by 101.

 SELECT COUNT(ft_ticket_number) + 101

Detailed explanation:

Initially there are no records for let's say ticket Id 70

COUNT(ft_ticket_number) will return 0 for ticket Id 70. So first value will be 0 + 101 = 101

Next time table already has 1 record. So COUNT(ft_ticket_number) will return 1 which will calculate Ticket Number as 1 + 101 = 102 and so on.

The counter will reset automatically for new Ticket Id.

Upvotes: 1

cb0
cb0

Reputation: 8613

What about telling it mysql directly. If you make ticket_number an auto increment value starting at 100.

If jps_ticket_number is your primary key this will work.

ALTER TABLE jps_final_tickets AUTO_INCREMENT=100;

Upvotes: 0

Jaimin
Jaimin

Reputation: 872

This would be appropriate for you..

INSERT INTO jps_final_tickets(ft_ticket_number, ft_event, ft_package_id, ft_contact_person)
                    SELECT COUNT(ft_ticket_number)+101, '$eventID' AS ft_event, '$ticketID' AS ft_package_id, '$contactPerson' AS ft_contact_person,
                    FROM jps_final_tickets WHERE ft_package_id = '$ticketID'

Upvotes: 1

Related Questions