2 Replies - 386 Views - Last Post: 14 July 2015 - 10:34 PM

#1 razer65  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 38
  • Joined: 14-July 15

How to select a specific piece of data in mySQL?

Posted 14 July 2015 - 10:13 PM

I am trying to create a trending list on my webpage, where a picture is displayed, then the caption, a picture is displayed, and then the caption. When a user clicks on the picture, they will be directed to the article. I want this trending list to be updated each time I insert an article, but instead of going inside the code and editing each piece of the list, the code will automatically update with the new article prepended. The list will be 3 items long. The way I want to submit an article is through a form only I can access, where I need to submit parameters like "link", "image address", "caption", when I click submit, I want this information to be stored in a database. I understand this part of the system, but I don't understand how I'm going to select each specific piece of data in the database and assign it to an HTML element.

For example:

id caption image address link
1 "Hello" hello.png www.example.com/this/hello
2 "Hi" hi.png www.example.com/this/hi
3 "bonjour" bonjour.png www.example.com/this/bonjour

<ul>
<li class="1"><a class="1" href=""><img class="1" src=""/></a></li>
<li class="2"><a class="2" href=""><img class="2" src=""/></a></li>
<li class="3"><a class="3" href=""><img class="3" src=""/></a></li>
</ul>

Please help, thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: How to select a specific piece of data in mySQL?

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5073
  • View blog
  • Posts: 13,697
  • Joined: 18-April 07

Re: How to select a specific piece of data in mySQL?

Posted 14 July 2015 - 10:26 PM

Well this depends on the language you are using to generate your pages. You can't do this simply with HTML. You will need a language like PHP or ASP.NET to query the database of articles, fetch the first three (sorted by the most recent date in descending order) and loop through the results, printing each list item.

For example in PHP you can try something along these lines...

$sth = $dbh->prepare("SELECT name, picture_file, url FROM articles ORDER BY created_date DESC LIMIT 3");
$sth->execute();


echo '<ul>';

// Loop through each row in the result fetched from database and print it as a list item.
while ($row = $sth->fetch()) {
  echo "<li><a href=\"{$row['url']}\"><img src=\"{$row['picture_file']}\"></a></li>";
}
echo '</ul>';




This code above is an example to show you how to think about it. Here $dbh would be a PDO object, $sth is a PDO statement object. If you are using PHP look up the PDO statement and PDO object classes for more information on what I am doing here. :)

Edit: You can of course use fetchall() method too and loop through that. Either way should work and both are covered in the PHP documentation site for PDO Statements.

This post has been edited by Martyr2: 14 July 2015 - 10:34 PM

Was This Post Helpful? 0
  • +
  • -

#3 razer65  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 38
  • Joined: 14-July 15

Re: How to select a specific piece of data in mySQL?

Posted 14 July 2015 - 10:34 PM

View PostMartyr2, on 14 July 2015 - 10:26 PM, said:

Well this depends on the language you are using to generate your pages. You can't do this simply with HTML. You will need a language like PHP or ASP.NET to query the database of articles, fetch the first three (sorted by the most recent date in descending order) and loop through the results, printing each list item.

For example in PHP you can try something along these lines...

$sth = $dbh->prepare("SELECT name, picture_file, url FROM articles ORDER BY created_date DESC LIMIT 3");
$sth->execute();


echo '<ul>';

// Loop through each row in the result fetched from database and print it as a list item.
while ($row = $sth->fetch()) {
  echo "<li><a href=\"{$row['url']}\"><img src=\"{$row['picture_file']}\"></a></li>";
}
echo '</ul>';




This code above is an example to show you how to think about it. Here $dbh would be a PDO object, $sth is a PDO statement object. If you are using PHP look up the PDO statement and PDO object classes for more information on what I am doing here. :)/>




Hmm, I think I'm getting it a bit now, I'm going to read some more about PDO, and understand a bit, then i'll try to create it. Thanks so much!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1