SQL like query

Archives Yii 1.0.x Discussions
1

Hi @ all !

How i can make a simple SQL query that does not SELECT * but SELECT names like in:


SELECT `name` FROM `names` WHERE `name` LIKE '%foobar%';

I surely should use findAllByAttributes() but is there an example ?

2

try this:


$criteria = new CDbCriteria;

$criteria->condition = 'name LIKE :keyword';

$criteria->params = array(':keyword'=>'%'.$keyword.'%');

// or on yii 1.1

// $criteria->addSearchCondition('name', $keyword);
3

You should use ‘LIKE’ instead of ‘=’. However, the latest versions of Yii provide an even better way:




$criteria = new CDbCriteria;

$criteria->addSearchCondition('name', $keyword);


SomeClass::model()->findAll($criteria);

http://www.yiiframework.com/doc/api/CDbCriteria#addSearchCondition-detail

4

It does not work as expected because i get a multidimensional array.

I would like to generate a non pair/value array like :


array('John','Peter','Patrick');

(It is for an autocomplete javascipt that works over Prototype.)

5

you can use addSearchCondition() in yii 1.1rc

6

addSearchCondition() is available since 1.0.10 and I think it will not solve my problem.

When I try to launch direct mysql_query() queries, i get errors like "ACCESS DENIED" for user "foobar" USING PASSWORD…"

I would like to retrieve some names from a field called names in my database using the operator LIKE ‘%’.$foobar.’%’ and put these usernames into a very basic array like :


array('John','Peter','Patrick');

So can you give me the easiest way to do this job ?

7

Are you trying to combine a LIKE and IN query? If so, I don’t think that is possible using built-in Yii functions. You will have to manually create your query.

See if this helps:

http://www.yiiframework.com/forum/index.php?/topic/6001-addsearchcondition-for-multiple-values/page__view__findpost__p__30940

8

Check your connection settings. Note that Yii uses PDO, so don’t assume you’re connected when using the non-pdo mysql_query() function.

http://www.yiiframework.com/doc/guide/database.dao

9

Is that means there is no any quick method to do this with the ActiveRecord class ?

10

I’m actually not really sure what you want. Do you want a list of activerecords matching any of the names in the array you specified, or do you want a list of names matching a single search term?

Both are doable with active records, though both will involve addSearchCondition().

11

Sander - did you see my post above? Do you know of any other way I could have done this?

12

I would like to get all the names that matches the search condition LIKE %foobar%

Then I ll have to give this array to CJSON::encode() for outputing at JSON format like in :


			echo '{'."\n";

			echo 'query:\''.$q."',\n";

			echo 'suggestions:'.CJSON::encode($myarray)."\n";

			echo '}';

But I have to put a simple array (non-associative)

Then the autocomplete JS function will take that JSON response and show the suggestions like @ the Yii Class reference search input :

http://www.yiiframework.com/doc/api/1.0.11/

13

You’ll probably have to do something like this:




$names = array();

$criteria = new CDbCriteria;

$criteria->addSearchCondition('searchColumn', 'foobar');

$results = SomeClass::model()->findAll($criteria);

foreach($results as $result) {

    $names[] = $result->name;

}

I actually started the issue that was referenced in your thread. I’m still using the solution I proposed there though, I don’t really understand why it was rejected. See [topic=5532]this thread[/topic] for my specific case. Hope that helps :)

14

Thanks Sander,

I knew this way, it works fine but I expected a most clean way to do this using a nice method from the Yii power :wink: that avoids the use of a foreach loop.

15

That’s why I suggested DAO, you can select a single column and have all results returned with queryAll() :) Active Records will always be objects, so some additional logic is always needed.