SQL query

22 views
Skip to first unread message

Paul Swingewood

unread,
Sep 21, 2022, 4:20:09 AMSep 21
to Joomla! General Development
Hi,

I hope someone can help me with this.

MySQL:

SELECT `stallreference` FROM `tioei_maccs_market_stalls_allocations`
WHERE `startdate` between '2022-09-11' and '2022-09-17'
and `marketname` = 'Rag Market'
and `days` IN (2)

Works a treat!

$dbMarket = Factory::getDbo();
        $stallbookedQuery = $dbMarket->getQuery(true)
          ->select('stallreference')
            ->from($dbMarket->quoteName('#__maccs_market_stalls_allocations'))
            ->where($dbMarket->quoteName('startdate') . " BETWEEN " . $dbMarket->quote($startdate)
            .'AND'. $dbMarket->quote($enddate)
            .'AND'. $dbMarket->quoteName('marketname') . " = " . $dbMarket->quote($market)
          .'AND'. $dbMarket->quoteName('days') . " IN (" . $dbMarket->quote($days) . ")");
        $dbMarket->setQuery($stallbookedQuery);
        $stallbooked = $dbMarket->loadAssoc();

Does the same thing but doesn't work :(

Can anyone tell me what i'm doing wrong please?

Paul


Viper

unread,
Sep 21, 2022, 5:28:00 AMSep 21
to Joomla! General Development
Do echo $stallbookedQuery; and compare queries.

Paul Swingewood

unread,
Sep 21, 2022, 5:28:04 AMSep 21
to Joomla! General Development
The problem appears to be the IN. 
This works:
.'AND'. $dbMarket->quoteName('days') . " IN (2,5)");

But when I do this 

$days = implode(",",$data['days']);

.'AND'. $dbMarket->quoteName('days') . " IN (" . $dbMarket->quote($days) . ")");

It doesn't :(

Hannes Papenberg

unread,
Sep 21, 2022, 6:26:30 AMSep 21
to joomla-de...@googlegroups.com
You don't have to do the whole 'AND' stuff. Just call ->where() multiple
times. It automatically concatenates them with AND.
$dbMarket->quote($days) makes one single string out of the values and
not X array entries, each quoted independently. You can use something
like ArrayHelper::toInt() or something like that or send the array to
the quote function and then concatenate it afterwards.

Am 21.09.2022 um 11:28 schrieb 'Paul Swingewood' via Joomla! General
Development:
> --
> 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 view this discussion on the web, visit
> https://groups.google.com/d/msgid/joomla-dev-general/9b5dd89a-8873-49a7-9818-b90536820eefn%40googlegroups.com <https://groups.google.com/d/msgid/joomla-dev-general/9b5dd89a-8873-49a7-9818-b90536820eefn%40googlegroups.com?utm_medium=email&utm_source=footer>.

Maciek Wołpiuk

unread,
Sep 21, 2022, 6:30:28 AMSep 21
to joomla-de...@googlegroups.com
Hello

First question.
Items in IN clausule are string variable or INT variable?
If Int U dont need to use $dbMarket->quote().
.'AND'. $dbMarket->quoteName('days') . " IN (" . $dbMarket->quote($days) . ")");
If I good think it returns
IN ( " 2,5 ") not IN (2,5).
Try to remove  quote function.
Try this one:
.'AND'. $dbMarket->quoteName('days') . " IN (" . $days . ")");

Pozdr
Maciek


--
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 view this discussion on the web, visit https://groups.google.com/d/msgid/joomla-dev-general/9b5dd89a-8873-49a7-9818-b90536820eefn%40googlegroups.com.

Darren Forster

unread,
Sep 21, 2022, 7:03:53 AMSep 21
to joomla-de...@googlegroups.com
Have you checked the format of $startdate and $enddate if it's a PHP DateTime class you'll need to convert it using the format function


$startdate->format ('Y-m-d')

or

date_format ( $startdate, 'Y-m-d')

If you're not using the DateTime class (much better to use the class). 

--
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.

Paul Swingewood

unread,
Sep 21, 2022, 10:46:29 AMSep 21
to Joomla! General Development
Just a quickie thinking about this.

I am comparing list 1 (2,4,5,6) or whatever the user has selected with what's in the database (2,4,5,6) or whatever was stored.
So comparing two lists. Is this actually do able with IN?

is 2 in the database lst
is 4 in the database list
etc etc....

It seems to work for a single value but not a list of values

Paul

Darren Forster

unread,
Sep 23, 2022, 3:51:29 AMSep 23
to joomla-de...@googlegroups.com
Towards the end of me writing this I just spotted a major problem (it's only a tiny thing but it might solve your problems - read the bit in bold at the end first!)

I guess that all depends on how $days is formatted - when you say the user selects "days" - how are they selecting it - is it being returned as an array, class or text?

Testing the SQL in operator on w3 schools https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_in it should accept multiple values.

I think looking at it what your problem may be is the dbMarket->quote ( $days ) ; function as quote accepts a string and puts quotes round it - so with your code your in statement is going to look something like this

AND 'days' IN ('2,3,4,5')  

which isn't exactly what you want you want...

AND 'days' in ('2', '3', '4', '5' ) 

so ideally something like

<?php

$dayArray = explode ( ',', $days ) ;
$dayIn = '' ;
foreach ( $dayArray as $day )
  $dayIn .= $dbMarket->quote ( $day ) ;
...
'AND'. $dbMarket->quoteName('days') . " IN ( $dayIn )");

?>

Ideally you need to know what kind of data $days is holding - is it an array or is it text?  You can do this by doing the following..

<?php
echo '<pre>'.print_r ( $days, true ).'</pre>' ;
?>

Also you could see what your IN statement is too by doing the following

<?php
echo '<pre>'.print_r ( 'AND'. $dbMarket->quoteName('days') . " IN (" . $dbMarket->quote($days) . ")");
?>

Also just noticed too - all your 'AND' statements need to have spaces either side (like with IN and between...

            ->where($dbMarket->quoteName('startdate') . " BETWEEN " . $dbMarket->quote($startdate)
            .' AND '. $dbMarket->quote($enddate)
            .' AND '. $dbMarket->quoteName('marketname') . " = " . $dbMarket->quote($market)
          .' AND '. $dbMarket->quoteName('days') . " IN (" . $dbMarket->quote($days) . ")");

otherwise the SQL you are gettting is 

WHERE startdate BETWEEN '$startdate'AND'$enddate'AND"marketname" = '$market'AND'days' IN '$days'

rather than

WHERE startdate BETWEEN '$startdate' AND '$enddate' AND "marketname" = '$market' AND 'days' IN '$days'

I think the first thing to try is putting spaces around the AND statements and that may fix your problem!
Reply all
Reply to author
Forward
0 new messages