Load Meta data dynamically from mysql database

Skorpio · March 7, 2013

I have a basic MySQL database setup holding relevant meta information for a few pages, starting small, I want the meta information to load dynamically depending on the page that the user is viewing.

I have the connection script working fine, I have echoed the data out via variables and everything is looking good however I am now at the stage where I want to echo that data out on the page in the relevant fields but cannot seem to get it working.

So far I have

<?php

		include 'connect.php';
		
		$title = 'page_title';
		$pname = 'page_name';
		$desc = 'meta_description';
		$key = 'meta_keywords';
		$aut = 'author';
		$copyr = 'copyright';
		$email = 'email';
		$rating = 'rating';
		$robots = 'robots';
		$visit = 'revisit';
		$exp = 'expires';
		$dist = 'distribution';
		
		$query = $db->query("SELECT * FROM meta_data");
		
			while ($row = $query->fetch(PDO::FETCH_ASSOC)){
			echo $row[$pname] . '<br>';
			echo $row[$title]. '<br>';
			echo $row[$desc]. '<br>';
			echo $row[$key]. '<br>';
			echo $row[$aut]. '<br>';
			echo $row[$copyr]. '<br>';
			echo $row[$email]. '<br>';
			echo $row[$rating]. '<br>';
			echo $row[$robots]. '<br>';
			echo $row[$visit]. '<br>';
			echo $row[$exp]. '<br>';
			}
?>

The while loop was just testing that I was getting the data, it echoes out fine, although I do get every page in the DBase.

I then have

<link rel="apple-touch-icon" href="apple_touch_icon.png"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="description" content="<?php echo $desc; ?>" />
<meta name="keywords" content="<?php echo $key; ?>" />
<meta name="copyright" content="<?php echo $copyr; ?>" />
<meta name="author" content="<?php echo $aut; ?>" />
<meta name="email" content="<?php echo $email; ?>" />
<meta name="Distribution" content="<?php echo $dist; ?>" />
<meta name="Rating" content="<?php echo $rating; ?>" />
<meta name="Robots" content="<?php echo $robots; ?>" />
<meta name="Revisit-after" content="<?php echo $visit; ?>" />
<meta name="expires" content="<?php echo $exp; ?>" />
<link rel="shortcut icon" href="favicon.ico" type="image/icon" /> <!-- favicon for browser, the image is .ico file in root directory-->
<title><?php echo $title; ?></title>

As it stands at the moment all I get in the page fields is the database field name but as I say I get the data from the database when echoing the row.

Could someone advise me where I am going wrong and steer me in the right direction to get the appropriate data for the page in question?

Thanks for any help.selectdata.php home.php

Barand · March 7, 2013

When you use SELECT * no-one can tell what you are selecting and thereby get an idea of your table structure.

Skorpio · March 7, 2013

The table structure is as the variables, that is every field in the dbase.

Barand · March 7, 2013

So is that a correctly normalized meta_data table?

meta_name | meta_value

or is it

description | keywords | copyright | author | etc

As you see, there is no way for us to know

Skorpio · March 7, 2013

The field names are 'page_title'; etc and then I have created variables to store the data from the records

mikosiko · March 7, 2013

all I get in the page fields is the database field name but as I say I get the data from the database when echoing the row.

Well... seems that you are answering your own question...

<?php echo $desc; ?> is not the same as echo $row[$desc]. '<br>'; (at least that you are doing $desc = $row[$desc] in some place in your code)

Skorpio · March 7, 2013

I realise that

echo $row[$desc]

will give me the data but what I cannot work out is how I only get the relevant data for that page.

I.e. I only want the title for the home page in the home page title field but I am getting all of the pages title fields.

So I guess my question is which is the best way to ensure that the title field data from the mysql database for the home page is the only data that goes into the title field on the homepage?

Thanks

Skorpio · March 7, 2013

When I enter

echo $row[$title]

