adding multiple rows into databse incrementing one field.

[nook6]

Im trying to write some code for a raffle, when someone buys one ticket it works well but if someone buys ten tickets. i would like it to put each one on a new row, the last column is the ticket number which is got by another table called count and i want the new count in the last column of each row.

In the actual script there is more than two columns but this is an example just to try to let you know what im trying to do.

As you can see i want the ticket number to increment by one every time someone buys tickets. (the ticket number is in a simple table with just id and ticket number)

EXAMPLE someone buys 2 tickes

name | ticket number

John | 1

john | 2

then someone buys three tickets

jane | 3

jane | 4

jane | 5

This is what i have. (WORKING EXAMPLE of the code tha doesnt work.) as you can see the ticker number stays the same and not increment by one.

<?php
$num //is a number between 1 and 10 
$tr //is the current count got from database (this needs to count up by one every entry) 
include 'includes/connect.php'; 
$num = "3"; // number of tickets someone buys. 
$count = "5"; // count of tickets already sold (so this is start count for this transaction). 
$id = "1"; // this is the line the counter is on to keep count updated for the amount of tickets sold. 
$name = 'john'; //example name 

for($i=0;$i< $num;$i++){ 
                        $count="$count+1"; // increments count by 1 
                        $sql123 = "UPDATE count SET count=$count WHERE id='$id'"; //should update database to new count 
                        
                        $sql = "INSERT INTO test (name, number) VALUES ('$name', '$count')"; 
                        if($result = mysqli_query($con, $sql)){ 
                        echo "<br>tickets bought and entered into database,<br>Thank you<br>"; 
                        } else { 
                        echo "Error: " . $sql . "<br>" . $con->error;
  } 
  }
?>

Not sure what im doing wrong?

Thank you in advance

Nook6

[Barand]

Make ticket_number an auto_increment column

CREATE TABLE raffle (
  name VARCHAR(50)
  ticket_number INT NOT NULL auto_increment primary key
);

INSERT INTO raffle (name) VALUES
('Curly'),
('Curly'),
('Curly'),
('Larry'),
('Larry'),
('Larry'),
('Larry'),
('Larry'),
('Mo'),
('Mo'),
('Mo'),
('Mo'),
('Mo'),
('Mo');

SELECT name, ticket_number FROM raffle;


+-------+---------------+
| name  | ticket_number |
+-------+---------------+
| Curly |             1 |
| Curly |             2 |
| Curly |             3 |
| Larry |             4 |
| Larry |             5 |
| Larry |             6 |
| Larry |             7 |
| Larry |             8 |
| Mo    |             9 |
| Mo    |            10 |
| Mo    |            11 |
| Mo    |            12 |
| Mo    |            13 |
| Mo    |            14 |
+-------+---------------+

 

[nook6]

4 minutes ago, Barand said:

Make ticket_number an auto_increment column

CREATE TABLE raffle (
  name VARCHAR(50)
  ticket_number INT NOT NULL auto_increment primary key
);

INSERT INTO raffle (name) VALUES
('Curly'),
('Curly'),
('Curly'),
('Larry'),
('Larry'),
('Larry'),
('Larry'),
('Larry'),
('Mo'),
('Mo'),
('Mo'),
('Mo'),
('Mo'),
('Mo');

SELECT name, ticket_number FROM raffle;


+-------+---------------+
| name  | ticket_number |
+-------+---------------+
| Curly |             1 |
| Curly |             2 |
| Curly |             3 |
| Larry |             4 |
| Larry |             5 |
| Larry |             6 |
| Larry |             7 |
| Larry |             8 |
| Mo    |             9 |
| Mo    |            10 |
| Mo    |            11 |
| Mo    |            12 |
| Mo    |            13 |
| Mo    |            14 |
+-------+---------------+

 

 

Hi i cant do it like that because in my actual table there are more than one raffle so in the actual table it is item number which holds what raffle they are buying then thier name and thier ticket numbers (so you could buy 5 tickets for raffle 1 and three for raffle 2 and they are all stored in same database i just search for raffle name to get the results for that raffle)

[ginerjm]

You could use one database but have separate tables for each raffle.  Not the best approach but if you want to use auto-inc, then that would be one method.   Have a radio button on your entry form to choose which raffle you are selling for at the moment which you would  then translate to the correct table name in your script.

[nook6]

3 minutes ago, ginerjm said:

You could use one database but have separate tables for each raffle.  Not the best approach but if you want to use auto-inc, then that would be one method.   Have a radio button on your entry form to choose which raffle you are selling for at the moment which you would  then translate to the correct table name in your script.

HI

 

Unfortunately this method is not feasable for the script im doing if i was only going to run one or two raffles that would be good but i dont want to be setting up tables for every raffle that is run.

 

Nook6

[Barand]

Oh Joy!

I love it when someone tells us what they want and you give up your time to tell them how, only to have them then tell us that it wasn't really what they want so the exercise was a waste of time.

However, if you use a MyIsam table you can still do it. Just make the PRIMARY KEY (raffle_number, ticket_number)

