Solved

How Do I Prevent Empty Query Results From Displaying In My Results List?

Posted on 2015-02-08
11
70 Views
Last Modified: 2015-02-09
My script cycles through multiple environments and if a user is found, it prints out their ID. The desired output is as follows:

environment1
<user_id>

Open in new window


However, my script is currently working to print out every environment even if there isn't a user id, like follows:

environment1
<user_id>
environment2
environment3
environment4
<user_id>

Open in new window


I would like the code to print out "NO USER FOUND" if the user_id does not exist, rather than excluding the environment altogether.

The code below is what is being utilized:

#log it all
{

  echo "environment"
  sshpass -p $ldappw ssh $ldapuser@12.345.67.89 'mysql --skip-column-names -hhost -u user -ppassword database -e \
"select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"' 


} > $log

Open in new window


Any help would be much appreciated for this issue. Unfortunately I'm just beginning to learn Linux and command line tools and am not quite proficient enough to know how to handle this problem.
0
Comment
Question by:TampaJay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597441
This is a case for "CASE".

Something like this:

select CASE WHEN first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is not NULL THEN user_id ELSE 'NO USER FOUND' END from users ;"'

You will have to experiment a bit (particularly with the single and double quotation marks) because right now I do not have a MySQL database at hand for testing.

Additionally, please see the MySQL docs for "CASE":
http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#operator_case
0
 

Author Comment

by:TampaJay
ID: 40597457
Unfortunately when running that query it will go through every record of the table printing output.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597464
Parentheses?

select CASE WHEN ( first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is not NULL ) THEN user_id ELSE 'NO USER FOUND' END from users ;"'

Sorry, I come from Oracle where the syntax is slightly different.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TampaJay
ID: 40597468
No, that brings up the same results.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597482
Are there cases where first_name and last_name match but userid is empty?

select CASE WHEN ( first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is not NULL ) THEN user_id WHEN  ( first like '"'%${first_name}%' and last like '%${last_name}%' and user_id is NULL ) THEN 'NO USER FOUND'  ELSE NULL END from users ;"'

I fear I won't be able to provide more help unless it's made clear what values "first_name" and "last_name" can take in the script as well as "first" and "last"  in the database and if "user_id" is indeed NULL or just "empty" or "space" if "the user_id does not exist".
0
 

Author Comment

by:TampaJay
ID: 40597579
first_name variable will be a name like "Jay" and last_name variable will be a name like "lefler"

The Database fields first and last are also first and last names.

There might be multiple accounts with the jay/lefler combination. User ID will never be a NULL value
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40597670
It's hard to understand how it should be possible that a "user_id does not exist" (as stated in your question) when on the other hand a "User ID will never be a NULL value" (as stated in your last comment).

Be that as it may, let's make it simple (yet I fear this won't make it any better):

#log it all
{
UID=$(sshpass -p $ldappw ssh $ldapuser@12.345.67.89 'mysql --skip-column-names -hhost -u user -ppassword database -e \
"select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"')
if [[ ! -z $UID ]]; then
   echo "environment"
   echo $UID
     else
        echo "NO USER FOUND"
fi 
} > $log

Open in new window


I'm afraid you'll have to post the whole script so we might be able to see what's it all about.
0
 

Author Comment

by:TampaJay
ID: 40597711
User ids are unique but if there is no combination that matches first and last name their wont be an associated user_id.
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 40597891
So my last suggestion above should be the way to go.

Here it is again:

#log it all
{
UID=$(sshpass -p $ldappw ssh $ldapuser@12.345.67.89 'mysql --skip-column-names -hhost -u user -ppassword database -e \
"select user_id from users where first like '"'%${first_name}%' and last like '%${last_name}%';"'"')
if [[ ! -z $UID ]]; then
   echo "environment"
   echo $UID
     else
        echo "NO USER FOUND"
fi 
} > $log

Open in new window

0
 

Author Comment

by:TampaJay
ID: 40598475
woolmilkporc,

What if there are multiple user ID's for a first/last name combination? While I have tested your script and it appears to work for finding a single user, will it print multiple user id's if they are found?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40598646
As you can see I used your query. When this query returns multiple users then my script will display them.

Currently all IDs will appear in one single line. To show each ID on a line of its own change

echo $UID

to

echo "$UID"
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question