snipt

Ctrl+h for KB shortcuts

SQL

Using a set of store hours. is the store open.

1
2
3
4
5
6
SELECT day, open_time, close_time
FROM stores
JOIN store_hours
ON stores.id = store_hours.`store_id`
WHERE `day` = WEEKDAY(CURDATE())
AND TIME(now()) between open_time and close_time
https://snipt.net/embed/e1968fb2455fd88bd87bbc15f0d61878/
/raw/e1968fb2455fd88bd87bbc15f0d61878/
e1968fb2455fd88bd87bbc15f0d61878
sql
SQL
6
2019-07-09T17:23:12
True
False
False
/api/public/snipt/26100/
using-a-set-of-store-hours-is-the-store-open
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="k">day</span><span class="p">,</span> <span class="n">open_time</span><span class="p">,</span> <span class="n">close_time</span> </span><span id="L-2"><a name="L-2"></a><span class="k">FROM</span> <span class="n">stores</span> </span><span id="L-3"><a name="L-3"></a><span class="k">JOIN</span> <span class="n">store_hours</span> </span><span id="L-4"><a name="L-4"></a><span class="k">ON</span> <span class="n">stores</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">store_hours</span><span class="p">.</span><span class="o">`</span><span class="n">store_id</span><span class="o">`</span> </span><span id="L-5"><a name="L-5"></a><span class="k">WHERE</span> <span class="o">`</span><span class="k">day</span><span class="o">`</span> <span class="o">=</span> <span class="n">WEEKDAY</span><span class="p">(</span><span class="n">CURDATE</span><span class="p">())</span> </span><span id="L-6"><a name="L-6"></a><span class="k">AND</span> <span class="n">TIME</span><span class="p">(</span><span class="n">now</span><span class="p">())</span> <span class="k">between</span> <span class="n">open_time</span> <span class="k">and</span> <span class="n">close_time</span> </span></pre></div> </td></tr></table>
"store hours", "time open"

SQL

FULL TEXT Search Multiple Tables

1
2
3
4
5
6
7
SELECT *, ( MATCH(style_number, fabric) 
AGAINST(' Green Assante') OR MATCH(category_name) AGAINST('Green Assante') OR MATCH(color) AGAINST('Green Assante') ) as score
FROM style
JOIN category ON style.category_id = category.category_id
JOIN product ON product.style_id = style.style_id
WHERE ( MATCH(style_number, fabric) AGAINST(' Green Assante') OR MATCH(category_name) AGAINST('Green Assante') OR MATCH(color) AGAINST('Green Assante') )
ORDER By score desc 
https://snipt.net/embed/4be3576fe421a0de5acdb7ea4b25b40b/
/raw/4be3576fe421a0de5acdb7ea4b25b40b/
4be3576fe421a0de5acdb7ea4b25b40b
sql
SQL
7
2019-07-09T22:30:52
True
False
False
/api/public/snipt/23574/
full-text-search-multiple-tables
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a> <a href="#L-7">7</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="o">*</span><span class="p">,</span> <span class="p">(</span> <span class="k">MATCH</span><span class="p">(</span><span class="n">style_number</span><span class="p">,</span> <span class="n">fabric</span><span class="p">)</span> </span><span id="L-2"><a name="L-2"></a><span class="n">AGAINST</span><span class="p">(</span><span class="s1">&#39; Green Assante&#39;</span><span class="p">)</span> <span class="k">OR</span> <span class="k">MATCH</span><span class="p">(</span><span class="n">category_name</span><span class="p">)</span> <span class="n">AGAINST</span><span class="p">(</span><span class="s1">&#39;Green Assante&#39;</span><span class="p">)</span> <span class="k">OR</span> <span class="k">MATCH</span><span class="p">(</span><span class="n">color</span><span class="p">)</span> <span class="n">AGAINST</span><span class="p">(</span><span class="s1">&#39;Green Assante&#39;</span><span class="p">)</span> <span class="p">)</span> <span class="k">as</span> <span class="n">score</span> </span><span id="L-3"><a name="L-3"></a><span class="k">FROM</span> <span class="n">style</span> </span><span id="L-4"><a name="L-4"></a><span class="k">JOIN</span> <span class="n">category</span> <span class="k">ON</span> <span class="n">style</span><span class="p">.</span><span class="n">category_id</span> <span class="o">=</span> <span class="n">category</span><span class="p">.</span><span class="n">category_id</span> </span><span id="L-5"><a name="L-5"></a><span class="k">JOIN</span> <span class="n">product</span> <span class="k">ON</span> <span class="n">product</span><span class="p">.</span><span class="n">style_id</span> <span class="o">=</span> <span class="n">style</span><span class="p">.</span><span class="n">style_id</span> </span><span id="L-6"><a name="L-6"></a><span class="k">WHERE</span> <span class="p">(</span> <span class="k">MATCH</span><span class="p">(</span><span class="n">style_number</span><span class="p">,</span> <span class="n">fabric</span><span class="p">)</span> <span class="n">AGAINST</span><span class="p">(</span><span class="s1">&#39; Green Assante&#39;</span><span class="p">)</span> <span class="k">OR</span> <span class="k">MATCH</span><span class="p">(</span><span class="n">category_name</span><span class="p">)</span> <span class="n">AGAINST</span><span class="p">(</span><span class="s1">&#39;Green Assante&#39;</span><span class="p">)</span> <span class="k">OR</span> <span class="k">MATCH</span><span class="p">(</span><span class="n">color</span><span class="p">)</span> <span class="n">AGAINST</span><span class="p">(</span><span class="s1">&#39;Green Assante&#39;</span><span class="p">)</span> <span class="p">)</span> </span><span id="L-7"><a name="L-7"></a><span class="k">ORDER</span> <span class="k">By</span> <span class="n">score</span> <span class="k">desc</span> </span></pre></div> </td></tr></table>
search