PHP Array with implode not translating correctly inside php postgresql query

Question

I have an array that when imploded with the appropriate syntax is formatted correctly. That is to say that it tests perfect in PGAdmin. However, when I try to display use the variable in my query it is changing the characters. Here is my code:

Posted from HTML Form:

$media= $_POST["userMedia"];

implode:

$media_names = "'".implode( "%','", $media)."%'";

dump of $media_names:

string(25) "'COMM_7029%','COMM_7030%'"

What $media_names looks like in query error:

 m.name LIKE any (array[''COMM_7029%','COMM_7030%''])

I tried to eliminate the first " ' " from implode, but this was the resulting string dump:

string(24) "COMM_7029%','COMM_7030%'"

The rest of the code:

User Form from which data is selected for POST:

<select multiple name="userMedia" class="form-control" id="userMedia[]" style="height:350px;">
<?php
$conn = pg_connect("dblogin");

if (!$conn) {
echo "Did not connect.\n";
exit;
}
$sql = "SELECT medias.name FROM public.medias where medias.startdate >  '2015-01-01'";
$rs = pg_query($conn, $sql);


if (pg_num_rows($rs) > 0) {
// output data of each row
while($row = pg_fetch_assoc($rs)) {
$menu .= "<option value=".$row['name'].">" . $row['name']. "</option>";
}
}

echo $menu;

pg_close($conn); 

?> 
</select>

PHP for Database Query:

$datea= $_POST["userDatea"];
$media= $_POST["userMedia"];
$datez= $_POST["userDatez"];
$media_names = "'".implode( "%','", $media)."%'";


var_dump($media_names);

 if( !empty($_SERVER['REQUEST_METHOD']) && (strcasecmp($_SERVER['REQUEST_METHOD'], 'post')===0)  ) {
// Create connection
$conn = pg_connect("dbconnect");

// Check connection
if (!$conn) {
echo "Did not connect.\n";
exit;
}
$result = pg_query($conn,
"SELECT
date (b.starttime),
Count(b.starttime) as Plays,
Count(distinct(b.playerid)) as Stores

FROM
public.billing b,
public.medias m,
public.players p

WHERE
b.mediaitemid = m.id and
p.id = b.playerid and
m.name LIKE any (array['$media_names']) and
b.starttime >= date('$datea') and 
b.starttime < date('$datez')+1 and
m.startdate >  '2015-01-01'

GROUP BY
date (b.starttime)

ORDER BY
date (b.starttime);");

if (!$result) {
echo "Query failed.\n";
exit;
}

Can you show the code that actually is ran for us to troubleshoot? — nerdlyist
– nerdlyist, Commented Jun 5, 2017 at 16:55

KevMoe · Accepted Answer · 2017-06-05 17:49:04Z

1

I removed the single quotes around $media_names in the query and it returned the data as expected.

Changed from:

m.name LIKE any (array['$media_names'])

to this:

m.name LIKE any (array[$media_names])

edited Jun 5, 2017 at 17:49

answered Jun 5, 2017 at 17:11

KevMoe

4434 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP Array with implode not translating correctly inside php postgresql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related