[RESOLVED] Attempting to display all information and Image from a database

mrw2020

Hi Everyone

I'm trying to retrieve an image and other information from a database and display it in a table. This worked well with another page after recieving excellent advice from this forum. But for some reason I can't display more than 4 records (there are five in the table). The other big problem is I don't know how to display an image file in the table.

Any help would be greatly appreciated.

Here is the code:

for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if 
	   ($col == 1) {
       $cell += $i;
        echo $records[$cell - 1]['pi_image'].'<br />';
       echo '<strong class="navtext">'.$records[$cell - 1]['pi_title'].'</strong><br />';
       echo $records[$cell - 1]['pi_des'].'<br />';
       } else {
        $cell += $norows;
       echo $records[$cell - 1]['pi_image'].'<br />';
       echo '<strong class="navtext">'.$records[$cell - 1]['pi_title'].'</strong><br />';
       echo $records[$cell - 1]['pi_des'].'<br />';

   }
   echo"</td>";
}
  echo"</tr>";
}
?>

boufos

Try to use the while loop:

while($row = mysql_fetch_array($result))
{
  extract($row);

  etc
}

bradgrafelman

boufos;10968645 wrote:
Try to use the while loop:
while($row = mysql_fetch_array($result))
{
  extract($row);

  etc
}

I doubt that's even possible (or sensible) based on the manner in which the data is being displayed.

@: Where do you define $norows and $nocol?

mrw2020

Sorry about that, I didn't put the full code in - this is the original code that works beautifully for a different page and a different table without images, I would like to use the same tabular format but with images added. I have uploaded images in an 'uploads' file as BLOB objects. Any help would be greatly appreciated and thank you for your comments so far


require_once ('../mysql_connect.php'); // Connect to the db.

// Number of records to show per page:
$display = 15;

// Determine how many pages there are. 
if (isset($_GET['np'])) { // Already been determined.

$num_pages = $_GET['np'];

} else { // Need to determine.

// Count the number of records
$query = "SELECT COUNT(*) FROM retailers ORDER BY ret_name ASC";
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];

// Calculate the number of pages.
if ($num_records > $display) { // More than 1 page.
	$num_pages = ceil ($num_records/$display);
} else {
	$num_pages = 1;
}

}

// Determine where in the database to start returning results.
if (isset($_GET['s'])) {
	$start = $_GET['s'];
} else {
	$start = 0;}

// Make the query:
$q = "SELECT ret_name,ret_add1,ret_add2,ret_town,ret_county,ret_pcode,ret_phone,ret_email,ret_web FROM retailers ORDER BY ret_name ASC LIMIT $start, $display";        

$result = mysql_query ($q); // Run the query.Define the $result variable and display or send error message

  while ($rows = mysql_fetch_array($result))
  $records[] = $rows;

$total = count($records);
  $nocol = 3;
  $norows = $total / $nocol;


for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if 
	   ($col == 1) {
       $cell += $i;
       echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
       echo $records[$cell - 1]['ret_add1'].'<br />';
       echo $records[$cell - 1]['ret_add2'].'<br />';
       echo $records[$cell - 1]['ret_town'].'<br />';
       echo $records[$cell - 1]['ret_county'].'<br />';
       echo $records[$cell - 1]['ret_pcode'].'<br />';
       echo $records[$cell - 1]['ret_phone'].'<br />';
       echo $records[$cell - 1]['ret_email'].'<br />';
       echo $records[$cell - 1]['ret_web'].'<br />';
       } else {
        $cell += $norows;
       echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
       echo $records[$cell - 1]['ret_add1'].'<br />';
       echo $records[$cell - 1]['ret_add2'].'<br />';
       echo $records[$cell - 1]['ret_town'].'<br />';
       echo $records[$cell - 1]['ret_county'].'<br />';
       echo $records[$cell - 1]['ret_pcode'].'<br />';
       echo $records[$cell - 1]['ret_phone'].'<br />';
       echo $records[$cell - 1]['ret_email'].'<br />';
       echo $records[$cell - 1]['ret_web'].'<br />';

   }
   echo"</td>";
}
  echo"</tr>";
}


  mysql_close($dbc);
  unset($dbc);

  // Make the links to other pages, if necessary.
