Paul Witschger Sat Sep 17 04:18:00 -0400 2011

Subject: [Solved] Search form covering two models

  • PHP 5.3.6
  • MySQL 5.1.52
  • PHP-AR Stable (I believe. I used a CodeIgniter Spark to install it).

I've created a search form, so that users can type in their search criteria into specified fields, but the problem is, some of the fields are in one model, and some are in a related model.

What is the best way (effeciency and speed) to handle this? The only way I can think to handle it, is to get the results of Model::all($criteria_for_model_array), and then do a loop through Model::all() to check the criteria of the related model

$results = Model::all($criteria_for_model_array);

foreach(Model::all() as $model){
  if(in_array($criteria_for_related_model, $model->related))
  {
    $results[] = $model;
  }
}

(That is untested code, but the logic should be understandable)

Is there a better way of handling it?

Thanks.


Paul Witschger Sat Sep 17 18:15:58 -0400 2011

Update:

Table Schemas:

CREATE TABLE `tutors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `f_name` varchar(255) NOT NULL,
  `l_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `bio` text NOT NULL,
  `url_identifier` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)


CREATE TABLE `tutor_prefs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tutor_id` int(11) NOT NULL,
  `allow_groups` tinyint(1) NOT NULL DEFAULT '0',
  `individual_price` decimal(5,2) NOT NULL,
  `group_per_price` decimal(5,2) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `grade_level_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)


CREATE TABLE `tutor_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tutor_pref_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `rating` decimal(3,2) NOT NULL,
  `review` text NOT NULL,
  PRIMARY KEY (`id`)
)

Search Fields:

  • Subject
  • Price
  • City
  • State
  • Rating

Current Code (in app/models/Tutor.php):

 1 public static function get_search()
 2     {
 3         $CI =& get_instance();
 4 
 5         $subject = $CI->input->post('subject');
 6         $price = $CI->input->post('price');
 7         $city = $CI->input->post('city');
 8         $state = $CI->input->post('state');
 9         $stars = $CI->input->post('stars');
10 
11         $search_criteria = array();
12         $conditions = array();
13         $conditions[] = ''; //first element for $search_criteria
14         $params = array();
15 
16         $join = array();
17 
18         //array('conditions' => array('genre = ? AND price < ?', 'Romance', 15.00))
19 
20         if($subject)
21         {
22             $search_criteria[] = 'tutor_prefs.subject like ?';
23             $conditions[] = '%'.$subject.'%';
24             $join = 'prefs';
25         }
26 
27         if($price)
28         {
29             $search_criteria[] = '(tutor_prefs.individual_price = ? OR tutor_prefs.group_per_price = ?)';
30             $conditions[] = $price;
31             $conditions[] = $price;
32             $join = 'prefs';
33         }
34 
35         if($city)
36         {
37             $search_criteria['city'] = $city;
38         }
39 
40         if($state)
41         {
42             $search_criteria['state'] = $state;
43         }
44 
45         if($stars)
46         {
47             $search_criteria[] = 'tutor_reviews.rating = ?';
48             $conditions[] = $stars;
49             $join = 'reviews';
50         }
51 
52         if(count($join) > 0)
53         {
54             $params['joins'] = $join;
55         }
56 
57         if(count($search_criteria) > 0)
58         {
59             $conditions[0] = implode(' AND ', $search_criteria);
60             $params['conditions'] = $conditions;
61         }
62 
63         return Tutor::all($params);
64     }

This returns an error of:
Uncaught exception 'ActiveRecord\DatabaseException' with message '42S02, 1051, Unknown table 'tutors''

Any and all help is greatly appreciated.

Thanks.

Matthew Machuga Mon Sep 19 17:16:20 -0400 2011

Paul,

Thanks for using my Spark :) Anyway, the simplest way to do what you're attempting would likely be to use joins (http://phpactiverecord.com/projects/main/wiki/Finders#joins) and pull the information that way. You need to know a bit about SQL to use this most efficiently, but it'll certainly be more affective than several DB calls or fighting with the models.

Let me know if this is helpful or not

Paul Witschger Mon Sep 19 17:58:28 -0400 2011

Matthew,

Thanks for the reply.

If you take a look at my second posting, you'll see that I am using joins. I'm using a combination of joins and conditions (and now select).

I figured out what my issue was though. When setting the $join variable, I had forgotten to put the [], so I was setting it to a string, and not appending another array element.

Once again, thanks for the reply.

(1-3/3)