Topic: Custom Search Page help

[willerby]

Could do with some custom coding help for a page - probably PHP?

I have a new simple database table called 'vehicles' which has two fields I'd like to search on and display results of the entire row (6 fields).

I'd like an SP page with a simple search input box, two check boxes to indicate which field is being searched (similar to member list search) and a search button. Results should display in a list below the search fields so that further amended searches can be made.

Can anyone suggest code for such a page? Happy to explain further if necessary. Thanks for any help

W

[willerby]

Paypal donation waiting? Can't be out of the ordinary stuff can it?

[AngelinaBelle]

You could start by creating a custom PHP page, or a custom PHP block. You can decide later where you like it better. I can give you some help next week.
 
What do you know about HTML, php, and SQL?
You will write some php code that will decide whether to display a form or use the input from the form to do a query and create output. This is exactly one of the things php was designed to do.
 
First, design the form in HTML, and get php to produce it.
Next comes code to recognize whether the form has been submitted.
Finally comes the code to make the input safe and do the SQL query.
 
http://www.w3schools.com/html/html_forms.asp
http://www.w3schools.com/PHP/php_forms.asp
http://www.w3schools.com/sql/default.asp
 
One question -- how did all that data get on the new table? Do you already have a custom page that puts data on the table?

[willerby]

The data was created by import and doesn't need to be added to by members so I have no input forms.

I don't understand much about PHP at all, HTML I can normally muddle through using sites like that mentioned, and SQL I can usually get a query to output what I want but often through trial and error!

Any help appreciated.

[AngelinaBelle]

If you can figure out HTML, you can muddle through with php. And there are always a few people around willing to help anyone whose behavior is friendly, considerate, appreciative, and willing to learn.
 
Here's what I suggest for your next post:

• An html form for the search, with the html between  code tags so it is easy to see, select, and work with.
• The table definition, also between  code tags.

If you post that, I will show you some php code you can test in your block. If you read a little about making a php form submit to itself, you will understand what it does. It is like magic the first time you make it happen, but it is pretty easy.

[willerby]

Ok here goes nothing (!)

The search button...

Code: [Select]

<form name="input" action="html_form_submit.asp" method="get">
Chassis Number or Vehicle Registration:
<input type="text" name="vehicle_search" />
<input type="submit" value="Search" />
</form>
Not sure what you mean by table definition but assuming you are after the output in a table format, in html, I think its something like this (?):

Code: [Select]

<table>
<tr>
  <td>Chassis No:</td>
  <td>Registration Number:</td>
  <td>Details:</td>
</tr>
<tr>
  <td>vehicle_chassis_no</td>
  <td>vehicle_reg_no</td>
  <td>vehicle_details</td>
</tr>
</table>

The second row is the output from the database search of a table 'vehicles' which has a very limted number of fields, all text. Do you need definitions for each of these fields (is that what you mean by table definition? if so what exactly do I need to provide?)

Thanks AngelinaBelle - sorry if I appear dumb, hence why I asked for help!

W

[AngelinaBelle]

Not sure what you mean by table definition

What I mean is -- the definition of the SQL table, which will be needed to write some SQL code.

To output your html as php, you just wrap it in the php command to write it to the page.  And you have to tell your form to come back to itself when you submit it.  As long as you aren't worried about the search terms showing up in the URL, this will be great.
 
Lets suppose your page is going called pageVehicleSearch. SMF supplies the global variable $scripturl, and you use it to tell the form where to send your page.
 
In the file SSI.php, in your smf directory, you can see some examples of how SQL queries are put together and run on the database.
 

Code: [Select]

  global $scripturl;
$pageid='pageVehicleSearch';
   echo '<h2>Vehicle Search</h2>', "\n"; 
// This 'if' statement checks if you've submitted the form. This is what php was born to do.
if ( !isset($_GET['vehicle_search'] ) )
{
   echo '
   <form name="input" action="',  $scripturl , '" method="get">
   <input type="hidden" name="page" value="', $pageid, '" />
   Chassis Number or Vehicle Registration:
   <input type="text" name="vehicle_search" />
   <input type="submit" value="Search" />
   </form>
    ';
}
else
{
           echo 'You have submitted the following search:
           vehicle_search: ', $_GET['vehicle_search'],  '
           ';
}


[willerby]

Sorry - I need more info on where / how to obtain a table definition? PHPMyAdmin? Structure?

