Dynamically SELECT fields from table

PHP
1

Hi,
I am trying to use an implode() function to create a string variable to build an array.

The commented out line is what I am trying to produce. But I can not see how to repeat a [key] using implode(). Implode() seems to always go on to the next key. Perhaps I should use a foreach() loop.
The keys are ‘userid’ and ‘effort’

  $build  = "array('";
  $build .= .implode(array_keys(fields))."` => $row['" .implode(array_keys(fields))."'],'";
  $build .= .implode(array_keys(fields))."' => $row['.implode(array_keys(fields));
  $build .= "'])";
        
  foreach ($result as $row)
  {
    \\$newNotes[] = array('userid' => $row['userid'], 'effort' => $row['effort']);
    $newNotes[] = $build;
  }

Any help would be greatly appreciated,
Thanks,
Shane

2

What is the matter with what you already have http://www.sitepoint.com/community/t/passing-an-id-to-query-where-in-a-table/105606/11.

As far as I can tell output would be something like this.

Array
(
    [0] => Array
        (
            [userid] => 1
            [effort] => 3
        )

    [1] => Array
        (
            [userid] => 1
            [effort] => 4
        )

)
3

Hi,
Thanks for the reply, but I am trying to create an output like this.

array('userid' => $row['userid'], 'effort' => $row['effort']);

What i am trying to do is something like this,

<?php

   // array containing data
   $array = array(
      "name" => "John",
      "surname" => "Doe",
      "email" => "j.doe@intelligence.gov"
   );

   // build query...
   $sql  = "INSERT INTO table";

   // implode keys of $array...
   $sql .= " (`".implode("`, `", array_keys($array))."`)";

   // implode values of $array...
   $sql .= " VALUES ('".implode("', '", $array)."') ";

   // execute query...
   $result = mysql_query($sql) or die(mysql_error());

?>

which is an example from http://php.net/manual/pt_BR/function.implode.php

Thanks,
Shane

4
  1. implode:

This array function concatenate the elements as string using delimiter.

Example:

<?php $bykes[0] = “Hondabike”; $bykes[1] = “Herobike”;
5

Hi,
I got it,

        $build = "array("; 
        foreach($_POST['fields'] as $key => $value):
           $build .= "'" . $value . "' => \$row['" . $value . "'], ";
        endforeach;
        $build = substr($build, 0, -2);
        $build .= ")";

and then I can use $build like this,

  foreach ($result as $row)
  {
    //$newNotes[] = array('userid' => $row['userid'], 'effort' => $row['effort']);
    $newNotes[] = "$build";
  }

I am not sure if there is a better way but this seems to be working fine.

To show some context, the full block of code is,

if (isset($_POST['action']) and $_POST['action'] == 'searchview')
{ 
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';
    echo "<pre>";
    print_r($_POST);
    echo "</pre>";
    $build = "array("; 
    foreach($_POST['fields'] as $key => $value):
        $build .= "'" . $value . "' => \$row['" . $value . "'], ";
        endforeach;
        $build = substr($build, 0, -2);//removes the last two characters from this string, which are the comma and the space
        $build .= ")";
        echo $build;
        $select = implode(", ", $_POST['fields']);
        echo $select;

  try
  {
    $sql = 'SELECT "$select" FROM notes WHERE 
    userid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
    }
    
  catch (PDOException $e)
  {
    $error = 'Error fetching by fields list of notes.';
    include 'error.html.php';
    exit();
  }
  
   $result = $s->fetchAll();
       
  foreach ($result as $row)
  {
    //$newNotes[] = array('userid' => $row['userid'], 'effort' => $row['effort']);
    $newNotes[] = "$build";
  }
  include 'searchview.html.php';
  exit();
}

Thanks,
Shane

6

Hi,
The string $build is correct but it doesn’t work with this,

foreach ($result as $row)
  {
    //$newNotes[] = array('userid' => $row['userid'], 'effort' => $row['effort']);
    $newNotes[] = "$build";
  }

Does anyone have any ideas?

Thanks,
Shane

7

Your $build is being parsed as a string. You have quotes around it.

8

Using double quotes causes PHP to expand any variables within a string, which means there’s actually no difference between:

$newNotes[] = "$build";

and

$newNotes[] = $build;
9

Wow. I was always told different than that. Thanks for the lesson. I’ll go back to my noob corner :slight_smile:

10

Hi ofeyofey,

Why exactly are you trying to do it this way, rather than just using the array that is returned by fetchAll()? If you were to do:

$newNotes = $result;

would it not give you the results you’re after?

11

Hi,
Thanks for the replies.

How exactly do i do that?

 $result = $s->fetchAll();
       
  foreach ($result as $row)
  {
    //$newNotes[] = array('userid' => $row['userid'], 'effort' => $row['effort']);
    $newNotes[] = array($result);
  }

Or just don’t do anything after fetchAll() ?

Thanks,
Shane

12

So then is there also no difference between
$select and “$select” in

 $select = implode(", ", $_POST['fields']);
        echo $select;

  try
  {
    $sql = 'SELECT "$select" FROM notes WHERE 
    userid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
    }

When I take out the double quotes I get an error,

> Error fetching by fields list of notes.      exception 'PDOException' 
> with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 
> '$select' in 'field list'' in 
> /home2/shanegib/public_html/artgibney/admin/notes/index.php:47
> Stack trace:
> #0 /home2/shanegib/public_html/artgibney/admin/notes/index.php(47): 
> PDOStatement->execute()
> #1 {main}
13

The simplest way would just be to assign the results of the fetchAll call to $newNotes directly:

$newNotes = $s->fetchAll();
14

See that your string above is using single quotes, so PHP doesn’t expand $select, it just treats it as a string. This would work:

$sql = "SELECT $select FROM notes WHERE userid = :id";
15

There’s another change you might want to make to your script. As you’re taking a user-submitted array of DB columns and including it in your SQL, your code is vulnerable to an SQL injection attack. The best way to avoid this would be to have a whitelist of table columns that the user is allowed to select and check against that:

$whitelist = array(
    'userid',
    'effort',
    // etc
);

$columns = array();
foreach($_POST['fields'] as $field) {
    if (in_array($column, $whitelist)) {
        $columns[] = '`'. $column . '`'; // Add proper delimiters around column names
    }
}

$select = implode(", ", $columns);
1 Like
16

Hi,
I understand what you meana bout the whitelist and will put it in. But first the problem that i am having with the single quote around $select is that the SELECT statement itself is already in a single quote, so by putting a single quote inside it, I presume that is read as ending the single quote of the SELECT statement.

$sql = 'SELECT '$select' FROM notes WHERE 
userid = :id';

So how do i get around that? Maybe put the SELECT in double quotes, but i am sure that won’t work.

Thanks,
Shane

17

Hi,
This seems to be working,

$sql = "SELECT $select FROM notes WHERE 
userid = :id";

Double quotes around everything and no quotes around $select.

Thanks,
Shane

18

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.