Displaying hyperlinks from a database

[ivalum21]

In my database, I have a column of hyperlinks for the assigned companies. For example, if ABC Company has a website, in the HYPERLINKS column in my database, it has

http://www.abccompany.com

or something. It's not a real website, but I'm sure people will click on it anyway...

I have outputted the company name into a table, and what I would like to do, for those of the companies that have a website listed in my database, to hyperlink the name of the company to the assigned database.

Any ideas??

 

[grtfercho]

I guess you have something like

Company_name website
abcCompany [ignore]www.abccompany.net[/ignore]
xyzIndustries [ignore]www.xyzind.com[/ignore]
mycompany
Net4all [ignore]www.net4all.org[/ignore]

and you want to show something like

abcCompany
xyZIndustries
mycompany
Net4all



if that is the case just surround the name of the company with the html code along with the hyperlink column.

I figure you have something like #Comp_name# on your code.
Replace that with the following logic. where hyper_column will be the name of your column holding the hyperlinks

<cfif trim(hyper_column) is not "">
<a href="#hyper_column#">#comp_name#</a>
<cfelse>
<b>#comp_name#</b>
</cfif>

that should work.

You could play with CSS if you want to customize a little better. I just showed the companies with no hyperlinks in bold just to tell them apart.


grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[ivalum21]

Alright...I just figured out a new little tid-bit in this problem. grtfercho is correct, that is the way the table is set up, but along with the company name and URL, there is a City column. And a company can have many locations in a city. And the URL for that company isn't always the first record, it could be down the list due to the fact that the ONE company has more than one location in the SAME city.

Example:

companyName City URL
ABC Atlanta NULL
ABC Atlanta NULL
ABC Atlanta

http://www.abc.com

So when that code goes in to check for a URL, it's just checking the first location, it's not going down through the list. Any ideas??

 

[webmigit]

Try this.. in your sql statement.. try this order by clause

ORDER BY CompanyName ASC, City ASC, len(Website) DESC

That will set it so the co/city listing with a website listed will be first.. whell whichever site has the longest url will be listed first so the url should show up for you like you want.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[ivalum21]

I may not have been clear in my description. After I outputted all my information (i.e. company name and city), I want to hyperlink the company name with the website provided in the database.

Again, I have used grtfercho's code above to do this and it's not working because of what I stated above, the code is only looking at the first location for a website.

webmigit, when I put in your code, I still am not getting the company name hyperlinked...???

 

[grtfercho]

why don't you give us a piece of the code that you are using probably that would make things go faster.

I guess that at the very basic you have a query of some type, then a loop based on the query and inside the loop the individual table cells with calls to the individual record values.
SInce this is a general scenario it doesn't help much on trying to resolve the problem.It'll be much easier to see what the problem is if we had something else other than a general idea of how things are working.

I'm thinking a SQL statement may be the solution but I don't want to throw it here without having a good understanding.



grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[ivalum21]

I attempted to add another SQL statement right before my loop, but to no avail. I get an error message saying "Incorrect syntax near the keyword 'FROM'" in my GetURL query.

<!--- Query for displaying the institutions, city, and state. --->
<cfquery name="GetResults" datasource="datasheet">
    SELECT compName, City
    FROM CompLocation
	WHERE (Class = 'M' OR Class = 'S')
    ORDER BY compName
</cfquery>

<html>
<head>
    <title>IMBA</title>
</head>

<body>

<!--- Build table for results --->
<table align="center"
 cellspacing="2"
 cellpadding="2"
 border="8"
 frame="border">
 
<!--- Display column headings --->
<th bgcolor="#ffa500">Company Name</th>
<th bgcolor="#ffa500">City</th>

<!--- Alternate color in rows --->
<cfset colorRow = 1>
	<cfoutput query="GetResults" group="compName">
	<tr>
		<td bgcolor="<cfif colorRow MOD 2 EQ 0>##ffa500<cfelse>##FFFFFF</cfif>">
			<!--- Display company name with hyperlink if applicable --->
			<cfquery name="GetURL" datasource="datasheet">
			SELECT compURL
			FROM CompLocation
			WHERE compName = compName FROM GetResults
				AND compURL IS NOT NULL
			</cfquery>

			<cfif compURL is not "">
				<a href="compURL">#compName#</a>
				<cfelse>
					<b>#compName#</b>
			</cfif>

		</td>
		<td align="center" bgcolor="<cfif colorRow MOD 2 EQ 0>##ffa500<cfelse>##FFFFFF</cfif>">
	<!--- Display all cities for each company --->
	<cfset cityNum = 0>
	<cfset cityList = "">
		<cfoutput>
			<!--- If a city is listed more than once for one company, only show city once in table --->
			<cfif not listFindNoCase(cityList,City)>
				<cfset cityList = listAppend(cityList,City)>
			</cfif>
		</cfoutput>
	<!--- Create a variable for the list of cities --->
	<cfset loopLen = listLen(#cityList#)>
		<!--- Loop cities into appropriate cells seperated by a "," --->
        <cfloop from = "1" to = "#loopLen#" index = "thisCity">
        	#listGetAt(cityList, thisCity)#
        	<cfif thisCity neq listLen(#cityList#)>
				,
			</cfif>
        	<cfif thisCity Mod 3 eq 0>
				<br>
			</cfif>
        </cfloop>
	</tr>
	<!--- Increment counter --->
    <cfset colorRow = colorRow+1>
    </cfoutput>
    
</table>

</body>
</html>

 

[grtfercho]

I see what you are trying to do now. Is it possible that your table could have more than one entry for the same company in different cities??

What I mean is if abcComp has 5 locations in two cities and a website for each city(2 websites total), how do you plan to show that? is it even possible?

I'm checking the code......will let you know

grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[ivalum21]

That is possible, that one company could have 3 locations in Atlanta and 2 in Chicago. But out of the 3 in Atlanta, only ONE entry in the database will have a website, the other 2 will be null...same goes for Chicago and so on.

 

[ivalum21]

I got it figured out...here is the code for my second query:

<cfquery name="GetURL" datasource="datasheet">
	SELECT compURL
	FROM compLocation
	WHERE compName= '#GetResults.compName#'
		AND compURL IS NOT NULL
	</cfquery>
			<cfif GetURL.RecordCount gt 0>
				<a href="http:\\#GetURL.compURL#">#compName#</a>
				<cfelse>
					<b>#compName#</b>
			</cfif>