?
Solved

Displaying Text and Images from SQL database in HTML page

Posted on 2009-04-21
18
Medium Priority
?
543 Views
Last Modified: 2013-12-12
Hi guys,

I want to be able to take data from a database which items which are either text or images.

If text then the text is placed into the database complete with white spaces.

If image then an image ID is stored which links to an image table which contains the filepath. (if its easier this can be changed to have the filepath stored in the main table).

I want to the display the items based on a critera of which page they are on and then show that Page in a simple HTML form.

On any given page there could be several paragraphs of text in an item followed by another item with more text followed by an item with an image.  There can be any combination of text and images with as many as the user has created - practically about 3-8 is likely - if needed this can be capped.

Currently I use a method in PHP to run a query on the database which just returns the text content (havent tried to do the image as have no idea how to integrate it, but do have code to show the image but dont know how to switch content or return different types so it displays).

This method returns the text but doesnt remember line breaks - in the database its stored with line breaks.

So just recap I just want to be able to display text and images from the database in a format similar to this:

////////////////////////////////////////////////////
heading
////////////////////////////////////////////////////
text item
/////
image item
////
text item
////
text item
////////////////////////////////////////////////////

Attached is the code I have for query so far:

Please help and its quite urgent as well.  Happy to give any more information or provide some links etc for anyone that needs it.  Will be around pretty constantly so can provide a quick answer to any question however small/large.

Thanks
PHP:
 
public function BuildPreviewPagesList()
{
$result = $this->mysqlConnection->query('SELECT * FROM chunks INNER JOIN pages ON chunks.id = pages.chunkid WHERE username="' . $Susername . '" AND pages.pageid="' . $currentPage . '" ' . 'ORDER BY pages.orderno ASC');
while ($row = $result->fetch_assoc()) 
{ 
	$myList .= '<li id="' . ($row['id']) . '">' . ($row['content']) . '</li>';
}
 
return $myList;
}
 
HTML:
 
<p>
	<?php 
		$myPagesList = new PagesList(); 
		echo $myPagesList->BuildPreviewPagesList();
	?>
</p>
 
SQL:
 
chunks (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	pageid INT UNSIGNED NOT NULL,
	orderno INT UNSIGNED NOT NULL default '0',
	username VARCHAR(11) NOT NULL,
	title VARCHAR(43) NOT NULL default '',
	content VARCHAR(3000) default '',	
	isimage TINYINT(1) NOT NULL default 0,
	imageid INT UNSIGNED,
	PRIMARY KEY (id),
	INDEX (username)
)
 
create table images (
	imageid INT UNSIGNED NOT NULL AUTO_INCREMENT,
	filepath VARCHAR(500) NOT NULL default '',
	INDEX(imageid)
)

Open in new window

0
Comment
Question by:Vanq69
  • 9
  • 9
18 Comments
 
LVL 3

Expert Comment

by:joshianurag
ID: 24201734
Not very clear on what problem you are facing with the image stuff. On the line breaks not displaying, use nl2br function as follows

echo nl2br($row['content']);
0
 

Author Comment

by:Vanq69
ID: 24201759
The problem is basically how do I display mulitple items when some are type text and some are type image.

All I need it to do is display all the items that match the search query in order and if they are an image then display an image if they are text then display the text.

Does that make it clearer?

Thanks for the nl2br that works fine.
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24201918
Can you give me the schema/structure of the all the tables you are dealing with?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Vanq69
ID: 24202161
Sure they are attached.

To clarify I have also created a simple DB diagram.

Basically a user creates a chunk.  

The chunk can be of type text (text goes in content field with isimage = 0)/
OR the chunk can be of type image (image id goes in imageid field with is image = 1) image table contains the filepath - as i mentioned if easier this can be put into chunks table - the file is uploaded to folder.

That chunk is then added to a page.  Which page the chunk is added to is controlled by top option and secondary options.

When a page is selected (ie when i want to view it) it is done so by the id of the page in pages table.

