I have a field in a table recipes that has been inserted using mysql_real_escape_string, I want to count the number of line breaks in that field and order the records using this number.
我在使用mysql_real_escape_string插入的表格配方中有一个字段,我想计算该字段中的换行符数量并使用此数字对记录进行排序。
p.s. the field is called Ingredients.
附:该领域被称为成分。
Thanks everyone
4 个解决方案
#1
This would do it:
这样做:
SELECT *, LENGTH(Ingredients) - LENGTH(REPLACE(Ingredients, '\n', '')) as Count
FROM Recipes
ORDER BY Count DESC
The way I am getting the amount of linebreaks is a bit of a hack, however, and I don't think there's a better way. I would recommend keeping a column that has the amount of linebreaks if performance is a huge issue. For medium-sized data sets, though, I think the above should be fine.
然而,我获得线路数量的方式有点像黑客,我不认为有更好的方法。如果性能是一个巨大的问题,我建议保留一个具有换行量的列。但是对于中型数据集,我认为上面应该没问题。
If you wanted to have a cache column as described above, you would do:
如果您想拥有如上所述的缓存列,您可以:
UPDATE
Recipes
SET
IngredientAmount = LENGTH(Ingredients) - LENGTH(REPLACE(Ingredients, '\n', ''))
After that, whenever you are updating/inserting a new row, you could calculate the amounts (probably with PHP) and fill in this column before-hand. Or, if you're into that sort of thing, try out triggers.
之后,无论何时更新/插入新行,您都可以计算金额(可能使用PHP)并在此之前填写此列。或者,如果您遇到这种情况,请尝试触发器。
#2
I'm assuming a lot here, but from what I'm reading in your post, you could change your database structure a little bit, and both solve this problem and open your dataset up to more interesting uses.
我在这里做了很多假设,但是从你在帖子中阅读的内容来看,你可以稍微改变你的数据库结构,并且解决了这个问题并打开你的数据集直到更有趣的用途。
If you separate ingredients into its own table, and use a linking table to index which ingredients occur in which recipes, it'll be much easier to be creative with data manipulation. It becomes easier to count ingredients per recipe, to find similarities in recipes, to search for recipes containing sets of ingredients, etc. also your data would be more normalized and smaller. (storing one global list of all ingredients vs. storing a set for each recipe)
如果将成分分离到自己的表中,并使用链接表来索引哪些成分出现在哪些配方中,那么通过数据操作进行创作会更容易。每份食谱计算成分,在食谱中找到相似之处,搜索含有各种成分的食谱等变得更容易,您的数据也会更加标准化和更小。 (存储所有成分的全局列表与存储每个食谱的集合)
If you're using a single text entry field to enter ingredients for a recipe now, you could do something like break up that input by lines and use each line as an ingredient when saving to the database. You can use something like PHP's built-in levenshtein() or similar_text() functions to deal with misspelled ingredient names and keep the data as normalized as possbile without having to hand-groom your [users'] data entry too much.
如果您现在使用单个文本输入字段输入配方的成分,您可以执行某些操作,例如按行分解输入并在保存到数据库时将每一行用作成分。您可以使用PHP内置的levenshtein()或similar_text()函数来处理拼写错误的成分名称,并尽可能将数据保持标准化,而无需过多地手工修改[用户]数据条目。
This is just a suggestion, take it as you like.
这只是一个建议,随心所欲。
#3
You're going a bit beyond the capabilities and intent of SQL here. You could write a stored procedure to scan the string and return the number and then use this in your query.
你在这里超出了SQL的功能和意图。您可以编写存储过程来扫描字符串并返回该数字,然后在查询中使用它。
However, I think you should revisit the design of whatever is inserting the Ingredients so that you avoid searching strings in of every row whenever you do this query. Add a 'num_linebreaks' column, calculate the number of line breaks and set this column when you're adding the Indgredients.
但是,我认为您应该重新考虑插入成分的设计,以便在执行此查询时避免在每行中搜索字符串。添加'num_linebreaks'列,计算换行符数,并在添加Indgredients时设置此列。
If you've no control over the app that's doing the insertion, then you could use a stored procedure to update num_linebreaks based on a trigger.
如果您无法控制正在执行插入的应用程序,则可以使用存储过程根据触发器更新num_linebreaks。
#4
Got it thanks, the php code looks like:
得到它谢谢,PHP代码看起来像:
$check = explode("\r\n", $_POST['ingredients']);
$lines = count($check);
So how could I update all the information in the table so Ingred_count based on field Ingredients in one fellow swoop for previous records?
那么我怎样才能更新表格中的所有信息,以便根据一位研究员的字段成分获取之前记录的Ingred_count?
#1
This would do it:
这样做:
SELECT *, LENGTH(Ingredients) - LENGTH(REPLACE(Ingredients, '\n', '')) as Count
FROM Recipes
ORDER BY Count DESC
The way I am getting the amount of linebreaks is a bit of a hack, however, and I don't think there's a better way. I would recommend keeping a column that has the amount of linebreaks if performance is a huge issue. For medium-sized data sets, though, I think the above should be fine.
然而,我获得线路数量的方式有点像黑客,我不认为有更好的方法。如果性能是一个巨大的问题,我建议保留一个具有换行量的列。但是对于中型数据集,我认为上面应该没问题。
If you wanted to have a cache column as described above, you would do:
如果您想拥有如上所述的缓存列,您可以:
UPDATE
Recipes
SET
IngredientAmount = LENGTH(Ingredients) - LENGTH(REPLACE(Ingredients, '\n', ''))
After that, whenever you are updating/inserting a new row, you could calculate the amounts (probably with PHP) and fill in this column before-hand. Or, if you're into that sort of thing, try out triggers.
之后,无论何时更新/插入新行,您都可以计算金额(可能使用PHP)并在此之前填写此列。或者,如果您遇到这种情况,请尝试触发器。
#2
I'm assuming a lot here, but from what I'm reading in your post, you could change your database structure a little bit, and both solve this problem and open your dataset up to more interesting uses.
我在这里做了很多假设,但是从你在帖子中阅读的内容来看,你可以稍微改变你的数据库结构,并且解决了这个问题并打开你的数据集直到更有趣的用途。
If you separate ingredients into its own table, and use a linking table to index which ingredients occur in which recipes, it'll be much easier to be creative with data manipulation. It becomes easier to count ingredients per recipe, to find similarities in recipes, to search for recipes containing sets of ingredients, etc. also your data would be more normalized and smaller. (storing one global list of all ingredients vs. storing a set for each recipe)
如果将成分分离到自己的表中,并使用链接表来索引哪些成分出现在哪些配方中,那么通过数据操作进行创作会更容易。每份食谱计算成分,在食谱中找到相似之处,搜索含有各种成分的食谱等变得更容易,您的数据也会更加标准化和更小。 (存储所有成分的全局列表与存储每个食谱的集合)
If you're using a single text entry field to enter ingredients for a recipe now, you could do something like break up that input by lines and use each line as an ingredient when saving to the database. You can use something like PHP's built-in levenshtein() or similar_text() functions to deal with misspelled ingredient names and keep the data as normalized as possbile without having to hand-groom your [users'] data entry too much.
如果您现在使用单个文本输入字段输入配方的成分,您可以执行某些操作,例如按行分解输入并在保存到数据库时将每一行用作成分。您可以使用PHP内置的levenshtein()或similar_text()函数来处理拼写错误的成分名称,并尽可能将数据保持标准化,而无需过多地手工修改[用户]数据条目。
This is just a suggestion, take it as you like.
这只是一个建议,随心所欲。
#3
You're going a bit beyond the capabilities and intent of SQL here. You could write a stored procedure to scan the string and return the number and then use this in your query.
你在这里超出了SQL的功能和意图。您可以编写存储过程来扫描字符串并返回该数字,然后在查询中使用它。
However, I think you should revisit the design of whatever is inserting the Ingredients so that you avoid searching strings in of every row whenever you do this query. Add a 'num_linebreaks' column, calculate the number of line breaks and set this column when you're adding the Indgredients.
但是,我认为您应该重新考虑插入成分的设计,以便在执行此查询时避免在每行中搜索字符串。添加'num_linebreaks'列,计算换行符数,并在添加Indgredients时设置此列。
If you've no control over the app that's doing the insertion, then you could use a stored procedure to update num_linebreaks based on a trigger.
如果您无法控制正在执行插入的应用程序,则可以使用存储过程根据触发器更新num_linebreaks。
#4
Got it thanks, the php code looks like:
得到它谢谢,PHP代码看起来像:
$check = explode("\r\n", $_POST['ingredients']);
$lines = count($check);
So how could I update all the information in the table so Ingred_count based on field Ingredients in one fellow swoop for previous records?
那么我怎样才能更新表格中的所有信息,以便根据一位研究员的字段成分获取之前记录的Ingred_count?