How Do I Retrieve Content Specific Content Using PHP and MySQL

Question

I am brand new to PHP and MySQL. I watched the Lynda.com tutorial entitled "PHP with MySQL Essential Training". I followed along and converted my static site to a dynamic CMS. This tutorial showed us how to insert content into a database, but just text.

After the course, I was left wondering the following--"How do I get the specific content to render? For example, I have a static page called "Websites". This page contains a couple divs with screenshot images of websites I've developed and the images are linked to those sites. When I created this page in the database I just added some regular text content to the database.

How do I get that html to render that contains the specific content associated with that page? Am I to write some SQL Insert statement into the database inserting the html?

I'd appreciate your guidance. Thanks, Chris

The most common way (that I've seen) is to store the image path in the database with an ID associated with it. When you go to a certain page, you can query the image paths associated with that ID, store them in php variables, and echo / inject (if you have templates) these paths as `href`s within your `img`s. — Mattiavelli, Jul 29 '13 at 19:05

score 0 · Answer 1 · answered Jul 29 '13 at 18:30

0

You would need to write a query, yes.

See the MySQL manual entry for SELECT statements.

SELECT is what you use to fetch data from the Database, INSERT is used when you need to put data inside.

answered Jul 29 '13 at 18:30

Madara's Ghost

172,118
50
264
308

What will the query look like? For example I need the following to render-

– Chris Mazzochi Jul 29 '13 at 18:34

score 0 · Answer 2 · answered Jul 29 '13 at 18:35

For beginner, you just get sample source and study it http://css-tricks.com/php-for-beginners-building-your-first-simple-cms/

Here is video step-by-step' How to Build PHP and MySQL CMS Website Software

After you ran a CMS application properly by yourself, you should learn about concept and pattern, etc.

score 0 · Answer 3 · answered Jul 29 '13 at 18:37

0

You should familiarize yourself with the concepts client side and server side code. PHP is executed on the server and will generate HTML or code in other client side languages. SQL will allow you to do a various amount of things including insert and select.

You should use a SELECT statement to retrieve the corresponding data. SQL is used inside PHP. And will generate the HTML for the client.

To use SQL in PHP you should take a look at a tutorial

answered Jul 29 '13 at 18:37

chrisblomm

314
1
7

I understand. Say I have a page that has an id of '3' in the database. When this page is selected, what I am trying to figure out is how to get the html that is specific to id '3''s content. Do I INSERT the html into the 'content' field in the database associate with the page with the id of '3'? – Chris Mazzochi Jul 29 '13 at 18:41
No, INSERTing is for storage purposes only. When you'd like to retrieve data use SELECT. Within a SELECT query you can choose which fields to selected based on a set of criteria. I.e. SELECT id,content FROM pages WHERE id = '3'; – chrisblomm Jul 30 '13 at 08:45

score 0 · Answer 4 · edited May 23 '17 at 12:05

Here is the most common way to do it:

Create a table for your portfolio, with appropriate columns
Write SQL query to retrieve values from desired columns
"Pour" values into desired HTML containers

An example portfolio table, based on your information provided, might look like:

proj_id (PK) | site_link | screenshot | proj_description

where proj_id is an auto-incremented primary key, site_link (varchar) contains the link to your project, screenshot (varchar) contains a link to your screenshot image and project_description (text or varchar, depending on the length of your description) contains any details you might want to provide about the project, such as technologies used, time taken, your responsibilities in the project etc.

Now, it's just a matter of writing a query to retrieve the appropriate row(s) and "pouring" it into the HTML.

<?php
include("inc/db.inc.php");

$connection = mysqli_connect($host, $user, $pwd, "portfolio");

if(mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query = "SELECT * FROM portfolio WHERE proj_id = 1"; //example query
$result = mysqli_query($connection, $query);

while($row = mysqli_fetch_array($result)) {
    echo '<div class="portfolio" id="proj-'. $row['proj_id'] . '">';
    echo '<a href="' . $row['site_link'] . '">';
    echo '<img src="' . $row['screenshot'] . '"/></a>';
    echo '<p>'. $row['proj_description'] . '</p>';
    echo '</div>';
}

mysqli_close($connection);
?>

Not very elegant, but it should get you started in the right direction. Also, be sure to read more about escaping user input when you start accepting input from your users.

Welcome to the world of PHP & MySQL, happy coding!

Thank you very much Agent.Logic_ for taking the time to provide this solution. I will work it in and see what happens. — Chris Mazzochi, Jul 31 '13 at 20:40

How Do I Retrieve Content Specific Content Using PHP and MySQL

4 Answers4