Forums

Hello,

I follow your code and I made the search work.

in where sql:

<?php
if(JRequest::getVar('division')){
?>
WHERE select_2 = 
"<?php echo JRequest::getVar('division') ; ?>"
<?php
}
?>

in header:

<input type="text" name="division">

This works great when I type in my search string in the search box and then press enter. Now, I want to make the search as a dropdown list. How do I do this? Do I need to add a "submit" button? and how?

Division: <select id="division" size="1" name="division">
  <option value="DivisionA">DivisionA</option>
  <option value="DivisionB">DivisionB</option>
  <option value="DivisionC">DivisionC</option>
  <option value="DivisionD">DivisionD</option>
  <option value="DivisionE">DivisionE</option>
  <option value="DivisionF">DivisionF</option>
</select>

Thank you.

Hi batmon, yes, just add your dropdown code with a submit button code!

Max

Cool~ that works! I am just amazed that CC always work like magic... 😀

Hello,

Couple requests:

1) It works after adding a submit button. However after page refresh, the pulldown goes back to "DivisionA". It is a bit confusing for my users. Can it stay as what was selected? with the "selected" keyword?
2) Is it possible to do this without the submit button? It would be nice if user can choose the dropdown and then the search kicks in without pressing the submit button.

Thank you.

Hi,

#1- you will need some PHP here, do it like the first option in the code below! :

    Division: <select id="division" size="1" name="division">
      <option <?php if(JRequest::getVar('division') == "DivisionA"){echo "selected";} ?> value="DivisionA">DivisionA</option>
      <option value="DivisionB">DivisionB</option>
      <option value="DivisionC">DivisionC</option>
      <option value="DivisionD">DivisionD</option>
      <option value="DivisionE">DivisionE</option>
      <option value="DivisionF">DivisionF</option>
    </select>

#2- you need a javascript code to submit form on change!

Regards
Max

Cool~ that works. I will do some research on Javascript then. Thank you.

Hello, excuse my english since I am Cuban. Maybe you could help me...

I have been working with CC and I have achieved to see the data I have in a database in the front end. Now, i need to do a small search engine for that.

Sorry about my english😶

Bye, Ernest

Hola Ernest,

why don't you use the code at the first post here ? this is exactly what you need!

Regards,
Max

Max Thank's a LOT!!!!!!!!! my "basic" search engine is working now....Really, thank's for your help.

Regards, Ernest🙂

So, using this code, I've have a search function that works for searching one column and returning the row associated with it. How can I make a search function that returns anything associated with more than one column? Let's say I have a column for 'firstname' and one for 'lastname', but I want the search function to search for 'name' and return anything related to what's typed in whether it's a first or last name.

Then, the search works, but if there are more results than what the pagination limit is set to, the pagination bar shows up with the correct number of pages, but once you click to visit one of those pages, it just reverts back to displaying the results for the entire database.

Here's the code I have entered so far:
WHERE:

<?php
if(JRequest::getVar('lastname')){
?>
WHERE lastname =
"<?php echo JRequest::getVar('lastname') ; ?>"
<?php
}
?>

Header:

<div style="float:left">The SCS Awards Recipient Database.</div><div style="float:right">
Search for a <b>last name</b> here: <input type="text" name="lastname">
<input type="submit" value="Search" name="undefined" style='width:80px; color:#cccccc; background-color:#222222; cursor:pointer;' /></div>
<br />

And here's the link to the page display:
http://starcrestawards.com/component/chronoconnectivity/?connectionname=SCS_awards

Hi benblee,

Here's some code that might help with the multiple search:

<?php
$search_array = array('firstname', 'lastname', 'city'); // for example
$where = array();
foreach ( $search_array as $search ) {
    if (JRequest::getVar($search, '' , 'post')) {
        $where[] = " $search = ".JRequest::getVar($search, '' , 'post')." ";
    }
}
if ( !empty($where) ) {
    echo "WHERE ".explode(' AND ', $where);
}
?>

NB Not tested and probably needs debugging! Be particulalry careful to leave enough spaces in the query statement, it's easy to let the word run into each other.

This will accept any number of entries in $search_array and add an extra AND for each of them.

Bob

