PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Dec 12, 2019 12:13 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 29 posts ]  Go to page Previous  1, 2
Author Message
PostPosted: Wed Aug 23, 2017 11:47 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA


Top
 Profile  
 
PostPosted: Wed Aug 23, 2017 3:42 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172


Top
 Profile  
 
PostPosted: Thu Aug 24, 2017 1:38 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA


Top
 Profile  
 
PostPosted: Thu Aug 24, 2017 3:16 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172


Top
 Profile  
 
PostPosted: Thu Aug 24, 2017 5:35 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA


Top
 Profile  
 
PostPosted: Sat Aug 26, 2017 7:06 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172


Top
 Profile  
 
PostPosted: Sat Aug 26, 2017 7:40 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
Yes, you can use regular expressions (in MySQL) but that doesn't solve the problem.

Say you've got "milk" and "Milk" (should match), "smilk" and "milke" (should not match), and "powdered milk" (should match... well, maybe, but let's say it should).

- Searching ="milk" will find the two good ones, not find the two bad ones, but not find the last
- Searching LIKE "%milk%" will find all the good ones but also all the bad ones
- Searching LIKE "milk%" or "%milk" will find one of the two bad ones

The only other one left to try is a regex:
- Searching REGEX "\bmilk\b" (\b is a word boundary) will find the two good ones, not find the two bad ones, and will find the last

Regex is slow but it works, right? No, because we simply haven't found a conflicting example.
- Searching REGEX "\begg\b" will find "egg" (good) but not find "eggs" (bad)

I'm thinking you
a) Tell the user that all ingredients must be normalized, meaning "eggs" must be "egg" instead. No plurals or other similar forms of words. You can't really enforce that automatically.
b) Force normalization but do it in code. This is a bit harder as you'll need a library that understands stemming in English - you can't simply remove a trailing "s" and the like.
c) Use another engine for searches - one that also understands stemming. That means doing searches through it, not MySQL.

(a) is a burden on the user and not reliable, and (c) is at least a burden on you because there's a lot more work that goes into it. I think (b) is your best option. If you want to output "6 eggs" given count=6 and ingredient=egg then you've got a bit of a problem, however the same library may be able to make words plural too.


Top
 Profile  
 
PostPosted: Sat Aug 26, 2017 8:22 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
For c.), it might be worth taking a look at something like Elastic Search. It's not particularly difficult to set up and should help with what you're trying to accomplish.

_________________


Top
 Profile  
 
PostPosted: Sat Aug 26, 2017 3:06 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Ouch,
this seems even harder than I thought from the start.

What Im searching is ingrediens from food labels so that gives me an option to refuse plurals.

I was thinking of having some friends help me add lots of product info. When I have some hundred+ different products I sort out doubles and save it as an ingredient table where i also add different names that means the same thing like citric acid - E330.
Then when adding new products i do AJAX call to that table so if someone try to add eggs they will get egg as suggestion. If they still try to type eggs instead they must add eggs as an new ingredient to the ingredient table and i can flagg the ingredient and product for manual inspektion. If its a legal new ingredient its cleared, if it is egg-eggs eggs can be linked with egg in the table...

Will be lots of controlling in the beginning bit after some 1000 products added it will be less and less work.
Maybe some thing like that is needed anyway since some ingredients can have multiple sales names which is hard to find through regular searchdictionaries..?


Top
 Profile  
 
PostPosted: Sun Aug 27, 2017 1:49 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
More food for thought (heh): I have something here made with "white grape juice from concentrate".

If what you described for inputting ingredients works then that's the safest option: a human who knows the rules manually approves each new ingredient. Problem is it takes manpower to do that. You need staff to work on it.
You don't necessarily have to be so strict, though. The user could be forced to pick "egg" and then have an option to say "but the ingredients list actually says 'eggs'" - store the latter, link to the former. A human could still review it, but the optional extra step should help keep the data correct. Later when someone enters "egg" they only see the singular version, but if they also override with the plural then you can reuse that ingredient.

Personally I would still look into stemming. It won't fix all the potential problems, but it could at least assist with the most common issues; user enters "eggs" (new ingredient), stemmer recognizes it as a plural of "egg" (existing ingredient), system adds and links automatically.


Top
 Profile  
 
PostPosted: Tue Aug 29, 2017 5:58 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172


Top
 Profile  
 
PostPosted: Tue Aug 29, 2017 6:09 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Syntax: [ Download ] [ Hide ]
if ($stmt = $mysqli->prepare("SELECT
                EAN
                FROM Swe_Products WHERE EAN like CONCAT(?,'%') "
)) {
                $stmt->bind_param('i', $q);
                $stmt->execute();
                $stmt->bind_result($EAN[]);
                $stmt->store_result();
                $stmt->fetch();
                }


Well I actually solved it :)


Top
 Profile  
 
PostPosted: Fri Sep 22, 2017 3:43 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Is it possible to populate my array from different tables inside the fetchloop?
The greyed out code doesnt work.

Syntax: [ Download ] [ Hide ]
if ($stmt = $mysqli->prepare("SELECT
    EAN
    ..
    .
   
    FROM table1 WHERE EAN like CONCAT(?,'%') LIMIT 5"
)) {
    $stmt->bind_param('i', $q);
    $stmt->execute();
    $stmt->bind_result($ean,  .. .);
    $stmt->store_result();
    $i=0;
    while($row=$stmt->fetch()){
        $result[$i][ean]=$ean;
        ..
        .
        /*
        if ($stmt = $mysqli->prepare("SELECT
                Table2.name,
                Table2.unit,
                Table3.amount
                FROM Table2, Table3
                WHERE Table2.EAN = ? AND Table2.list = Table3.id")){
                $stmt->bind_param('s', $data["EAN"]);
                $stmt->execute();
                $stmt->bind_result($name, $unit, $value);
                $stmt->store_result();
                while($row = $stmt->fetch()){
                    $result[$i]["nutrition_info"][$name][$value] = $unit;
                }
            }
            */

        $i++;
    }
}
 


Top
 Profile  
 
PostPosted: Fri Sep 22, 2017 4:24 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
You can't reuse $stmt and $row in there - different variables should work. But it would probably be better to combine the two queries so that you only need one loop.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 29 posts ]  Go to page Previous  1, 2

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group