[Continued in other topic] The best way to show MySQL data in content page

[Art-art]

Hi,

Currently I show some MySQL data by applying a User Defined Tag.
This tag is basically formatted like this:

Code: Select all

//User Defined Tag: Factbookdata

mysql_close($connectiondata);

$carrierchoice = urldecode($_GET['carrierchoice']);
// This makes a connection to the file containing the logon data//
require ('connection.php');

$connectiondata = mysql_connect($host,$user,$password, true)
or die ("couldn't connect to server");

$db = mysql_select_db($database,$connectiondata)
or die ("Couldn't select database");

// Combine carriers and country names  and select desired carrier//
$query =
"SELECT * FROM tbl_carrier, tbl_country
WHERE carr_country_iso3=coun_iso3
and carr_name ='".$carrierchoice."';";

$result = mysql_query($query)
or die (mysql_error());

// Display results //
	while ($row = mysql_fetch_array($result))
	{
		extract($row);
		echo "			

			<table>		
			.....
			.....
			.....
			</table>		
					
			";		
	}				
					
	mysql_close($connectiondata);				

To show the data I entered the User Defined Tag in the content of the desired page.

The problem is that up till now I didn't manage to lay-out the table shown (discussed in my post http://forum.cmsmadesimple.org/index.php/topic,14935.msg73986.html#msg73986). Somehow the tag editor doesn't allow me to enter anything that is related to style sheets or even HTML lay-out things like BGCOLOR.

Now I thought it would be a good idea to move the content of the User Defined Tag to the page itself.
This seems to have solved the problem of styling the table. However...

A new problem is introduced:
After opening the page, the content area that is supposed to show the table, initially shows

//User Defined Tag: Factbookdata mysql_close($connectiondata); $carrierchoice = urldecode($_GET['carrierchoice']); // This makes a connection to the file containing the logon data// require ('connection.php'); $connectiondata = mysql_connect($host,$user,$password, true) or die ("couldn't connect to server"); $db = mysql_select_db($database,$connectiondata) or die ("Couldn't select database"); // Combine carriers and country names and select desired carrier// $query = "SELECT * FROM tbl_carrier, tbl_country WHERE carr_country_iso3=coun_iso3 and carr_name ='".$carrierchoice."';"; $result = mysql_query($query) or die (mysql_error()); // Display results // while ($row = mysql_fetch_array($result)) mysql_close($connectiondata);

In other words it shows the code in stead of the result.