I gave this a try but couldn't get it to work. I'm not sure that I'm calling it right in the header though. Do I need to refer to the database table "jos_chronoforms_form1" instead of referring to the column to be searched "lastname" like mentioned above? If so, how is the syntax different here?

Hi benblee,

you need to put Bob's code in the "SQL/WHERE" box and choose your table in the "Tables" dropdown list, did you do this ?

Cheers,
Max

this is what I had:

TableName(s): jos_chronoforms_scs

WHERE SQL:

    <?php
    $search_array = array('firstname', 'lastname', 'city'); // for example
    $where = array();
    foreach ( $search_array as $search ) {
        if (JRequest::getVar($search, '' , 'post')) {
            $where[] = " $search = ".JRequest::getVar($search, '' , 'post')." ";
        }
    }
    if ( !empty($where) ) {
        echo "WHERE ".explode(' AND ', $where);
    }
    ?>

Header:

<div style="float:left">The SCS Awards Recipient Database.</div><div style="float:right">
Search for a <b>last name</b> here: <input type="text" name="lastname">
<input type="submit" value="Search" name="undefined" style='width:80px; color:#cccccc; background-color:#222222; cursor:pointer;' /></div>

there's more to the header, this is just where I was putting the search box in.

I have the site set back to the simple last name only search because it's a live site right now.

mmm, its not working right now using this code ?

Nope. I've got it up and posted now and will leave it for a while so you can see it.
It's at this url:
http://starcrestawards.com/component/chronoconnectivity/?connectionname=SCS_awards

I get this error:

Unknown column 'Array' in 'where clause' SQL=SELECT count(*) FROM jos_chronoforms_scs WHERE Array

and this:

Warning: Invalid argument supplied for foreach() in /home/starcres/public_html/components/com_chronoconnectivity/chronoconnectivity.html.php on line 172

Hi benblee,

