Forums

Newbie Question - Connecting to custom table the 'concrete5' way

Permalink
I am having some difficulty sorting out how to 'connect and query a custom table' the concrete5 way.

What I know/have done so far:
1. controller/ecom_product.php
<?php
Loader::model('ecom_product');
class EcomProductController extends Controller {
   function doStuff() {
      $db = Loader::db();
      $db->setDebug(true);
      $ep = new EcomProduct();
      print_r($ep); 
   }
}


2. models/ecom_product.php
<?php
class EcomProduct extends Model {
   public $_table = "EcomProducts";
   public function __construct() {
      parent::__construct($this->_table);
   }
}


3. single_pages/ecom_product.php
<?php
   if($this->controller->getTask() == 'doStuff') {
      print_r($message);
   }
?>
<form method="post" action="<?=$this->action('doStuff')?>">
   <input type="submit" value="Go!" />
</form>


4. config/db.xml
<?xml version="1.0"?>
<schema version="0.3">
   <table name="EcomProducts">
      <desc>A product that is representative of a page in an ecommerce store. Related to Variation (Child).</desc>
      <field name="ecom_product_id" type="I">
         <key />
         <autoincrement />
      </field>
      <field name="name" type="X2">
      </field>
      <field name="description" type="X2">
      </field>
   </table>
   <sql>
      <desc>Insert some test products into the EcomProducts table.</desc>
Viewing 15 lines of 20 lines. View entire code block.


I can connect to the db and create tables the more manual way i.e. $db->Execute({sql statement}) and am pretty sure I could write 'direct access' code, but am really looking to get a handle on how to leverage the AXMLS and ADODB stuff available.

A simple example or assistance as to why I get the attached output would greatly appreciated.

1 Attachment

 
Tony replied on at Permalink Reply
Tony
looks like you've gotten pretty far already. first I'd probably turn off the database debugging. most of that stuff you don't really need to see and isn't very helpful. Can you tell if the EcomProducts table has been created in the database? I think your db.xml file might need to be called site_db.xml, and then if you add this line to the config/site.php file:

define('ENABLE_DEVELOPER_OPTIONS', true);

...then you'll be able to create those tables with the "Refresh Schema" tool on: dashboard -> sitewide settings -> debug.

btw, I probably wouldn't do any inserting of products from within that xml file, but maybe you're just trying that for testing purposes.
oxigeno replied on at Permalink Reply
I really appreciate your assistance Tony!

I have now successfully managed to 'generate my custom table'.

I did as you said and all worked a treat. Now I believe I should be able to begin following the docs on ADODB to start working with the DB Rows as objects.

I will add a note to the Model's docs that outlines the secret sauce here, as I was never going to find it on my own.

cheers from New Zealand :)
Benji replied on at Permalink Reply
Benji
Hello oxigeno,

I read one of your posts from elsewhere on the concrete5 site saying that you could put up some "Intro to Connecting to a Database the c5 Way" docs. I myself have been struggling with database basics in c5, so I was wondering if you had any pointers. I googled around and found EE's documentation (http://expressionengine.com/docs/development/usage/database.html), which I thought was nicely laid out, but of course that's EE, not c5. Now I'm trying to comb through the ADOdb Active Record documentation (http://phplens.com/lens/adodb/docs-active-record.htm), but it's fairly heady. So, just thought I'd ask.
oxigeno replied on at Permalink Reply
Hi Benji,

Apologies for not getting back to you sooner.
Trawling through the docs is pretty much mandatory in terms of learning how to manipulate the Records objects.

No short cuts to offer at this stage I am afraid.
Benji replied on at Permalink Reply
Benji
Thanks anyway, oxigeno. I'm glad for any response, and a-trawling I shall go then.

I wonder if someone could help me with this specifically--I have:
<?php
$db = Loader::db();
$q = 'SELECT name, code2 FROM worldCountry ORDER BY name ASC';
$r = $db->query($q);
?>

...which seems to work fine. But then later I use:
<?php
while ($row = mysql_fetch_array($r)) {
  echo '<option value="' . $row['code2'] . '">' . htmlspecialchars($row['name']) . '</option>';
} 
?>

...and just get a warning that the $r is not a valid MySQL result resource. I suppose that's because it runs through the ADODB query process, instead of the usual PHP mysql_query. So I guess I need to find out how you work with ADODB results.
johndorsay replied on at Permalink Reply
johndorsay
OK, so I just started using c5 yesterday and I love it until something like this happened to me. How do I do a simple database call. And it took me forever to figure out but here it is. I included this code on the view.php page of a basic_test block (the demo they tell you to start with).

$db = Loader::db();
$r = $db->query("SELECT * FROM jd_Country");
$country = array();
   while ($row = $r->fetchRow()) {
      $country[] = ($row['Country']);
   }
//spit out first result in array   
echo $country[0];
Remo replied on at Permalink Reply
Remo
guys, check this pagehttp://phplens.com/lens/adodb/docs-adodb.htm...

it doesn't look beautiful but it shows you pretty much everything you need to know about adodb
Tony replied on at Permalink Reply
Tony
john: as a shortcut, you can also do something like this:

$countries = $db->getAll('SELECT * FROM jd_Country');