a little guidance please

[phppup]

Let me start bty saying that I'm not looking for anybody to write the code for me, but I am considering a function and am not sure of the best approach (or whether something exists in either PHP or MySQL that will handle this directly).

 

Note: I have not developed the database for this project yet, so perhaps there are clues or shortcuts at that level.

 

What I am intending is to track orders.  The items on these orders (let's say pants, shirts, socks and hats) will be input to a central database under a customer name with associated customer information.

 

The datbase will be used to provide item quantities so that inventory issues can be addressed.

 

Here's the twist.  Each item may be handled by a different department.  In fact, if someone wants 4 hats it will require 4 'tickets' (one for each) to be processed.  What I want to do is include a statement on each ECHOed ticket that would state "This is item number x of y pieces for this order."

 

The ordering of the count can be arbitrary, as long as I can provide an accounting of items beginning with the number 1 for each customer.

 

So, do I use the COUNT function, a formula with an n+1, or should I be thinking in terms of longwritten IF statements (probably the wrong approach/more difficult path -- which explain the reason for this post).

 

Your guidance is greatly appreciated.

[Dizel]

if i understood you right you want to make the update button for # of quenities?

you should keep your items in array like this

$this->items[$id]['qty'] = 1;

and if the user has pressed the update button $this->items[$id]['qty'] + 1;

that's the basics but you have to write the 2 or 3 different functions

[xyph]

Edit - nevermind. I see what you're doing. Here's how I'd do it.

 

[xyph]

I'd have two top-level tables.

 

customers - Would store all customer data

items - Would store all item data

 

Then, I'd have a secondary table

orders - Would store each individual order, linked to a customer's ID

 

And I'd make a table for items listed in the orders

tickets - Each item would go in here, linked to an order's ID

 

Here's my dump

--
-- Database: `db`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `name`) VALUES
(1, 'bill'),
(2, 'tom');

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `name`) VALUES
(1, 'hat'),
(2, 'shirt'),
(3, 'pants');

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `customer_id`) VALUES
(1, 1),
(3, 1),
(2, 2);

-- --------------------------------------------------------

--
-- Table structure for table `tickets`
--

CREATE TABLE IF NOT EXISTS `tickets` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` smallint(5) unsigned NOT NULL,
  `item_id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `tickets`
--

INSERT INTO `tickets` (`id`, `order_id`, `item_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 1),
(5, 2, 3),
(6, 3, 2);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

--
-- Constraints for table `tickets`
--
ALTER TABLE `tickets`
  ADD CONSTRAINT `tickets_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

 

And here's how I'd access and manipulate that data.

 

View all tickets

mysql> SELECT
    ->   tickets.id as ticket_id,
    ->   orders.id as order_id,
    ->   customers.name as customer_name,
    ->   items.name as item_name
    -> FROM tickets
    -> LEFT JOIN
    ->   orders ON tickets.order_id = orders.id
    -> LEFT JOIN
    ->   customers ON orders.customer_id = customers.id
    -> LEFT JOIN
    ->   items ON tickets.item_id = items.id;
+-----------+----------+---------------+-----------+
| ticket_id | order_id | customer_name | item_name |
+-----------+----------+---------------+-----------+
|         1 |        1 | bill          | hat       |
|         2 |        1 | bill          | shirt     |
|         3 |        1 | bill          | pants     |
|         4 |        2 | tom           | hat       |
|         5 |        2 | tom           | pants     |
|         6 |        3 | bill          | shirt     |
+-----------+----------+---------------+-----------+
6 rows in set (0.00 sec)

 

You can easily modify that to only select tickets for a certain user, or items in a certain ticket

mysql> SELECT
    ->   tickets.id as ticket_id,
    ->   orders.id as order_id,
    ->   items.name as item_name
    -> FROM tickets
    -> LEFT JOIN
    ->   orders ON tickets.order_id = orders.id
    -> LEFT JOIN
    ->    items ON tickets.item_id = items.id
    -> WHERE orders.customer_id = 1;
+-----------+----------+-----------+
| ticket_id | order_id | item_name |
+-----------+----------+-----------+
|         1 |        1 | hat       |
|         2 |        1 | shirt     |
|         3 |        1 | pants     |
|         6 |        3 | shirt     |
+-----------+----------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->   tickets.id,
    ->   items.name as item_name
    -> FROM tickets
    -> LEFT JOIN
    ->    items ON tickets.item_id = items.id
    -> WHERE tickets.order_id = 1;
+----+-----------+
| id | item_name |
+----+-----------+
|  1 | hat       |
|  2 | shirt     |
|  3 | pants     |
+----+-----------+
3 rows in set (0.00 sec)

 

And when adding an order, transactions will help you ensure integrity

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql>   INSERT INTO orders ( customer_id ) VALUES ( 2 );
Query OK, 1 row affected (0.01 sec)

mysql>   INSERT INTO tickets ( item_id, order_id )
    ->     VALUES
    ->       ( 3, LAST_INSERT_ID() ),
    ->       ( 2, LAST_INSERT_ID() ),
    ->       ( 1, LAST_INSERT_ID() );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

[phppup]

Looks like I'd better get some sleep before jumping into this one.  But thanks for the help.