if ($num_pages > 1) {

echo '<br /><p class="navtext">';
// Determine what page the script is on.	
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page != 1) {
	echo '<a href="shops.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
}

// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
	if ($i != $current_page) {
		echo '<a href="shops.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
	} else {
		echo $i . ' ';
	}
}

// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
	echo '<a href="shops.php?s=' . ($start + $display) . '&np=' . $num_pages . '" class="navtext">Next</a>';
}

echo '</p>';

} // End of links section.

kernel-stack

If 'pi_image' is a database entry with an url (ex. http://address/images/image.png), you could wrap an img src around it. Like:

echo '<img src="' .$result[$cell - 1]['pi_image']. '"/><br />';

mrw2020

Brilliant - what a great idea!, I knew there must be a way but just couldn't figure it out. I'll test it out and let you know how it goes. Many thanks for all your help

mrw2020

I've tried the code suggested by kernal-stack but unfortunately it still doesn't show the image, here is the adapted code: It displays everything except the image does anyone have any ideas? Or is this something that's not possible - the page is in the same directory as the uploads folder. Any help would be appreciated

for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if 
	   ($col == 1) {
       $cell += $i;
       echo '<img src="uploads/' .$result[$cell - 1]['ber_image']. '"/><br />';
       echo $records[$cell - 1]['ber_title'].'<br />';
       echo $records[$cell - 1]['ber_des'].'<br />';
       } else {
        $cell += $norows;
       echo '<img src="uploads/' .$result[$cell - 1]['ber_image']. '"/><br />';
       echo $records[$cell - 1]['ber_title'].'<br />';
       echo $records[$cell - 1]['ber_des'].'<br />';
       }
       echo"</td>";
    }
      echo"</tr>";
}

xxdalexx

It could be just a path error from the directory your php file is in relation to where the image folder is. After you load the page view the source code to see the html and look at the path its showing for the image. Depending on the relation of where the php document is and the image folder you may have to back up a folder in the path or something along those lines to get the correct path.

Edit: Also could be something like you have two "/" in a row depending on how you have the image path in you DB.

mrw2020

Because it's wrapped in php code, I can't see the path information in 'view source mode' in my browser, am I correct in this or am I missing something? Many thanks for your input

mrw2020

sorry about that, I've been using Firefox as a browser which doesn't show the code but IE does and this is what the source is, it's pointing to the correct folder and path but I think there must be something wrong with the php code, does anyone have any ideas?

<td valign="top" bgcolor="#FBF4D7">
<table border="0" cellpadding="5" class="maincopy">
<tr>

<td><img src="uploads/"/><br />
Cranberries<br />
Just 100% cranberries!<br />
</td>

<td>
<img src="uploads/"/><br />
Raspberry<br />
Just a 100% raspberry<br />
</td></tr><tr>

<td>
<img src=uploads/><br />
Blackcurrants<br />
Just 100% blackcurrants!<br />
</td>

<td>
<img src="uploads/"/><br />
Mango Chunks<br />
Delicious mango chuncks<br />
</td>

<td>
<img src="uploads/"/><br />
Strawberries<br />
Just 100% organic strawberries!.<br />
</td>

</tr> 
</table>
 </td>

Here is the php code for the image:

for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if 
	   ($col == 1) {
       $cell += $i;
       echo '<img src="uploads/' .$result[$cell - 1]['ber_image']. '"/><br />';
       echo $records[$cell - 1]['ber_title'].'<br />';
       echo $records[$cell - 1]['ber_des'].'<br />';
       } else {
        $cell += $norows;
       echo '<img src="uploads/' .$result[$cell - 1]['ber_image']. '"/><br />';
       echo $records[$cell - 1]['ber_title'].'<br />';
       echo $records[$cell - 1]['ber_des'].'<br />';
       }
       echo"</td>";
    }
      echo"</tr>";
}

