Simple Joomla / MySql question

[Christian McCabe]

I have been struggling trying to translate a PHP mysql query to Joomla. Not sure why it is not working but I am simply trying to search a table and return only the rows that match a string or $variable value. For some reason I can't get it working with any of the PHP syntax examples that I have found. 

Could someone please give me the syntax to search a database in Joomla. Here is what I have:

$subject = "design";

$db = JFactory::getDBO();

$query = $db->getQuery(true);

$query->select('course_html');

$query->from('#__ws_courses');

$query->where('course_subject LIKE '$subject' ' );

$db->setQuery($query);

$rows = $db->loadObjectList();

I either get no result or a php error when I try different syntax for the WHERE LIKE statement is... 

--

Kind Regards,

Christian McCabe

[Donald Gilbert]

change your query up like this:

$query

    ->select('course_html')

    ->from('#__ws_courses')

    ->where("course_subject LIKE '%{$subject}%' ");

$rows = $db->setQuery($query)->loadObjectList();

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send an email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Olivier Nolbert]

Hi,

Try this (not tested) :

$subject = "design";

$db = JFactory::getDBO();

$query = $db->getQuery(true);

$query->select('course_html');

$query->from('#__ws_courses');

$query->where('course_subject LIKE '.$db->quote($subject) );

$db->setQuery($query);

$rows = $db->loadObjectList();

2013/2/16 Christian McCabe <see...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send an email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Olivier Nolbert, Jiliko

14 passage Lecroisey 76600 Le Havre, France

(+33)6 48 09 46 25

SIREN : 517 564 365

www.jiliko.net - Newsletter subscription - Twitter - Google + - Facebook

[Donald Gilbert]

You need the % in there to do partial matching (the purpose of LIKE). Meaning, if the course_subject was "Web Design" then what i wrote would match that. However, if you are wanting exact match then you should drop the LIKE and replace it with =

[Christian McCabe]

@donald

Your suggestion was just what I needed. It worked like a charm! Thanks

Kind Regards,

Christian McCabe

[hoochicken]

Hi,
know it's already solved, but I got a question
with "setQuery()" one loads the query; with "query()" it is executed:

$db->setQuery($query); /*Sets the SQL statement string for later execution, see: http://docs.joomla.org/JDatabase::setQuery/11.1*/
$db->query(); /*Execute the SQL statement. see: http://docs.joomla.org/JDatabase::query/11.1*/
$rows = $db->loadObjectList();

Question: Did it work without the "query()"? If yes, somebody knows why?
Greetings
Mareike

[Christian McCabe]

Yes it worked with the following code:

$subject = $params->get('message');

$db = JFactory::getDBO();

$query = $db->getQuery(true);

$query->select('*')

->from('#__ws_courses')

    ->where("course_subject LIKE '%{$subject}%' ");

$db->setQuery($query);

$rows = $db->loadObjectList();

Not sure why, given the instructions you posted. Maybe someone else can let us know why. But I can confirm that this module with this code is working as expected and calling from the database.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send an email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[hoochicken]

Hi,
thanks for the reply.
So looked up in the source code (http://hg.joomla.org/joomla-platform/src/247ba8d88526/libraries/joomla/database/database.php#cl-699);
seems in loadObjectList() the query() method is executed automatically.

        // Execute the query and get the result set cursor.
        if (!($cursor = $this->query())) return null;
       
So when using loadObjectList() setting the Query is enough:-)
Greetings
Mareike

[Christian McCabe]

AH! Nice find. I am just learning and this is the first time I used the loadObjectList() - I saw it used in another component and started there.

Greetings
Mareike

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send an email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Herman Peeren]

Generally you use loadObjectList() to execute a select-query (and get a result-set back). The query()-method executes any query and is generally used for updates, inserts and deletes.