into the title field I do not get any data in the title field although I have included the selectdata.php (attatched to post #1) file which has the "SELECT * FROM meta_data" query

Skorpio · March 7, 2013

<?php echo $title; ?>

outputs the following source

<title>page_title</title>

and

echo $row[$title]

when embedded into the php pages gives me

<title></title>

As I say I cannot work out why when I use echo $row[$title] on the query page I get oputput echoed out but on the individual pages I get nadda.

The echo statements in the php file with the query give me all the data from all the fields as expected.

Edited March 7, 2013 by Skorpio

Barand · March 7, 2013

I'd recommend (as always) you go for a normalized table design

page_id | meta_name | meta_value

Then all you need to for a particular page ($page_id) is

$sql = "SELECT mete_name, meta_value
        FROM meta_data
        WHERE page_id = $page_id";

$result = $db->query($sql);

while (list($name,$val) = $result->fetch_row()) {
    echo "<meta name=\"$name\" content=\"$val\" />\n";
}

This has the added advantage that individual pages can have as many or as few meta tags as you want.

Skorpio · March 7, 2013

Sorry I must not be explaining myself, I know what you are saying about the database and normalisation however as you have seen from the original post all the fields have names relevant to the data stored within it and yes I realise I could go through naming all the fields in the select statement but as I am using all the fields I used the * as the DBASE has 13 fields it just keeps the code tidier (in my opinion for what it is worth).

My fields are

meta_id
page_name
page_title
meta_description
meta_keywords
author
copyright
email
rating
robots
revisit
expires
distribution

all of those fields are going to be called hence the * in the select statement

As the following returns the data, all be it everything in the table, I am just unsure of how to pick the relevant elements for the right page.

include 'connect.php';
		
		$title = 'page_title';
		$pname = 'page_name';
		$desc = 'meta_description';
		$key = 'meta_keywords';
		$aut = 'author';
		$copyr = 'copyright';
		$email = 'email';
		$rating = 'rating';
		$robots = 'robots';
		$visit = 'revisit';
		$exp = 'expires';
		$dist = 'distribution';
		
		$query = $db->query("SELECT * FROM meta_data");
		
			while ($row = $query->fetch(PDO::FETCH_ASSOC)){
			echo $row[$pname] . '<br>';
			echo $row[$title]. '<br>';
			echo $row[$desc]. '<br>';
			echo $row[$key]. '<br>';
			echo $row[$aut]. '<br>';
			echo $row[$copyr]. '<br>';
			echo $row[$email]. '<br>';
			echo $row[$rating]. '<br>';
			echo $row[$robots]. '<br>';
			echo $row[$visit]. '<br>';
			echo $row[$exp]. '<br>';
			} // this is just for testing and it works but using what I have already what do I need to add to get the relevant data fed to the correct page?

Thanks

Barand · March 7, 2013

try

$sql = "SELECT *
        FROM meta_data
        WHERE meta_id = $meta_id";

$result = $db->query($sql);

$row = $query->fetch(PDO::FETCH_ASSOC);                                                         

unset ($row['meta_id']) ;  // discard that one 

foreach ($row as $desc => $content) {
    echo "<meta name=\"$desc\" content=\"$content\" />\n" ;
}

Skorpio · March 7, 2013

As that snippet stands I am sure I am getting closer but I am getting the following returned

Notice: Undefined variable: meta_id in C:\xampp\htdocs\MyLatestSite\includes\fetchmetadata.php on line 19

Notice: Undefined variable: query in C:\xampp\htdocs\MyLatestSite\includes\fetchmetadata.php on line 23

Fatal error: Call to a member function fetch() on a non-object in C:\xampp\htdocs\MyLatestSite\includes\fetchmetadata.php on line 23

Line 23 being

$row = $query->fetch(PDO::FETCH_ASSOC);

and 19 being

$sql = "SELECT * FROM meta_data WHERE meta_id = $meta_id";

Barand · March 7, 2013

You have to give a value to $meta_id to tell it which record to select.

It should be "$query = $db->query($sql);"

Skorpio · March 8, 2013

Ok so now I have

<?php

                // fetchmetadata.php

		include 'connect.php';
		
		$id = 'meta_id';
		$title = 'page_title';
		$pname = 'page_name';
		$desc = 'meta_description';
		$key = 'meta_keywords';
		$aut = 'author';
		$copyr = 'copyright';
		$email = 'email';
		$rating = 'rating';
		$robots = 'robots';
		$visit = 'revisit';
		$exp = 'expires';
		$dist = 'distribution';
		
		$sql = "SELECT * FROM meta_data WHERE meta_id = $id";
		$query = $db->query($sql);
		
		
		$row = $query->fetch(PDO::FETCH_ASSOC);                                                         
		
		unset ($row['meta_id']) ;  // discard that one 
		
		foreach ($row as $desc => $content) {
			echo "<meta name=\"$desc\" content=\"$content\" />\n" ;
		}
?>

When I view the page I get the following returned from the dbase, bearing in mind these are sample keywords etc as have data to input once I have this working

<meta name="page_name" content="home" />
<meta name="page_title" content="Home Page" />
<meta name="meta_description" content="introduction to our products and services offered at competitive prices" />
<meta name="meta_keywords" content="web design, bespoke websites, dynamic websites, web packages" />
<meta name="author" content="name" />
<meta name="copyright" content="© organisations name, 2013" />
<meta name="email" content="organisations email" />
<meta name="rating" content="General" />
<meta name="robots" content="INDEX,FOLLOW" />
<meta name="revisit" content="7 Days" />
<meta name="expires" content="Tue, 24 dec 17:20:00 GMT" />
<meta name="distribution" content="Global" />

So far so good, now I come to the physical pages on the site, i.e. home/index.php, I have removed all the php from the homepage bar the include 'fetchmetadata.php'; for the fetchmetadata.php page and when I view the source of the homepage I now get

<meta name="page_name" content="home" />
<meta name="page_title" content="Home Page" />
<meta name="meta_description" content="introduction to our products and services offered at competitive prices" />
<meta name="meta_keywords" content="web design, bespoke websites, dynamic websites, web packages" />
<meta name="author" content="author" />
<meta name="copyright" content="© organisations name, 2013" />
<meta name="email" content="organisations email" />
<meta name="rating" content="General" />
<meta name="robots" content="INDEX,FOLLOW" />
<meta name="revisit" content="7 Days" />
<meta name="expires" content="Tue, 24 dec 17:20:00 GMT" />
<meta name="distribution" content="Global" />
<!DOCTYPE HTML>
<html>
<head>
<link rel="apple-touch-icon" href="apple_touch_icon.png"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="description" content="distribution" /> <!--  info should be here -->
<meta name="keywords" content="" />
<meta name="copyright" content="" />
<meta name="author" content="" />
<meta name="email" content="" />
<meta name="Distribution" content="" />
<meta name="Rating" content="" />
<meta name="Robots" content="" />
<meta name="Revisit-after" content="" />
<meta name="expires" content="" />
<link rel="shortcut icon" href="favicon.ico" type="image/icon" /> <!-- favicon for browser, the image is .ico file in root directory-->
<title></title>
</head>

<body>

</body>
</html>

The home page php/html is as follows

<?php include_once('../includes/fetchmetadata.php'); ?>
<!DOCTYPE HTML>
<html>
<head>
<link rel="apple-touch-icon" href="apple_touch_icon.png"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="description" content="<?php echo $desc; ?>" /> <!--  info should be here -->
<meta name="keywords" content="" />
<meta name="copyright" content="" />
<meta name="author" content="" />
<meta name="email" content="" />
<meta name="Distribution" content="" />
<meta name="Rating" content="" />
<meta name="Robots" content="" />
<meta name="Revisit-after" content="" />
<meta name="expires" content="" />
<link rel="shortcut icon" href="favicon.ico" type="image/icon" /> <!-- favicon for browser, the image is .ico file in root directory-->
<title></title>
</head>

<body>

</body>
</html>

As I say I removed the php tags from all but 1 field until it is working as now I have duplicated the info for some reason.

Skorpio · March 8, 2013

Ok so worked out my first mistake, removed all the meta data from the home page and then included the php file in the right location, so now I get

<!DOCTYPE HTML>
<html>
<head>
<link rel="apple-touch-icon" href="apple_touch_icon.png"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="page_name" content="home" />
<meta name="page_title" content="Home Page" />
<meta name="meta_description" content="introduction to our products and services offered at competitive prices" />
<meta name="meta_keywords" content="web design, bespoke websites, dynamic websites, web packages" />
<meta name="author" content="author" />
<meta name="copyright" content="© organisations name, 2013" />
<meta name="email" content="organisations email" />
<meta name="rating" content="General" />
<meta name="robots" content="INDEX,FOLLOW" />
<meta name="revisit" content="7 Days" />
<meta name="expires" content="Tue, 24 dec 17:20:00 GMT" />
<meta name="distribution" content="Global" />
<link rel="shortcut icon" href="favicon.ico" type="image/icon" /> <!-- favicon for browser, the image is .ico file in root directory-->
<title></title>
</head>

<body>

</body>
</html>

However if I view say the contact page I am getting the exact same details and the html <title></title> is still blank, instead I am getting

<meta name="page_title" content="Home Page" />

Jessica · March 8, 2013

You have to tell it WHAT page you want to view the data for.

Barand · March 8, 2013

As Jessica said

You have to give a value to $meta_id to tell it which record to select.

Jessica · March 8, 2013

Which at some point you might as well move the content into the DB and have a cms lol!

Skorpio · March 8, 2013

As it stands at this moment in time, I am getting data from the database into the correct fields but there is only 1 set of data being used from the database. I think it is because although I have the variables set up that I am not telling what page should be associated with which id etc, the code so far is

// getmetadata.php

include 'connect.php';
		
		$id = 'meta_id';
		$title = 'page_title';
		$pname = 'page_name';
		$desc = 'meta_description';
		$key = 'meta_keywords';
		$aut = 'author';
		$copyr = 'copyright';
		$email = 'email';
		$rating = 'rating';
		$robots = 'robots';
		$visit = 'revisit';
		$exp = 'expires';
		$dist = 'distribution';
		
		$sql = "SELECT * FROM meta_data WHERE meta_id = $id";
		$query = $db->query($sql);
		
		$row = $query->fetch(PDO::FETCH_ASSOC);                                                         
		
		unset ($row['meta_id']) ;  // discard that one 
		
		foreach ($row as $desc => $content) {
			echo "<meta name=\"$desc\" content=\"$content\" />\n" ;
		}

The home page is as follows

<!-- home.php -->

<!DOCTYPE HTML>
<html>
<head>
<link rel="apple-touch-icon" href="apple_touch_icon.png"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<?php include_once('../includes/fetchmetadata.php'); ?>
<link rel="shortcut icon" href="favicon.ico" type="image/icon" /> <!-- favicon for browser, the image is .ico file in root directory-->
<title></title>
</head>

<body>

</body>
</html>

Together the output from home.php is

<!DOCTYPE HTML>
<html>
<head>
<link rel="apple-touch-icon" href="apple_touch_icon.png"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>page_title</title> <!-- not getting the title of the page but entering the dbase field name -->
<meta name="page_name" content="home" />
<meta name="page_title" content="Home Page" /> <!-- this should be going in the html title tags and need to delete this from the php script -->
<meta name="meta_description" content="introduction to our products and services offered at competitive prices" />
<meta name="meta_keywords" content="web design, bespoke websites, dynamic websites, web packages" />
<meta name="author" content="author" />
<meta name="copyright" content="© Organisations Name, 2013" />
<meta name="email" content="organisations email" />
<meta name="rating" content="General" />
<meta name="robots" content="INDEX,FOLLOW" />
<meta name="revisit" content="7 Days" />
<meta name="expires" content="Tue, 24 dec 17:20:00 GMT" />
<meta name="distribution" content="Global" />
<link rel="shortcut icon" href="favicon.ico" type="image/icon" /> <!-- favicon for browser, the image is .ico file in root directory-->

</head>

<body>

</body>
</html>

I tried an if statement to get the title but all that gave me was the same as what I was getting at the start, page_title inbetween the html title tags. Guru has got me 3/4 of the way there but just need another nudge to get the title working and then the meta information to work dynamically depending on the page that is chosen.

Thanks Guru for getting me this far.

Skorpio · March 8, 2013

Which at some point you might as well move the content into the DB and have a cms lol!

That is my intention eventually, I have already put a very basic one together through a tutorial and have ideas of how to plan my own out but first wanted to get this working before I even think of going any further.

Jessica · March 8, 2013

How do you know what row of data you want?

Skorpio · March 8, 2013

You have to tell it WHAT page you want to view the data for.

This is part of my original question, Guru has kindly given me what I have so far but this was part of the original problem I had when I came in here, I am unsure how to specify the page. What I did in my previous code thinking it may solve the issue did nothing at all so on this part I am back to the drawing board.

Do I need to continue with the code Guru provided, i.e.

unset ($row['page_title']) ;  // so it clears the previous title

Then I was thinking maybe adding to the foreach loop but that didn't work so I am stumped at the moment.

Skorpio · March 8, 2013

*

Edited March 8, 2013 by Skorpio

Skorpio · March 8, 2013

How do you know what row of data you want?

I dont know at the moment because if I were to hazard a guess my current code only gives me the first record in the database

Sign In

Load Meta data dynamically from mysql database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information