467,144 Members | 1,173 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,144 developers. It's quick & easy.

Linking Image Table w/ Text Table

tharden3
512MB
Hey Bytes,
The website I'm working on is coming along just fine, and I'd like to thank all of you PHP folks who have been helping me out. I'm almost done with the coding!

I'm trying to get the data-basing code finished with. I've got my products, with lines of text next to it that serve as descriptions. With each of those entries, I have images. I've implemented the code for displaying the text, and I've also implemented the code for the images, but here is my problem:

The code is separate. The images are queried and displayed to the user in a list formation, and so is the text, but I don't have a clue how to put the two together.

Here is my code for displaying the text components:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. include('books_login.php');
  3. $connection=mysql_connect($mysql_host,$mysql_user,$mysql_password);
  4. if(!$connection){
  5.    die("Could not connect to the database: <br/>". mysql_error());
  6. }
  7. $db_select=mysql_select_db($mysql_database);
  8. if(!$db_select){
  9.    die("Could not select the database: <br/>".mysql_error());
  10. }
  11.  
  12. // how many rows to show per page
  13. $rowsPerPage = 10;
  14.  
  15. // by default we show first page
  16. $pageNum = 1;
  17.  
  18. // if $_GET['page'] defined, use it as page number
  19. if(isset($_GET['page']))
  20. {
  21.     $pageNum = $_GET['page'];
  22. }
  23.  
  24. // counting the offset
  25. $offset = ($pageNum - 1) * $rowsPerPage;
  26.  
  27. $query = " SELECT * FROM air_registers" .
  28.          " LIMIT $offset, $rowsPerPage";
  29. $result = mysql_query($query) or die('Error, query failed');
  30.  
  31.  
  32. while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
  33.    $type=$row["Type"];
  34.    $number=$row["Number"];
  35.    $desc=$row["Description"];
  36.    echo "<div id='type'><strong>Model Type: </strong>$type</div>";
  37.    echo "<div id='number'><strong>Model #: </strong>$number</div>";
  38.    echo "<br />";
  39.    echo "<div id='desc'><strong>Description: </strong>$desc</div>";
  40.    echo "<br />";
  41.    echo "<hr>";
  42. }
  43. // how many rows we have in database
  44. $query   = "SELECT COUNT(*) AS numrows FROM air_registers";
  45. $result  = mysql_query($query) or die('Error, query failed');
  46. $row     = mysql_fetch_array($result, MYSQL_ASSOC);
  47. $numrows = $row['numrows'];
  48.  
  49. // how many pages we have when using paging?
  50. $maxPage = ceil($numrows/$rowsPerPage);
  51.  
  52. // print the link to access each page
  53. $self = $_SERVER['PHP_SELF'];
  54. $nav  = '';
  55.  
  56. for($page = 1; $page <= $maxPage; $page++)
  57. {
  58.    if ($page == $pageNum)
  59.    {
  60.       $nav .= " $page "; // no need to create a link to current page
  61.    }
  62.    else
  63.    {
  64.       $nav .= " <a href=\"$self?page=$page\">$page</a> ";
  65.    }
  66. }
  67. // ... the previous code
  68.  
  69. // creating previous and next link
  70. // plus the link to go straight to
  71. // the first and last page
  72.  
  73. if ($pageNum > 1)
  74. {
  75.    $page  = $pageNum - 1;
  76.    $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
  77.  
  78.    $first = " <a href=\"$self?page=1\">[First Page]</a> ";
  79. }
  80. else
  81. {
  82.    $prev  = '&nbsp;'; // we're on page one, don't print previous link
  83.    $first = '&nbsp;'; // nor the first page link
  84. }
  85.  
  86. if ($pageNum < $maxPage)
  87. {
  88.    $page = $pageNum + 1;
  89.    $next = " <a href=\"$self?page=$page\">[Next]</a> ";
  90.  
  91.    $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
  92. }
  93. else
  94. {
  95.    $next = '&nbsp;'; // we're on the last page, don't print next link
  96.    $last = '&nbsp;'; // nor the last page link
  97. }
  98.  
  99. // print the navigation link
  100. echo $first . $prev . $nav . $next . $last;
  101.  
  102. mysql_close($connection);
  103. ?>
This works fine and displays my text information.

Here is the code for the image output to the user:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. include('books_login.php');
  3. include('pix.php');
  4. $connection=mysql_connect($mysql_host,$mysql_user,$mysql_password);
  5. if(!$connection){
  6.    die("Could not connect to the database: <br/>". mysql_error());
  7. }
  8. $db_select=mysql_select_db($mysql_database);
  9. if(!$db_select){
  10.    die("Could not select the database: <br/>".mysql_error());
  11. }
  12.  
  13. $strSQL = "select * from pix";
  14. $rsPix = mysql_query($strSQL);
  15. $numRows = mysql_numrows($rsPix);
  16. $i = 0;
  17.  
  18. while($i < $numRows){
  19. ?>
  20. <img src="pix.php?pixID=<?php echo mysql_result($rsPix,$i,"pixID"); ?>"/>
  21. <?php
  22. $i++;
  23. }
  24. ?>
  25.  
  26.  