Try replacing 'explode' with 'implode' - I can never remember which of these is which :-(

Bob

I get this error with implode:

Unknown column 'works' in 'where clause' SQL=SELECT count(*) FROM jos_chronoforms_scs WHERE lastname = works

Hi benblee,

A couple of extra quotes needed to be added. But I had some problems getting them to stick, so added an extra temporary variable. The final code reads

<?php
$search_array = array('firstname', 'lastname', 'state'); 
$where = array();
foreach ( $search_array as $search ) {
  $value = JRequest::getVar($search, '' , 'post');
  if ( $value ) {
    $where[] = " $search LIKE '$value%' ";
  }
}
if ( !empty($where) ) {
  echo " WHERE ".implode(' AND ', $where);
}?>

The LIKE '$value%' syntax allows partial searches - so that 'sm' finds all the 'smith's for example.

There are still some problems to solve - the search values aren't retained so, for example, you can't go to the second page if there are a lot of results :-(

Bob

This is great and very much appreciated! Thank you for your time on this.

I've got my search form up and running, but I'm having pagination issues. The first page works fine, but going to other pages in the pagination list resets the search so it displays all results. What do I need to do to get make the search results stay the same past the first page? (Changing the number of results per page also resets the search.)

Hi, you will need to recover the limit in the WHERE statement code somehow, I didnt test this myself before!

Regards
Max

Okay. That's a little beyond me, but I've just disabled pagination for now.

I have the search working (beautifully by the way), but I am wanting to add a bit more functionality. I was wanting to add a radio button or checklist that will do an ORDER BY. I have discovered that if you leave Order Fields blank then you can add ORDER BY to the WHERE SQL section and it will work, now I just want the user to be able to pass through the value. All of my attempts thus far have fallen flat. Can anyone help me add this feature? Thanks.

Hi hilltopper06,

If I understand correctly you want to add a user variable to the ORDER BY

You should be able to do this something like this:

<?php
$where = "WHERE ".some_value_or_other;
$order = JRequest::getVar('order', 'id', 'post');
$where .= "ORDER BY ".$order;
echo $where;
?>

Where 'order' is the field name of the order field and returns a valid column name.

Bob

I'm following the code in this post trying to get the same results, the search is working fine, but selecting any item in the list of items in the database, returns the data for the first item instead, as if the ID of the selected item is not being assigned. I'm imagining that this is an easy straight forward fix, but I just have no clue what to edit. Would you mind please taking a look?

Thanks for your time,
Jonathon

WHERE

<?php
$search_array = array('document', 'publication', 'author', 'year', 'description', 'notes', 'check2');
$where = array();
foreach ( $search_array as $search ) {
  $value = JRequest::getVar($search, '' , 'post');
  if ( $value ) {
    $where[] = " $search LIKE '%$value%' ";
  }
}
if ( !empty($where) ) {
  echo " WHERE ".implode(' AND ', $where);
}?>

HEADER

<div style="float:left"><b><a href="index.php?option=com_chronocontact&chronoformname=KHCDocumentsAddFinal">Add New Document</a> | Search KHC Documents Database.</b></div><div style="float:left">
<table style="text-align: left; width: 650px;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold;"><label for="document">Document</label></td>
      <td style="font-weight: bold;"><label for="author">Author</label></td>
      <td style="font-weight: bold;"><label for="publication">Publication</label></td>
      <td style="font-weight: bold;"><label for="year">Year</label></td>
    </tr>
    <tr>
      <td><input class="cf_inputbox" title="" type="text" id="text_1" name="document"></td>
      <td><input class="cf_inputbox" title="" type="text" id="text_3" name="author"></td>
      <td><input class="cf_inputbox" title="" type="text" id="text_2" name="publication"></td>
      <td><div class="form_element cf_dropdown">
<select class="cf_inputbox validate-selection" id="select" size="1" title="" name="year">
<option value="">Select Year</option>
<option value="2000">2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
<option value="2009">2009</option>
<option value="2010">2010</option>
</select>   
</div></td>
    </tr>
    <tr>
      <td style="font-weight: bold;"><label for="description">Description</label></td>
      <td style="font-weight: bold;"><label for="notes">Notes</label></td>
      <td style="font-weight: bold;"><label for="check2">Categories</label></td>
    </tr>
    <tr>
      <td><input class="cf_inputbox" type="text" id="text_9" name="description"></td>
      <td><input class="cf_inputbox" type="text" id="text_10" name="notes"></td>
      <td><div class="form_element cf_dropdown">
<select class="cf_inputbox validate-selection" id="select" size="1" title="" name="check2">
<option value="">Choose Option</option>
<option value="cardiology">Cardiology</option>
<option value="ob gyn">OB-GYN</option>
<option value="best practice">Best Practice</option>
<option value="surgery">Surgery</option>
<option value="neuro">Neuro</option>
<option value="Womens Study">Women's Study</option>
<option value="pharmacy">Pharmacy</option>
<option value="psychiatric">Psychiatric</option>
<option value="icu">ICU</option>
<option value="ambulatory">Ambulatory</option>
<option value="nicu">NICU</option>
<option value="radiology">Radiology</option>
<option value="womens">Women's</option>
<option value="presentation">Presentation</option>
<option value="med surg unit">Med Surg Unit</option>
<option value="anesthesiology">Anesthesiology</option>
<option value="oncology">Oncology</option>
<option value="inpatients">Inpatients</option>
<option value="leed">Leed</option>
<option value="lab">Lab</option>
<option value="endo">Endo</option>
<option value="rehab">Rehab</option>
<option value="pediatric">Pediatric</option>
<option value="projects">Projects</option>
<option value="trauma">Trauma</option>
<option value="urgent care">Urgent Care</option>
<option value="lean">Lean</option>
<option value="dietary">Dietary</option>
<option value="ortho">Ortho</option>
<option value="mat mang">Mat Mang</option>
<option value="technology">Technology</option>
<option value="ebd">EBD</option>
</select>   
</div></td>
    </tr>
  </tbody>
</table>

<input type="submit" value="Search / Reset" name="undefined" style='width:100px; height:22px; color:#f2f2f2; border:0; background-color:#333333; cursor:pointer;' /><br>
</div>
<br /><br />


Please Note: Documents listed alphabetically.
<br />
<table style="text-align: left; width: 600px;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
      <td style="font-weight: bold; width: 5%;">#</td>
      <td style="font-weight: bold; width: 30%;">Document</td>
      <td style="font-weight: bold; width: 25%;">Author</td>
      <td style="font-weight: bold; width: 25%;">Publication</td>
      <td style="font-weight: bold; width: 15%;">Year</td>
    </tr>
  </tbody>
</table>
<div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>

BODY

    <p class="blocknumber">
    <span class="bignumber">
<table style="text-align: left; width: 650px;" border="0" cellpadding="2" cellspacing="0">
  <tbody>
    <tr>
	  <td style="font-weight: bold; width: 32%;"><?php echo sprintf('%02s', (1 + $counter)); ?></span><a class="smoothbox" name="<b>KHC Document Review</b> - {document}" href="#TB_inline?height=400&width=500&inlineId=cccontent_{id}_box" id="cccontent_{id}"   >    {document}</a></td>
      <td style="width: 23%;">{author}</td>
      <td style="width: 23%;">{publication}</td>
      <td style="width: 7%;">{year}</td>
      <td style="width: 20%;">{edit_record}{delete_record}</td>
    </tr>
  </tbody>
</table></p>
    <div id="cccontent_{id}_box" style="display: none;">
<br><br>

<b>Document Name</b>: {document}<br>
<b>Author</b>: {author}<br>
<b>Publisher</b>: {publication}<br>
<b>Year</b>: {year}<br><br>
<b>File</b>: <a href="components/com_chronocontact/uploads/KHCDocuments/{fileupload}">Download File</a><br><br>
<b>URL</b>: <a href="http://{website}" target="_blank">Website Link</a><br><br>
<b>Researched?</b> {radio3}<br>
<b>Peer Reviewed?</b> {radio4}<br><br>
<b>Description</b>: {description}<br><br>
<b>Notes</b>: {notes}<br><br>
<b>Categories</b>: {check2}
</div><?php $i++ ?>
<hr color="#f2f2f2">

MAIN LIST SHOWING ITEMS IN DB
[attachment=0]DocsList1.png[/attachment]

RESULTS AFTER SELECTING AN ITEM - TITLE CORRECT / ALL OTHER INFO FROM FIRST ITEM IN MAIN LIST
[attachment=1]DocsList2.png[/attachment]

I am by no means an expert at this stuff and what you see that I've typed up in these tutorials was with a lot of guidance from the guys here.

One thing that I would ask is if you created the database table with ChronoForms? In this tutorial:
http://www.chronoengine.com/forums.html?cont=posts&f=5&t=12324#p21887
I created the table with ChronoForms specifically because the extra ordering columns are place in there. When I created the table with strictly the columns I used from my information, the ordering did not work out just right.

benblee,
Thanks for the quick reply!

I actually did follow that tutorial to start off and i did use ChronoForms to create the database table. I just looked through the tutorial again though and I don't see anything out of sorts. I could be missing something, but I just don't gt it?

:? Anything else I should be checking???

Hi demo38,

I don't see anything wrong with the code - but I'm not very familiar with ChronoConnectivity. Is the page on-line yet?

Bob

I wish it were, it's a corporate intranet site behind a VPN...

I'm all ears for the next release, come on Max, we're all counting on you! (see, you set the bar so high that we need you now!)

Thanks guys,
J

Regarding searching multiple fields for the same value - in my case I have two parents in the same record who might have different last names - would like to just have one search box for last name. Tried this approach above and couldn't get it to work -anyone have any luck?

Hi thewib,

Try

WHERE (`her_lastname` LIKE '$lastname) OR (`his_lastname` LIKE '$lastname')

Bob

Bob,

LOL - I was way overthinking it! Still getting used to this.

However, I'm still getting the error below - any ideas? Thanks!

Warning: Invalid argument supplied for foreach() in /home/.asheema/alg/amazinglifegames.org/site/components/com_chronoconnectivity/chronoconnectivity.html.php on line 172

My where clause is:

WHERE (`Parent1Last` LIKE '$lastname') OR (`Parent2Last` LIKE '$lastname');

And my header code is this:

<body>
<p><b>Register in advance for the Evening Auction</b></P>
<p>Avoid the crazy lines at check-in and just whizz past to visit the friendly bartender or start sizing up the auction items!</p>

<p><b><span class="editlabel">{new_record}</span></b></p>

<div style="float:right">
Search for a <b>last name</b>: <input type="text" name="lastname">
<input type="submit" value="Search" name="undefined" style='width:80px; color:#cccccc; background-color:#222222; cursor:pointer;' /></div>

<table id="box-table">
   <thead>
    <tr>
      <th scope="col" style="width: 20%;">Register</th>
      <th scope="col" style="width: 20%;">First Name</th>
      <th scope="col" style="width: 20%;">Last Name</th>
      <th scope="col" style="width: 20%;">First Name</th>
      <th scope="col" style="width: 20%;">Last Name</th>
    </tr>
  </tbody>
</table>
</body>

Hi thewib,

I think I was writing a bit too much in shorthand - the code needs to be expanded a bit. Please try this:

<?php
$lastname = JRequest::getString('lastname', '', 'post');
echo "WHERE (`Parent1Last` LIKE '$lastname') OR (`Parent2Last` LIKE '$lastname') ";
?>

Bob

Bob,

Sorry - still getting used to this - and when I saw your previous answer my thought was something like "I didn't know we could write it that plainly". Trying to do this in too much of a hurry. It's working perfectly. Thanks!

Elizabeth

I got my search and sort working. My code may not be of too much help to anyone else, as I was only wanting to sort by 2 methods (name or day), but I suppose someone could adapt it to suite their own purposes.

WHERE SQL:

WHERE School = 'bremen'
<?php
$search_array = array('FullName', 'DayOut', 'AbsenceType', 'Code', 'SubName', 'Type');
$sort = "Sort";
$value = JRequest::getVar($sort);
$sort = $value;
$where = array();
foreach ( $search_array as $search ) {
  $value = JRequest::getVar($search, '' , 'post');
  if ( $value ) {
    $where[] = " $search LIKE '$value%' ";
  }
}
if ( !empty($where) ) {
  echo " AND ".implode(' AND ', $where);
}
if ( $sort == "2" )
{
echo " ORDER BY School, DayOut, FullName, AbsenceType, Code, HalfDay, SubName ";
}
else
{
echo " ORDER BY School, FullName, DayOut, AbsenceType, Code, HalfDay, SubName ";
}
?>

HEADER SEARCH, SORT, FILTER:

<b>Search for:</b> <br><b>Full Name</b>: <input type="text" name="FullName"></br>
<br><b>Day Out</b>: <input type="text" name="DayOut" value="<?php print(Date("Y-m")); ?>"></br>
<br><b>Absence Type</b>: <input type="text" name="AbsenceType"></br>
<br><b>Absence Code</b>: <input type="text" name="Code"></br>
<br><b>Sub Name</b>: <input type="text" name="SubName"></br>
<br><b>Filter By</b>:</br>
<br><input type="radio" name="Type" value="Class"> Classified</br>
<br><input type="radio" name="Type" value="Cert"> Certified</br>
<br><b>Sort By</b>:</br>
<br><input type="radio" name="Sort" value="1" checked> Name</br>
<br><input type="radio" name="Sort" value="2"> Day Out</br>
<br><input type="submit" value="Search" name="undefined"  cursor:pointer;' /></br>

I used <?php print(Date("Y-m")); ?> to automatically enter the current date and month in the Day Out search box. It doesn't automatically filter these results until the use presses search, but it works pretty well.

This does not rearrange the order of your table columns. Just the sorting of the contents of those columns.

If you do put any type of ORDER BY code in your WHERE SQL box, please remove anything you have in the Order Fields: box at the bottom or you will get an error. I will answer any questions that I can, but the majority of this was learned through these forums and with the almighty google.com.

EDIT: A couple of other notes. If you place some code outside of the php tags in WHERE SQL (like I have done with School = 'bremen') Then you need to make sure that AND is used instead of WHERE as seen below:

if ( !empty($where) ) {
echo " AND ".implode(' AND ', $where);
}

If you are not using any static variables, then use the original examples WHERE statement:

if ( !empty($where) ) {
echo " WHERE ".implode(' AND ', $where);
}

Also, using the ELSE function at the end allows a default SORT BY to be passed through, so that your results are filtered before Search is pressed. Otherwise they are filtered by order of entry, which is sloppy at best.