How to TRIM a SELECT using JFactory?

46 views
Skip to first unread message

Nicolas Gameiro

unread,
Mar 21, 2017, 4:59:27 PM3/21/17
to Joomla! General Development
Hi,
I'm trying to develop in joomla 3x, but I am getting some problems.
How can TRIM a SELECT using JFactory?

I tried this:

query->select($db->quoteName(array('id', TRIM($idFieldClinic), getIdFields(FIELD_EMAIL), $idDateCheckIn, getIdFields(FIELD_SENT)),array('id',TRIM('name'), "email", "datacheckin", "sent")));
$query->from($db->quoteName('#__visforms_2'));
$query->where($db->quoteName($idDateCheckIn) . ' IS NOT NULL AND' . $db->quoteName($idDateCheckIn) . ' <> ""');
$query->order($idFieldClinic);


But TRIM($idFieldClinic) is not working at all and I have some space that destroy all my order.

Someone can help me please?

brian teeman

unread,
Mar 21, 2017, 7:35:37 PM3/21/17
to Joomla! General Development


On Tuesday, 21 March 2017 20:59:27 UTC, Nicolas Gameiro wrote:
Hi,
I'm trying to develop in joomla 3x, but I am getting some problems.
How can TRIM a SELECT using JFactory?

its not really a jfactoy issue but a regular mysql issue

Nicolas Gameiro

unread,
Mar 22, 2017, 5:18:24 AM3/22/17
to Joomla! General Development
Any way to solve it?

Hannes Papenberg

unread,
Mar 22, 2017, 5:34:05 AM3/22/17
to joomla-de...@googlegroups.com
Put your quotes correctly and you should be fine. Right now you are
calling the PHP trim() function on a bunch of variables instead of the
MySQL TRIM() function on a column.

Hannes
> --
> 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
> <mailto:joomla-dev-gene...@googlegroups.com>.
> To post to this group, send email to joomla-de...@googlegroups.com
> <mailto:joomla-de...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/joomla-dev-general.
> For more options, visit https://groups.google.com/d/optout.

Nicolas Gameiro

unread,
Mar 22, 2017, 6:19:19 AM3/22/17
to Joomla! General Development

I'm not very familiar with the joomla SQL.

What do you mean? I tried this, but no sucess:
$query->select($db->quoteName(array('id', "TRIM(".$idFieldClinic.")",..

Hannes Papenberg

unread,
Mar 22, 2017, 6:32:21 AM3/22/17
to joomla-de...@googlegroups.com
Please read up on what $db->quote() and $db->quoteName() are for and
what they do.
> > an email to joomla-dev-gene...@googlegroups.com
> <javascript:>
> > <mailto:joomla-dev-gene...@googlegroups.com
> <javascript:>>.
> > To post to this group, send email to joomla-de...@googlegroups.com
> <javascript:>
> > <mailto:joomla-de...@googlegroups.com <javascript:>>.
> > Visit this group at
> https://groups.google.com/group/joomla-dev-general
> <https://groups.google.com/group/joomla-dev-general>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> 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
> <mailto:joomla-dev-gene...@googlegroups.com>.

Nicolas Gameiro

unread,
Mar 22, 2017, 7:45:30 AM3/22/17
to Joomla! General Development
When I ask in forums it's because I already tried everything. I know how to trim in SQL, but not in joomla. Tried with quote, quotename, without both, I already put the trim in alias, in order, with diferent quotes and nothign work and the funny is no one in google used a trim in SQL JOOMLA, not event the documentation has examples.
>     > an email to joomla-dev-general+unsub...@googlegroups.com
>     <javascript:>
>     > <mailto:joomla-dev-general+unsub...@googlegroups.com
>     <javascript:>>.
>     > To post to this group, send email to joomla-de...@googlegroups.com
>     <javascript:>
>     > <mailto:joomla-de...@googlegroups.com <javascript:>>.
>     > Visit this group at
>     https://groups.google.com/group/joomla-dev-general
>     <https://groups.google.com/group/joomla-dev-general>.
>     > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> 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

Mike Smith

unread,
Mar 22, 2017, 8:01:00 AM3/22/17
to joomla-de...@googlegroups.com
You may want to update the table to remove the whitespace, and trim new records on insert rather than spend processing cycles on trimming every time you query.

>     > an email to joomla-dev-general+unsubscribe@googlegroups.com
>     <javascript:>
>     > <mailto:joomla-dev-general+unsubsc...@googlegroups.com
>     <javascript:>>.
>     > To post to this group, send email to joomla-de...@googlegroups.com
>     <javascript:>
>     > <mailto:joomla-de...@googlegroups.com <javascript:>>.
>     > Visit this group at
>     https://groups.google.com/group/joomla-dev-general
>     <https://groups.google.com/group/joomla-dev-general>.
>     > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> 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
> To post to this group, send email to joomla-de...@googlegroups.com
> <mailto:joomla-de...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/joomla-dev-general.
> For more options, visit https://groups.google.com/d/optout.

--
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-general+unsub...@googlegroups.com.
To post to this group, send email to joomla-dev-general@googlegroups.com.

Nicolas Gameiro

unread,
Mar 22, 2017, 8:22:41 AM3/22/17
to Joomla! General Development, b10...@gmail.com
A found a way to solve the problem. Some string has not spaces but "Tabs" so the solution was:

$query->order("TRIM( REPLACE('name',  '\t',  '' ) )");

Using a replace as ms said.
I hope this will help someone in the future.

Thank you everyone.
>     > an email to joomla-dev-general+unsub...@googlegroups.com
>     <javascript:>
>     > <mailto:joomla-dev-general+unsub...@googlegroups.com
>     <javascript:>>.
>     > To post to this group, send email to joomla-de...@googlegroups.com
>     <javascript:>
>     > <mailto:joomla-de...@googlegroups.com <javascript:>>.
>     > Visit this group at
>     https://groups.google.com/group/joomla-dev-general
>     <https://groups.google.com/group/joomla-dev-general>.
>     > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> 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
> To post to this group, send email to joomla-de...@googlegroups.com
> <mailto:joomla-de...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/joomla-dev-general.
> For more options, visit https://groups.google.com/d/optout.

--
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-general+unsub...@googlegroups.com.
To post to this group, send email to joomla-de...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages