Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-08
11
Medium Priority
?
76 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 2000 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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