When flights get cancelled due to fog, what triggers the cancellation? Fog is too thick, too wide-spread, blocks the view of tall buildings or other landmarks? Or is the problem the type of air traffic? Too many helicopters or small-ish aircraft?
Other answers have addressed the case of weather that's too bad for flights to operate, but another situation that can occur is congestion.
Take a look at the Average Arrival Rate chart for San Francisco International Airport (SFO) (more details on this in this blog post by an airline dispatcher). During visual conditions with favorable winds, they can operate in normal flow and accept 54+ arrivals/hour into two parallel runways, with departures using the perpendicular runways in between arrivals. But that only works when the cloud ceilings are above 3,000-3,500ft. That procedure relies on aircraft being able to visually see and avoid each other when flying in close proximity.
If there's fog (or low stratus clouds that don't touch the ground, as is typical), that can't happen; you can't send jets blindly into a cloud 750 feet apart from each other. If the clouds are high enough (per the chart, a minimum ceiling of 1,600ft and 4mi visibility), they can use a special SOIA procedure (Simultaneous Offset Instrument Approach)—essentially a guided, monitored way to direct jets into a cloud 2,000 feet apart—and accept around 36 arrivals/hour. And if the weather is too poor for that, the average arrival rate can drop to 25 arrivals/hour or below. The extra procedures and spacing required to safely manage traffic in the weather reduces the airport's capacity.
When this happens, there's nothing that stops any specific commercial flight from using the airport—they all have the necessary technology to land in most cloudy conditions—, but the airport's capacity is now too low to accommodate everyone. The FAA will impose a ground delay program to meter traffic into the airport until conditions improve (otherwise you end up with too many flights circling and diverting to other airports, causing many other problems), and the result is flight delays and cancellations.
For landing aircraft, the Instrument Approach Procedures available will dictate the required ceiling (height of clouds above ground) and visibility (horizontal). The most common approach type for airlines is an Instrument Landing System (ILS).
ILS Category I requires a ceiling of at least 200 feet and visibility of at least 1800 feet. With special equipment on the ground and in the plane plus specially trained crews, this can be improved to Category II at 100/1200, Category IIIA at 50/700, or Category IIIB at 50/300. "Fog" is defined as a cloud at ground level, though, so these requirements still wouldn't be met.
ILS Category IIIC theoretically allows landing with 0/0, but the standard has never been implemented and will probably be rescinded.
If the weather doesn't meet the minima for any approach procedure, arriving aircraft cannot legally land and must divert elsewhere. If such conditions are forecast for when they plan to arrive, there is also likely to be a ground delay program that prevents them from even taking off.
For departing aircraft, airlines are not allowed to take off from an airport where they cannot legally land in the event a problem forces them to return. Private pilots are allowed to do so, but few will unless they know the fog is localized, which is common in coastal areas.
At bigger airports when it's foggy, the limits are set by light transmissiometers along the runway that measure the horizontal visibility, giving a value called Runway Visual Range (RVR). You follow Low Visibility departure procedures, which include minimum RVR requirements as well as certain runway lighting requirements, departure alternate airport requirements (because you can't return to that airport to land if something goes bad on the departure) etc, that have to be met.
So if the departure is cancelled due to fog, it's probably because the RVR is too low (too foggy along the runway itself) for whatever procedure the crew is qualified for. Or the available runway doesn't have the low vis equipment requirements. Or there is no usable departure alternate airport.
Or the departure may also be cancelled due to fog forecast at the destination.