php sql table help

[mleeadams]

Hi, New here.

 

I am also relatively new to sql & php. The code below shows a 1 colmn of images with captions.

Example http://www.spain-holiday-sun.com/holidays/templates/english/public/test.php

 

Could someone be kind enough to show me a working example, using the code below to show the same pictures but in 2 columns using a table.

 

Thanks in advance.

 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';

// Fetch the data for the pictures
$sql = "SELECT `photo_id`, `photo_caption_1` , `photo_listing`
        FROM `listing_photo`
	WHERE `photo_listing`= 127
	LIMIT 10";
$result = mysql_query($sql) or trigger_error(mysql_query(), E_USER_ERROR);

// Display each picture
while($row = mysql_fetch_assoc($result)){
    $src = $file_path . $row['photo_id'] . ".jpg";

?>

    <div class="Image">
    <img src="<?=$src?>" alt="<?=$row['photo_caption_1']?>" title="<?=$row['photo_caption_1']?>"><br>
        <span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000"> 
        <?=$row['photo_caption_1']?>
        </font></span> </div>

<?php } ?>

[harristweed]

Put each photo with text in to a div and position with  CSS

 

http://www.starvillasjavea.com/4_bed_villas.php

[Wolphie]

I think that's what you're after.

 

<?php
// Database connection info

// Execute query to retrieve data
$sql = mysql_query("SELECT column1 FROM tblname");

if (mysql_num_rows($sql) > 0) {
  // Default numver of columns
  $num_cols = 2;
  
  while ($row = mysql_fetch_array($sql)) {
    $items[] = array(1 => $row['names']);
  }
  
  // Number of items in the array
  $num_items = count($items);
  
  // Number of rows
  $num_rows = ceil($num_items / $num_cols);
  
  // Begin HTML table
  echo '<table width="100%">';
    
  for ($row = 1; $row < $num_rows; $row++) {
    $cell = 0;
    
    // Start each new row
    echo '<tr>';
    
    for ($col = 1; $col <= $num_cols; $col++) {
      echo '<td>';
      if ($col === 1) {
        $cell += $row;
        echo $items[$cell - 1][1];
      }
      else {
        $cell += $row;
        echo $items[$cell - 1][1];
      }
      echo '</td>';
    }
    echo '</tr>';
  }
  
  echo '</table>';
}
?>

[Wolphie]

Or an improved version:

 

<?php
/**
* This script allows people to get a data set from
* a single table in the database and display the data
* in multiple columns of an HTML table.
*
*
* Example usage:
*
* $sql = "SELECT url_links FROM websites";
* echo display_columns($sql, 'url_links', 3, 'my_table_class');
*/


/**
* This function will allow you to display a single 
* data set into multiple columns of an HTML table.
*
* @param $query
*    This is the MySQL query that is ran to return the
*    data set.
*
* @param $field
*    This is the name of the table field we should columnise.
*
* @param $num_cols
*    The number of columns to split the data set up into.
*
* @param $class
*    The CSS class of the table so that it can be styled.
*    This is optional.
*
* @return
*    Returns a formatted HTML string containing the tabulated
*    data from the data set.
*/
function display_columns($query, $field, $num_cols, $class = '') {
  // Execute query
  $sql = mysql_query($query);
    
  if (mysql_num_rows($sql) > 0) {
    // Stick the results in another array
    while ($row = mysql_fetch_array($sql)) {
      $items[] = array(1 => $row[$field]);
    }
    
    // Number of items in the array
    $num_items = count($items);
    // Number of rows
    $num_rows = ceil($num_items / $num_cols);
    
    // Begin HTML table
    $output = '<table width="100%" class="'. $class .'">';
      
    for ($row = 1; $row < $num_rows; $row++) {
      // Start at cell 0
      $cell = 0;
      
      // Start each new row
      $output .= '<tr>';
      
      for ($col = 1; $col <= $num_cols; $col++) {
        $output .= '<td>';
        if ($col === 1) {
          // For each iteration, add the row number so that we know what element to get data from
          $cell += $row;
          // Use the row number subtract one to get the correct element we're after
          $output .= $items[$cell - 1][1];
        }
        else {
          // For each iteration, add the row number so that we know what element to get data from
          $cell += $row;
          // Use the row number subtract one to get the correct element we're after
          $output .= $items[$cell - 1][1];
        }
        $output .= '</td>';
      }
      $output .= '</tr>';
    }
    
    // End HTML table
    $output .= '</table>';
  }
  
  // Return the data
  return $output;
}
?>

