How to remove from the database in the column xfields only the word after |label| they are not present in all cells.

These I cells with such content audio|name.mp3||image|name.jpg||wave|name.jpg||data|06.03.2017||label|Vision

or

audio|name.mp3||image|name.jpg||wave|name.jpg||data|06.03.2017

or

audio|name.mp3||image|name.jpg||wave|name.jpg||label|Vision

It should look like this:

Vision - 50 tracks

Spinnin - 35 tracks

Sony - 20 tracks

....

enter image description here

We do this in order, this code displays all the fields xfields

audio|name.mp3||image|name.jpg||wave|name.jpg||data|06.03.2017||label|Vision

etc. How to deduce from this line only the word Vision ? And count in how many cells is it present?

 <?php if(!defined('DATALIFEENGINE')) { die("Hacking attempt!"); } global $config; $limit = $limit ? intval($limit) : "20"; if (!$r_short) { $sql = $db->query("SELECT * FROM " . PREFIX . "_post ORDER BY id DESC LIMIT 0,{$limit}"); while ($row = $db->get_row($sql)) { $r_short .= "<p>{$row['xfields']}</p>"; } } echo $r_short; ?>

PS

I made that only these words were output, which after label| . Now it is necessary that the repeated words be counted, and no void is displayed, if there is no label| .

 <?php if(!defined('DATALIFEENGINE')) { die("Hacking attempt!"); } global $config; $limit = $limit ? intval($limit) : "20"; if (!$r_short) { $sql = $db->query("SELECT * FROM " . PREFIX . "_post ORDER BY id DESC LIMIT 0,{$limit}"); while ($row = $db->get_row($sql)) { echo '<p>'; echo explode('label|', $row['xfields'])[1]; echo '</p>'; } } echo $r_short; ?>

    3 answers 3

    You do not have the right approach to solving common problems. Here is one of the solutions to the problem:

    You need to implement relationships between the tables. In your table, the xfields column xfields not be in principle. Instead of this column, you need to create a separate table in the xfields database.

    You did not popka name of your current table, so we give it the name popka The table structure xfields will be something like this: id - id xfield - your keyword popka_id - id, element (host) in the popka table

    Thus, in order to get the number of "xfield" for example, for the popka element with id 123 it is enough to run the following query

     SELECT count(*) from `xfields` where `xfields`.`popka_id` = 123

    In order to calculate the grouped number of "hfildov" for "ass" 123 request is not tricky:

     SELECT count(*) as 'count', `xfield` FROM `xfields` WHERE `xfields`.`popka_id`=123 GROUP BY `xfields`.`xfield` ORDER BY count(*) DESC

    And of course, when creating a new xfield, you need to specify the owner to whom this xfield belongs.

    • The table is written in the code FROM " . PREFIX . "_post I can’t get a separate table, this is how much the site writes and much is associated with this table (search, etc.). - steep
    • How to collect all this? - steep
    • Changed the output line $r_short .= "<p>... to this echo explode('label|', $row['xfields'])[1]; now only the right words are output. How to count the repetition of these words and how else <p></p> substitute this line? - steep
    • Well, you yourself have chosen the path of suffering and torment) - qpi
    • What other way is there without changing the table? - steep

    Well, you go through the results of your query by rows while ($row = $db->get_row($sql)) { } . Well, that's it. Before this cycle, start a counter or array. Each value obtained in the cycle is cut by the divisor '|' function explode(delimiter, string) . As a result, you get an array of all the values ​​in your line. Well, after that you are doing with this array what your heart desires, Go through, compare, get by index. Increase your counter if you find the right one. Etc.

      Something like this:

       select substring_index(substr(xfields,instr(concat('||',xfields),'||label|')+6),'|',1) label, count(1) cnt from x_post where concat('||',xfields) like '%||label|%' group by label

      To work with such strings, learn the MySQL string functions . Although it would be better to modify the structure of the database. Even if the data in this table is written by a certain engine, you can finish the database without changing its code. To do this, you need to create a separate table with the fields post_id, key, val in which the keys and their values ​​would lie in separate rows. You can populate this table automatically with a trigger to change the post table. The trigger will take xfields, break it apart by || and insert in a separate table in separate rows. With some desire, you can do this even with a single request.

      And your php string did not get right. Since this is a kind of engine, you can not guarantee that the label field will be the last. And if it is not the last your explode will give the wrong result.

      • I figured out the output, there is already configured to work with arrays in the engine, I just need to group it, but this is in another question, only it was indicated as a duplicate. ru.stackoverflow.com/questions/635804 - steep
      • @steep And why is he so different from this? You in the same question asked to group you wrote here “ It should look like this: Vision - 50 tracks Spinnin - 35 tracks Sony - 20 tracks ” and my answer is exactly that grouped and gives ... - Mike
      • You still say that the pictures are the same. You look at my code in another question, there is an implementation without explode , if I did as written here in the answers, I would not ask another question. And if the decision is different, I asked a new question. - steep
      • @steep OK, so what’s the difference between the output of the query I’m giving from what you wanted to get for this question and how it differs from the one you’d expect from the answer to that second question - Mike
      • You yourself wrote above what differs `And if it is not the last one your explode will give an incorrect result.` By this you made the settings of the engine. - steep