kernel-stack

Are the images in the 'uploads' folder? If yes, the way I understand it, the 'ber_image' column should have a text entry with the full image name like "raspberries.png" for your code to work.

mrw2020

Sorry I haven't made myself very clear, this application has several parts:

1)
A CMS application written in php for adding items into a database, editing and deleting images and information on a variety of products.

2)
Retrieving that information dyanamically and displaying it on public pages. That means that I cannot name the image files directly, they have to be dynamically retrieved via the database BLOG item and linked to the images in the uploads folder, this work fine when I use just a simple table layout like the following:

/ Make the query:
$q = "SELECT sau_id,sau_image,sau_title,sau_des FROM sauces ORDER BY sau_title ASC LIMIT $start, $display";        

$result = mysql_query ($q); // Run the query.Define the $result variable and display or send error message

// Table header
echo '<table align="left" cellspacing="5" cellpadding="5" class="maincopy">
    <tr>
	<td colspan="2"><p class="maintitle">Our Current Range of Sauces</p></td>
    </tr>
    ';

// Fetch and print all records.
while ($row = mysql_fetch_array($result)) {
echo '<tr valign="middle">
		<td align="left"><h2>' . $row['sau_title'] . '</h2><p>' . "<img src=uploads/{$row['sau_image']} />" . '</p>
							</td>
		<td align="left">' . $row['sau_des'] . '</td>
    </tr>
    ';
}


echo '</table>';

But I'm wanting it to look cleaner and more apealing on the public side that's why I would like to make the three column cell version work. Any Ideas anyone?

xxdalexx

Exactly what text is contained in ber_image in your db, because judging by the html source you posted it doesn't contain anything. For this to work

 echo '<img src="uploads/' .$result[$cell - 1]['ber_image']. '"/><br />';

ber_image needs to have a text entry of the file name for the image in your uploads folder.

For the other piece of code you listed that you said did work, if you want to have it in 3 columns you can sort of combine the way you did it with the other way by listing the 3 pieces of information in a cell, then set up a count loop.

$i ++
//<td>your code adding to the cell</td>

if ($i == 3)
   {
      echo "</tr><tr>";
      $i = 0;
   }

Doing it this way, every three cells you build in the while loop, it adds the row end and row start, effectively giving you 3 columns.
And a side note, for firefox, just right click on the page and select view page source.

mrw2020

Yes you're quite right there are three fields in the table I want to display,

Image field (only an image)
title field
Description field

The problem is that I don't have the skills to combine the two pieces of code as you suggest, I'm assuming that I would have to declare the $i variable something like:


$i= 1;
while ($row = mysql_fetch_array($result)) {
    $i ++
'<td align="left"><h2>' . $row['sau_title'] . '</h2>
	<p>' . "<img src=uploads/{$row['sau_image']} />" . '</p>
   ' . $row['sau_des'] . '</td><br />
      ';

}


if ($i == 3)
   {
      echo "</tr><tr>";
      $i = 0;
   }

I always have problems knowing where to put those darn curly braces, I have tried the above code without success, any thoughts as to how I can code this correctly?

xxdalexx

Your getting there. I took your code and cleaned it up, and fixed some syntax errors for you.

$i= 0;
while ($row = mysql_fetch_assoc($result))
{
   $i ++
   echo "<td align='left'><h2>" . $row['sau_title'] . "</h2><p>" . "<img src=uploads/" . $row['sau_image'] . "/></p>" . $row['sau_des'] . "</td>";

if ($i == 3)
   {
      echo "</tr><tr>";
      $i = 0;
   } 
}

These brackets {} are used for if, while, foreach, and so on, for when you have more then one argument to accomplish duration of these statements. Like if you have 3 things you need to do when you have an if statement and it returns true. My explanation is probably confusing if someone else can word it better for me, but I would defiantly recommend some reading up on the while and if arguments, and maybe html tables from the way you wrote some of them out.