After entering $carrierchoice = urldecode($_GET['carrierchoice']); by clicking on one of the options in the left column of the page (in here the links look like
http://website.com/index.php?page=fact- ... e=CompanyA) the required data is shown.

My analysis is that since there is initially no carrierchoice entered the query doesn't know what data should be shown and makes an error.

How should this be solved?

• Getting back to the User Defined Tag?
• Somehow entering an initial value for carrierchoice
• Some other thing?

Thanks,

Arthur

[alby]

Code: Select all

//User Defined Tag: Factbookdata

mysql_close($connectiondata);

$carrierchoice = urldecode($_GET['carrierchoice']);
// This makes a connection to the file containing the logon data//
require ('connection.php');

$connectiondata = mysql_connect($host,$user,$password, true)
or die ("couldn't connect to server");

$db = mysql_select_db($database,$connectiondata)
or die ("Couldn't select database");

// Combine carriers and country names  and select desired carrier//
$query =
"SELECT * FROM tbl_carrier, tbl_country
WHERE carr_country_iso3=coun_iso3
and carr_name ='".$carrierchoice."';";

$result = mysql_query($query)
or die (mysql_error());

// Display results //
	while ($row = mysql_fetch_array($result))
	{
		extract($row);
		echo "			

			<table>		
			.....
			.....
			.....
			</table>		
					
			";		
	}				
					
	mysql_close($connectiondata);				

To show the data I entered the User Defined Tag in the content of the desired page.

The problem is that up till now I didn't manage to lay-out the table shown (discussed in my post http://forum.cmsmadesimple.org/index.php/topic,14935.msg73986.html#msg73986). Somehow the tag editor doesn't allow me to enter anything that is related to style sheets or even HTML lay-out things like BGCOLOR.

Now I thought it would be a good idea to move the content of the User Defined Tag to the page itself.
This seems to have solved the problem of styling the table. However...

A new problem is introduced:
After opening the page, the content area that is supposed to show the table, initially shows


After entering $carrierchoice = urldecode($_GET['carrierchoice']); by clicking on one of the options in the left column of the page (in here the links look like
http://website.com/index.php?page=fact- ... e=CompanyA) the required data is shown.

My analysis is that since there is initially no carrierchoice entered the query doesn't know what data should be shown and makes an error.

How should this be solved?

• Getting back to the User Defined Tag?
• Somehow entering an initial value for carrierchoice
• Some other thing?

Why you don't check the isset of variable?
Quick code (or use a default value for carrierchoice with $params['carrierchoice'], view later for this) :

//User Defined Tag: Factbookdata

mysql_close($connectiondata);

// This makes a connection to the file containing the logon data//
require ('connection.php');

$connectiondata = mysql_connect($host,$user,$password, true)
or die ("couldn't connect to server");

$db = mysql_select_db($database,$connectiondata)
or die ("Couldn't select database");


if(isset($_GET['carrierchoice']))
{
  $carrierchoice = urldecode($_GET['carrierchoice']);

  // Combine carriers and country names  and select desired carrier//
  $query = "SELECT * FROM tbl_carrier, tbl_country
  WHERE carr_country_iso3=coun_iso3
  and carr_name ='".$carrierchoice."'";

  $result = mysql_query($query) or die (mysql_error());

  // Display results //
while ($row = mysql_fetch_array($result))
{
extract($row);
echo "


.....
.....
.....


";
}
}
else
{
  $query = "SELECT carr_name FROM tbl_carrier, tbl_country
  WHERE carr_country_iso3=coun_iso3";

  $result = mysql_query($query) or die (mysql_error());

  // Display results //
while ($row = mysql_fetch_array($result))
{
echo ''.$row['carr_name'].'';
}
}

mysql_close($connectiondata);

Remember, this is UNTESTED.
For better result you must use adodb e smarty method for this

Some other thing?
Use params for UDT for style your output:
{myudt tdbgcolor="#fff" tdcell="#f00"}

In myudt use them with $params['tdbgcolor'] and $params['tdcell']

Look this for other info

Alby

[Art-art]

Hi Alby,

Thanks for your reply. I dropped the idea of the query inside the content page. There appeared more things that were wrong in this solution. I'll concentrate on laying out the UDT.
Further I'm afraid that my knowledge is insufficient again to understand the bottom of your reply.
I'm not a skilled programmer and just a hobbyist grown up with BASIC and some experience with querying in MS-Access. I'm starting to learn some SQL, php and CSS.

In your answer

Some other thing?
Use params for UDT for style your output:
{myudt tdbgcolor="#fff" tdcell="#f00"}

In myudt use them with $params['tdbgcolor'] and $params['tdcell']

Look this for other info

I understand that I can apply CCS.
Could you please give me some example on how to use this. I've got some books inhere to expand basic solutions but I need the basic idea.

For instance:

Use params for UDT for style your output:
{myudt tdbgcolor="#fff" tdcell="#f00"}

Is this code to be put inside my UDT, the Template of a Stylesheet applied in the Template?

What do you mean by

myudt

in relation to UDT, do you mean the UDT as I use in here?

Further the link you gave, leads to code under

How to Execute Smarty Tags from A User Defined Tag (UDT)

that looks like Russian to me.

So, please give me some very basic examples including the places to put the code and I'll expand it with the lay out I want.

[alby]

Further the link you gave, leads to code under

How to Execute Smarty Tags from A User Defined Tag (UDT)

that looks like Russian to me.

So, please give me some very basic examples including the places to put the code and I'll expand it with the lay out I want.

It's very difficult 
I try but English isn't good 

Alby

[alby]

In your answer

Some other thing?
Use params for UDT for style your output:
{myudt tdbgcolor="#fff" tdcell="#f00"}

In myudt use them with $params['tdbgcolor'] and $params['tdcell']

Look this for other info

I understand that I can apply CCS.
Could you please give me some example on how to use this. I've got some books inhere to expand basic solutions but I need the basic idea.

tdbgcolor and tdcell are two examples of params that you send to your UDT.
For use, you call them (in UDT code) with $params['tdbgcolor'] and $params['tdcell']

Example of use in UDT:
1.  {myudt tdbgcolor="#f00" tdcell="myclasscss"}

2.  myudt code:

while ($row = mysql_fetch_array($result))
{
extract($row);
echo "


field 1
field 2


";
}

For instance:

Use params for UDT for style your output:
{myudt tdbgcolor="#fff" tdcell="#f00"}

Is this code to be put inside my UDT, the Template of a Stylesheet applied in the Template?

The code of UDT is in Amin area > Extensions > UDT > NameOfUDT
You call him (execute code) with a smarty tag ({....}) in template, content page or GBC

What do you mean by

myudt

in relation to UDT, do you mean the UDT as I use in here?

I don't understand this,
but read this post for difference pieces of code

Thanks for your reply. I dropped the idea of the query inside the content page. There appeared more things that were wrong in this solution. I'll concentrate on laying out the UDT.

Ok, it's very bad idea use php code in content.
For your specific code, you can use mysql call in UDT (later when you take confidence, try with Adodb call - search in forum for many examples)

My different code is for a initial check of variable (carrierchoice) necessary for query:

if(isset($_GET['carrierchoice']))
{
VARIABLE PRESENT, DO ...
}
else
{
VARIABLE NOT PRESENT, DO OTHER ...
}

Alby

[Art-art]

Hi Alby,

Don't worry. English isn't my mother language too.
All together your knowledge of English is much better as my knowledge of CMSMS. So please give it a try.

In case you are not sure if you translate the Italian expression to English in a proper way, you can add the Italian expression. My wife peaks Italian pretty well (15 years of summer holiday in Italy with Italian friends and 10 years of Italian lessons in evening classes will do) and should be able to help me in understanding what you write.

I hope you can help me.

Cheers, Arthur

[alby]

Hi Alby,

This is a UDT.

//User Defined Tag: Factbookdata
global $gCms;
$db =& $gCms->GetDb();
$config =& $gCms->GetConfig();


if(isset($_GET['carrierchoice']))
{

//There is a carrierchoice!
$carrierchoice = urldecode($_GET['carrierchoice']);

$query = "SELECT * FROM tbl_carrier, tbl_country
WHERE carr_country_iso3=coun_iso3
and carr_name = ?";
$dbresult = $db->Execute( $query, array($carrierchoice) );

//Open table and first line with 3 header
//Substitute with your header and add other header (column)
echo "


HEADER 1
HEADER 2
HEADER 3

";

while($dbresult && $row = $dbresult->FetchRow())
{
//Substitute with your sql field (from query) and add other field (column)
echo "

".$row['field1']".
".$row['field2']".
".$row['field3']".

";
}

//Close table
echo "

";
return;

}
else
{

//No carrierchoice!
//Query without carrierchoice (first page)
$query = "SELECT carr_name FROM tbl_carrier, tbl_country
WHERE carr_country_iso3=coun_iso3";
$dbresult = $db->Execute( $query );

$url = $config['root_url'].'/index.php?'.$config["query_var"].'='.$gCms->variables['content_id'].'&carrierchoice=';

//List of carrierchoice
while($dbresult && $row = $dbresult->FetchRow())
{
echo ''.$row['carr_name'].'';
}
return;

}

[Art-art]

Hi Alby,

This looks quite impressive. I will have time in the upcoming weekend to spend on this site. Hopefully I can solve the problems. I'll let you know anyway on the result.

Kind regards,

Arthur

[Art-art]

Hi Alby,

As I wrote; I'll leave the discussion regarding the if-then statement for the carrierchoice. When this whole thing is done in a User Defined Tag (UDT) it all works.
Now I want to bring the whole discussion of laying out a table, so I decided to post the topic again in a clear cut way.
I hope you don't mind.
(For new topic see: http://forum.cmsmadesimple.org/index.php/topic,15719.msg78044.html#msg78044)

[Art-art]

Hi Alby,

One more reply:
The problem is solved!
Please see http://forum.cmsmadesimple.org/index.php/topic,15719.msg78044.html#msg78044 for the solution.
As alway solutions are sometimes so easy. The problem is finding them is so hard!

Regards,

Arthur

[alby]

Hi Alby,

One more reply:
The problem is solved!

Alby