SQL query

[Paul Swingewood]

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]

Do echo $stallbookedQuery; and compare queries.

[Paul Swingewood]

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]

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]

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]

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

https://www.php.net/manual/en/datetime.format.php

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

To view this discussion on the web, visit https://groups.google.com/d/msgid/joomla-dev-general/97803368-1986-4b5c-9012-cbc398bfee12n%40googlegroups.com.

[Paul Swingewood]

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]

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!