I don't follow all of this and am wondering whether we are using simpleportal pages with this code? If so the page url will be www.xxxxxx.com/forum/index.php?page=vehiclesearch not ...forum/pagevehiclesearch. 

In other words I'm intending to use a simpleportal page (not a block) for both the search input and the results (below the search input box)...

Does this make sense?

[[SiNaN]]

Something like this should work in a SimplePortal page:

Code: [Select]

global $smcFunc;

echo '
<form action="" method="post">
Chassis Number or Vehicle Registration:
<input type="text" name="vsearch" />
<input type="submit" name="submit" value="Search" />
</form>';

if (!empty($_POST['submit']) && !empty($_POST['vsearch']))
{
$request = $smcFunc['db_query']('', '
SELECT *
FROM vehicles
WHERE vehicle_chassis_no = {int:search}
OR vehicle_reg_no = {int:search}',
array(
'search' => (int) $_POST['vsearch'],
)
);
$items = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$items[] = $row;
$smcFunc['db_free_result']($request);

if (empty($items))
{
echo '
There are no items!';
}
else
{
$fields = array_keys($items[0]);

echo '
<table>
<tr>';

foreach ($fields as $field)
echo '
<td>', $field, '</td>';

echo '
</tr>';

foreach ($items as $item)
{
echo '
<tr>';

foreach ($item as $detail)
echo '
<td>', $detail, '</td>';

echo '
</tr>';
}

echo '
</table>';
}
}
I guess you can figure out the rest from this point.

[willerby]

Thank you, thank you SiNaN...

I shall give it a try tomorrow - insistent wife says I have to take her out tonight - and a donation will be winging its way to simpleportal. 

 

[AngelinaBelle]

I think [SiNan]'s example will work great for you. I think the method he used for handling the form is probably better than the one I used, for forms on an SMF forum.
 
Please let us know how it turns out!
 
Thanks
 

[willerby]

Something not quite right but probably me rather than your code...

I have amended as follows to tie in with the database table name lrsoc_vehicles' and the fields within that table that I wish it to serach through - chassis_no, reg_no

Code: [Select]

global $smcFunc;

echo '
<form action="" method="post">
   Chassis Number or Vehicle Registration:
   <input type="text" name="vsearch" />
   <input type="submit" name="submit" value="Search" />
</form>';

if (!empty($_POST['submit']) && !empty($_POST['vsearch']))
{
   $request = $smcFunc['db_query']('', '
      SELECT *
      FROM lrsoc_vehicles
      WHERE chassis_no = {int:search}
         OR reg_no = {int:search}',
      array(
         'search' => (int) $_POST['vsearch'],
      )
   );
   $items = array();
   while ($row = $smcFunc['db_fetch_assoc']($request))
      $items[] = $row;
   $smcFunc['db_free_result']($request);

   if (empty($items))
   {
      echo '
There are no items!';
   }
   else
   {
      $fields = array_keys($items[0]);

      echo '
<table>
   <tr>';

      foreach ($fields as $field)
         echo '
      <td>', $field, '</td>';

      echo '
   </tr>';

      foreach ($items as $item)
      {
         echo '
   <tr>';

         foreach ($item as $detail)
            echo '
      <td>', $detail, '</td>';

         echo '
   </tr>';
      }

      echo '
</table>';
   }
}
I have only put one entry in the database but all search regardless of what I key in the serach box result in that entry coming up... ?

Any suggestions? I can Pm the page url if that helps

W

[willerby]

Looks like it may be the {int:search} in the WHERE statement?

Works fine for chassis_no which is normally an integer but reg_no is text and includes alpha characters. Having input further data, all records show when searching on reg_no...

W

[[SiNaN]]

I thought they both were integers. Then try this:

Code: [Select]

global $smcFunc;

echo '
<form action="" method="post">
Chassis Number or Vehicle Registration:
<input type="text" name="vsearch" />
<input type="submit" name="submit" value="Search" />
</form>';

