insert dinamic data into static mysql table

[tealcsg]

Hi.my name is Teal'c and I'm from Croatia.I have a promblem with inserting dinamic data into static mysql table and I need help.I'm working in a project for a store.Table skladiste(=stock) is static. In there is list of all items (750 items) and now is empty. I need to fill it with data (amount) from 'order form' and 'receipt'. Data in these documents (tables kolicina(=amount), naziv_materijala(=name of item)) is dinamic. Let me explain.I put data from 'order form' into two tables (kolicina and naziv_materijala). Whay in two tables? Because I don't know how to put more diferent dinamic data into one table.Document 'order form' look like this:naziv_materijala | kolicina |----------------------------------item_17 | 25 |item_345 | 13 |item_623 | 758 |item_99 | 90 |----------------------------------Table skladiste (=stock) look like this (it's static, and allways the same):id | naziv_materijala | kolicina |----------------------------------------1. | item_1 | |2. | item_2 | |3. | item_3 | | . | . | | . | . | | . | . | |750. | item_750 | |----------------------------------------Column kolicina is empty and fills with values from table kolicina where naziv_materijala is in another table naziv_materijala.Table kolicina (=amount) look like this (this is dinamic table, depends of content in document 'order form'):id | kolicina |-----------------1. | 25 |2. | 13 |3. | 758 |4. | 90 |-----------------Table naziv_materijala (=name of items) look like this (this is also dimanic table and depends of content in 'order form'):id | naziv_materijala |----------------------------1. | item_17 |2. | item_345 |3. | item_623 |4. | item_99 |----------------------------I need to put values from the table kolicina (25, 13, 758, 90) in the table skladiste WHERE naziv_materijala=values from the table naziv_materijala (item_17, item_345, item_623, item_99)!I don't know how to build a query to do this!I hope that is clear enough, and if is not just ask and i will provide more details.thx in advance

[boen_robot]

I'm not sure I see the point of all this over separation... you can simply update the kolicina value of skladiste directly whenever new quantities are added, and you can use the names for your checks (if for whatever reason you can't use the IDs), like so:

UPDATE skladisteSET kolicina = 25WHERE naziv_materijala = 'item_17'

(constructed accordingly for each item by PHP of course)

[tealcsg]

I'm not sure I see the point of all this over separation... you can simply update the kolicina value of skladiste directly whenever new quantities are added, and you can use the names for your checks (if for whatever reason you can't use the IDs), like so:

UPDATE skladisteSET kolicina = 25WHERE naziv_materijala = 'item_17'

(constructed accordingly for each item by PHP of course)

Yes for static data, but this is all dinamic data and is not allways the same. In the previous post is just an example.I can't write SET kolicina=25 if it's maybe 55 or 253 or a 1000 because it depends of document 'order form' whose content is dinamic.I thinking something like this:

UPDATE skladiste SET kolicina = '$kolicina' (dinamic data from table kolicina) WHERE naziv_materijala = '$naziv_materijala' (dinamic data from table naziv_materijala)

but it doesn't workMaybe I started i wrong direction!?What I need is:From Web form ('order form') import data to a new Web form ('receipt') and save to table skladiste.Keep in minde that 'order form' is dinamic form of data (now is just a 5 rows, next time maybe it wil be 267, or 99, or 500 etc I don't know how meny row and which data will be in that form - this is that dinamic part - it's not a constant).This is a...

<?php foreach($kolicina as $kol)   {	  print "<table width=\"100%\" border=\"0\" >  <tr>	<td align=\"center\" height=\"25\"><input name=\"kolicina[]\" type=\"text\" id=\"kolicina\" size=\"10\" maxlength=\"10\" value=\"$kol\" /></td>  </tr></table>";   }?>

...part of script in 'order form' for the values of kolicina where as a result I get rows of data in previous post.And data that look like this in script...

<?php for ($i=0; $i<$broj_nm; $i++)	{foreach($kolicina as $kol)	{		$broj_kolicina[]=$kol;// this is number of how meny rows of data I heave	}	if($broj_kolicina[$i]!='0')	echo "<input name=\"kolicina[]\" type=\"text\" id=\"kolicina[$i]\" size=\"5\" maxlength=\"5\" value=\"$broj_kolicina[$i]\" /><br>"; else echo ""; }?>

...I need to store in table skladiste.If this is wrong way, or someone heave better idea I will gladly listen to advice.thx

[Ixzion]

I think in the "Order Form", instead of using naziv_materijala | kolicina |----------------------------------item_17 | 25 |item_345 | 13 |item_623 | 758 |item_99 | 90 |----------------------------------It might be easier to do this:naziv_materijala_id | kolicina |----------------------------------17 | 25 |345 | 13 |623 | 758 |99 | 90 |----------------------------------Where the id is equal to the id of the table skladiste. That would make it easier to reference, I believe.

[[dx]]

Well, you can make one table with ID, naziv_materijala and kolicina. Then as boen_robot explained.| -- ID -- | -- naziv_materija -- | -- kolicina -- |------------------------------------------------------| 1 | nesto 1 | 3 || 2 | nesto 2 | 4 |etc.And as he stated UPDATE skladisteSET kolicina = 5WHERE naziv_materijala = nesto 1

[boen_robot]

kolicina is of type integer, right? You don't need quotes around it then... but what's more important is that if the value needs to be relative to the existing one, you can easily define aritmetic operations on it, like:

$query = "UPDATE skladisteSET kolicina = kolicina + " . (int) $kolicina . "WHERE naziv_materijala = 'item_17'";

I don't know for sure, but I think you should also be able to do:

$query = "UPDATE skladisteSET kolicina += " . (int) $kolicina . "WHERE naziv_materijala = 'item_17'";

(again, try it, it may not work)Offtopic://Damn, slavonic languages again... I too understood everything, even without translations, including "thing" (mentioned by Haris S, above). I feel so cool when that happens, even though I know it's a falsy feeling (because that's the case historically, and not by some sort of agreement) .

[tealcsg]

thx guys,i will try