[mleeadams]

Thanks Wolphie

 

I am still not sure where to put the...

 

<img src="<?=$src?>" alt="<?=$row['photo_caption_1']?>" title="<?=$row['photo_caption_1']?>"><br>
        <span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000"> 
        <?=$row['photo_caption_1']?>
        </font>

 

...to display the images.

 

Sorry, I am a beginner and I would like to see my code implemented with yours.

 

The images are kept in a folder

 

Regards

 

[Wolphie]

Okay, this should work for you.

 

<?php
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';

$sql = mysql_query("SELECT photo_id, photo_caption_1, photo_listing
        FROM listing_photo
        WHERE photo_listing = 127
        LIMIT 10");

if (mysql_num_rows($sql) > 0) {
  // Default numver of columns
  $num_cols = 2;
  
  while ($row = mysql_fetch_array($sql)) {
    $items[] = array('photo_id' => $row['photo_id'], 'photo_caption_1' => $row['photo_caption_1']);
  }
  
  // Number of items in the array
  $num_items = count($items);
  
  // Number of rows
  $num_rows = ceil($num_items / $num_cols);
  
  // Begin HTML table
  echo '<table width="100%">';
    
  for ($row = 1; $row < $num_rows; $row++) {
    $cell = 0;
    
    // Start each new row
    echo '<tr>';
    
    for ($col = 1; $col <= $num_cols; $col++) {
      echo '<td>';
      if ($col === 1) {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg'; .'" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      else {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg'; .'" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      echo '</td>';
    }
    echo '</tr>';
  }
  
  echo '</table>';
}
?>

[mleeadams]

Thanks Wolphie

 

I am getting an error...

Parse error: syntax error, unexpected '.' in /usr/local/home/spainhs/www/holidays/templates/english/public/test1.php on line 54

 

On this line...

echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg'; .'" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';

 

Any ideas?

 

[Wolphie]

Try that

 

<?php
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';

$sql = mysql_query("SELECT photo_id, photo_caption_1, photo_listing
        FROM listing_photo
        WHERE photo_listing = 127
        LIMIT 10");

if (mysql_num_rows($sql) > 0) {
  // Default numver of columns
  $num_cols = 2;
  
  while ($row = mysql_fetch_array($sql)) {
    $items[] = array('photo_id' => $row['photo_id'], 'photo_caption_1' => $row['photo_caption_1']);
  }
  
  // Number of items in the array
  $num_items = count($items);
  
  // Number of rows
  $num_rows = ceil($num_items / $num_cols);
  
  // Begin HTML table
  echo '<table width="100%">';
    
  for ($row = 1; $row < $num_rows; $row++) {
    $cell = 0;
    
    // Start each new row
    echo '<tr>';
    
    for ($col = 1; $col <= $num_cols; $col++) {
      echo '<td>';
      if ($col === 1) {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg'" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      else {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      echo '</td>';
    }
    echo '</tr>';
  }
  
  echo '</table>';
}
?>

[mleeadams]

Im now getting...

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ',' or ';' in /usr/local/home/spainhs/www/holidays/templates/english/public/test1.php on line 55

 

On line...

echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg'" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';

[Wolphie]

<?php
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';

$sql = mysql_query("SELECT photo_id, photo_caption_1, photo_listing
        FROM listing_photo
        WHERE photo_listing = 127
        LIMIT 10");

if (mysql_num_rows($sql) > 0) {
  // Default numver of columns
  $num_cols = 2;
  
  while ($row = mysql_fetch_array($sql)) {
    $items[] = array('photo_id' => $row['photo_id'], 'photo_caption_1' => $row['photo_caption_1']);
  }
  
  // Number of items in the array
  $num_items = count($items);
  
  // Number of rows
  $num_rows = ceil($num_items / $num_cols);
  
  // Begin HTML table
  echo '<table width="100%">';
    
  for ($row = 1; $row < $num_rows; $row++) {
    $cell = 0;
    
    // Start each new row
    echo '<tr>';
    
    for ($col = 1; $col <= $num_cols; $col++) {
      echo '<td>';
      if ($col === 1) {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      else {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      echo '</td>';
    }
    echo '</tr>';
  }
  
  echo '</table>';
}
?>

[mleeadams]

Great, it working

 

However, im only getting 4 of the possible 6 pictures showing.

 

Any ideas

 

 

[Wolphie]

Are you sure the images are there? Have you checked the permissions on them? Is the path right?

[mleeadams]

Not sure how to do a dump of the database! I have a screen grab of the image database but dont know how to get it to you.

 

Whats happening is this...

 

Pic1    Pic2

 

Pic2    Pic3

 

Then stops.

 

The number of images for a particular property could be from 6 / 10

[Wolphie]

Can you post the HTML source? You can upload images for free to http://tinypic.com/ and post the link here.

[mleeadams]

Here´s the image of the database...

 

<a href="http://tinypic.com" target="_blank"><img src="http://i39.tinypic.com/atu0pz.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

 

The images are stored in a folder and for the example property "WHERE photo_listing = 127" in our code above, photo_listing 127 has 6 images...

115.jpg

116.jpg

117.jpg

118.jpg

119.jpg

120.jpg

 

The test page is here...

http://www.spain-holiday-sun.com/holidays/templates/english/public/test1.php

 

[Wolphie]

I'm setting up a copy of your database and images. Can I have the other 3 images please?

[mleeadams]

Try this url for the database image http://i39.tinypic.com/atu0pz.jpg

[mleeadams]

I'm setting up a copy of your database and images. Can I have the other 3 images please?

 

Sorry Wolphie, not sure what you mean

[Wolphie]

Can you post the other three images please?

[mleeadams]

This them here...

http://www.spain-holiday-sun.com/holidays/templates/english/public/test.php

 

[mleeadams]

Wolphie

 

It seems to me the code is almost correct.

 

It shows the images in 2 column´s but only 4 images. (there could be 6, 8 or 10 depending on the property)

 

Also the 2nd image in the first row repeats in the 1st column of the 2nd row.

 

I am guessing there is a quick fix for this.

 

Anyone help me with this?

 

Thanks again for all your help.

 

 

 

 

 

 

[mleeadams]

Forgot the code in last post...

 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Location where the images are stored
$file_path = 'http://www.mysite.com/holidays/files/photo_big/';

$sql = mysql_query("SELECT photo_id, photo_caption_1, photo_listing
        FROM listing_photo
        WHERE photo_listing = 127
	ORDER BY `photo_status_main` <> 'main'
	LIMIT 10");

if (mysql_num_rows($sql) > 0) {
  // Default numver of columns
  $num_cols = 2;
  
  while ($row = mysql_fetch_array($sql)) {
    $items[] = array('photo_id' => $row['photo_id'], 'photo_caption_1' => $row['photo_caption_1']);
  }
  
  // Number of items in the array
  $num_items = count($items);
  
  // Number of rows
  $num_rows = ceil($num_items / $num_cols);
  
  // Begin HTML table
  echo '<table width="75%">';
    
  for ($row = 1; $row < $num_rows; $row++) {
    $cell = 0;
    
    // Start each new row
    echo '<tr>';
    
    for ($col = 1; $col <= $num_cols; $col++) {
      echo '<td>';
      if ($col === 1) {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      else {
        $cell += $row;
        echo '<div class="Image"><img src="'. $file_path . $items[$cell - 1]['photo_id'] .'.jpg" alt="'. $items[$cell - 1]['photo_caption_1'] .'" title="'. $items[$cell - 1]['photo_caption_1'] .'" />';
        echo '<br />';
        echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $items[$cell - 1]['photo_caption_1'] .'</font></span></div>';
      }
      echo '</td>';
    }
    echo '</tr>';
  }
  
  echo '</table>';
}
?>

 

[mleeadams]

Anyone...?