if (!empty($_POST['submit']) && !empty($_POST['vsearch']))
{
$request = $smcFunc['db_query']('', '
SELECT *
FROM lrsoc_vehicles
WHERE chassis_no = {string:search}
OR reg_no = {string:search}',
array(
'search' => $smcFunc['htmlspecialchars']($_POST['vsearch'], ENT_QUOTES),
)
);
$items = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$items[] = $row;
$smcFunc['db_free_result']($request);

if (empty($items))
{
echo '
There are no items!';
}
else
{
$fields = array_keys($items[0]);

echo '
<table>
<tr>';

foreach ($fields as $field)
echo '
<td>', $field, '</td>';

echo '
</tr>';

foreach ($items as $item)
{
echo '
<tr>';

foreach ($item as $detail)
echo '
<td>', $detail, '</td>';

echo '
</tr>';
}

echo '
</table>';
}
}

[willerby]

Fantastic SiNaN. Donation made - you deserve it.

One more question though, how do you change the where statement to search for all database entries that include the string eg. if the search string was "JAS" I'd like it to return entries JAS766, JAS123, JAS555 etc. At the moment the search is only if the entire string matches the database entry...

Thanks

[willerby]

Still struggling with this as not sure how to format the information after the LIKE statement I believe I need to use.

Even with trial and error I can't get a LIKE and wild card % characters to work with {string:search} - what am I doing wrong? 

[willerby]

Any help anyone? I'm ready to launch my nifty little vehicle search but just need SiNaN's code amended to make the search look for a partial string - currently it only finds records where the database field is equal to the search query.

My normal approach of trial and error is failing miserably... sorry

[AngelinaBelle]

I think this is standard syntax for a parameterized LIKE search.  I did have a search through some SMF code for "LIKE" to make sure.  I have not tested this code, but I think it is the correct syntax:

Code: [Select]

  $request = $smcFunc['db_query']('', '
      SELECT *
      FROM lrsoc_vehicles
      WHERE chassis_no LIKE {string:search}
            OR reg_no LIKE {string:search}',
      array(
            'search' => '%' . $smcFunc['htmlspecialchars']($_POST['vsearch'], ENT_QUOTES). '%',
      )
);



[willerby]

Thanks will try it out. I was trying to get the % either side of the string search and not getting anywhere...

[willerby]

Hmmm...

Same result as my own attempts, no results returned at all whether exact match or partial match.
Syntax must be altering content of search string for exact matches not to be returned?

?

[willerby]

My fault! Forgot to change = to LIKE

All working brilliantly. Thanks SiNAN, Thanks AngelinaBelle

Awesome support. Sorry for being a pain but I learned a lot despite not quite getting there myself.

[AngelinaBelle]

You are welcome, Willerby.
I'm learning a lot here, too, so I'm happy to pass the favor on.

[[SiNaN]]

Sorry I couldn't get to this earlier. I have been ill for the past week. I'm glad that you were able to solve it. Though I would suggest this change to escape wildcard chars:

Code: (Find) [Select]

'search' => '%' . $smcFunc['htmlspecialchars']($_POST['vsearch'], ENT_QUOTES). '%',

Code: (Replace) [Select]

'search' => '%' . strtr($smcFunc['htmlspecialchars']($_POST['vsearch'], ENT_QUOTES), array('_' => '\\_', '%' => '\\%', '*' => '%')) . '%',

[willerby]

Why SiNaN? Can you explain how it is beneficial...

Thanks - and hope you are feeling better

[AngelinaBelle]

[SiNaN] has a good point. The code he added is to protect against characters that can cause a problem to the SQL query.

[willerby]

Thanks - makes sense...

[willerby]

Members are reporting that in Internet Explorer the Search button has to be clicked, entering a search string and hitting enter just refreshes the page? I've checked this and it works fine in Firefox but not IE... any ideas?

Thanks

[[SiNaN]]

Luckily, we have such a function in SP, added for shoutbox. You can use that as well.

Code: (Find) [Select]

global $smcFunc;

Code: (Replace) [Select]

global $smcFunc, $context;

Code: (Find) [Select]

<input type="text" name="vsearch" />

Code: (Replace) [Select]

<input type="text" name="vsearch"', $context['browser']['is_ie'] ? ' onkeypress="if (sp_catch_enter(event)) { this.form.submit(); return false; }"' : '', ' />

[willerby]

Thanks SiNaN but still same result in IE... 

[[SiNaN]]

Sorry. You'll need this change as well:

Code: (Find) [Select]

   <input type="submit" name="submit" value="Search" />
</form>';

if (!empty($_POST['submit']) && !empty($_POST['vsearch']))

Code: (Replace) [Select]

   <input type="submit" name="send" value="Search" />
</form>';

if (!empty($_POST['vsearch']))

[willerby]

