Topic awaiting preservation: Is this possible in PHP and MySQL?

Hi! I'm not sure if PHP and MySQL is what I need to be able to create a database to use at work.

I'll try to explain. I want to have a website with a form. I want to be able to write "20080101" and click submit, and then a new table in MySQL is created and called "20080101". Is this possible? My whole plan is to have a form where I can write order number/new job and then have lots of fields to write width, height and type of a product. Then be able to find the job in the database by using the order number (20080101).

I'm still new to PHP and MySQL so I don't know if it's possible to do this.

Thanks,

Yes, this is possible,
and at the same time a stupid idea.

You'd simply create a table of
orderNo, width, height, type, ...

and filter that by the orderNo
instead of creating a new table each time.

That way, you get all the cool things of a relation algebra - instead of just a really annoying way to store data.

But a new job will hold lots of information. Like if my job is called 20080101 and I write:

8stk - 150,3 x 180,5 - 3 - A - 4 X 3
5stk - 150,3 x 180,5 - 3 - A - 2 X 3
3stk - 150,3 x 180,5 - 3 - A - 2 X 2
8stk - 150,3 x 180,5 - 3 - A - 2 X 3

I want to get this information later. Then I create a new job called 20080102 and write:

1stk - 50,3 x 180,5 - 3 - B - 4 X 3
1stk - 82 x 180,5 - 3 - C - 2 X 3
1stk - 150,3 x 180,5 - 3 - A - 2 X 2
1stk - 150,3 x 180,5 - 3 - I - 2 X 3

and get this information later. Shouldn't I create a new table for each orderNo? Maybe I've been working on this for too long and can't think straight.

Never mind, I get it now, lol! Thanks you so much!

quote:

---

fenja said:

Shouldn't I create a new table for each orderNo?

---

Not a new *table* a new *entry* in the existing table.

So you have a table called "Entries" (or something) inside your table you will have the fields :

OrderNo Type Height Width Etc...

Make sense? You don't need a new table for every entry, you just need one table to hold all the entries. Your search function is going to be a heck of a lot easier and faster.

Later,

C:\

Thank you. I have another question. Is it possible for me to output everything into a table on a webpage and take maybe the value of width, add the number 5 to it and output that to a different cell in the table? These might be stupid questions, but I'm a newbie. :/

Sure. Either do it in php, or simply get the databse to do it...


select width, width + five as "With_Plus_Five" from your_table;


or in your case

$hRes = mysql_query('select *, width + five as "with_plus_five" from your_table order by orderNo');
print '<table>';
while ($arrRow = mysql_fetch_array($hRes))
{
   print '<tr>';
   foreach ( $arrRow as $strField)
   {
     print '<td>'. $strField .'</td>';
   }
   print '</tr>;
}
print '</table>';

so long,

->Tyberius Prime

Thank you! Bookmarked this thread. I really hope I can get everything the way I want it.

Hi Fenja,
Haven't seen you for a while. Hope you're doing OK!

.



-- not necessarily stoned... just beautiful.

Hi! It's been a while since I've been here. I'm a carpenter now and make furniture. But I really want to get back to web design and start posting here again. I bet this place is still as cool as it was years ago!

hey... I'll be building a bar soon
Good to know you're around - I might have some questions of my own!


And I'm sure you'll get your web project the way you want it .

Glad to have you back,
so long,

->Tyberius Prime

If you have any questions, I'll do my best to help. We should probably open a new woodworking forum in here...