CREATE TABLE `raffle` (
  `name` varchar(50) DEFAULT NULL,
  `raffle_number` int(11) NOT NULL,
  `ticket_number` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`raffle_number`,`ticket_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO raffle (name, raffle_number) VALUES
('Curly', 1),
('Curly', 1),
('Curly', 1),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Mo', 1),
('Mo', 1),
('Mo', 1),
('Mo', 2),
('Mo', 2),
('Mo', 2);

SELECT name 
     , raffle_number
     , ticket_number
FROM raffle
ORDER BY raffle_number, ticket_number;

+-------+---------------+---------------+
| name  | raffle_number | ticket_number |
+-------+---------------+---------------+
| Curly |             1 |             1 |
| Curly |             1 |             2 |
| Curly |             1 |             3 |
| Mo    |             1 |             4 |
| Mo    |             1 |             5 |
| Mo    |             1 |             6 |
| Larry |             2 |             1 |
| Larry |             2 |             2 |
| Larry |             2 |             3 |
| Larry |             2 |             4 |
| Larry |             2 |             5 |
| Mo    |             2 |             6 |
| Mo    |             2 |             7 |
| Mo    |             2 |             8 |
+-------+---------------+---------------+

 

[nook6]

12 minutes ago, Barand said:

Oh Joy!

I love it when someone tells us what they want and you give up your time to tell them how, only to have them then tell us that it wasn't really what they want so the exercise was a waste of time.

However, if you use a MyIsam table you can still do it. Just make the PRIMARY KEY (raffle_number, ticket_number)

CREATE TABLE `raffle` (
  `name` varchar(50) DEFAULT NULL,
  `raffle_number` int(11) NOT NULL,
  `ticket_number` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`raffle_number`,`ticket_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO raffle (name, raffle_number) VALUES
('Curly', 1),
('Curly', 1),
('Curly', 1),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Larry', 2),
('Mo', 1),
('Mo', 1),
('Mo', 1),
('Mo', 2),
('Mo', 2),
('Mo', 2);

SELECT name 
     , raffle_number
     , ticket_number
FROM raffle
ORDER BY raffle_number, ticket_number;

+-------+---------------+---------------+
| name  | raffle_number | ticket_number |
+-------+---------------+---------------+
| Curly |             1 |             1 |
| Curly |             1 |             2 |
| Curly |             1 |             3 |
| Mo    |             1 |             4 |
| Mo    |             1 |             5 |
| Mo    |             1 |             6 |
| Larry |             2 |             1 |
| Larry |             2 |             2 |
| Larry |             2 |             3 |
| Larry |             2 |             4 |
| Larry |             2 |             5 |
| Mo    |             2 |             6 |
| Mo    |             2 |             7 |
| Mo    |             2 |             8 |
+-------+---------------+---------------+

 

 well im sorry YOUR way DOESNT fit my needs im glad you berate people because YOUR answer doesnt fit what they need . i didnt realise you were the php god and YOUR way is the only way it should be done.

 

Edited January 26, 2020 by nook6

[ginerjm]

But - he gave you the solution.

My idea was to use separate tables because I didn't realize one could have multiple auto-inc fields.  Since Guru Barand (!) knows that one can in fact do that , he has GIVEN you the proper solution.

Thank him for that code AFTER you apologize.  And then remember to tell people your entire situation before asking for help next time. 

PS - you don't really have to repeat every post in your next post.  Just a lot of wasted space.

[Barand]

9 minutes ago, ginerjm said:

I didn't realize one could have multiple auto-inc fields.

There is only one auto_inc column. It's just that myisam will allow it to be placed as the second part of a multi-column key and thus give multiple auto-incremented ranges.

Thus, in this case, assuring unique values of 1 to N for each raffle.

[ginerjm]

And I learn something new once again!

[Zane]

Perhaps you should re-phrase your question because, obviously, the solutions given to you aren't what you're looking for, as you've pointed out.

If you're simply trying to increment the ticket id/number by one every time a ticket is added to the table, then the above solutions are what you need.  From the code you provided, it almost looks as if you're trying to place the current count of tickets purchased at the time of the new ticket insertion.  For instance, George buy 5 tickets and at that point in time there had already been 80 tickets sold, so you put 80 + 5 to put in that column.  Or, are you just trying to store information in a table all by itself which contains nothing but calculated values that you could have created within the query, but you want to create those values manually?  Please, rephrase your question and don't just dismiss legit solutions because you don't think they'll work.  Explain why they won't work before you just shrug off the efforts of the volunteering experts here.

[Psycho]

Heck, I'd still go with the original "simple" auto-increment field solution. All that really matters is that the numbers are unique for each raffle. What does it matter if the number are not sequential! E.g.

Quote

User | Raffle | Number
Bob     1        1
Bob     2        2
Bob     2        3
Dave    1        4
Dave    3        5
Dave    3        6
Ed      1        7
Ed      1        8

Raffle #1 has four tickets sold with four unique numbers: 1, 4, 7, & 8.

Edited January 28, 2020 by Psycho