Mysql Simple Query question

Question

I wrote out this Mysql query, but return a lot of blank fields for the customers_telephone table since it was never a required field. I would like to return some dummy data on my query (without inserting it into the database) if the field is NULL. I know MySQL uses if clauses but I think it's only for creating databases or ... ?

here's my stamp

SELECT orders.orders_id AS recipient_id, delivery_company, delivery_street_address, delivery_city, delivery_postcode, delivery_state, delivery_country, customers_telephone
FROM orders, orders_products
WHERE orders.orders_id = orders_products.orders_id
AND products_id = 416
ORDER BY `orders`.`orders_id` DESC

UPDATE

I tried doing this, but it still is not working, maybe I did it in the wrong place?

SELECT orders.orders_id AS recipient_id, delivery_company, delivery_street_address, delivery_city,  delivery_postcode, delivery_state, delivery_country, IFNULL(customers_telephone, 'no number') AS customers_number
FROM orders, orders_products
WHERE orders.orders_id = orders_products.orders_id
AND products_id = 416
ORDER BY `orders`.`orders_id` DESC

Returns

recipient_id    delivery_company    delivery_street_address     delivery_city       delivery_postcode   delivery_state  delivery_country    number
191743          This Chick Bakes    31-31 48th Ave #B           Long Island City    11101               NY              United States       917.848.6437
191724                              44 Miraflores Ave (studio)  San Rafael          94901               CA              United States    
191723                              397 South River Street      Wilkes-Barre        18702               PA              United States

Mark Byers · Accepted Answer · 2011-09-28 21:15:51Z

3

Use the IFNULL function to provide a value if the field is NULL:

IFNULL(customers_telephone, 'dummydata') AS customers_telephone

IFNULL is a non-standard MySQL extension. The standard way is to use the COALESCE function for this purpose. COALESCE is also supported by MySQL, but it is slightly slower than IFNULL, though it probably won't matter here.

answered Sep 28, 2011 at 21:15

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Byers Over a year ago

@ehime: Perhaps they are empty strings, or strings containing only whitespace or other invisible characters.

ehime Over a year ago

It does look like it's a white space, what are my options?

Mark Byers Over a year ago

IFNULL(NULLIF(TRIM(customers_telephone), ''), 'dummydata')

LesterDove · Accepted Answer · 2011-09-28 21:17:25Z

1

One way

SELECT COALESCE(customers_telephone, 'no number') FROM ...

answered Sep 28, 2011 at 21:17

LesterDove

3,0441 gold badge26 silver badges24 bronze badges

Collectives™ on Stack Overflow

Mysql Simple Query question

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related