How to add where not equal to in Query

Question

I'm trying to add a condition as active is not qual to 1, but I can't figure it out how it's working correctly in my query.

Here is my query

$career_solutions_data = DB::select("
SELECT
career_solutions.id,
role_users.role_id,
career_solutions.user_id,
career_solutions.subject,
career_solutions.date,
career_solutions.public,
career_solutions.views,
career_solutions.optional,
career_solutions.on_offer,
career_solutions.active,
users.username,
users.profile_picture,
categories.category,
categories.category_url,
categories.color,
career_solutions_categories.category as sub_category,
career_solutions_format.category as event_format,
career_solutions_certification.category as certification



FROM career_solutions



INNER JOIN categories
ON categories.id = career_solutions.topic_category_id

INNER JOIN career_solutions_format
ON career_solutions_format.id = career_solutions.topic_format_id

INNER JOIN career_solutions_certification
ON career_solutions_certification.id = career_solutions.topic_certification_id

INNER JOIN career_solutions_categories
ON career_solutions_categories.id = career_solutions.topic_subcategory_id

INNER JOIN users
ON users.id = career_solutions.user_id

LEFT JOIN role_users on role_users.role_id = users.id



INNER JOIN privacy_settings
ON privacy_settings.user_id = users.id

WHERE users.deleted_at IS NULL
AND (
(privacy_settings.career_solutions = 0 AND public = 1 )
OR (users.id IN (

SELECT contacts.contact_id
FROM contacts
WHERE contacts.user_id = $id
)
)
)

OR users.id = $id

ORDER BY date desc limit 5000
");


$role = User::with('role')
->where ('id', '=', $id)
->first();



                // $career_solutions_data;

                foreach ($career_solutions_data as $career_solution)
                {

                    preg_match('/<img.+src=[\'"](?P<src>.+?)[\'"].*>/i', $career_solution->optional, $image);
                    if(isset($image['src']))
                    {
                        $type_picture = $image['src'];
                    }
                    else{
                        $type_picture = "";
                    }

                    $temp_soluation = array();
                    $temp_soluation['type'] = 'Career Solution';
                    $temp_soluation['typee'] = 'briefcase';
                    $temp_soluation['subject'] = $career_solution->subject;

                    $temp_soluation['information'] = $career_solution->optional;
                    $temp_soluation['category'] = $career_solution->category;
                    $temp_soluation['category_url'] = $career_solution->category_url;
                    $temp_soluation['color'] = $career_solution->color;
                    $temp_soluation['all_url'] = 'search-career-solutions';
                    $temp_soluation['type_url'] = 'view-career-solutions';
                    $temp_soluation['id'] = $career_solution->id;
                    $temp_soluation['date'] = $career_solution->date;
                    $temp_soluation['public'] = $career_solution->public;
                    $temp_soluation['active'] = $career_solution->active;
                    $temp_soluation['sub_category'] = $career_solution->sub_category;
                    $temp_soluation['event_format'] = $career_solution->event_format;
                    $temp_soluation['certification'] = $career_solution->certification;
                    $temp_soluation['on_offer'] = $career_solution->on_offer;
                    $temp_soluation['username'] = $career_solution->username;
                    $temp_soluation['roleMe'] = $career_solution->optional;

                              $temp_soluation['role']  = $role->role[0]->id;


                    $temp_soluation['profile_picture'] = $career_solution->profile_picture;
                    $temp_soluation['type_picture'] = $type_picture;
                    // $news_events_opinions[] = $temp_soluation;
                    $my_career_solution[] = $temp_soluation;
                }
            }

This is what I have tried to add :

WHERE `career_solutions.active` <> `1`
or
WHERE `active` <> `1`
or
WHERE `active` != `1`

but didn't working.So, I need to not displaying the posts with active = 1.I have another 3 queries, different of that, where I have used this : ->where("active","!=",1);, but here I can't use it.

Look like your data have null values. use WHERE (career_solutions.active <>1 or career_solutions.active is null) if in case. — Sunil Chhimpa, Sep 23 '19 at 07:22

score 0 · Accepted Answer · answered Sep 23 '19 at 07:17

0

The problem are the backticks around the 1: This instructs MySQL to search for a column name 1.

Try WHERE career_solutions.active <> 1

answered Sep 23 '19 at 07:17

Eugen Rieck

64,175
10
70
92

Hi, thank you for your answer.I updated my code with your version.Do you know what's going on here? `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNER JOIN categories ON categories.id = career_solutions.topic_category_id INN' at line 25 (SQL:` – Andrew Sep 23 '19 at 07:23
There is a completly different error in the SQL causing that - post your complete SQL so that we can look at it. – Eugen Rieck Sep 23 '19 at 07:41
Copy-pasting this query produces no error: http://sqlfiddle.com/#!9/2b5cf/10 – Eugen Rieck Sep 23 '19 at 07:49
and `WHERE career_solutions.active <> 1` where should I add this? after `FROM career_solutions` ? – Andrew Sep 23 '19 at 07:57
Since you already have a `WHERE` clause, you can't add a second, but need to combine them. In your query set use an `AND` directly at the start of the existing where: `WHERE career_solutions.active <> 1 AND users.deleted_at IS NULL AND (...` – Eugen Rieck Sep 23 '19 at 08:00
thank you so much, it's working fine now!I just learned something new about where clause. – Andrew Sep 23 '19 at 08:08

How to add where not equal to in Query

1 Answers1