Please help with multiple search problem

[Sapper]

Hi again all,

I have a problem with a script I am writing and I have had a lot of help from people on this board. I would appreciate it if anyone could help solve this for me.

Basically I am tring to create a members search page which only pulls out exactly matched criteria. for example:

If someone wants to find a female aged 18-25
then they select F and 18-25 from the menu, on the html page I have created the two field names in this case are as below.

sex2
agegrp2

when the submit button is pressed it then pulls the data from another file called data_out.php3 which is listed below.

It does pull out the correct age groups but it pulls out both male and females can anyone help by looking at what I have done wrong.

I am new to this but I have obviously done something wrong with this part of it.

elseif (isset($submit)):
$sql = "select * from profiles where sex = '$sex2'";
$sql = "select * from profiles where agegrp = '$agegrp2'";
else:


Also if not to confuse matters I am trying to put a limit of a max 5 records to be displayed so any help here would also be appreciated.


<?php
/* This program gets members from the database */
$db = mysql_connect("localhost", "username" , "password");
mysql_select_db("dbname", $db);
if ($orderby == 'memid'):
$sql = "select * from profiles order by 'memid'";
elseif ($orderby == 'agegrp'):
$sql = "select * from profiles order by 'agegrp'";
elseif ($orderby == 'fname'):
$sql = "select * from profiles order by 'lname'";
elseif (isset($submit)):
$sql = "select * from profiles where sex = '$sex2'";
$sql = "select * from profiles where agegrp = '$agegrp2'";
else:
$sql = "select * from profiles";
endif;
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
print("<tr><td bgcolor=\"#003399\"><b>");
printf("<font color=\"white\">%s</font></b></td></tr>\n",
$row["fname"]);
printf("<td>Email: <a href=\"mailto:%s\">%s</a>\n",
$row["email"], $row["email"]);
$offset = 0;
printf("<br>Account Number: %s<hr>\n",
$row["memid"]);
print "<img src=\"/gfx/".(string)$row["memid"].".jpg\">";
printf("<br><hr>Age Group: %s\n",
$row["agegrp"]);
printf("<br><hr>Location: %s<hr>\n",
$row["location"]);
printf("Interested In: <br>%s<hr>\n",
$row["interestedin"]);
printf("My Likes Dislikes: <br>%s</td></tr>\n",
$row["likesdislikes"]);
}
?>


I have experienced problems trying to post messages on this board so if I dont answer quickly I am not being ignorant I just seem to have hassles after posting.

my email address is ppeters@flexiweb.net

[CrazyCrane]

Looking at this snippet of code first:

elseif (isset($submit)): 
$sql = "select * from profiles where sex = '$sex2'"; 
$sql = "select * from profiles where agegrp = '$agegrp2'"; 
else:

To have more than one argument in a SQL query you need to use the AND keyword. So in your case, to query on both sex and age group, you'd need to rewrite that snippet to be something like

elseif (isset($submit)): 
$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2'";
else:

Also worth noting, the code you had before was setting the value of $sql then immediately overwriting it with another value, which is why your query was only working on age group.



If you want to limit the number of records retrieved, you can use the LIMIT keyword in mySQL. You'd add this to the SQL above, creating:

elseif (isset($submit)): 
$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2' limit 5";
else:

This restricts the query to bring back (at most) 5 records.

Hope that helps .

[Sapper]

Thanks for your help one last problem i need to add more than 2 search criterias and the and command will only work for 2 so is there anything else that I can add.

ie.

$sql = "select * from profiles where iama = '$iama2' and agegrp= '$agegrp2'";

the third thing i need to add is interestedin= '$interestedin2'
I am about to try your limit suggestion thanx once again

[Sapper]

The limit part works fine except that it gives no option to display any remaining on another page.

Any ideas??? sorry but I am very new to this..

[CrazyCrane]

You can keep adding as many ANDs as you like:

$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2' and interestedin= '$interestedin2'";

- as I say you can have as many ANDs as needed.

To display the other records on another page you'd simply create a link to a page, and on that page execute the same SQL query but omit the limit part. A neater way is to call the same page, but pass in a parameter to say "I want to see all records, not just the first 5".

Your page is called data_out.php3 I think? In that case you might make the "show all records" link point to "data_out.php3?show=all" or something like that. Then in your code, where you currently have

