Graph for counting lines in a field and order

Question

I have a field in a table recipesthat was inserted with mysql_real_escape_string, I want to count the number of line breaks in this field and arrange the records using this number.

ps box called Ingredients.

Thank you all

Answer 1

This would do it:

SELECT *, LENGTH(Ingredients) - LENGTH(REPLACE(Ingredients, '\n', '')) as Count
FROM Recipes
ORDER BY Count DESC

However, I get the number of lines, it’s a little hack, and I don’t think that there is a better way. I would recommend keeping a column with the number of rows if the performance is huge. However, for medium sized datasets, I believe this should be good.

If you want to have a cache column as described above, you should:

UPDATE
    Recipes
SET
    IngredientAmount = LENGTH(Ingredients) - LENGTH(REPLACE(Ingredients, '\n', ''))

, , / , (, PHP) . , , triggers.

Answer 2

, , , .

, , , . , , , .., . ( )

, - . - levenshtein() similar_text() PHP , , [].

, , .

Answer 3

SQL . , .

, , , , , . "num_linebreaks", Indgredients.

If you do not control the application that performs the insertion, you can use the stored procedure to update the num_linebreaks based on the trigger.

Answer 4

Thanks, the PHP code looks like this:

$check = explode("\r\n", $_POST['ingredients']); 
$lines = count($check);

So, how can I update all the information in the table, so Ingred_count based on the Ingredients fields in one fell swoop for previous records?