Gentlemen, you need help in formulating a request to the database, or maybe advice on how to organize everything more competently. Given 2 sql tables in the first stored data on the drawings table format id | ch | onop | twop | thop | thop | in the columns | onop | twop | thop | contain rates for each operation. The second table is a report for the day (history) using data from the first table in the format | operator | detal | operaciya | cena | which is filled from the usual web form from selects and one drop-down list from the database. Here is the question, the whole thing is displayed in the html face and everything would be great, but you need to write down the price of the operation of each product, and here I told how to do this without a clue. Everything is written in history remarkably through joins, but the variable from the select with the choice of operation is written in the database with the name of the column. I just need good advice or at least some kind of help, I rummaged through a lot of literature on sql and pkhp and killed it for a week already, although I am sure that it is easier to implement

  • sorry, off topic, but grammar and spelling - fire :) - teran

2 answers 2

You have two tables:

The first (table1) describes the details, fill it only with details.

In the second (table2) operation, write the type of operations into it, indicating in the detal column (id of the first column)

When outputting, do a cross-query for the example like this:

SELECT table2.* FROM table2 LEFT JOIN table1 ON table1.id=table2.detal

Well, output the result.

  • I thought in this direction, but the question was how to put a face into the web, there is a table in which data is taken through a join based on a history table with columns | date | operator | details | price / item | and I don’t know how to slip the price into this table, or rephrase if you tell the script to take data from the column from the second operation, for example - freakrock
  • In this table I print $strSQL = "select * from chertesh,cnc where detal=id iner "; $res = mysql_query($strSQL); while($row = mysql_fetch_array($res)) { $strSQL = "select * from chertesh,cnc where detal=id iner "; $res = mysql_query($strSQL); while($row = mysql_fetch_array($res)) { $strSQL = "select * from chertesh,cnc where detal=id iner "; $res = mysql_query($strSQL); while($row = mysql_fetch_array($res)) { echo '<tr> and through the row in each column its data, in general, everything is correctly displayed, but how can I not insert my mind here for the price - freakrock
  • So from the above, I see that the data from the second table is not taken. $ strSQL = "select * from chertesh LEFT JOIN chertesh ON chertesh.id = cnc.detal where detal = id"; So try and add a column cena - Ruslan Semenov
  • In general, I came out select * from oi JOIN chertesh ON id = id_ch where id_ch=id and yes you were right the columns appeared but with them there was a question now I have output | onop | twop | thop | Let's say, how can we now attach an operaciya column to them from the history table where the number of this operation is recorded so that only one value we need is displayed as a result. Or it may be better to change the values ​​in the operation select list now there are stupid names of operation columns, that is, onop, twop . - freakrock
  • or now I decided to try a workaround in the script of the history fill handler to immediately replace the values ​​with variables and find the required $cena = mysql_query(" select '".$operaciya."' from oi LEFT JOIN chertesh ON id =" ". $ detal." '"); $ cena = $ _ POST ['cena']; $ result = mysql_query ("INSERT INTO cnc (operatorname, cena, operaciya, detal, colvodetal, brakop, data, brakotk) VALUES ('". $ operatorname. "', '". $ cena. "', '". $ operaciya. "','". $ detal. "','". $ colvodetal. "','". $ brakop. "','". $ data. "','". $ brakotk. "') ");` but it was a fiasco here, the request works in the admin and not in the script ( - freakrock

In general, I won the task by adding another table that indicates which data belongs to which operation, but it is not the easiest way to work. I make a selection: select * from |Ρ‚Π°Π±Π»ΠΈΡ†Π° ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‰Π°Ρ ΠΊΠ°ΠΊΠΎΠΌΡƒ Ρ‡Π΅Ρ€Ρ‚Π΅ΠΆΡƒ ΠΊΠ°ΠΊΡƒΡŽ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΡŽ Π±Ρ€Π°Ρ‚ΡŒ| , |Ρ‚Π°Π±Π»ΠΈΡ†Π° с "историСй"| LEFT JOIN |Ρ‚Π°Π±Π»ΠΈΡ†Π° со списком Ρ‡Π΅Ρ€Ρ‚Π΅ΠΆΠ΅ΠΉ| ON |id Π΄Π΅Ρ‚Π°Π»ΠΈ| = id left join |Π½ΠΎΠΌΠ΅Ρ€ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ| on |опСрация ΠΈΠ· истории| = |id ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ с Ρ‚Π°Π±Π»ΠΈΡ†Π΅ΠΉ ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‰Π΅ΠΉ ΠΊΠ°ΠΊΠΎΠ΅ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ Ρƒ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ| select * from |Ρ‚Π°Π±Π»ΠΈΡ†Π° ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‰Π°Ρ ΠΊΠ°ΠΊΠΎΠΌΡƒ Ρ‡Π΅Ρ€Ρ‚Π΅ΠΆΡƒ ΠΊΠ°ΠΊΡƒΡŽ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΡŽ Π±Ρ€Π°Ρ‚ΡŒ| , |Ρ‚Π°Π±Π»ΠΈΡ†Π° с "историСй"| LEFT JOIN |Ρ‚Π°Π±Π»ΠΈΡ†Π° со списком Ρ‡Π΅Ρ€Ρ‚Π΅ΠΆΠ΅ΠΉ| ON |id Π΄Π΅Ρ‚Π°Π»ΠΈ| = id left join |Π½ΠΎΠΌΠ΅Ρ€ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ| on |опСрация ΠΈΠ· истории| = |id ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ с Ρ‚Π°Π±Π»ΠΈΡ†Π΅ΠΉ ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‰Π΅ΠΉ ΠΊΠ°ΠΊΠΎΠ΅ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ Ρƒ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΠΎΠΏΠ΅Ρ€Π°Ρ†ΠΈΠΈ| I think it will be necessary for someone who understood exactly what I wanted to achieve) Thank you for the rest and especially Ruslan_Semenov for the tip on Left Join