mrw2020

I agree the tables are a bit bad but I inherited the site and it was littered with tables within cells within tables - it was a bit of a nightmare, I prefer to work with css to place elements. Thank you for your response, I really do appreciate it however I must be doing something wrong as it still doesn't work and I can't display the page at all.

The following code works fine and shows the image, title and description, do you have any ideas how I can adapt this code so that it display's each record in a table cell within a 3 column table? Any help would be greatly appreciated.

// Make the query:
$q = "SELECT sau_id,sau_image,sau_title,sau_des FROM sauces ORDER BY sau_title ASC LIMIT $start, $display";        

$result = mysql_query ($q); // Run the query.Define the $result variable and display or send error message

// Table header
echo '<table align="left" cellspacing="5" cellpadding="5" class="maincopy">
    <tr>
	<td colspan="2"><p class="maintitle">Our Current Range of Sauces</p></td>
    </tr>
    ';

// Fetch and print all records.
while ($row = mysql_fetch_array($result)) {
echo '<tr valign="middle">
		<td align="left"><h2>' . $row['sau_title'] . '</h2><p>' . "<img src=uploads/{$row['sau_image']} />" . '</p>
							</td>
		<td align="left">' . $row['sau_des'] . '</td>
    </tr>
    ';
}


echo '</table>';

mrw2020

I have now resolved the problem of displaying images using a version of the folllowing code:

$total = count($records);
  $nocol = 3;
  $norows = $total / $nocol;

for ($i=1; $i <= $norows; $i++) {
    $cell = 0;
    echo "<tr>";
    for($col=1; $col <= $nocol; $col++) {
       echo "<td>";
       if 
	   ($col == 1) {
       $cell += $i;

   echo '<strong class="navtext">'.$records[$cell - ]['ret_name'].'</strong><br />';
   echo $records[$cell - 1]['ret_add1'].'<br />';
   echo $records[$cell - 1]['ret_add2'].'<br />';
   echo $records[$cell - 1]['ret_town'].'<br />';
   echo $records[$cell - 1]['ret_county'].'<br />';
   echo $records[$cell - 1]['ret_pcode'].'<br />';
   echo $records[$cell - 1]['ret_phone'].'<br />';
   echo $records[$cell - 1]['ret_email'].'<br />';
   echo $records[$cell - 1]['ret_web'].'<br />';
   } else {
    $cell += $norows;
   echo '<strong class="navtext">'.$records[$cell - 1]['ret_name'].'</strong><br />';
   echo $records[$cell - 1]['ret_add1'].'<br />';
   echo $records[$cell - 1]['ret_add2'].'<br />';
   echo $records[$cell - 1]['ret_town'].'<br />';
   echo $records[$cell - 1]['ret_county'].'<br />';
   echo $records[$cell - 1]['ret_pcode'].'<br />';
   echo $records[$cell - 1]['ret_phone'].'<br />';
   echo $records[$cell - 1]['ret_email'].'<br />';
   echo $records[$cell - 1]['ret_web'].'<br />';

   }
   echo"</td>";
}
  echo"</tr>";
}

However it only displays records in multiples of three! for example if you have 45 records that's fine as it's divisable by three colums, but if you have 47 records it doesn't display 47 it only displays 45 does any one have any ideas as to why this is? Any help would be appreciated.

xxdalexx

That's because your limiting it with $norows. Recommend setting $norows with using ceil()

mrw2020

Hi xxdalexx

Sorry - you've completly lost me there, I marginally understand the 'ceil' thingy and I have a vague idea that I need to get $norows into a connection with the pagination script, unfortunately I don't think I have the skills to do it! Is there a turorial or something that could point me in the right direction?😕

xxdalexx

Using your example with 47 records, 47/3 = 15.666667, so on the loop its only running the loop 15 times, leaving out the last 2 records. ceil() rounds up the number to the next whole number, so in this case 16, giving you one more loop which includes you final 2 records that are missing with out it.

$norows = ceil($total / $nocol);