Need mysql qurey help...

[thara]

Hello everyone...

 

I use this mysql query in my php script.

 

	$q = "SELECT introduction_note, institute_code,
		DATE_FORMAT(registration_date, '%Y-%m-%d') AS date, image_name
		FROM institutes 
		INNER JOIN institute_images ON institutes.institute_id = institute_images.institute_id
		WHERE institute_name = \"$institute\" AND institute_images.institute_id = $instituteId AND
		institute_images.image_type = \"slogan\"
		LIMIT 1";

 

Its work fine if it has an image in institute_images table with its image_type is slogan. My problem is when I run my script at first time there is no such an image in my institute_images table. In this case there is no content in my webpage. but when I add an image with its type in slogan to institute_images table every things display in my page properly.

 

can anybody tell me how can I figure out this query when it is run at first time. This mean I need to display my page even there is not such image in my table.

 

any comments are greatly appreciated.

 

thank you.

[Jessica]

Change the INNER join to a LEFT join.

[thara]

Thanks for quick response...

 

I changed INNER JOIN to LEFT JOIN.. but it is not working if there is no image in my institute_images table.

[Jessica]

Try this

SELECT introduction_note, institute_code,
DATE_FORMAT(registration_date, '%Y-%m-%d') AS date, image_name
FROM institutes 

LEFT JOIN institute_images 
ON institutes.institute_id = institute_images.institute_id
AND institute_images.image_type = 'slogan'

WHERE institute_images.institute_id = $instituteId 
LIMIT 1

If you have the ID, why are you searching on the name too>

[thara]

I did as you said. The query is working only if there is an image in database and still nothing happen if database haven't an image.

[Jessica]

SELECT introduction_note, institute_code,
DATE_FORMAT(registration_date, '%Y-%m-%d') AS date, image_name
FROM institutes 

LEFT JOIN institute_images 
ON institutes.institute_id = institute_images.institute_id
AND institute_images.image_type = 'slogan'

WHERE institutes.institute_id = $instituteId 
LIMIT 1

 

 

Sorry, the whole query is kind of written backwards. The last line should refer to institutes not images.

 

When you write the joins, write everything for the first table and make sure it works. The only stuff in the WHERE clause should be for the main table. anything else should be in the join clauses.

[thara]

Thank you.. Now its working for me.

 

Now I need to check 'image_name' has a value or not. if it has a value need to display that image in a DIV. if there is no value I dont need to display image DIV. So can you tell me how can I check there is a value or not????

[Jessica]

You should be able to use either isset or empty or maybe need strlen. It depends on the rest of the code.