Jump to content


Photo

query for images and pagination


  • Please log in to reply
3 replies to this topic

#1 libinaz

libinaz
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 20 September 2006 - 05:08 AM

Honestly, I think I am just too intimidated by this. I have a database that contains images, names and websites of  people. I just want to query the database, and have 20 distinct records per page, 4 columns across and each record contains three rows (the first row contains the images, the second row contains the first_name last_name with their website as a link and the third row is just a spacer between.)

I have tried numerous things, but all have been quite unfruitful and very ugly. If anyone can help me with the initial query and how to add them to the table, I might be able to figure this out. None of the scripts, tutorials I have found seem to be simple enough for what I am looking for.

pretty much i know what I want the outcome to be, this is two rows (6 table rows) of records:
    <table width="100%" border="0" cellpadding="8" cellspacing="0" class="protext">
                      <tr>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                      </tr>
                      <tr>
                        <td><a href="http://www.theirsite.com">Person's first_name last_name with the website as
                          the link</a></td>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                      </tr>
                      <tr>
                        <td colspan="4">&nbsp;</td>
                        </tr>
                      <tr>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                        <td><img src="http://www.mysite.com/artists/image1.jpg" width="126" height="158"></td>
                      </tr>
                      <tr>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                        <td><a href="http://www.theirsite.com">Person's first_name
                            last_name with the website as the link</a></td>
                      </tr>
                      <tr>
                        <td colspan="4">&nbsp;</td>
                      </tr>
                    </table>

my database schema is
CREATE TABLE `artists` (
  `artist_key` int(11) NOT NULL auto_increment,
  `artist_firstname` varchar(100) NOT NULL,
  `artist_lastname` varchar(100) NOT NULL,
  `artist_website` varchar(200) NOT NULL,
  `artist_photo` varchar(200) NOT NULL,
  `date_added` date NOT NULL,
  PRIMARY KEY  (`artist_key`)
)

Any help is much appreciated. I think I am just at a major total block and intimidated greatly by this.

#2 mitzleah

mitzleah
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationManila, Philippines

Posted 20 September 2006 - 05:59 AM

You must first put the data into variables. For example:

$query = "Your query here...";
$result = mysql_query($query);
$count = mysql_num_rows($result);
$x = 0;
while ($row = mysql_fetch_array($result)) {
        $data1[$x] = $row[0];
        $data2[$x] = $row[1];
        $x++;
}

Then format it any way you wanted:

echo "<table>";
$temp = 1;
$columns = 2; // number of columns
for ($i = 0; $i < $count; $i++)
{
    if ($temp == 1) {
          echo '<tr>';
    }

    if ($col != $columns) {
          echo '<td>'.$data1[$i].'</td>';
          $col++;
    } else {
          echo '<td>'.$data1[$i].'</td></tr>';
          $col=1;
    }

}
echo "</table>";




#3 libinaz

libinaz
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 20 September 2006 - 02:26 PM

Using the above code I get a "Parse error: syntax error, unexpected T_STRING in /home/.macintoshdryer/etc on line 13

Line 13 is while ($row = mysql_fetch_array($result)) {

<?php ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);
require_once('dbconnection.php');
$conn = db_connect();
  	if (!$conn)
    	return 'Could not connect to database server - please try later.';

$query = "SELECT artists_lastname, artist_firstname, artist_website, artist_photo FROM artists ORDER BY artist_lastname ASC";
$result = mysql_query($query);
$count = mysql_num_rows($result);
$x = 0;
while ($row = mysql_fetch_array($result)) {
        $data1[$x] = $row[0];
        $data2[$x] = $row[1];
        $x++;
} ?>



#4 libinaz

libinaz
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 20 September 2006 - 04:43 PM

okay, I have at least the images loading onto the page with the artists name's and website as a link in the names. I opted to go with the names on the right of the image. Now for the pagination. I am thinking of no more than 6 rows per page. If anyone knows of a way to do this...cool...any help is appreciated. I am off to phpfreaks tutorials for some pagination lessons.

<?php
$query = "SELECT artist_photo, artist_lastname, artist_firstname, artist_website FROM artists ORDER BY artist_lastname ASC";
$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error());
if($result && mysql_num_rows($result) > 0)
{
$i = 0;
$max_columns = 2;
while($row = mysql_fetch_array($result))
{
if($result)
{
$artist_photo=$row['artist_photo'];
$artist_lastname=$row['artist_lastname'];
$artist_firstname=$row['artist_firstname'];
$artist_website=$row['artist_website'];

extract($row);

if($i == 0)
echo "<tr>";

if($artist_photo != "" && $artist_photo != null)
echo "<td><img src=\"$artist_photo\"></td>";
echo "<td><a href='$artist_website'>$artist_firstname $artist_lastname</a>";
if(++$i == $max_columns)
{
echo "</tr>";
$i=0;
}
}
}
}
if($i < $max_columns)
{
for($j=$i; $j<$max_columns;$j++)
echo"<td>&nbsp;</td>";
}
echo"</tr>";
?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users