Thread: Find products without images

I am having a hard time figuring out where the image paths are stored in the database. I would like to make a custom report that shows all products without an image. I was thinking something around about like this:

Code:

SELECT sku FROM ?table? WHERE ?colum_with_image? IS NULL OR ?colum_with_image? = 'nopicture.gif'

If I am making this more complicated that it needs to be, please enlighten me.
Thank you for your time,
Eric

Image paths or the image itself is not saved on the db, there are a couple of other features as well to consider with regards to image resolution like:
- default resolution using productid,
- use sku as filename for image
- use image filename override
- actual availability of the image path.

Another reason is to allow ftp-uploading images manually so the image file resolution should be dynamic.

Using sql is possible taking into consideration the given settings, although the result may not be entirely accurate.

What you can use instead, output a report through xmlpackage.

For the purpose of this example, i just used the existing extension function LookupProductImage, letting it take care of the image resolution rules. The key here is to expect the filename. As you may have already noticed, if a product does not have an image, a placeholder image "nopicture/nopictureicon.gif" is used.

Code:

<query name="Products" rowElementName="Product">
		<sql>
			<![CDATA[
                select p.*
				from Product p
				where p.Published = 1 and p.Deleted = 0
            ]]>
		</sql>
	</query>

	<PackageTransform>
		<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
			<xsl:output method="html" omit-xml-declaration="yes" />


			<xsl:template match="/">
				<table border="1">

					<xsl:for-each select="/root/Products/Product" >
						<xsl:variable name="imgHtml" select="aspdnsf:LookupProductImage(ProductID, ImageFilenameOverride, SKU, 'icon', 1, '')" />

						<!--list only products with image paths to "nopicture", hence picture isn't available-->
						<xsl:if test="contains($imgHtml, 'nopictureicon.gif')">						
							<tr>
								<td>
									<xsl:value-of select="ProductID" />
								</td>
								<td>
									<xsl:value-of select="aspdnsf:GetMLValue(Name)" />
								</td>
								<td>
									<xsl:value-of select="$imgHtml" disable-output-escaping="yes" />
								</td>
							</tr>
						</xsl:if>
					</xsl:for-each>

				</table>
			</xsl:template>

		</xsl:stylesheet>
	</PackageTransform>

I am trying to do the same thing. I don't see where I can do this report, though. Are you suggesting to create a custom report of some kind? I am familiar with the package validator, but not a report tool.

This xmlpackage wil check for & display missing icon, medium & large images.
It's the very first xmlpackage I wrote years ago.

Create a new file with the following as the contents, name it missingimages.xml.config & upload it to your xmlpackages directory, then simply call it with http://www.yourdomain.com/e-missingimages.com

Code:

<?xml version="1.0" standalone="yes" ?>
<package version="2.1" displayname="Missing Product Images" debug="false" includeentityhelper="false" allowengine="true">

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[
select ProductID, Name, SEName, SKU,ImageFilenameOverride
from product
where issystem=0 and deleted=0 and published=1
order by SKU
]]>
</sql>
</query>

<PackageTransform>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
<xsl:output method="html" omit-xml-declaration="yes" />

<xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />
<xsl:param name="WebConfigLocaleSetting" select="/root/Runtime/WebConfigLocaleSetting" />
<xsl:param name="HidePicsInTableCondensed"><xsl:value-of select="aspdnsf:AppConfig('HidePicsInTableCondense d')"/></xsl:param>

<xsl:template match="/">
<h1>Products without images</h1>
<xsl:choose>
<xsl:when test="count(/root/Products/Product) = 0 ">
<p>Failed to retrieve any products from the database.</p>
</xsl:when>
<xsl:otherwise>
<table border="0" cellpadding="0" cellspacing="4" width="100%">
<xsl:apply-templates select="/root/Products" />
</table>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

<xsl:template match="Products">
<tr class="DarkCell">
<td><font class="CondensedDarkCellText"><b><xsl:value-of select="aspdnsf:StringResource(concat('show', 'section', '.aspx.18'), $LocaleSetting)" disable-output-escaping="yes" /></b></font></td>
<td><font class="CondensedDarkCellText"><b><xsl:value-of select="aspdnsf:StringResource(concat('show', 'section', '.aspx.20'), $LocaleSetting)" disable-output-escaping="yes" /></b></font></td>
<td><font class="CondensedDarkCellText"><b>Image</b></font></td>
</tr>
<xsl:apply-templates select="/root/Products/Product" />
</xsl:template>

<xsl:template match="Product">
<xsl:param name="pName" select="aspdnsf:GetMLValue(Name)" />
<xsl:param name="sename" select="aspdnsf:GetMLValue(SEName)" />
<xsl:param name="URL">p-<xsl:value-of select="ProductID" disable-output-escaping="yes" />-<xsl:value-of select="$sename"/>-.aspx</xsl:param>
<xsl:param name="IconImageURL"><xsl:value-of select="aspdnsf:ProductImageUrl(ProductID, ImageFilenameOverride, SKU, 'icon', 0)"/></xsl:param>
<xsl:param name="MediumImageURL"><xsl:value-of select="aspdnsf:ProductImageUrl(ProductID, ImageFilenameOverride, SKU, 'medium', 0)"/></xsl:param>
<xsl:param name="LargeImageURL"><xsl:value-of select="aspdnsf:ProductImageUrl(ProductID, ImageFilenameOverride, SKU, 'large', 0)"/></xsl:param>
<xsl:if test="contains($IconImageURL, 'nopicture')" >
<tr>
<td><a href="{$URL}"><xsl:value-of select="$pName" disable-output-escaping="yes"/></a></td>
<td><xsl:value-of select="SKU" /></td>
<td align="center" valign="bottom">Icon</td>
</tr>
</xsl:if>
<xsl:if test="contains($MediumImageURL, 'nopicture')" >
<tr>
<td><a href="{$URL}"><xsl:value-of select="$pName" disable-output-escaping="yes"/></a></td>
<td><xsl:value-of select="SKU" /></td>
<td align="center" valign="bottom">Medium</td>
</tr>
</xsl:if>
<xsl:if test="$LargeImageURL = ''" >
<tr>
<td><a href="{$URL}"><xsl:value-of select="$pName" disable-output-escaping="yes"/></a></td>
<td><xsl:value-of select="SKU" /></td>
<td align="center" valign="bottom">Large</td>
</tr>
</xsl:if>
</xsl:template>

</xsl:stylesheet>
</PackageTransform>
</package>

TTFN

BFG

Thanks, I'll try it out!

Have you made any changes to this xml setup.
I tried running it and posted it every place possible on my site and could not get it to load.

thanks for the info.

Copy the BFG 9000's code and paste it in notepad and give meaningful name to it.
Say MissingImage.xml.config.

Put that config file in your xml package directory.

Then type this -

www.yoursite.com/e-missingimage.aspx

It works for me.

BFG 9000 example works for me too. Thanks again BFG 9000 for sharing your time and code! Thank you Mark for taking the time answer my question.