And the code above refers to pix.php:
Expand|Select|Wrap|Line Numbers
  1. <?php 
  2. include('books_login.php');
  3. $connection=mysql_connect($mysql_host,$mysql_user,$mysql_password);
  4. if(!$connection){
  5.    die("Could not connect to the database: <br/>". mysql_error());
  6. }
  7. $db_select=mysql_select_db($mysql_database);
  8. if(!$db_select){
  9.    die("Could not select the database: <br/>".mysql_error());
  10. }
  11. if (IsSet($_GET['pixID'])){
  12. $gotten = @mysql_query("select imgdata from pix where pixID = ".$_GET['pixID']);
  13. header("Content-type: image/jpeg");
  14. while ($row = mysql_fetch_array($gotten,MYSQL_ASSOC))
  15. {
  16. print $row['imgdata'];
  17.  
  18. }
  19. mysql_free_result($gotten);
  20. }
  21. ?>
I have two tables in my mysql DB:
-one for the images (composed of a BLOB field and an int field for Unique Key)
-one for the text (composed of three or four varchar fields and an int field for Unique Key)

I believe I'm supposed to link these tables together using keys... is that correct? Then maybe I could write code that will utilize the two linked tables? I'm not sure what direction I should go here.
Feb 8 '09 #1
  • viewed: 1826
Share:
8 Replies
tharden3
512MB
You know, I think I almost have this figured out. I'll post new code when I solve the problem.
Feb 8 '09 #2
Markus
Expert 4TB
Assuming your ID (unique key) columns are a reliable relationship between the tables, simply perform a query to get your image data (as well as getting it's ID). Then use this ID in your second query for the details (using the ID as a WHERE clause).

You get me? Sorry if I'm a little hazy, I was at a wedding yesterday.
Feb 8 '09 #3
tharden3
512MB
Hey Markus, thanks for the reply.

I've tried instead just making one table with all of my different items (I merged the imgdata with all of my varchars into one table). Is this being lazy? I understand that this method is poor normalization, but it seems easiest for me right now. Here is what I tried (and its not working):
Expand|Select|Wrap|Line Numbers
  1. $query = " SELECT * FROM air_registers" .
  2.          " LIMIT $offset, $rowsPerPage";
  3. $result = mysql_query($query) or die('Error, query failed');
  4.  
  5.  
  6. while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
  7.    $thumb=$row["Thumbnail"];
  8.    $type=$row["Type"];
  9.    $number=$row["Number"];
  10.    $desc=$row["Description"];
  11.    $i = 0;
  12.    echo "<div id='type'><strong>Model Type: </strong>$type</div>";
  13.    echo "<div id='number'><strong>Model #: </strong>$number</div>";
  14.    echo "<br />";
  15.  
  16.    echo '<img src="pix.php?pixID=<?php echo mysql_result($result,$i,"pixID"); ?>"/>';
  17.  
  18.    echo "<br />";
  19.    echo "<div id='desc'><strong>Description: </strong>$desc</div>";
  20.    echo "<br />";
  21.    echo "<hr>";
  22.    $i++;
Everything in bold is what was taken from my old list.php file. I know this is sloppy, but I'm trying to make it work. I'm very unhappy with the way it looks now, so more than likely I'll try to query two different tables (like you suggested Markus).
Feb 8 '09 #4
tharden3
512MB
Hey all,

I ended up just dropping the images from my database. I put them in the file manager, and I'm using this bit of code to pull them out and onto the page:
Expand|Select|Wrap|Line Numbers
  1. echo "<img src=\"".$pixID."\" />"
$pixID is a field in my database that refers to the image name. This works fine for now, and I'll look into using the BLOB field later. I'm going to look through a lot of documentation and tutorials before I try that method again (the method that utilizes a BLOB field with images in the database instead of the file manager).

Question:
If I have my images in the file manager, can I still implement an extension that will automatically re-size my pictures? I was looking at a GD extension provided by Atli in post #2 of this thread. Can I still write a PHP script that will change the picture size before it is rendered using this GD extension? Or is that a problem because my pictures are on the file manager instead of the MySQL database?
Feb 11 '09 #5
Markus
Expert 4TB
@tharden3
You can still use GD on files that are located on your server. Although, providing 'on-the-fly' resized images is very resource intensive. The smarter thing to do is save a resized version and the original.
Feb 11 '09 #6
tharden3
512MB
@Markus
Ok, thats what I've been doing for now. I have a "full version" of each image and a re-sized "thumbnail" version. I've been using GIMP to re-size each image before I put it on the server. Is that ok for now? Or is there something quicker I could be doing? Anything to cut down on my production time is welcome ;)
Feb 11 '09 #7
Markus
Expert 4TB
@tharden3
You could cut out GIMP and upload the images through a form, which PHP would then create a resized image, and save them both.
Feb 11 '09 #8
tharden3
512MB
@Markus
I'll try this out and post my results.
Feb 11 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

15 posts views Thread by Rob Ratcliff | last post: by
1 post views Thread by Daveyk0 | last post: by
11 posts views Thread by dskillingstad@gmail.com | last post: by
5 posts views Thread by chrisse_2@hotmail.com | last post: by
1 post views Thread by kavithadevan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.