Thanks SiNaN - that fixed it. All working but since adding the original code this page has been throwing up an undefined error in the error log as follows:

http://www.xxx.com/forum/index.php?page=vehicle_search
8: Undefined variable: context
File: /home2/xxx/public_html/forum/Sources/Subs-Portal.php(1333) : eval()'d code
Line: 6

Line 6 is

Code: [Select]

<input type="text" name="vsearch"', $context['browser']['is_ie'] ? ' onkeypress="if (sp_catch_enter(event)) { this.form.submit(); return false; }"' : '', ' />

[AngelinaBelle]

Code: (Find) [Select]

global $smcFunc;

Code: (Replace) [Select]

global $smcFunc, $context;

That should take care of the missing $context

[willerby]

It's already in the page PHP code...

Hmmmm...

[AngelinaBelle]

That's hard to understand.  Can you attach the file?

[willerby]

This is the code cut and pasted from the SP page

Code: [Select]

global $smcFunc; $context;

echo '
<form action="" method="post">
   Enter Chassis Number or Vehicle Registration:
   <input type="text" name="vsearch"', $context['browser']['is_ie'] ? ' onkeypress="if (sp_catch_enter(event)) { this.form.submit(); return false; }"' : '', ' />
<input type="submit" name="send" value="Search" />
</form>';

if (!empty($_POST['vsearch']))
{
   $request = $smcFunc['db_query']('', '
      SELECT *
      FROM lrsoc_vehicles
      WHERE chassis_no LIKE {string:search}
         OR reg_no LIKE {string:search} ORDER BY chassis_no, year',
      array(
         'search' => '%' . strtr($smcFunc['htmlspecialchars']($_POST['vsearch'], ENT_QUOTES), array('_' => '\\_', '%' => '\\%', '*' => '%')) . '%',     )
   );
   $items = array();
   while ($row = $smcFunc['db_fetch_assoc']($request))
      $items[] = $row;
   $smcFunc['db_free_result']($request);

   if (empty($items))
   {
      echo '
<br /><strong>Sorry, there are no matches for your search!</strong>';
   }
   else
   {
      $fields = array_keys($items[0]);

      echo '
      foreach ($items as $item)
      {
         echo '
        <table><tr>';

         foreach ($item as $detail)
            echo '
      <td>', $detail, '</td>';

         echo '
   </tr>';
      }

      echo '
</table>';
   }
}

[AngelinaBelle]

How about this?

Code: [Select]

global $smcFunc; $context;
echo '<form action="" method="post">   
Enter Chassis Number or Vehicle Registration:   
<input type="text" name="vsearch"', $context['browser']['is_ie'] ? ' onkeypress="if (sp_catch_enter(event)) { this.form.submit();return false; }"' : '', ' />
<input type="submit" name="send" value="Search" /></form>';
if (!empty($_POST['vsearch']))
{
$request = $smcFunc['db_query']('', '
SELECT *
FROM lrsoc_vehicles
WHERE chassis_no LIKE {string:search}
OR reg_no LIKE {string:search}
ORDER BY chassis_no, year',
array(
'search' => '%' .
strtr($smcFunc['htmlspecialchars']($_POST['vsearch'], ENT_QUOTES), array('_' => '\\_', '%' => '\\%', '*' => '%')) .
'%',
)
);

$items = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$items[] = $row;

$smcFunc['db_free_result']($request);
if (empty($items))
{
echo '<br /><strong>Sorry, there are no matches for your search!</strong>';   
}
else
{
$fields = array_keys($items[0]);
echo '
<table>
<tr>';



foreach ($fields as $field)
echo '
<td>', $field, '</td>';

echo '
</tr>';

foreach ($items as $item)
{
echo '
        <tr>';
foreach ($item as $detail)
echo '
<td>', $detail, '</td>';
echo '
</tr>';
}

echo '
</table>';   


}
}


[[SiNaN]]

Willerby, the first line you have is this:

Code: [Select]

global $smcFunc; $context;
The semi-colon at the center should a comma.

Code: [Select]

global $smcFunc, $context;

[AngelinaBelle]

I think I'm going blind!
 
 
 

[willerby]

Thanks Angelinabelle and SiNaN. I can't believe my own stupidity.

When it says find and replace why do I think I know better and can see and edit the differences manually? Thanks for your help.

 

[AngelinaBelle]

One of these days, I'm going to have to go to a larger font size.