[SOLVED] inventory?

[arn_php]

I am working on a social organization where they are doing fund raising dinner parties. The attendance needs to purchase tickets to be able to present there. I am able to get the forms and etc but stumble into one issue. How can I limit the tickets?  Just say if the dinner party is only sold to 20 seats.. when the 20th ticket sold, then the ticket purchasing form should be closed automatically.

 

This is the table to sell the ticket.

 

CREATE TABLE `gtown_eticket` (

  `eticket_id` mediumint( unsigned NOT NULL auto_increment,

  `title_event` text NOT NULL,

  `category` smallint(1) NOT NULL default '0',

  `amount` text NOT NULL,

  `qty_ticket` text NOT NULL, ------> field to put the numbers of the ticket on each sell.

  `first_name` text NOT NULL,

  `last_name` text NOT NULL,

  `address` text NOT NULL,

  `city` text NOT NULL,

  `state` text NOT NULL,

  `zipcode` text NOT NULL,

  `phone` text NOT NULL,

  `email` text NOT NULL,

  `comments` text NOT NULL,

  `date_added` text NOT NULL,

  PRIMARY KEY  (`eticket_id`)

) ENGINE=MyISAM  ;

 

The table below is for the dinner/event that would sell the ticket.

 

CREATE TABLE `gtown_events` (

  `events_id` mediumint( unsigned NOT NULL auto_increment,

  `title` text NOT NULL,

  `short_description` text NOT NULL,

  `long_description` text NOT NULL,

  `host` text NOT NULL,

  `date_added` text NOT NULL,

  `price` text NOT NULL, ------------>Ticket price field

  `limitation_ticket` text NOT NULL, --------->The field that limit how many ticket needs to be sold.

  `field_display_order` int(10) unsigned NOT NULL default '0',

  `flag_status` tinyint(1) unsigned NOT NULL default '0',

  PRIMARY KEY  (`events_id`)

) ENGINE=MyISAM  ;

 

So the first table collects the data of the ticket buyer, including how many tickets would be included on each transaction.  Logically, it should be able to be counted to reach the same number with the number that limits the ticket that suppose to be sold on the other table, then the form would automatically closed or switched to a statement saying that The Tickets are SOLD OUT.

 

Any idea, how the php code should be to close the purchasing ticket form? Or maybe a javascript?

Thanks, any help is appreciated.

[Daniel0]

It doesn't look like you have a foreign key on gtown_eticket for gtown_event. If we say that you have a field in that table called events_id that links etickets to events then you can run a query like this:

 

SELECT SUM(
    SELECT SUM(qty_ticket)
    FROM gtown_eticket AS t
    INNER JOIN gtown_events AS e
      ON t.events_id=e.events_id
    GROUP BY e.eticket_id
  ) AS tickets_sold
  FROM gtown_events
  WHERE events_id = 123;

 

I haven't tested it, but I think it should work. It'll get you the amount of tickets sold for a particular event. Then you just have to check that it doesn't exceed the allowed amount using a simple conditional.

[arn_php]

Ok, I try to put it in as this one.. I use the code to close down the SUBMIT button of the form.

The category field of the gtown_eticket is the foreign key that connects this table to the gtown_events, collects the data from events_id. 

 

I tried it, but it doesn't work.. maybe the if(mysql_num_rows($result) == 0) is wrong. Or the whole code is wrong.. sorry for my error.

 

<?php

$query="SELECT SUM(

    SELECT SUM(qty_ticket)

    FROM gtown_eticket AS t

    INNER JOIN gtown_events AS e

      ON t.category=e.events_id

    GROUP BY e.eticket_id

  ) AS tickets_sold FROM gtown_events WHERE events_id = '$id'";

$result=mysql_query($query);

if(mysql_num_rows($result) == 0){

echo '<div align="center"><input type="submit" name="purchase" value="Purchase Ticket"></div>';

}else{

while($row=mysql_fetch_array($result))

{

echo '<div align="center"><strong><font size="4">TICKETS ARE SOLD OUT</font></strong></div>';

}

}

?>

[Daniel0]

Try to run the query directly on the db instead.

[arn_php]

SQL query: Documentation

 

SELECT SUM(

SELECT SUM(qty_ticket)

FROM gtown_eticket AS t

INNER JOIN gtown_events AS e ON t.category = e.events_id

GROUP BY e.eticket_id ) AS tickets_sold

FROM gtown_events

WHERE events_id =3

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(qty_ticket)

    FROM gtown_eticket AS t

    INNER JOIN gtown_events AS ' at line 2

 

That is what I put and this is the error message I got. I do need it to close the form though.. how would be the code to do so? Maybe a php or javascript code? Also I notice that I do not see the code that mention about the limitation of how many tickets should be sold.  So, when the numbers of the ticket (qty_ticket) of all the transactions (eticket_id, category=events_id) of certain events (events_id) match the ticket limitation (limitation_ticket) of that certain events then the form should be closed automatically and switch to a statement of TICKETS ARE SOLD OUT.

 

Thanks.. I guess I am totally lost here.

[Daniel0]

Hmm... I guess you cannot put a subquery in SUM(). I've moved your topic to MySQL in case somebody who knows more about MySQL than me has a solution.

[arn_php]

This is a php code I tried to put:

 

<?php

$query="SELECT * FROM gtown_events WHERE events_id='$id' and flag_status ='1' LIMIT 1";

$result=mysql_query($query);

$limitation="'.stripslashes($row['limitation_ticket']).'"

 

$query="SELECT SUM(

    SELECT SUM(qty_ticket)

    FROM gtown_eticket AS t

    INNER JOIN gtown_events AS e

      ON t.category=e.events_id

    GROUP BY e.eticket_id

  ) AS tickets_sold FROM gtown_events WHERE events_id = '$id'";

