sql php help

[mladams]

Hi New here

I´m having trouble with this code.

It outputs the following...

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

It should print 9 images but it is showing...
pic1 pic2
pic2 pic3
pic4 pic5
pic3 pic6

This is the database screen shot...
http://i42.tinypic.com/25q99qt.jpg

Can anyone see where i´m going wrong.

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/';

$sql = mysql_query("SELECT photo_id, photo_caption_1, photo_listing
        FROM listing_photo
        WHERE photo_listing = 136
		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="70%">';

  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'] .'" height="213" width="300"/>';
    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'] .'" height="213" width="300"/>';
    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>';
}
?>

[bradgrafelman]

Your for() statement for printing out the rows should use the <= operator, not the < operator, when checking the loop condition. Otherwise, you stop one short from the max rauer than including it.

[leeadams]

Thanks bragrafelman
I made the change you sugested.

Still having problems.

I now have the code below but the pictures dont show correctly.
Example: http://www.spain-holiday-sun.com/holidays/templates/english/public/test.php

Interestingly, if I change the $num_cols = 3 I get this...
http://www.spain-holiday-sun.com/holidays/templates/english/public/test1.php

Can anyone see where the stupid mistake is.

// 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 = 136
		ORDER BY `photo_status_main` <> 'main'
		LIMIT 10");

if (mysql_num_rows($sql) > 0) {
  // Default numver of columns
  $num_cols = 3;

  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="70%">';

  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'] .'" height="213" width="300"/>';
    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'] .'" height="213" width="300"/>';
    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>';
}
?>

[halojoy]

Interestingly, if I change the $num_cols = 3 I get this...

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

That one looks perfect to me.
3 rows with 3 pics on each row
and description below each pic

But maybe you have corrected your script since that post?

[niroshan]

I think your problem is image repetition right?

Try this,

$sql = 'SELECT * FROM images';
$row = mysql_query($sql);
$noOfRecs = mysql_num_rows($row);
while($result = mysql_fetch_array($row))
{
	$files[] = array('id' => $result['id'], 'url' => $result['url']);
}

$noOfCols = 3;

$totalRows = ceil($noOfRecs / $noOfCols);
$actualNeed = $totalRows * $noOfCols;

print '<table border="1" width="100%">';

$x = 0;
for($i=0; $i < $noOfRecs;$i++)
{
	if($x==0)
		print '<tr>';

print '<td>'.$files[$i]['url'].'</td>';

$x++;

if($x == $noOfCols)
{
	print '</tr>';
	$x=0;
}	
}

if($actualNeed > $noOfRecs)
{
	for($i=$noOfRecs; $i < $actualNeed;$i++)
		print '<td>&nbsp;</td>';

print '</tr>';
}

print '</tabel>';

you can change the noOfCols to reflect the no of columns and the table will adjust accordingly.

Regards,
Niroshan

[mladams]

Yes niroshan thats my problem, the pictures are repeating.

Thanks for the code sample but how would I add my specific info to it.

Not sure where the image stuff goes or the titles.

If you could show me a working example i would be grateful.

M

[niroshan]

hi mladams,

Did you read and understood my code snap it? Because if you do, change that in to your requirement would be really simple. My first advice for you is please read the code and understand it so next time if u encounter similar problem you dont have to ask anyone. 🙂

By the way i found one mistake in your query.

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

what do you mean by ORDER BY photo_status_main <> 'main' . Why are you putting a condition for a order by statement?

Here goes the code,

In the sql I have removed the ORDER BY photo_status_main <> 'main' because i`m not quite sure about your requirement in that.

$file_path = 'http://www.mysite.com/holidays/files/photo_big/'; 

$sql = 'SELECT photo_id, photo_caption_1 FROM listing_photo WHERE photo_listing = 136 LIMIT 0, 10';
$row = mysql_query($sql);
$noOfRecs = mysql_num_rows($row);
while($result = mysql_fetch_array($row))
{
	$files[] = array('id' => $result['photo_id'], 'caption' => $result['photo_caption_1']);
}

$noOfCols = 3;

$totalRows = ceil($noOfRecs / $noOfCols);
$actualNeed = $totalRows * $noOfCols;

print '<table border="1" width="100%">';

$x = 0;
for($i=0; $i < $noOfRecs;$i++)
{
	if($x==0)
		print '<tr>';

echo '<td><div class="Image"><img src="'. $file_path . $files[$i]['id'] .'.jpg" alt="'. $files[$i]['caption'] .'" title="'. $files[$i]['caption'] .'" height="213" width="300"/>';
echo '<br />'; 
echo '<span><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#000000">'. $files[$i]['caption'] .'</font></span></div></td>'; 

$x++;

if($x == $noOfCols)
{
	print '</tr>';
	$x=0;
}	
}

if($actualNeed > $noOfRecs)
{
	for($i=$noOfRecs; $i < $actualNeed;$i++)
		print '<td>&nbsp;</td>';

print '</tr>';
}

print '</tabel>';

[mladams]

Thanks nirosham

Your a star

I am understanding more each day and your example will give me a big push. Your example is simpler than my original.

I guess thats part of my learning curve, there are so many different ways to accomplish the same thing.

ORDER BY photo_status_main <> 'main'

This picks the main photo to display first. When clients upload their pictures, they can chose the main one and this shows first.

I tried ORDER BY photo_status_main = 'main' but it didnt work.

Be Lucky!

[niroshan]

hi mladams,
If you want to show the main photo first and the rest of the photos, here is an idea.

change the photo_status_main to int(1) and keep it default 0. If it`s primary image then change that field to 1. so in this way you can simply use the order by descending photo_status_main which will always return your primary image first and the rest afterwards.

Hope this helps.

Thanks,
Best regards,
Niroshan

[mladams]

In row "photo_status_main" in the DB the main picture is denoted by the term 'main'

that why I ORDER BY photo_status_main <> 'main'

Not sure about the "<>" but it works!