Carol,

Everytime I have a question (as well as everybody else), I'm always amazed at how you have the time and patience to help!

Of course, I have another question as well! :-)

I'm needing to do two things... One is display the number of items in a record field (I already know that it is either a yes or no in the field) - and the other is to wrap a predefined URL (one that searches the database) around each of those numbered results.

I'm using the following to get the results:


And the URL works out in my case to be:


However, because you're displaying the results of the record search as:


I am unable to wrap a different search URL for each of the items returned.

Because I know all the possibilities, is there some how I can format the results better in HTML so I can wrap a URL around each one?

I'm thinking that the thread at http://www.gossamer-threads.com/...m12/HTML/000235.html may be my ticket, but I'm unsure.

I'm guessing that if I can open the database and say "How many 'yes's' are in field 14" I could wrap a URL around that number. The problem comes into play when there are loops that affect the HTML code as well as the dynamic code in the database.

Any ideas on how to pull that off?

Thanks again!
Dave

Can you restate the problem again. I don't understand.

Well, maybe I can tell you what I think you want and you can tell me if I'm right or not.

You want to count how many records have "Yes" in a given field. Is that correct?

Where I really get confused is

I don't know what you mean.


------------------
JPD

When I mean "Wrap a URL around it", I mean the section:


They are predefined URL encoded searches connected to each result.

So let's say that it works out that Yes - 304 would be valid result of a number of 'yes' records in a particular field. I would then be able to "wrap" the URL above around the word "Yes". That search is most likely going to be a search that shows you all of the 'Yes' 's in the database.

In my case a manager wants to know how many "open" trouble tickets there are. Now he can open the front page and see without have to go to the search form everytime. He can then click on the number to see each of those trouble tickets (this would be a dynamic search because of the Form posting via the encoded URL).

Of course, there may be more than just a yes or a no in the field. I may use the solution to look for three different items - yes, no, and on hold.

Many thanks!
Dave

Unfortunatly, this is the wrong direction. I need to get out of looping through HTML code. In my exact case, there are four records I would like to show information about on the home page.

Complete:
UAT:
Emergency:
Approved:

These happen to only contain a possible 'Yes' or 'No', but in the future may contain more possibilties. The results are usually heavily lopsides so that there may be 1200 'Yes' and 23 'No'. Therefor, I don't need to show the 'Yes' results - only the 'no' results. But on the catagory, there may be 800 'No' and 30 'Yes'. Then I would want to only show the results for 'Yes'.

Directly, I want to ask the Database this question every time the home page is shown:

"How Many ($variable) are in the field #3."

and have it give me a number. With that number I can HTML format it anyway I want to.

In my case, I'm going to be asking four different "Questions" of the database. Some may be of the same field, some may not.

I hope that helps.

Thanks!
Dave

Change 4 to match the field you want to count.

When you print out the links, use


This assumes that you have added the urlencode subroutine to your db.cgi script. If you haven't, you can pick it up at www.jpdeni.com/dbman/Mods/changes.txt .

I'm still not sure what you need to do beyond this. If you need more, I'll need you to try to explain it once again.


------------------
JPD







[This message has been edited by JPDeni (edited October 06, 1999).]

Okay. Let's try it again.

There are a finite number of options for each of four fields.
For each of the fields, you wish to count how many records have each option.
The options are the same across fields.

Therefore the %count hash has to include both the name of the field and the option.

Instead of

You can use (for each of the fields):

You will need to replace $fieldnum with the number of the field, though.

That will give you something like

$count{Complete}{Yes} = 800
$count{Complete}{No} = 43
$count{UAT}{Yes} = 487
$count{UAT}{No} = 300

Is that what you need?

If so, do you need the script to figure out which of the options has the lowest count and just print that out? Or is this something you're going to do yourself?



------------------
JPD

Yes! :-) That's what I need. So that in my case the statement:

$count{Complete}{Yes}

could be stored in a variable. I'm a little confused at where I would ask the script what to find in the database, though - unless in this case, I don't use all of the results in my output - just what I (personally) want to have displayed.

So as in above, I ask the script to tell me how many 'Yes's are in a field and to store that answer in a variable that I can then use in my HTML. Once I know the numbers, I can then use my (overused) "If Then" statements as well.

So, Yes. I would like your help in showing me a way to script that. I don't need to know which is lower or higher.

Am I safe to assume that I could do that with other fields as well:

$count{'First Name'}{Bob}

would give me the number of how many Bob's are in the field, 'First Name'?

Thanks again for your patience.

-Dave

That's right. (I think it's very important to know how many "Bob"s are in one's database! )


Does this help at all?


------------------
JPD

Carol,

Thank you so much! It's working perfectly now. After working through this many times (and figuring out why it would't work 75% of the time!) - it's clear that correct scripting does help solve the problems!

I do have one last request for this thread: Now that I can ask for counts, I'm using:


to get easier to read stats. How do I chop off the extra numbers so that I only get something like 15.54%?

Again, many thanks for your patience.

-Dave

You need it rounded to two decimal places? Piece o' cake!

After your line


add


You're welcome!


------------------
JPD

One small extra to this (now that I've changed one of my fields to be a text field instead of a yes/no field)

Is there an easy way to count how many records there are with anything but a null in a field? I've run through counting out all of the possibilities in a field - but now I'd like to just have a total count of items in the field. This is different from how many total records in the database. Not all of the records will have something in this field.

I'm using the above example perfectly... But in one field - I don't need seperate counts... Just a total count of empty or not empty.

Thanks!

By changing the possible values of the field, you have changed the way the code needs to work.

I'll need to to start from the beginning, explaining what you need.


------------------
JPD