Okay, I've been working on this and have come across a problem. When I'm going to add a new job to the database, I want it to automatically add the date (I've figured out how to do this). But I don't want the whole date, just the year and month: 2008-01. I want the next number to be the order number for that month. Like if I add 5 jobs in January, the whole order number for those jobs would be:

2008-01-01
2008-01-02
2008-01-03
2008-01-04
2008-01-05

Then when February comes, I want it to start the count on 1 again. So if I add 50 jobs in Feb, the order number on the last job would be:

2008-02-50

Is there a simple way to do this?

id -- unsigned int (auto_increment, primary key)
ordernumber -- unsigned int
...

Your input line would look something like this...

// This gets the number of orders starting with "2008-01-..."
$order_number = date("Y-m-");
$query = "SELECT `id` FROM `orders` WHERE `ordernumber` LIKE '" . $order_number . "';";

// The number of rows we find are the number of orders we've had this month.
// Add one to this, as it will be the next order.
$num_orders_this_month = mysql_num_rows(mysql_query($query));
$order_number .= ($num_orders_this_month + 1);

// $order_number = "2008-01-5" or something like that
// You can use a zerofill function to make it 2008-01-05 if you want.
$query = "INSERT INTO `job_queue` VALUES('', NOW(), '" . $order_number . "', ...
mysql_query($query);

Needless to say, using the datetime datatype would be great, because you can run functions on it. There are better ways of doing this.

<? include("signature.php"); ?>

(Edited by rukuartic on 01-26-2008 22:58)

(Edited by rukuartic on 01-26-2008 22:59)

Thank you! I'll try that.

Make sure you know what the code's doing though, and how it can be handled better. Both PHP and MySQL have excellent online documentation.

<? include("signature.php"); ?>

I'm still learning PHP and MySQL and I think working on a project like this is fun while learning it. I do have a question about the code above. Do I have to create a new table called 'job_queue'? Is it not possible to store the ordernumber in 'orders'? Thanks

I have absolutely nothing to add to this topic, except: long time no see, fenja! I missed the little squirrel.


___________________________
Suho: www.liminality.org | Cell 270 | Sig Rotator | the Fellowship of Sup

I was actually quite surprised to see it myself when I posted, hehe! "Oh, my squirrel is still here!!"

Glad to see your squirrel is still there

I have another question. Should I create a table for each customer/firm? We have 3-4 big firms that we produce window frames to. (not sure if window frames is the correct word in English..) When I add a new huge order to the customer 'Remi', is it best to create a table for him and store all his orders in there? I think one of my problems is figuring out what structure I should use for my database.

no, you don't create a new table for each customer .

You create table for 'kinds of data'.
Ie. you have one table with customers, one with producs, one with order details and one with order items,
and you *link* one with the other.

so customer would look like
id
name
address1
address2
telephone
...

products would look like
id
description
costPerUnit
...

order details would pretty much be
id
customerId
date
...

and order items would be a table containing
id
orderId
productId
quantity



Catching my drift?

ah, I see! That should keep me busy for a while. I'm going make a test database and see if I can get everything to work. Thank you!

Okay, I'm back! I've made 3 tables called bestillinger (orders), sprosser (window frames) and kunder (customers).

Kunder
-kid
-name
-address
-tel

Sprosser
-sid
-quantity
-width
-height
-no
-type
...

Bestillinger
-id
-date
-orderid
-sid (sprosse id)
-kid (customer id)

I made a page with a table that showed customer name, order date, type.. by using this code:

$query ="SELECT 
kunder.navn, 
bestillinger.orderid, 
bestillinger.dato, 
sprosser.type, 
sprosser.antall 
FROM kunder, sprosser, bestillinger WHERE (kunder.kid = bestillinger.kid)";

That worked very well! Then I made an order form. I filled out the form and hit submit. Everything worked fine except 'sid' didn't show up automatically in the 'bestillinger' table in the column 'sid'. When I fill out the form, Sprosser gets an id automatically. I want that same id to show up in the table bestillinger when I click submit.

Here are a part of the code:

$query = "INSERT INTO sprosser VALUES ('$sid','$antall','$bredde','$hoyde','$malenr','$type','$rutb','$ruth','$merk')";
mysql_query($query);

$query = "INSERT INTO bestillinger VALUES ('', NOW(), '$orderid', '$sid','2')"; 

mysql_query($query);

How can I fetch that 'sid' from sprosser and put it in 'sid' in bestillinger when the user clicks submit form?

php->mysql_insert_id() is what you're looking for.

Thanks! It works! Now all I have to do is figure out how to show the ordernumber the way I want. I've been working on the code I got above, and it shows the year and day + 1 like this: 2008011. But it shows up like that in every new row and it won't count the order id. Here's the code I'm using:

$order_number = date("Ym");
$query = "SELECT bestillinger.id FROM bestillinger WHERE bestillinger.orderid LIKE '" . $order_number . "'";

$num_orders_this_month = mysql_num_rows(mysql_query($query));
$order_number .= ($num_orders_this_month + 1);

$query = "INSERT INTO bestillinger(id, dato, orderid, sid, kid) VALUES ('', NOW(), '" . $order_number . "', '$sid','2')";

I don't understand why it won't add +1 to the new row?

Edit: I also want to add that I've been reading the PHP manual, but I find it difficult to figure out what to use where when I'm writing the code. I guess it will get easier as I continue working on this.

(Edited by fenja on 01-29-2008 12:21)

Because you're writing the first query with like '200801' insteod of like '200801%'.

Anyhow you should use the sql aggregation function... so that first query could read

$query = "SELECT count(bestillinger.id) as cnt FROM bestillinger WHERE bestillinger.orderid LIKE '" . $order_number . "'";

$hRes = mysql_query($query);
$arrRow = mysql_fetch_array($hRes);
$order_number .= $arrRow['cnt'];.


That way the database can do it's magic and save on processing time.

So long,

->Tyberius Prime

I got the first code to work by changing it a little:

$order_number = date("Ym");
$query = "SELECT bestillinger.id FROM bestillinger";

$num_orders_this_month = mysql_num_rows(mysql_query($query));
$order_number .= ($num_orders_this_month+1);

I've written down your code above and I'll try it when I come home. Thanks!

EDIT: I just figured out how to get the zero in front of the number...



(Edited by fenja on 01-29-2008 14:14)

php->strpad will do what you need.

Ah! That will work even better than the way I did it.

Hmm...I'm stuck again. How can I show all the orders from one specific customer? I thought the code I posted earlier worked, but I only had one order in the database. When I added 3 more orders, it showed the 4 orders 4 times. Here's the code again:

$query ="SELECT 
kunder.navn, 
bestillinger.orderid, 
bestillinger.dato, 
sprosser.type, 
sprosser.antall 
FROM kunder, sprosser, bestillinger WHERE (kunder.kid = bestillinger.kid)";

$result=mysql_query($query);
$num=mysql_numrows($result);

I guess I'm not supposed to count the rows? If John has 3 orders and Michael has 2 orders, what code do I use to list them?



(Edited by fenja on 01-30-2008 00:22)

Change

FROM kunder, sprosser, bestillinger 
WHERE (kunder.kid = bestillinger.kid)

To

FROM  bestillinger 
   LEFT OUTER JOIN kunder 
      ON (kunder.kid = bestillinger.kid) 
   LEFT OUTER JOIN  sprosser 
      ON ( sprosser.sid = bestillinger.sid)

I think that will do what you want. The reason you're getting four copies of each order is that you joined to sprosser without specifying a column to match so it matched every value in sprosser to the results you got from the rest of your join.

Let's say that WHERE (kunder.kid = bestillinger.kid) matches one order and there are four rows in sprosser. Since you did not specify a value to match from sprosser, but you did specify that you want a couple of columns from sprosser selected, the server gives you all the rows in sprosser beside the value for the order you asked for.

I hope that makes sense. It's a little bit complicated and I think I'm too tired to be explaining this.




-- not necessarily stoned... just beautiful.


(Edited by hyperbole on 01-30-2008 04:31)

That fixed it! I now have a page with the new orders showing. I'm going to work with what I have before doing anything more. I really appreciate all the help from you guys and I've learned a lot!

quote:

---

Suho1004 said:

long time no see, fenja

---

Ditto. Beautiful coders of beautiful code should not take such long leave.