php sql table help

mleeadams · March 12, 2010

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 · March 12, 2010

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

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

Wolphie · March 12, 2010

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 · March 12, 2010

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 · March 12, 2010

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 · March 12, 2010

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 · March 12, 2010

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 · March 12, 2010

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 · March 12, 2010

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 · March 12, 2010

<?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 · March 12, 2010

Great, it working

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

Any ideas

Wolphie · March 12, 2010

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

mleeadams · March 12, 2010

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 · March 12, 2010

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

mleeadams · March 12, 2010

Here´s the image of the database...

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 · March 12, 2010

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

mleeadams · March 12, 2010

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

mleeadams · March 12, 2010

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 · March 12, 2010

Can you post the other three images please?

mleeadams · March 12, 2010

This them here...

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

mleeadams · March 12, 2010

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 · March 12, 2010

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 · March 13, 2010

Anyone...?

Sign In

php sql table help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information