PHP Search Script

CBResources · May 10, 2011, 3:46pm

Hi CT,

It’s cleaned up the links a bit now but still displaying everything immediately, not just the names… see for yourself:

Also, it’s still returning to a blank form when either of the names are clicked.

It needs to display just the names as search results, and then display the rest of the details for a specific name once it’s clicked on.

And Cups - It is just male and female… I don’t think anybody will be looking for names for their baby hermaphrodites

Cups · May 10, 2011, 4:25pm

In that case searchgenderterms is in fact a boolean value.

1=male
0-female

which corresponds to a far easier query, the equivalent of:


..... AND gender = 1;

Plus it is easier to filter and sanitize.


$gender=0; // default to female
if( isset($_GET['gender'] && (int)$_GET['gender'] == 1 ){
$gender= 1;
}


"..... AND gender = $gender";

You could use ‘male’, ‘female’ instead of 1, 0 if you wanted.

Cute_Tink · May 10, 2011, 4:40pm

If you only want to display the name on the first page, then we need to adjust your query to reduce unnecessary overhead and adjust the $results array accordingly. In a way, we will be restoring it to what it was before (changed snippets only):


$searchSQL = "SELECT name FROM names WHERE `name` LIKE '{$searchTermDB}%' AND `gender` = '{$searchgenderTerm}' ";

while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = $row['name'];
}

foreach ( $results as $value ) {
      echo '&lt;a href="babynames.php?searchTerms=' . $searchTerms . '&amp;searchgenderTerms=' . $searchgenderTerms . '&amp;name=' . $value . '"&gt;' . $value . '&lt;/a&gt;&lt;br /&gt;';
   }

Those are the lines that you would change to show just the names on the search page. I think I kept the query right, based on what you said for the gender part before.

And I agree with cups on the simplification of the query, but I would argue for extra security reasons. If you want to stick with male/female, then this would simplify your security for it:


$searchgenderTerm = ( $_GET['gender'] != 'male' ? 'female' : 'male' );

Didn’t think about that before, but when it is one or the other, this is easier than using mysql_real_escape_string() or htmlspecialchars(), etc.

Cups · May 10, 2011, 6:47pm

@Cute Tink, yeah, agree on the security method as long as gender is actually set, and if you plump for male / female flags.

From the db point of view though I admit I prefer 1/0 - but that is just a matter of personal choice.

The thing is CBResources, you are muddying your code by terming it searchgenderterm, if you HTML field had the name=“gender” ( and maybe id=“gender”) and you used the variable name $gender in PHP and your database field was called gender, well it would all be a) easier to type b) easier to read and ultimately you would make fewer errors and also be far more likely to want to come back and edit this code a few months down the line.

I have a second nagging question, what happens if/when a name is spelt the same for both male and female?

Cute_Tink · May 10, 2011, 7:47pm

On that point, I realized I didn’t type something right:


$searchgenderTerm = ( $_GET['searchgender'] != 'male' ? 'female' : 'male' );

and it should be inside the if statement that checks whether $_GET[‘searchgender’] is set.

Whether it is 1/0, m/f or male/female, it all works the same if the column is set to enum and it is consistent. Like you said, it is whatever you prefer.

As for what happens when the same name is for both male and female, like Robin perhaps, there would have to be two database entries, one for each, because some times there are different meanings or origins.

CBResources · May 12, 2011, 7:22am

Absolutely right CT, there is a database entry for every “version” of the name so “Chris” for instance would have an entry for Male and an entry for Female. When people are searching, they choose the gender they are searching for “Male” or “Female” so it will only bring up the one relative to the gender they are looking for.

CT, I’ll try out your suggestions today and come back to you on this thread to let you know how they worked

Thanks again to all of you for your help on this

CBResources · June 14, 2011, 8:37pm

Hi Again,

Sorry for bringing this threat back to life after so long - Other work got in the way so I’m just getting back to this now.

