In the database I save the list of selected products, in the table zakazi in the cell tovar .

The entry in the tovar cell has the following form: 2, 4, 4, 4, 4, 4 - where 2, 4 and so on - Product ID.

Next I want to bring all these products:

 $iddT = $tovar['tovar']; // имеет вид 2, 4, 4, 4, 4, 4 $catalog_tov = mysql_query("SELECT * FROM tovari WHERE id IN (".$iddT.") "); while ($tovar_c = mysql_fetch_assoc($catalog_tov)) { echo $tovar_c['id']."<br />"; //ID товара которые выводятся }

As a result, the page displays only:

 2 4

and all

The question of why the record does not display completely, in my case like this:

 2 4 4 4 4 4

Let me explain: the ID of the same product is repeated several times - this is in order to find out how many pieces of this product the user ordered.

  • why should? you put only filtering condition. - etki
  • @Etki Could you write this condition in response? Didn't quite understand - iKey
  • In mysql, the result of the query can not be several of the same records. Maximum one - Artem Y
  • @ Denis IN is looking for records in those IDs that are listed in it. And of course it displays each of the entries only 1 time. Read on the Internet about the "Normal Form" and never keep lists of something in one field. - Mike
  • @Denis A after the base is normalized i.e. given to at least 3 NF, you have the goods for the order will be in a separate table as separate rows, you can easily make a select from 2 tables at once and get all the goods for the order with all the properties in the desired quantity. SQL is intended to select any information of interest in a single query with any conditions and connections of several tables. Ps. And I would recommend to store the goods-quantity, rather than 1,1,1 to the order -

2 answers 2

Your base structure does not even correspond to 1 normal form . Samples from relational databases with such a structure are extremely complex, because SQL is designed to work with at least some normalized data.

But even your question can be solved with pure SQL, but with some perversions. To work, we need a service table with sequence numbers, it should contain records 1,2,3,4,5 ... in a row. The number of entries in this table should certainly be more than the ID of the orders in your row. In principle, it would be possible to use any table with a suitable number of records, but I prefer sequence numbers, such a table is useful in different situations. Let's call this table seqnum and seqnum 64 entries to it:

 create table seqnum(X int not null); insert into seqnum values(1); insert into seqnum values(2); insert into seqnum select X+2 from seqnum; insert into seqnum select X+4 from seqnum; insert into seqnum select X+8 from seqnum; insert into seqnum select X+16 from seqnum; insert into seqnum select X+32 from seqnum;

Next, we make a request that selects your line with the list 64 times (the number of records in seqnum ), seqnum first id from the current line and shorten the line to this first id at each iteration. Just in case, remove all spaces. Well, immediately re-glue it with your goods:

 select * from ( select trim(both " " from substr(@S,1,instr(@S,",")-1)) id, @S:=substr(@S,instr(@S,",")+1) from seqnum,(select @S:=concat("2, 4, 4, 4, 4, 4",",") as S) S where @S!="" ) A, tovari B where B.id=A.id

But even after this, our query is not at all optimal, you consider php records, which is not true in the cortex, MySQL itself can count the records perfectly:

 select A.*,count(1) as cnt from ( select trim(both " " from substr(@S,1,instr(@S,",")-1)) id, @S:=substr(@S,instr(@S,",")+1) from seqnum,(select @S:=concat("2, 4, 4, 4, 4, 4",",") as S) S where @S!="" ) A, tovari B where B.id=A.id group by A.id

This query will give us all the columns from the table of products and at the end another 1 column cnt with the number of such goods.

I wrote all this to show that SQL can do a lot, and not just select * from table , it is necessary to create a competent database structure, learn SQL more deeply and any work with data and development will go much faster. Your request, with the correct database structure, should look something like this ( zakTov is a table with product id and quantity of this product in the order):

 select A.*,B.cnt from tovari A, zakTov B where A.id=B.tov_id and B.zakaz_id=1

Agree, it looks obviously simpler than the previous parses of the list in the line.

    With your request you tell the DBMS the following: "Give me all the goods id, which is in the set {2, 4, 4, 4, 4, 4}"

    Accordingly, the DBMS finds a product with id = 4 and id = 2. There are no other products.

    To display a product as many times as it is in your list, it will be necessary to split the string 2, 4, 4, 4, 4, 4 by commas into an array using split() and walk through this array converting the string id to a number ( intval() ) and comparing with id in the array of selected products that were received from the database (in this case only 2) - and output the element that matched ( intval(strid) == $tovar_c['id'] )

    Although of course, if it is possible to change the database schema, do it.