close ad
Help us test the new Databridge BETA with MySQLi support
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

nearly there

Thread began 11/04/2009 2:07 pm by i.edwards384429 | Last modified 11/05/2009 4:44 pm by Jason Byrnes | 2615 views | 7 replies |

i.edwards384429

nearly there

Ok the title is not desacriptive to you but it is to me!!!!!

Last problem ( Ok it wont be)

used the dataassist search wizard and the tutorial line by line to create the search at

search.php

So I wasted an hour working out it failed to work because I did not havbe any data in the table ( my turn for the brain cell tomorrow)

So I added three records and as I had only added one search criteria that of sex thought I could search on either male or female.

When I select the dropdown box I see the gender enrty for all three records listed.

So it seems to me that I am displaying the records from the table rather than doing a search.

I am of course only using the users table from the solution do I need two tables to acheive the search function?

thanks

Ian

Sign in to reply to this post

Jason ByrnesWebAssist

On the search, you need to modify the recordset you are using to populate the list:
SELECT DISTINCT genderColumn FROM tblName


using DISTINCT will filer out duplicates.

Sign in to reply to this post

i.edwards384429

Update

Hi Jason

there I was saying I was nearly there ;-)

In my search.php


(search.php)

I changed the record set - first selecting the advanced button - to

SELECT DISTINCT UserAge,UserSex,UserTypes,UserArea,UserEthnicity
FROM users

where UserAge,UserSex,UserTypes,UserArea and UserEthnicity are the field names.

However when I run the page on my test server it still when I use the box select arrow choices of

male
female
male

Is this recordset codong correct?

thanks

Ian

Sign in to reply to this post

Jason ByrnesWebAssist

The distinct key word returns a distinct result for all of the columns in the select statement.

Where you only need the UserSex column to be returned, it should be the only column in your select statement


SELECT DISTINCT UserSex
FROM users


IF you *did* need all of the other columns returned, dont use distinct, use GROUP BY:

SELECT UserAge,UserSex,UserTypes,UserArea,UserEthnicity
FROM users GROUP BY UserSex



For more information on the DISTINCT key word:
sql_distinct.asp

For more info on GROUP BY:
sql_groupby.asp

Sign in to reply to this post

i.edwards384429

update

Hi Jason

don't seem to be getting very far as I still have the same issuedespite implementing your last code suggestion

I cant get the search at search.php to behave as I want it to.

I need to search the database and allow the user by means of the drop down box to create their own filter ie show them Males who are asian and who are between 30-40 years old.

The search does work but all of the select boxes seem to want to show the entries for each record.

Are searches supposed to work in this way in mysql?

thanks

Ian

Sign in to reply to this post

Jason ByrnesWebAssist

can you send a copy of the search page?

Sign in to reply to this post

i.edwards384429

update

Hi Jason

I've zipped up the search,results and an sql dump and uploaded them.

thanks again

Ian

Attached Files
files.zip
Sign in to reply to this post

Jason ByrnesWebAssist

Your search page defines the rsItems recordset three times in a row:

php:
$query_rsitems = "SELECT UserAge,UserSex,UserTypes,UserArea,UserEthnicity FROM users GROUP BY UserSex";

$rsitems = mysql_query($query_rsitems, $localhost) or die(mysql_error());
$row_rsitems = mysql_fetch_assoc($rsitems);
$totalRows_rsitems = mysql_num_rows($rsitems);
$query_rsitems = "SELECT DISTINCT UserAge,UserSex,UserTypes,UserArea,UserEthnicity FROM users";
$rsitems = mysql_query($query_rsitems, $localhost) or die(mysql_error());
$row_rsitems = mysql_fetch_assoc($rsitems);
$totalRows_rsitems = mysql_num_rows($rsitems);
$query_rsitems = "SELECT DISTINCT UserAge, UserTypes, UserArea,UserSex FROM users";
$rsitems = mysql_query($query_rsitems, $localhost) or die(mysql_error());
$row_rsitems = mysql_fetch_assoc($rsitems);
$totalRows_rsitems = mysql_num_rows($rsitems);



PHP cpde is run from line 1 to the end, one line at a time, by the time you get to the code that creates the select list using the rsitems recordset, the last on is the one that is being used:
SELECT DISTINCT UserAge, UserTypes, UserArea,UserSex FROM users




Hers what you need to do:
Create a recordset fro each select list, one for the gender, one for the ethincity, one for the age etc....


rsGender:
SELECT DISTINCT UserSex FROM users

rsAge:
SELECT DISTINCT UserAge FROM users

rsType:
SELECT DISTINCT UserTypes FROM users

rsArea:
SELECT DISTINCT UserArea FROM users


rsEthnicity:
SELECT DISTINCT UserEthnicity FROM users

Each select list should use the recordset created for that list. The Gender list uses the rsGender recordset. The Age list uses the rsAge recordset etc....

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...