[insert_php]
$connArr = explode(‘:’, DB_HOST);
$conn = new mysqli($connArr[0], DB_USER, DB_PASSWORD, DB_NAME);
mysqli_set_charset($conn,”utf8″);

$sql_count = “SELECT distinct vd.website
FROM vendor_data vd
JOIN vendor_programs vp
ON vd.vendor_id = vp.vendor_id AND vp.program_id = 34
WHERE is_active = 1 AND is_public <> 1 AND in_house = 1”;
$result_count = $conn->query($sql_count);

$showAllSQL = “”;
if (mysqli_num_rows($result_count) >= 20){
$showAllSQL = ” AND in_house = 1 “;
}

$sql = “SELECT *,
CASE WHEN ((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) AND ((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’)) AND has_docs > 0 THEN 3
WHEN (((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) AND ((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’))) OR (((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) AND has_docs > 0) OR (((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’)) AND has_docs > 0) THEN 2
WHEN ((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) OR ((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’)) OR has_docs > 0 THEN 1
ELSE 0 END as media,
CASE WHEN vp.in_house = 1 AND vp.stand_alone = 1 THEN 1 ELSE 0 END as in_house_alone
FROM vendor_data vd
JOIN vendor_programs vp
ON vd.vendor_id = vp.vendor_id AND vp.program_id = 34
WHERE is_active = 1
AND is_public <> 1
AND vd.vendor_id IN (
SELECT MIN(B.vendor_id) as vendor_id FROM vendor_data B
WHERE B.website = vd.website)
$showAllSQL
ORDER BY in_house_alone DESC, preferred DESC, MAV DESC, media DESC, last_updated DESC, name ASC”;

$sql2 = “SELECT name FROM (SELECT name,
CASE WHEN ((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) AND ((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’)) AND has_docs > 0 THEN 3
WHEN (((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) AND ((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’))) OR (((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) AND has_docs > 0) OR (((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’)) AND has_docs > 0) THEN 2
WHEN ((media1_link != ” AND media1_link != ‘NULL’) OR (media2_link != ” AND media2_link != ‘NULL’) OR (media3_link != ” AND media3_link != ‘NULL’)) OR ((snapshot1_id != ” AND snapshot1_id != ‘NULL’) OR (snapshot2_id != ” AND snapshot2_id != ‘NULL’) OR (snapshot3_id != ” AND snapshot3_id != ‘NULL’)) OR has_docs > 0 THEN 1
ELSE 0 END as media,
CASE WHEN vp.in_house = 1 AND vp.stand_alone = 1 THEN 1 ELSE 0 END as in_house_alone
FROM vendor_data vd
JOIN vendor_programs vp
ON vd.vendor_id = vp.vendor_id AND vp.program_id = 34
WHERE is_active = 1
AND is_public <> 1
AND vd.vendor_id IN (
SELECT MIN(B.vendor_id) as vendor_id FROM vendor_data B
WHERE B.website = vd.website)
$showAllSQL
ORDER BY in_house_alone DESC, preferred DESC, MAV DESC, media DESC, last_updated DESC, name ASC LIMIT 20) a order by name”;

$result = $conn->query($sql);
$result2 = $conn->query($sql2);
$result3 = $conn->query($sql2);
[/insert_php]

TOP FINANCIAL WELLNESS PROGRAMS

Shortlister is trusted by employers and employee benefits consultants to identify their optimal vendor based on their budget,
demographics and program goals. Leverage our expertise to save yourself the time and hassle of navigating the crowded vendor marketplace.

 

Did You Know?

Many top employee benefits consultants already use Shortlister and may be able to provide complimentary access. Click below to find out if you qualify.

Click Here

[insert_php]
if (count($result)){
while($row = $result->fetch_assoc()){
if(strlen($row[‘summary’]) > 415){
$summary = substr($row[‘summary’], 0, 415).’…’;
}
else{
$summary = $row[‘summary’];
}
echo’

‘;}}
[/insert_php]

Get Industry Updates