#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Getting website fields from database


    Hi! I'm making a supersmall CMS for a customer..

    He has 4 text fields on the website. Now i'm trying to get them from a database..

    the database looks like this



    I'm using PDO with a fetchAll()

    and
    Code:
    <h3><?= $field[0]['content'] ?></h3>
    <h3><?= $field[1]['content'] ?></h3>
    to get the 2 fields for now.. but is this the way to go? is this best practice?

    here's the PDO

    Code:
    	$fields = $db->query('SELECT * FROM fields');
    	$fields->execute();
    	$field = $fields->fetchAll(PDO::FETCH_ASSOC);
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    several things:
    • Don't use "SELECT *". It's inefficient and can easily fetch unwanted columns in case you change the table structure someday. Select the specific columns you need for this particular task.
    • That "execute()" looks weird, because you're using a normal query, not a prepared statement. I think this is simply ignored. A query in PDO works like shown below.
    • You need to escape your content strings: Donít output raw values or insert them into the HTML page.


    PHP Code:
    $fields_query $db->query('
        SELECT
            id
            , content    -- add name if you need to
        FROM
            fields
    '
    );
    // simply loop over the rows if you only need them once
    foreach ($fields_query as $field) {
        
    // output $field

  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by Jacques1
    Hi,

    several things:
    • Don't use "SELECT *". It's inefficient and can easily fetch unwanted columns in case you change the table structure someday. Select the specific columns you need for this particular task.
    • That "execute()" looks weird, because you're using a normal query, not a prepared statement. I think this is simply ignored. A query in PDO works like shown below.
    • You need to escape your content strings: Donít output raw values or insert them into the HTML page.


    PHP Code:
    $fields_query $db->query('
        SELECT
            id
            , content    -- add name if you need to
        FROM
            fields
    '
    );
    // simply loop over the rows if you only need them once
    foreach ($fields_query as $field) {
        
    // output $field

    Thanks for the clear explanation! I'm following your tips. now the only thing i wonder is..

    The different fields are placed on different parts of the page... there's the homepage_header, the homepage_mission statement..

    Should i fetch then in an array or something? Almost there but i'm still a bit confused thanks!
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by notflip
    Should i fetch then in an array or something?
    Yes, but put them in an associative array with the "name" as the key:
    PHP Code:
    $fields = array();
    foreach (
    $fields_query as $field
        
    $fields[$field['name']] = $field['content']; 
    Then you can simply fetch a specific field when you need it.

    Comments on this post

    • notflip agrees
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by Jacques1
    Yes, but put them in an associative array with the "name" as the key:
    PHP Code:
    $fields = array();
    foreach (
    $fields_query as $field
        
    $fields[$field['name']] = $field['content']; 
    Then you can simply fetch a specific field when you need it.
    Perfect! thanks! I also found a way using

    Code:
    $fields = $db->query('SELECT name, content FROM fields')->fetchAll(PDO::FETCH_KEY_PAIR);
    but that's restricted to 2 columns i noticed! i'll try your method.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0
    but put them in an associative array with the "name" as the key:
    PHP Code:
    $fields = array();
    foreach ($fields_query as $field)
    $fields[$field['name']] = $field['content'];

IMN logo majestic logo threadwatch logo seochat tools logo