Search string query

Hi, I have setup a basic database, where you submit a persons information. - You input there name, address, telephone, age, religion etc…

The problem I am getting is when you search an age, for example ‘17’ it would also bring up results with people who have ‘17’ within their telephone number (07567651761).

Each bit of data is input to a MySQL table cell. - Here is the code I am using at the moment for the search string to pick out the data from a cell.

<?php	function get_num_posts() {
		$search_string = isset($_POST['search_string']) ? check_input($_POST['search_string']) : '';

		$result = $GLOBALS['db']->query("SELECT COUNT(p.post_id) FROM " . $GLOBALS['config']['db']['prefix'] . "database_posts p LEFT OUTER JOIN " . $GLOBALS['config']['db']['prefix'] . "categories c ON p.category_id = c.category_id 
WHERE p.title LIKE '&#37;$search_string%' OR p.age 
LIKE '%$search_string%' OR p.agegroup 
LIKE '%$search_string%' OR p.gender 
LIKE '%$search_string%' OR p.ethnic_id 
LIKE '%$search_string%' OR p.religion 
LIKE '%$search_string%' OR p.lifestage 
LIKE '%$search_string%' OR p.address_line1 
LIKE '%$search_string%' OR p.address_line2 
LIKE '%$search_string%' OR 
LIKE '%$search_string%' OR p.county 
LIKE '%$search_string%' OR p.postcode 
LIKE '%$search_string%' OR p.occupation 
LIKE '%$search_string%' OR p.ocother 
LIKE '%$search_string%' OR p.jobtitle 
LIKE '%$search_string%' OR p.hoccupation 
LIKE '%$search_string%' OR p.hjobtitle 
LIKE '%$search_string%' OR p.hseg 
LIKE '%$search_string%' OR 
LIKE '%$search_string%' OR 
LIKE '%$search_string%' OR 
LIKE '%$search_string%' OR p.cage 
LIKE '%$search_string%' OR p.cage2 
LIKE '%$search_string%' OR p.cage3 
LIKE '%$search_string%' OR p.cage4 
LIKE '%$search_string%' OR p.cage5 
LIKE '%$search_string%' OR p.clifestage 
LIKE '%$search_string%' OR p.clifeother 
LIKE '%$search_string%' OR p.pets 
LIKE '%$search_string%' OR p.pets2 
LIKE '%$search_string%' OR p.pets3 
LIKE '%$search_string%' OR p.pets4 
LIKE '%$search_string%' OR p.cmake 
LIKE '%$search_string%' OR p.cmake2 
LIKE '%$search_string%' OR p.cmodel 
LIKE '%$search_string%' OR p.regyear 
LIKE '%$search_string%' OR p.ctype 
LIKE '%$search_string%' OR p.ctype2 
LIKE '%$search_string%' OR p.mobprov 
LIKE '%$search_string%' OR p.mobprov2 
LIKE '%$search_string%' OR p.internet 
LIKE '%$search_string%' OR p.internet2 
LIKE '%$search_string%' OR p.gas 
LIKE '%$search_string%' OR p.gas2 
LIKE '%$search_string%' OR p.electric 
LIKE '%$search_string%' OR p.electric2 
LIKE '%$search_string%' OR p.landline 
LIKE '%$search_string%' OR p.landline2 
LIKE '%$search_string%' OR p.tvprov 
LIKE '%$search_string%' OR p.tvprov2 
LIKE '%$search_string%' OR p.pastres 
LIKE '%$search_string%' OR p.socialgrad 
LIKE '%$search_string%' OR p.reshistory 
LIKE '%$search_string%' OR p.reshistory2 
LIKE '%$search_string%' OR p.findme 
LIKE '%$search_string%' OR p.findme2 
LIKE '%$search_string%' OR p.content 
LIKE '%$search_string%' OR c.category_name 
LIKE '%$search_string%'");

		return $GLOBALS['db']->result($result, 0);
	} ?>

If anyone has any tips on how each search phrase can be searched exact, rather than picking up on other words or numbers within a database that would be great.

Many thanks,


You may have to elaborate on exact, do you know how your current code works?

I’m not being (or at least trying to be) patronising, but it would help to know where you are in this problem to help better explain a solution.


Ok, where I am at is that when you search, search results are to specific. If for example you search EDF, you would hope that all results would return with people using EDF Energy. - Instead it also shows people from bEDFordshire because it has the letters EDF in it.

Is there a way so that it will only pick up the single letters EDF rather than the letters within a word?


Sure, change all…

field LIKE '&#37;$search_string%'


field LIKE '$search_string'


Is it really as simple as that? - what does the percentage symbols mean? - always good to learn these things :slight_smile:

The MySQL manual can explain it much better than I. :slight_smile:

Ok cool thanks - do you know how I can write an if statement to only show a link if posts have been made within a database?

Well, you pose quite a generic question. However, given your previous snippet, something like…

if(0 < ($num = (int)get_num_posts())){
        '<a href="/path/to/resource/">View the &#37;d posts</a>',

Ok, I have tried that and returned 1 error… here is how I have input it:

		<table border="0" cellpadding="3" cellspacing="1" width="100&#37;">
				<td colspan="7" class="header"><h1>Posts</h1></td>
				<td width="10%" class="table_row"><strong>Delete:</strong></td>
			  	<td width="30%" class="table_row"><strong>Name:</strong></td>
				<td width="50%" class="table_row"><strong>Email:</strong></td>
				<td width="20%" class="table_row"></td>
			<?php foreach (get_posts($page) as $post) { ?>
					<td align="center" width="10%" class="table_row"><input type="checkbox" name="delete<?php echo $post['post_id']; ?>" value="yes" /></td>
					<td width="30%" class="table_row"><a href="/admin/database/posts/edit.php?post_id=<?php echo $post['post_id']; ?>" onclick="return popup('/admin/database/posts/edit.php?post_id=<?php echo $post['post_id']; ?>')"><?php echo $post['title']; ?></a></td>
					<td width="50%" class="table_row"><a href="mailto:<?php echo $post['email']; ?>"><?php echo $post['email']; ?></a></td>
					<td width="20%" class="table_row"><a href="/admin/database/posts/edit.php?post_id=<?php echo $post['post_id']; ?>" onclick="return popup('/admin/database/posts/edit.php?post_id=<?php echo $post['post_id']; ?>')"><img src="/admin/images/button_edit.gif" border="0" alt="Edit" /></a></td>
            <?php $emails[] = $post['email']; } $emails = implode(";",array_unique($emails)); ?>
	<?php echo multipage(get_num_posts(), 15, $page, '/admin/database/posts/index.php?page='); ?>
    <table align="center" width="25%" border="0" cellspacing="0" cellpadding="0">
    <td><p align="center">
	<?php if(0 < ($num = (int)get_num_posts())){
				   '<a href='".$emails."'><img src='/admin/images/button_email.gif' width='138' height='22' border='0' /></a>',
    <td><p align="center"><input type="submit" name="submit" value="Submit Changes" /></p></td>

And here is the error I return…

Parse error: syntax error, unexpected T_STRING in C:\inetpub\wwwroot\admin\ emplates\database\posts\index.tpl.php on line 33

Line 33 is:

‘<a href=‘“.$emails.”’><img src=’/admin/images/button_email.gif’ width=‘138’ height=‘22’ border=‘0’ /></a>',

The quotes that you are using for the HTML attributes should be double quotes instead.
Single quotes for PHP strings, double quotes for HTML attributes.

Cool, all works fine… thanks :slight_smile:

With regards to the if statement you wrote to show links only if a post is made… how can you input some PHP code rather than a link? Here is what I tried but it didn’t work, it just shows the line of PHP code as text…

<?php } if(0 < ($num = (int)get_num_posts())){
			printf( '$emails[] = $post["email"]; $emails = implode(";",array_unique($emails))', $num

I have also tried it with “” rather than ‘’ and returned this error…

Parse error: syntax error, unexpected ‘]’, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\inetpub\wwwroot\admin\ emplates\database\posts\index.tpl.php on line 25

Line 25 is …

Parse error: syntax error, unexpected ‘]’, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\inetpub\wwwroot\admin\ emplates\database\posts\index.tpl.php on line 25

So you do NOT want to print any results on the page? If so

Just get rid of the printf if you do not want to display the data.

No I only want the ‘$emails = $post[“email”]; $emails = implode(“;”,array_unique($emails))’ line to work if there are posts (results). If there are no posts made then I don’t want the code to work.

Hope that makes sense.

You’re confusing me. How do your requirements differ from what wackyjoe suggested?

I think I am getting confused myself - basically I want to enable some PHP code within this if statement…

<?php } if(0 < ($num = (int)get_num_posts())){
			printf( '$emails[] = $post["email"]; $emails = implode(";",array_unique($emails))',

This line ‘$emails = $post[“email”]; $emails = implode(“;”,array_unique($emails))’ currently just reads as text when there are posts, when really I want the code to actually work.

Does that make sense? :slight_smile:

Yes, that makes sense. Let me quote from wackyjoe

So instead of printf(‘foo’, bar); you just want foo; instead.

Ok, this worked a charm - wahey.

Now my next problem (sorry, it’s just one after another :frowning: ) - now that the code works fine the email link which is supposed to pick up from the line of code in the if statement doesn’t load.

<?php } if(0 < ($num = (int)get_num_posts())){
			$emails[] = $post['email']; $emails = implode(";",array_unique($emails)); } ?>
<?php if(0 < ($num = (int)get_num_posts())){
				   '<a href="'.$emails.'"><img src="/admin/images/button_email.gif" width="138" height="22" border="0" /></a>',

the .$emails. in the second bit of PHP code does not pick up on the first bit of PHP code.

It doesn’t return any errors, the bcc= should show all the email addresses, but instead shows nothing.

I don’t think that the implode part should be done until after all of the emails have been added.

Perfecto - you are absolutely right! Thank you all so very much for your kind help :slight_smile: