Getting a certain record from DB!

[jmansa]

I'm trying to display a specific image from a table in my db. The table is build with records from different users with there own ID like this.

Code: Select all

uid
imagenumber
image

Now I have no problem selecting the user I want, but lets say that uid "3" have 7 pictures in the table listed as imagenumber 1 - 7 and I want to only show image 4 how do I go about that... The way I do it I only can get the first image... Here is what I have tryid so far.

My action.php:

Code: Select all

	$query =
      "SELECT * FROM ".cms_db_prefix ().
      "module_clubmanager_images WHERE uid = ?";
    $dbresult = $db->Execute ($query, array($params['uid']));
    if (!$dbresult)
      {

	$this->smarty->assign ('message', $this->Lang ('error_dberror'));
	$this->smarty->assign ('error', 1);
	echo $this->ProcessTemplate ('editsponsor.tpl');
	return;
      }
    $row = $dbresult->FetchRow ();

	$smarty->assign ('imagenumber', $row['imagenumber']);
	$smarty->assign ('image, $row['image']);

And then in my .tpl file I try like this:

Code: Select all

<p style="font-size:12px; font-weight:bold">{$title} - {$user}</p> <br>
{if $message != ''}
  {if $error != ''}
    <p><font color="red">{$message}</font></p>
  {else}
    <p>{$message}</p>
    <p> </p>
    <p>{/if}
      {else}
{if $imagenumber == '1'}
{$image}
{elseif $imagenumber == '4'}
{$image}
{/if}
{/if}

But with no result... Can somebody please guide me in the right direction?

[cyberman]

Have you tried to use {get_template_vars} inside .tpl to see which variables are available in Smarty?

[jmansa]

I didnt get anything out of that!

Let me try to explain what I have done so far and what I have experinced!

First I have these records in my DB table:
(uid, imagenumber, image)

• 2, 3, test3.jpg
• 4, 1, test1.jpg
• 2, 1, test1.jpg

My PHP looks like this:

Code: Select all

if ( !$this->CheckPermission('Use Sponsor Manager') )
  {
    $this->_DisplayErrorPage($id, $params, $returnid,
			     $this->Lang('accessdenied'));
    return;
  }

    $db =& $this->GetDb();
    $this->smarty->assign ('title', $this->Lang ('editsponsor'));

    if (!isset ($params['uid']))
      {
	$this->smarty->assign ('message', $this->Lang ('error_nosponsor'));
	$this->smarty->assign ('error', 1);
	return;
      }

    $query = "SELECT * FROM ".cms_db_prefix ()."module_sponsormanager_users WHERE uid = ?";
    $dbresult = $db->Execute ($query, array($params['uid']));

    $course = $dbresult->FetchRow ();
	
	$smarty->assign ('user', $course['user_name']);

    $query =
      "SELECT * FROM ".cms_db_prefix ().
      "module_sponsormanager_sponsor WHERE uid = ?";
    $dbresult = $db->Execute ($query, array($params['uid']));
	
	
    if (!$dbresult)
      {

	$this->smarty->assign ('message', $this->Lang ('error_dberror'));
	$this->smarty->assign ('error', 1);
	echo $this->ProcessTemplate ('sponsor.tpl');
	return;
      }
    $row = $dbresult->FetchRow ();
    
	if (!$row)
      {
	$this->smarty->assign ('message', $this->Lang ('error_dberror'));
	$this->smarty->assign ('error', 1);
	echo $this->ProcessTemplate ('sponsor.tpl');
	return;
      }
	  
	$smarty->assign ('image', $row['image']);
	$smarty->assign ('imagenumber', $row['imagenumber']);
	
    echo $this->ProcessTemplate ('sponsor.tpl');

And my template looks like this:

Code: Select all

<p style="font-size:12px; font-weight:bold">{$title} - {$club}</p> <br>
{if $message != ''}
  {if $error != ''}
    <p><font color="red">{$message}</font></p>
  {else}
    <p>{$message}</p>
  {/if}
{else}
<table width="450" border="0" cellspacing="2" cellpadding="0">
  <tr>
    <td>{if $imagenumber == '1'}
<table width="200" border="0" cellspacing="0" cellpadding="2">
  <tr><td>1</td></tr>
  <tr><td height="50"><img src="../uploads/{$image}" width="150" height="50" /></td></tr>
</table>
{else}
<table width="200" border="0" cellspacing="0" cellpadding="2">
  <tr><td>1</td></tr>
  <tr><td height="50">No image!</td></tr>
</table>
{/if}</td>
    <td>{if $imagenumber == '2'}
<table width="200" border="0" cellspacing="0" cellpadding="2">
  <tr><td>2</td></tr>
  <tr><td height="50"><img src="../uploads/{$image}" width="150" height="50" /></td></tr>
</table>
{else}
<table width="200" border="0" cellspacing="0" cellpadding="2">
  <tr><td>2</td></tr>
  <tr><td height="50">No image!</td></tr>
</table>
{/if}</td>
    <td>{if $imagenumber == '3'}
<table width="200" border="0" cellspacing="0" cellpadding="2">
  <tr><td>3</td></tr>
  <tr><td height="50"><img src="../uploads/{$image}" width="150" height="50" /></td></tr>
</table>
{else}
<table width="200" border="0" cellspacing="0" cellpadding="2">
  <tr><td>3</td></tr>
  <tr><td height="50">No image!</td></tr>
</table>
{/if}</td>
  </tr>
</table>
{/if}

As you can see I want to be able to look through the db table and find imagenumber 1,2,3 for a certain uid, and if the image doesnt exist then go to else... In this case my uid = 2. It finds without any problems the first record in my db table with the imagenumber = 3 but then it stops looking... It doesnt find imagenumber = 1 It seems like it stops looking after first record... What am I doing wrong?

[Duketown]

jmansa,

Could it be that the values for $message and $error are not initiated once set. So for the 2nd and 3rd (etc) row read from the database.
Does the output contain something (does it show a picture) without all the if statements? So just outputting the variables?
What were the results of cybermans remark?

[alby]

Code: Select all

    $query =
      "SELECT * FROM ".cms_db_prefix ().
      "module_sponsormanager_sponsor WHERE uid = ?";
    $dbresult = $db->Execute ($query, array($params['uid']));
	
    if (!$dbresult)
      {

	$this->smarty->assign ('message', $this->Lang ('error_dberror'));
	$this->smarty->assign ('error', 1);
	echo $this->ProcessTemplate ('sponsor.tpl');
	return;
      }
    $row = $dbresult->FetchRow ();
    
	if (!$row)
      {
	$this->smarty->assign ('message', $this->Lang ('error_dberror'));
	$this->smarty->assign ('error', 1);
	echo $this->ProcessTemplate ('sponsor.tpl');
	return;
      }
	  
	$smarty->assign ('image', $row['image']);
	$smarty->assign ('imagenumber', $row['imagenumber']);
	
    echo $this->ProcessTemplate ('sponsor.tpl');

What am I doing wrong?

Where is a loop for read your results?
In this mode you read first result only

Try with:

Code: Select all

    $query =
      "SELECT * FROM ".cms_db_prefix ().
      "module_sponsormanager_sponsor WHERE uid = ?";
    $dbresult = $db->Execute ($query, array($params['uid']));
    if (!$dbresult)
      {
	$this->smarty->assign ('message', $this->Lang ('error_dberror'));
	$this->smarty->assign ('error', 1);
	echo $this->ProcessTemplate ('sponsor.tpl');
	return;
      }
      while ($row = $dbresult->FetchRow())
      {
	//Personalized your array, this is: (1=>'test1.jpg, 3=>'test3.jpg')
	$image[$row['imagenumber']] = $row['image'];
      }
    $smarty->assign ('image', $image);

Alby

[Duketown]

Alby is right.
I take it that you (jmansa) modify your first message so it states in the beginning [Solved].