Skorpio Posted March 7, 2013 Share Posted 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.phphome.php Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/ Share on other sites More sharing options...
Barand Posted March 7, 2013 Share Posted March 7, 2013 When you use SELECT * no-one can tell what you are selecting and thereby get an idea of your table structure. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417317 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted March 7, 2013 The table structure is as the variables, that is every field in the dbase. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417318 Share on other sites More sharing options...
Barand Posted March 7, 2013 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417320 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted March 7, 2013 The field names are 'page_title'; etc and then I have created variables to store the data from the records Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417322 Share on other sites More sharing options...
mikosiko Posted March 7, 2013 Share Posted 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) Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417324 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417328 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417329 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted March 7, 2013 (edited) <?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 Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417330 Share on other sites More sharing options...
Barand Posted March 7, 2013 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417336 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417349 Share on other sites More sharing options...
Barand Posted March 7, 2013 Share Posted 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" ; } Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417352 Share on other sites More sharing options...
Skorpio Posted March 7, 2013 Author Share Posted 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"; Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417354 Share on other sites More sharing options...
Barand Posted March 7, 2013 Share Posted 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);" Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417363 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417389 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted 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" /> Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417393 Share on other sites More sharing options...
Jessica Posted March 8, 2013 Share Posted March 8, 2013 You have to tell it WHAT page you want to view the data for. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417398 Share on other sites More sharing options...
Barand Posted March 8, 2013 Share Posted March 8, 2013 As Jessica said You have to give a value to $meta_id to tell it which record to select. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417399 Share on other sites More sharing options...
Jessica Posted March 8, 2013 Share Posted March 8, 2013 Which at some point you might as well move the content into the DB and have a cms lol! Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417401 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417403 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417404 Share on other sites More sharing options...
Jessica Posted March 8, 2013 Share Posted March 8, 2013 How do you know what row of data you want? Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417405 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417406 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted March 8, 2013 (edited) * Edited March 8, 2013 by Skorpio Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417408 Share on other sites More sharing options...
Skorpio Posted March 8, 2013 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/275384-load-meta-data-dynamically-from-mysql-database/#findComment-1417409 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.