elseif (isset($submit)): 
$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2' limit 5";
else:

(with any extra AND consitions you might need), rewrite it slightly to be:

elseif (isset($submit)): 
$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2'";
if ($show != "all"):
    $sql .= " limit 5";
endif;
else:

In that code, you create the start of your SQL statement. Then you check to see if the page was called with "show=all" in the querystring. If it wasn't you only want to show five records, so you add the "limit 5" onto the SQL statement (note the ".=" will append it to the string, where just "=" would overwrite it). If "show=all" was present you want to show all records, so you don't bother adding the limit on.

Obviously that's just an example of how it could be done, you should feel free to change it to however works best for you.

Hope you can understand that poor explanation .

[Sapper]

Hi again back to this and option.

I have tried adding more than one and command but it still returns no files when I know that the options I select should return at least 2 files.

any ideas on this while I try your other suggestion with regards to extra pages

[Sapper]

I am sorry to be a pain the show all code works fine except this is a database that is going to contain hundreds of profiles and I really need to be able to have a next 5 and previous five records or something like that, it would be too big for them all to be displayed on one page..

Is it possible or am I trying to achieve the impossible???

[Sapper]

Crazy Crane Thanks for your help I have finally got the and commands to work. Thankyou

[CrazyCrane]

No problem at all, glad it helped . Sorry I didn't reply to your previous question, but I see you sorted it out anyway .

As for "paging" the data (to show say 5 at a time with previous and next links), you can indeed do that with just a bit of reworking.

Where above I told you to make the link be "data_out.php3?show=all", change that a bit to be "data_out.php?page=n" where n is the page number (page 1, 2, 3 etc.) So page 1 will show the first 5 records, page 2 shows the next 5 etc.

Now... the LIMIT keyword I showed you above can be given another parameter to tell it where to start. Writing "... limit 5" restricted it to 5 records - writing "... limit 2, 5" for example would tell it to get 5 records starting from the third record - so the first parameter is 0-based. If it's omitted it's assumed to be 0 (i.e. starting from the first record).

So now, where we used to have

$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2'";
if ($show != "all"):
    $sql .= " limit 5";
endif;

we change it to be

$sql = "select * from profiles where sex = '$sex2' and agegrp = '$agegrp2' limit " . (($page - 1) * 5) . ", 5";

This way, when page is 1 you're saying "limit 0, 5" (get the first 5 records), when page is 2 you're saying "limit 5, 5" (get 5 record starting from record 6) etc.

Your previous/next page links then just need to be along the lines of "data_out.php3?page=" . ($page - 1) and "data_out.php3?page=" . ($page + 1) - I'll leave it to you to sort it so that on page 1 you don't have a link to page 0 etc.

One more thing - it's probably worth adding a little bit of code before that up there, which sets $page = 1 if it's not set (so if they just call data_out.php3 it defaults to page 1).

Hope that helps .

[Sapper]

I am eternally greatful for your help, and always suprised by the speed and eagerness of people to help people like myself.

I have started inserting the code above but before I finish is it not possible to have those links actually appear on the foot of the original page that is generated since the searches will always produce a different number of pages which would be impossible to guess.

so say that the search pulled in 30 records it would print the first 5 to screen then offer the option to see the rest over page 2,3,4,5,6 .

Sorry to keep pestering you if you have had enough just let me know and I will stop..

Thanx

[CrazyCrane]

Hehe don't worry about it .

It certainly is possible to do that, but I have to say now that this may not be the best way to do it. I've never actually written any PHP, I'm only just now setting out to learn it, so there could well be a better way round this - hopefully if there is someone else will post an example .

However, what you basically need to do is find out how many records match your query, so that you can figure out how many pages you have. One way to do this would be to create another SQL query along the lines of "select count(*) from profiles...." with the same where clause and "and" cases as your other query. This will return the number of records which match the query you're about to perform. You can then divide this number by 5 (rounding up) to find out how many pages you'll have, so you can create the paged links I talked about above (just create linkes from page=1 to page=number_of_pages).

Sorry I haven't included any sample code, but it's just a simple case of performing a query to find out how many records you'll have first.

If there's a better way, hopefully someone could post it? (I know in ASP you could get the total recordcount from the recordset without needing to do a seperate query, I don't know if there's an equivalent in PHP...?)

[Sapper]

Thanks for your help anyway.

I have been trying to post this message all day