$result=mysql_query($query);

if(mysql_num_rows($result) == $limitation){

echo '<div align="center"><strong><font size="4">TICKETS ARE SOLD OUT</font></strong></div>';

}else{

while($row=mysql_fetch_array($result))

{

echo '<div align="center"><input type="submit" name="purchase" value="Purchase Ticket"></div>';

}

}

?>

[arn_php]

Sorry, I happened to click post before finishing up. So that was the code I tried then the page just going blank when you open it..? Hmm? Is there any guru out there?

[fenway]

Yeah, you can't do that.... SUM() can't take a result set.

[arn_php]

How about doing it like this:

BATAS (gtown_events) minus the number of all JUMLAH field (gtown_eticket) per CATEGORY (gtown_eticket, - events_id dari gtown_events), if the result is zero or minus, then the SUBMIT button would be changed to SOLDOUT statement.

 

I am using a mysql 4.1.22, this is the mysql dumb:

(please use this one and ignore the one above).

CREATE TABLE `gtown_eticket` (

  `eticket_id` mediumint( unsigned NOT NULL auto_increment,

  `title_event` text NOT NULL,

  `category` smallint(1) NOT NULL default '0',

  `donation_amount` text NOT NULL,

  `jumlah` text NOT NULL,

  `first_name` text NOT NULL,

  `last_name` text NOT NULL,

  `address` text NOT NULL,

  `city` text NOT NULL,

  `state` text NOT NULL,

  `zipcode` text NOT NULL,

  `phone` text NOT NULL,

  `email` text NOT NULL,

  `comments` text NOT NULL,

  `date_added` text NOT NULL,

  PRIMARY KEY  (`eticket_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

CREATE TABLE `gtown_events` (

  `events_id` mediumint( unsigned NOT NULL auto_increment,

  `title` text NOT NULL,

  `short_description` text NOT NULL,

  `long_description` text NOT NULL,

  `host` text NOT NULL,

  `tanggal` text NOT NULL,

  `price` text NOT NULL,

  `batas` text NOT NULL,

  `field_display_order` int(10) unsigned NOT NULL default '0',

  `flag_status` tinyint(1) unsigned NOT NULL default '0',

  PRIMARY KEY  (`events_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

[FaT3oYCG]

havent read whatever anyone else has put but how about just coding some php at the top of your page to find out how many tickets have been sold and put a field into your table that says how many tickets you can sell in total and the the number of rows of tickets is compared to the number of tickets you can sell in total and then you set a php variable either to the submit button or to a message so that you can later on echo the variable in your form and it will either have a submit button or have a message saying sold out so the user wont be able to submit the form solving your problem [/long sentance that has no formatting or punctuation aka wall of text]

[arn_php]

sorry, I am lost here.. but would you be able to write the php code you mentioned there? please know, I am not an expert, most likely I am only a novice here....

[arn_php]

no problem, I figure it out by myself. Thanks anyway.

[fenway]

no problem, I figure it out by myself. Thanks anyway.

Then post the solution, please!!!