Application work: trying to create an dynamic SQL function
By Jostein Rosenlund on Friday, 27 December 2013
Posted in Technical Issues
Replies 12
Likes 0
Views 822
Votes 0 




public function itemID($itemID){

			$db = JFactory::getDbo();

			$query = $db->getQuery(true);

			$query->select(	'typeID','groupID','typeName','description','mass',

							'volume','capacity','scapacity','portionSize','raceID',

							'basePrice','published','marketGroupID','chanceOfDuplicating')

           ->from('#__eve_invTypes  ')

		   ->where('typeID = ' .  "'". $itemID  . "'" );

			$db->setQuery($query);

			$this->set( 'invTypes', $db->loadRow());

	

	

	}


but my issue is that the only way i know how to call this function are to 



$typeID = "12345";

$sql		 		= $this->getModel( 'sql' );

$sql->                                itemID($typeID);



and then 

$this->set ('querr', $sql->invTypes);


but the way i currently want to do it, are.


  • Iv created a Model, that creates an array. (one of the values is an typeID)
  • then i print out this array as an form in an theme.


then i want to do $db->loadRow()); on some of the value in that table. (this i have to do everywhere in my application)

So I thought it would be smart to create an function for this. but the issue here is that the value $itemID has to be printed out before the Theme are printed out, but i don't know how.
Nvm, me who asked weird.

I have that part solved out, i parse the XML using PHP XML Parser. were i put all the data into arrays. And then i use foreach and do to loop the data into the views.
was just looking for a more clean way to program my data into the views, but ill figure it out.

tnx for your assistance. you can consider this case closed,
Jostein Rosenlund
·
Monday, 30 December 2013 19:34
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

I don't quite understand what you are trying to achieve here. Your code actually looks fine. Could you elaborate more on what you are looking to achieve?
Jason Rey
·
Friday, 27 December 2013 15:08
·
0 Likes
·
0 Votes
·
0 Comments
·
What i want to do is basically want are to be able to

Define an $var in default/canvas/showinfo.php

lets name it $typeID.

Then I want to use an function, to run an SQL querry.

			$db = JFactory::getDbo();

			$query = $db->getQuery(true);

			$query->select(	'value' 'value')

           ->from('#__eve_invTypes  ')

		   ->where('typeID = ' .  "'". $var  . "'" ); <----- this $var it fetches from the template.

			$db->setQuery($query);

			$invTypes = $db->loadRow(); <----- send this back to the template to be outputed in <? echo $invTypes['0'] ?>



