Getting website fields from database

Dev Shed Forums Sponsor:

January 31st, 2013, 08:42 AM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

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);

January 31st, 2013, 09:08 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,855

Time spent in forums: 1 Month 2 Weeks 1 Day 16 h 16 m 32 sec
Reputation Power: 813

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

}

January 31st, 2013, 09:24 AM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

Quote:

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!

January 31st, 2013, 09:40 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,855

Time spent in forums: 1 Month 2 Weeks 1 Day 16 h 16 m 32 sec
Reputation Power: 813

Quote:

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!

January 31st, 2013, 09:42 AM

notflip

Contributing User

Join Date: Sep 2012

Posts: 148

Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1

Quote:

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.

January 31st, 2013, 10:02 PM

jrose

Registered User

Join Date: Jan 2013

Posts: 1

Time spent in forums: 31 m 59 sec
Reputation 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'];

Getting website fields from database

Developer Shed Advertisers and Affiliates