MySQL queries from Shell - unexpected result
List,
I have a problem with querying a MySQL database from Shell in batch mode. I have a file called 'mylist' in this format: 10.156.40.36 10.150.133.33 10.140.159.89 Now with a script I make a Class C of every IP address and put that in a tempfile: use sitedb SELECT C FROM sitelist WHERE A LIKE "10.156.40.0"; SELECT C FROM sitelist WHERE A LIKE "10.150.133.0"; SELECT C FROM sitelist WHERE A LIKE "10.140.159.0"; Then I merge the output with the following command: mysql --silent -h 10.10.10.10 -u username -ppassword < tempfile | paste mylist - This works extremely well and gives this output: 10.156.40.36 Rome 10.150.133.33 Brussels 10.140.159.89 Amsterdam However, if for instance 10.156.40.0 (Rome) is not found in the database, I don't get a result for that query (luckily). But my output gets shifted and doesn't match anymore: 10.156.40.36 Brussels 10.150.133.33 Amsterdam 10.140.159.89 In my opinion there are 2 options: Force MySQL to return 'nill' or something similar if the result of a query is nill (no clue how to do that) Adapt the script one or the other way so that it checks for a result after each query. Also no clue how to do that :( Any suggestion would be greatly appreciated... Kind regards, Phil |
You can try something like this:
Code:
SELECT '10.156.40.36', C FROM sitelist WHERE A LIKE "10.156.40.0"; |
Hmmm, I think I miss something here...
If its an unknow ip-address, it still gives nothing as result, while with a known address it gives 2 results? |
Try using the IFNULL function -
like Code:
SELECT IFNULL( C,'Not found.') FROM sitelist WHERE A LIKE "10.156.40.0"; |
This works if A (10.156.40.0) exists and C is empty (NULL), but NOT if A doesn't exist...
I want 'not found' if A doesn't exist. Is there a similar syntax that does this? Thx (we're getting close) - Phil |
Just to clarify, in 'pseudo-code', this would look like:
SELECT C FROM sitelist WHERE IFNOTFOUND( A,'unknown') LIKE "10.156.40.0"; |
All times are GMT -5. The time now is 12:08 AM. |