Closed Thread Icon

Topic awaiting preservation: Is this possible in PHP and MySQL? Pages that link to <a href="https://ozoneasylum.com/backlink?for=29914" title="Pages that link to Topic awaiting preservation: Is this possible in PHP and MySQL?" rel="nofollow" >Topic awaiting preservation: Is this possible in PHP and MySQL?\

 
Author Thread
fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 14:43

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,

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-24-2008 15:13

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.

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 15:27

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.

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 16:00

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

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

posted posted 01-24-2008 16:04
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:\

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 16:53

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. :/

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-24-2008 17:36

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

code:
$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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 17:50

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

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana
Insane since: Aug 2000

posted posted 01-24-2008 20:47

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

.



-- not necessarily stoned... just beautiful.

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 21:28

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!

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-24-2008 23:02

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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-24-2008 23:24

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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-26-2008 21:24

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?

rukuartic
Bipolar (III) Inmate

From: Curled up with a ... ...warm laptop?
Insane since: Jan 2007

posted posted 01-26-2008 22:58

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

Your input line would look something like this...

code:
// 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)

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-26-2008 23:19

Thank you! I'll try that.

rukuartic
Bipolar (III) Inmate

From: Curled up with a ... ...warm laptop?
Insane since: Jan 2007

posted posted 01-27-2008 00:49

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"); ?>

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-27-2008 02:00

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

Suho1004
Maniac (V) Mad Librarian

From: Seoul, Korea
Insane since: Apr 2002

posted posted 01-27-2008 13:19

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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-27-2008 13:44

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

poi
Paranoid (IV) Inmate

From: Norway
Insane since: Jun 2002

posted posted 01-27-2008 14:03

Glad to see your squirrel is still there

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-27-2008 14:55

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.

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-27-2008 18:36

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?

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-27-2008 19:20

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!

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-28-2008 23:18

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:

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:

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?

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-29-2008 09:42

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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-29-2008 12:15

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:

code:
$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)

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-29-2008 12:59

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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-29-2008 13:36

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

code:
$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)

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-29-2008 14:01

php->strpad will do what you need.

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-29-2008 14:16

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

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-30-2008 00:21

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:

code:
$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)

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana
Insane since: Aug 2000

posted posted 01-30-2008 04:22

Change

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



To

code:
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)

fenja
Bipolar (III) Inmate

From: Norway
Insane since: Mar 2002

posted posted 01-30-2008 11: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!

reisio
Paranoid (IV) Inmate

From: Florida
Insane since: Mar 2005

posted posted 01-31-2008 00:24
quote:
Suho1004 said:

long time no see, fenja


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

« BackwardsOnwards »

Show Forum Drop Down Menu