How to implement a query for db

Question

Hello everyone I should implement the possibility of not displaying a certain value if contained in another table as in this case: to show data certain parameters only plants that have not been associated with a customer

"SELECT * FROM store_locator WHERE store_locator.id NOT IN (SELECT impianto_id_campagna FROM campagne_cliente);

but within this query:

function get_stores_list2($criteria=array()) {
    $id = $criteria['id'];
    $lat = $criteria['lat'];
    $lng = $criteria['lng'];
    $page_number = $criteria['page_number'];
    $nb_display = $criteria['nb_display'];
    $distance_unit = $criteria['distance_unit'];
    $max_distance = $criteria['max_distance'];
    $category_id = $criteria['category_id'];

    $table_name = $GLOBALS['db_table_name'];
    $start = ($page_number*$nb_display)-$nb_display;

    $s1 = new MySqlTable();

    if($distance_unit=='miles') $distance_unit='3959'; //miles
    else $distance_unit='6371'; //km

    $sql = "SELECT *, 
    ( $distance_unit * acos( cos( radians('".$s1->escape($lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$s1->escape($lng)."') ) + sin( radians('".$s1->escape($lat)."') ) * sin( radians( lat ) ) ) ) AS distance 
    FROM ".$table_name." 
    WHERE 1";

    if($id!='') $sql .= " AND id='".$s1->escape($id)."'";
    if($category_id!='') $sql .= " AND category_id='".$s1->escape($category_id)."'";
    if($max_distance!='') $sql .= " HAVING distance<='".$s1->escape($max_distance)."'";

    if($lat!='' && $lng!='') $sql .= " ORDER BY distance";
    else $sql .= " ORDER BY name";

    if($nb_display>0) $sql .= " LIMIT $start, $nb_display";

    //echo $sql.'<br>';

    $locations = $s1->customQuery($sql);

    return $sql;
}

@tiagoperes "SELECT *, ( $distance_unit * acos( cos( radians('".$s1->escape($lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$s1->escape($lng)."') ) + sin( radians('".$s1->escape($lat)."') ) * sin( radians( lat ) ) ) ) AS distance FROM ".$table_name." WHERE 1 NOT IN (SELECT impianto_id_campagna FROM campagne_cliente)" but nothing — okok, Oct 20 '18 at 15:55
Possible duplicate of [How to select all records from one table that do not exist in another table?](https://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table) — philipxy, Oct 20 '18 at 17:31
Hi. I just googled "not displaying a certain value if contained in another table" & got the link above. This is (obviously) a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. PS Clarify via edit, not comment. — philipxy, Oct 20 '18 at 17:31
@Philipxy thanks for your asnwer, but how implement this in my query? — okok, Oct 20 '18 at 17:41
[What have you done towards reaching that goal?](https://stackoverflow.com/questions/52904575/how-to-implement-a-query-for-db#comment92720224_52904575) [Clarify via edit, not comment.](https://stackoverflow.com/questions/52904575/how-to-implement-a-query-for-db#comment92727302_52904575) [mcve] PS First get the SQL correct with minimal or no PHP. — philipxy, Oct 20 '18 at 22:59

How to implement a query for db

0 Answers0