All previous suggestions have worked a dream and my script is now running almost perfect… I just need some help with some minor modifications to the results display.

Currently, it displays in a long list. I want to split this list and paginate it.

I only want to display 50 results per page, further split down into 5 columns (so that’s 10 results per column).

I’ve no idea how to paginate and columnize SQL search results so I’m hoping someone can help me out…

Here is the part of the script that handles the results display:

<?php 

if (count($results) > 0) {

   echo "You searched for: <span id=\\"terms\\">{$searchTerms} ({$searchgenderTerm})</span> - Here are your results:<br /><br />";

   foreach ( $results as $value ) {
      echo '<a href="babynames-details.php?search=' . $value . '&amp;searchgender=' . $searchgenderTerm . '">' . $value . '</a><br />';
   }
  

}

?>

So I want it to display the results like so:

Name 1 Name 11 Name 21 Name 31 Name 41
Name 2 Name 12 Name 22 Name 32 Name 42
Name 3 Name 13 Name 23 Name 33 Name 43
Name 4 Name 14 Name 24 Name 34 Name 44
Name 5 Name 15 Name 25 Name 35 Name 45
Name 6 Name 16 Name 26 Name 36 Name 46
Name 7 Name 17 Name 27 Name 37 Name 47
Name 8 Name 18 Name 28 Name 38 Name 48
Name 9 Name 19 Name 29 Name 39 Name 49
Name 10 Name 20 Name 30 Name 40 Name 50

<<Previous Page >> Next Page

I hope that makes sense…

Cups · June 14, 2011, 9:18pm

Use Mysql’s LIMIT clause, as seen in plenty of examples [google]php pagination[/google] to get bunches of 50 results.

As for how to display them, every 10 results end a <td> and start another <td>.


$start_table = 'start table <table><tr></td>' . PHP_EOL;
$end_table = '</tr></table>' . PHP_EOL;
$tbody = "";
$cnt = 0;
foreach($results as $value) {
 $cnt++;
 $tbody .= $value;
 if( ( $cnt % 10) ==0 ) $tbody .= '</td><td>' . PHP_EOL;

}

echo $start_table, $tbody, $end_table;

You gotta do some work to chop off the last </td> from tbody with [fphp]rtrim[fphp] but you should get the gist.

CBResources · June 15, 2011, 1:37am

Hi Cups,

Sorry but you’ve lost me lol.

Where do I put my “echo” in that? And I don’t understand what you mean about the last </td> - I don’t know what [fphp]rtrim[fphp] means?

Any chance you could elaborate a little for me? I don’t know where to put my script specific info into your code?

Cups · June 15, 2011, 10:34am

Where I wrote $value, you enter your entire string:


$tbody = '<a href="babynames-details.php?search=' 
. $value . '&amp;searchgender=' . $searchgenderTerm . '">' . $value . '</a><br />';

(put as 2 lines to make it readable on this posting)

If you dont like the build up method I used, then just echo the variables straight onto the page instead:


echo 'START TABLE <table><tr><td>START TD' . PHP_EOL;

$cnt = 0;
foreach($results as $value) {
 $cnt++;
 echo '<a href="babynames-details.php?search=' 
         . $value . '&amp;searchgender=' . $searchgenderTerm . '">' 
         . $value . '</a><br />' . PHP_EOL;

  if( ( $cnt % 10) ==0 ) {
      echo '</td><td> START TD' . PHP_EOL;
   }
}

echo '</tr></table> END TABLE' . PHP_EOL;

** there was an error in my first code where the opening <td> was written </td>
The link to the manual was malformed, should be [fphp]rtrim[/fphp]

CBResources · June 15, 2011, 8:08pm

Ahh I see - Thank you Cups, I really appreciate your help.

I will have a play around with this tonight/tomorrow and see how I go but I’m sure this will be exactly what I need I’ll let you know how I get on.