Hope this clarifies, as I said happy to answer all and every query and will be here from now to do so quickly.
TABLE users ( 
	id INT PRIMARY KEY AUTO_INCREMENT, 
	username VARCHAR(11) UNIQUE NOT NULL,
	password VARCHAR(32) NOT NULL,
	homepageurl VARCHAR(500) NOT NULL default '',
	coursepageurl VARCHAR(500) NOT NULL default '',
	forumpageurl VARCHAR(500) NOT NULL default '',
	curtopoption INT NOT NULL default '0',
	chunkpage INT NOT NULL default '0',
	isedit TINYINT(1) NOT NULL default 0,
	editchunkid INT,
	index (curtopoption)
)
 
TABLE chunks (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	pageid INT UNSIGNED NOT NULL,
	orderno INT UNSIGNED NOT NULL default '0',
	username VARCHAR(11) NOT NULL,
	title VARCHAR(43) NOT NULL default '',
	content VARCHAR(3000) default '',	
	isimage TINYINT(1) NOT NULL default 0,
	imageid INT UNSIGNED,
	PRIMARY KEY (id),
	INDEX (username)
)
 
CREATE TABLE topoptions (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	topoptionname VARCHAR(11) NOT NULL,
	username VARCHAR(11) NOT NULL,
	secoption INT NOT NULL default '0',
	PRIMARY KEY (id),
	INDEX (username)
)
 
TABLE secoptions (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	secoptionname VARCHAR(11) NOT NULL,
	username VARCHAR(11) NOT NULL,
	topoptionid INT NOT NULL default '0',
	curpage INT NOT NULL default '0',
	PRIMARY KEY (id),
	INDEX (topoptionid),
	INDEX (username)
)
 
TABLE pages (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	pageid INT UNSIGNED NOT NULL,	
	orderno INT UNSIGNED NOT NULL default '0',
	chunkid INT UNSIGNED NOT NULL,
	topoptionid INT UNSIGNED NOT NULL,
	secoptionid INT UNSIGNED NOT NULL,
	PRIMARY KEY (id),
	INDEX (chunkid),
	INDEX (secoptionid)
)
 
TABLE images (
	imageid INT UNSIGNED NOT NULL AUTO_INCREMENT,
	filepath VARCHAR(500) NOT NULL default '',
	INDEX(imageid)
)

Open in new window

dbdiagram1.png
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24202415
Hey
You already have a field in the chunks table which tells you if it's text OR image. You can always use the value of this field to display your results.
For example :

if($row["isimage"] == 0)
{
   echo nl2br($row["content"])
}
else
{
   $imageid = $row["content"];
   $query = "select filepath from images where imageid = $imageid";
   $iresult = mysql_query($query);
   $irow = mysql_fetch_array($iresult);
   echo "<img src=".$irow["filepath"].">";
}

please let me know if this helps
0
 

Author Comment

by:Vanq69
ID: 24202449
How would I use that in my implementation, the code looks good but currently i use:

while ($row = $result->fetch_assoc())
{
      $myList .= '<li id="' . ($row['id']) . '">' . (nl2br($row['content'])) . '</li>';
}

return $myList;

then in the HTML use:

<?php
            $myPagesList = new PagesList();
            echo $myPagesList->BuildPreviewPagesList();
?>


If i make it simply return then it doesnt show anything.

Thanks for the help so far.
0
 

Author Comment

by:Vanq69
ID: 24202482
Actually ignore that question, it there was a missing ; after the echo.

This code displays the text fine but doesn't display the image.
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24202491
CHANGE the following code

while ($row = $result->fetch_assoc())
{
      $myList .= '<li id="' . ($row['id']) . '">' . (nl2br($row['content'])) . '</li>';
}


TO

while ($row = $result->fetch_assoc())
{
     if($row["isimage"] == 0)
     {
         $myList .= '<li id="' . ($row['id']) . '">' . (nl2br($row['content'])) . '</li>';
         
     }
     else
     {
          $imageid = $row["content"];
          $query = "select filepath from images where imageid = $imageid";
          $iresult = mysql_query($query);
          $irow = mysql_fetch_array($iresult);
          echo "<img src=".$irow["filepath"].">";
          $myList .= '<li id="' . ($row['id']) . '"><img src="' .$irow['filepath'] . '"></li>';
}      
return $myList;
0
 
LVL 3

Accepted Solution

by:
joshianurag earned 2000 total points
ID: 24202495
ah, i am sorry! I missed a }. Use the following code :