Or the ability to create an table, that i can insert into an template. (that will also be an approach to my issue
Jostein Rosenlund
·
Friday, 27 December 2013 15:11
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

Why is the $var in the template?

Before going to into the canvas, you should be in view.html.php display function, in which you can call your function to execute the SQL and return the value.

From there, you just need to do:



$this->set('invTypes', $invTypes);



echo parent::display('canvas/[appname]/showinfo');


And in your theme file, you will get access to $invTypes;
Jason Rey
·
Friday, 27 December 2013 15:34
·
0 Likes
·
0 Votes
·
0 Comments
·
that wont work. This is how my template looks now.

  <tr>

    <td><? 

	

			$db = JFactory::getDbo();

			$query = $db->getQuery(true);

			$query->select(	'typeName')

           ->from('#__eve_invTypes  ')

		   ->where('typeID = ' .  "'". $starbases['typeID']  . "'" );

			$db->setQuery($query);

			$invTypes = $db->loadRow();

			echo $invTypes['0'] ;

	

	?></td>

    <td></td>

  </tr>

  <tr>

    <td><? 

	

			$db = JFactory::getDbo();

			$query = $db->getQuery(true);

			$query->select(	'itemName')

           ->from('#__eve_mapDenormalize  ')

		   ->where('itemID = ' .  "'". $starbases['moonID']  . "'" );

			$db->setQuery($query);

			$invTypes = $db->loadRow();

			echo $invTypes['0'] ;

	

	?></td>

    <td></td>


and then the template is currently inserted into the site, this way

$posc = count($StarbaseList['starbases']);

$pos = "0";

do {	



$POSID = $StarbaseList['starbases'][$pos]['itemID'];

//$this->set('StarbaseDetail', $StarbaseDetail['starbases'][$pos]);

// prints out the header

$this->set('starbases', $StarbaseList['starbases'][$pos]);

echo parent::display( 'canvas/posheader');



//echo parent::display( 'canvas/fuel');

++$pos;} while($pos > $posc);
Jostein Rosenlund
·
Friday, 27 December 2013 15:43
·
0 Likes
·
0 Votes
·
0 Comments
·
I'v seen the table codes in the textbook, but i did not quite understand the code.

This is basically what i want to do.

$this->set('starbases', $StarbaseList['starbases'][$pos])



outputs



           starbases [itemID] => 1012851788722

           starbases[typeID] => 20064

           starbases[locationID] => 30001163

           starbases [moonID] => 40074015

           starbases[state] => 4

           starbases[stateTimestamp] => 2013-12-27 04:44:46

           starbases[onlineTimestamp] => 2013-12-04 01:44:19

           starbases[standingOwnerID] => 288377808


Then i want to put this code in an table, and insert that table in my template.

inside an 
do {	 ++$pos;} while($pos > $posc);
Jostein Rosenlund
·
Friday, 27 December 2013 15:48
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

Since you are doing this:



$this->set('starbases', $StarbaseList['starbases'][$pos]);

echo parent::display( 'canvas/posheader');


Assuming canvas/posheader is the single table row that you want to output:



$starbases = $StarbaseList['starbases'][$pos];



$typeName = getTypeName($starbases['typeID']);

$this->set('typeName', $typeName);



$itemName = getItemName($starbases['moonID']);

$this->set('itemName', $itemName);



echo parent::display( 'canvas/posheader');


And in your template:



<tr>

    <td><?php echo $typeName; ?></td>

</tr>

<tr>

    <td><?php echo $itemName; ?></td>

</tr>


On a sidenote, your sql function can be reduced to:



$invTypes = $db->loadResult();


This is exact equivalent to:


$result = $db->loadRow();

$invTypes = $result[0];
Jason Rey
·
Friday, 27 December 2013 16:00
·
0 Likes
·
0 Votes
·
0 Comments
·
ok, but thats not what im after. (i can do that, but i have lot of different info, that are going to be inserted in different places in this app. so im looking for an easy way out:)





if( $result )

		{

			foreach( $result as $row )

			{

				// Load up the textbook's ORM

				$book 	= $this->getTable( 'Textbook' );

				$book->bind( $row );



				$textbooks[]	= $book;

			}	

		}


Think this the easiest way for me are to do it like that, but i don't understand the logic of the GetTable class.
Jostein Rosenlund
·
Friday, 27 December 2013 16:11
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

To use the table, you first need to define a table class in [appname]/table/[tablename].php

The table class is following standard Joomla's JTable definition.
Jason Rey
·
Friday, 27 December 2013 16:18
·
0 Likes
·
0 Votes
·
0 Comments
·
OK, that answered a lot. (thumbs up for that.)

But as i can read from info, its ment to be injunction whit SQL.
You know what i can use if my Source data for the table are in XML?

Or can i use the JTable class for that as well?
Jostein Rosenlund
·
Friday, 27 December 2013 16:23
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

You can use PHP if you're source data is parsed by PHP even if it is XML. If the xml file is not parsed through PHP then unfortunately you can't use PHP codes.
Jason Rey
·
Friday, 27 December 2013 19:01
·
0 Likes
·
0 Votes
·
0 Comments
·
Thanks for updating, glad that your issues are resolved now.
Mark
·
Tuesday, 31 December 2013 12:28
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post