Extract info from MySQl db

PHP
1

Hi everyone,

I’m trying to wrap my head around this whole “database-driven website” thing. At the moment I have a responsive website, where I’m displaying a number of items. A catalogue of sorts. Each item consists of a name, an image, a table, and a div with a few paragraphs. I’m now attempting to put all of the text into a MySQl database. I have figured out how to create a database, tables, etc but I’m not sure how to extract the text from the database into the website. I assume that I need to have one template which includes the code for my item name, table, div etc?

This one template will then create the html code (the catalogue) and populate that code with the text from the database? Am I on the right track?

The number of paragraphs in my individual divs might vary. So how will my template know that I might need 2 paragraphs for my first record and 3 paragraphs for my second record?

I hope someone could please help me out.

Thank you! :slight_smile:

2

Hi RedBishop,

I think you’re on the right line there… if you’re dealing with catalogue items, I’d store certain pieces of data in their own fields (name, reference number, price etc) to allow them to be searched on, and the main body text for the item in a separate field.

When you store the body text for each item in the DB, you can include the inline markup (such as paragraphs and lists). This is how CMSs like Wordpress save post content. Just avoid including any of the containing HTML - it’s best to keep that in your template.

3

Hi fretburner,

thank you so much for your help - again!

I’ll will try and do that.

Just one question: Do you think I need to set up a while loop to create my catalogue items?

Thank you for your time.

4

If you’re building some sort of list page with multiple items then yeah, you’ll want to loop over a set of rows and populate your template. If you’ve more than a handful of items, you’ll want to think about pagination too.

5

fretburner,

once again, thanks for your quick reply.

I’ll give it try.

Ciao

6

Hi all & fretburner,

so I’m attempting to create my template into which my info from a database will be inserted. I need some pointers please! I’ve added some information into a table but how exactly do I proceed from here?

My original code on my website includes something like this:

<div class=“container”>
<p class=“name”></p>
<div class=“cat-img”>
<img src=“…/…/small2.jpg” title=“” alt=“”>
</div>
</div>

I’ve included <p class=“name”></p> in my example PHP code, but it only works when I remove the quotation marks from “name”, but then the text is no longer styled.

<?php
// Connects to your Database
mysql_connect(“localhost”, “username”, “password”) or die(mysql_error());
mysql_select_db(“Database_Name”) or die(mysql_error());
$data = mysql_query(“SELECT * FROM table”)
or die(mysql_error());

while($info = mysql_fetch_array( $data ))
{

Print “<p class=“name”>”.$info[‘name’] . " </p>";

}

?>

Thank you,
PHP challenged.

7

There are several ways you can output HTML with PHP variables:


// Escape the double quotes with backslashes
echo "<p class=\\"name\\">" . $info['name'] . "</p>";

// Replace the attribute double quotes with single quotes
echo "<p class='name'>" . $info['name'] . "</p>";

// Use single quotes around the string
echo '<p class="name">' . $info['name'] . '</p>';

When double-quoting strings, you can include variables within the string and PHP will automatically substitute them with their values:


echo "<p class=\\"name\\">$info[name]</p>";

echo "<p class='name'>{$info['name']}</p>";

Note that as we’re using an array within the string, we either need to remove the quotes from around the array key, or wrap the whole variable in curly braces so PHP doesn’t get confused.

8

Hi again,

that worked - thank you for all of the info. I really appreciate it.

If I use print or echo, do I need to include the term in front of every line or can I use it once to output the whole block?

<div class=“container”>
<p class=“name”></p>
<div class=“cat-img”>
<img src=“…/…/small2.jpg” title=“” alt=“”>
</div>
</div>

Thank you.

9

Yeah, you can output a whole block with a single echo statement if you want, but if you’re going to output a big chunk of HTML you can always step out of PHP temporarily - if you’ve more markup than variables to output this can be more readable:


<?php

// Query DB and prepare data etc...

// Step out of PHP and output HTML directly:
?>

<div class="container">
    <p class="name"><?php echo $item['name'] ?></p>
    <div class="cat-img">
        <img src="/images/<?php echo $item['image_url'] ?>" title="" alt="">
    </div>
</div>

<?php
// Back into PHP code..
10

Ok I got it.

Thanks for helping me out! Have a great day!