while ($row = $result->fetch_assoc())
{
     if($row["isimage"] == 0)
     {
         $myList .= '<li id="' . ($row['id']) . '">' . (nl2br($row['content'])) . '</li>';
         
     }
     else
     {
          $imageid = $row["content"];
          $query = "select filepath from images where imageid = $imageid";
          $iresult = mysql_query($query);
          $irow = mysql_fetch_array($iresult);
          echo "<img src=".$irow["filepath"].">";
          $myList .= '<li id="' . ($row['id']) . '"><img src="' .$irow['filepath'] . '"></li>';
     }
}      
return $myList;
0
 

Author Comment

by:Vanq69
ID: 24202583
This is working fine for Text but still not displaying the image.

This is the exact code I have (had to change the db calls) and I noticed that $imageid = $row["content"]; should be $imageid = $row["imageid"];

while ($row = $result->fetch_assoc())
{
      if($row["isimage"] == 0)
      {
            $myList .= '<li id="' . ($row['id']) . '">' . (nl2br($row['content'])) . '</li>';
      }
      else
      {
            $imageid = $row["imageid"];
            $iresult = $this->mysqlConnection->query('SELECT filepath ' . 'AS filepath FROM images WHERE images.imageid="' . $imageid . '"');
            $irow = $iresult->fetch_assoc();
            $irow = $row['filepath'];
            echo "<img src=".$irow["filepath"].">";
            $myList .= '<li id="' . ($row['id']) . '"><img src="' .$irow['filepath'] . '"></li>';
      }
}      
return $myList;
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24202646
The possible reason i see here is that actual path where the images are stored and the path stored in the imagepath field are different.
Can you give the folder structure (Directory tree) you have. I mean where your scripts are stored and where you are storing the images.

Also, can you give me a few records from the images table?
0
 

Author Comment

by:Vanq69
ID: 24202682
Ah your right there, I forget this part is in a different folder.

I the path that needs to be added would be "../noteaid/"  I belive.  Its going up a level then down into noteaid

An actual file path from the database is "idimg/8471b_3224687987_2bc5089140_o.png"  as images are stored in the folder idimg.

Sorry for that, should have remembered!
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24202756
No problem.
0
 

Author Comment

by:Vanq69
ID: 24202855
Ok still not displaying the image...

I have:

while ($row = $result->fetch_assoc())
{
      if($row["isimage"] == 0)
      {
            $myList .= '<li id="' . ($row['id']) . '">' . (nl2br($row['content'])) . '</li>';
      }
      else
      {
            $imageid = $row['imageid'];
            $iresult = $this->mysqlConnection->query('SELECT filepath ' . 'AS filepath FROM images WHERE images.imageid="' . $imageid . '"');
            $irow = $iresult->fetch_assoc();
            $filepath = $irow['filepath'];
            $filestart = "../noteaid/";
            $filepath = $filepath.$filestart;
            echo "<img src=".$filepath.">";
            $myList .= '<li id="' . ($row['id']) . '"><img src="' .$filename . '"></li>';
      }
}      
return $myList;
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24202930
ah..i see whats wrong. Replace the following line

$filepath = $filepath.$filestart;

WITH

$filepath = $filestart.$filepath;

ALSO

Replace

$myList .= '<li id="' . ($row['id']) . '"><img src="' .$filename . '"></li>';

WITH

$myList .= '<li id="' . ($row['id']) . '"><img src="' .$filepath . '"></li>';
0
 

Author Comment

by:Vanq69
ID: 24202944
Yay success! Thanks so much!  Cant believe I put that string the wrong way round, tiredness in full flow!
0
 

Author Closing Comment

by:Vanq69
ID: 31573123
Great help and also helped to trouble shoot after.
0
 
LVL 3

Expert Comment

by:joshianurag
ID: 24202954
No problems :)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question