Using Form data to build a query in my Controller

Hi,

I have a form that collects data from three SELECT inputs, two of which can have multiple options selected. I can get the data in my Controller using Request, $request and data is like this:

array:4 [▼ // app/Http/Controllers/FieldProv.php:71
  "_token" => "DK7cJuB6iJWTOh5OLqYR5jjxc8wE7X87HJCXxIGG"
  "site" => "5MT10647"
  "SUTyp" => array:3 [▶
    0 => "GEN"
    1 => "ISO"
    2 => "NST"
  ]
  "FeTyp" => array:3 [▶
    0 => "APE"
    1 => "ARP"
    2 => "ASP"
  ]
]

I have a query that was written 10+ years ago that works fine but it was originally done on an ASP page. If I take it and run it just as a SQL test (not in my controller) with just a single value it works fine:

SELECT DISTINCT tblSU.Site, 
tlkpSUTyp.SUTyp, 
tblSU.SUNum,       
tlkpSUDesc.SUDesc
FROM tlkpSUTyp
RIGHT JOIN (((tlkpSUDesc RIGHT JOIN tblSU ON tlkpSUDesc.SUDescCode = tblSU.SUDescCode)
	LEFT JOIN tblPD ON (tblSU.SUNum = tblPD.SUNum)
	AND (tblSU.Site = tblPD.Site))
LEFT JOIN tblFeature ON (tblPD.FeNum = tblFeature.FeNum)
	AND (tblPD.SUNum = tblFeature.SUNum)
	AND (tblPD.Site = tblFeature.Site))
	ON tlkpSUTyp.SUTypCode = tblSU.SUTypCode
WHERE tblSU.Site Like ‘123’	
	AND tblSU.SUTypCode LIKE ’syt’
	AND tblFeature.FeTypCode LIKE ‘ert’
ORDER By tlkpSUTyp.SUTyp ASC,
	tblSU.SUNum ASC

The values for AND tblSU.SUTypCode LIKE ’syt’ AND tblFeature.FeTypCode LIKE ‘ert’ can have multiple options from the SELECT input on the form.

So I have the query in my controller like this and it works when a single option is chosen, I just need the multiple options that are in the array above:

$site = $request->input('site');

$suTyp = $request->input('SUTyp');
$suTyp = implode(" ", $suTyp);
        
$feTyp = $request->input('FeTyp');
$feTyp = implode(" ", $feTyp);

$siteSql = "SELECT DISTINCT tblSU.Site, tlkpSUTyp.SUTyp, tblSU.SUNum,
                        tlkpSUDesc.SUDesc
                    FROM tlkpSUTyp
                    RIGHT JOIN (((tlkpSUDesc RIGHT JOIN tblSU ON tlkpSUDesc.SUDescCode = tblSU.SUDescCode)
                        LEFT JOIN tblPD ON (tblSU.SUNum = tblPD.SUNum)
                        AND (tblSU.Site = tblPD.Site))
                    LEFT JOIN tblFeature ON (tblPD.FeNum = tblFeature.FeNum)
                        AND (tblPD.SUNum = tblFeature.SUNum)
                        AND (tblPD.Site = tblFeature.Site))
                        ON tlkpSUTyp.SUTypCode = tblSU.SUTypCode
                    WHERE tblSU.Site Like :id AND tblSU.SUTypCode LIKE :suTyp AND tblFeature.FeTypCode LIKE :feTyp
                    ORDER By tlkpSUTyp.SUTyp ASC";
                        tblSU.SUNum ASC";

        $siteSql = DB::getPdo()->prepare($siteSql);
        $siteSql->setFetchMode(\PDO::FETCH_OBJ);
        $siteSql->execute([
            'id' => $site,
            'suTyp' => $suTyp,
            'feTyp' => $feTyp
        ]);
        $site = collect($siteSql->fetchAll());

So I was thinking that DB Query Builder might be the way to go (instead of this way) to handle the multiple options using whereIn() but I have no idea how to handle the those complicated Joins. Or maybe I am close with what I have and this just needs a few changes.

Thanks, Stan

stanhook

3 years ago

Level 1

UPDATE:

So if I do this:

$suTyp = $request->input('SUTyp');
$suTyp = "('" . implode("','", $suTyp) . "')";

$feTyp = $request->input('FeTyp');
$feTyp = "('" . implode("','", $feTyp) . "')";

I get the data (example) like this:

"('NST','OTH','STR')"

And in my controller I tried it in my WHERE clause:

WHERE tblSU.Site Like :id AND tblSU.SUTypCode IN :suTyp AND tblFeature.FeTypCode IN :feTyp

But when I try it in my controller as above it comes back - MySQL server version for the right syntax to use near '? AND tblFeature.FeTypCode IN ?

If I run this just straight SQL in my controller:

WHERE tblSU.Site Like '5mt10647' AND tblSU.SUTypCode IN ('NST','OTH','STR') AND tblFeature.FeTypCode IN ('BSP','BUP','BUR')

I get results.

I tried this:

$siteSql->execute([
            'id' => "5mt10647",
            'suTyp' => "('NST','OTH','STR')",
            'feTyp' => "('BSP','BUP','BUR')",
        ]);

I get the same error.

In my mind it (WHERE tblSU.Site Like :id AND tblSU.SUTypCode IN :suTyp AND tblFeature.FeTypCode IN :feTyp) should work in my controller but it doesn't.

Thanks for the help!

tisuchi

3 years ago

Level 70

@stanhook Is there any particular reason to use raw sql query instead of laravel eloquent?

stanhook

3 years ago

Level 1

@tisuchi Since I have about 80+ queries that were already written inside ASP pages, it was suggested when I started (here in Laracast) to use getPdo() and not re-write the queries. This has worked great up until this point with queries just like this one and some even more complicated. My issue is the form with multiple options. I had updated my post above with what I have tried in the past hour.

Thanks for the help!

1 like

Please or to participate in this conversation.