HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


Database manipulation in OCP

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#101600 (In Topic #19928)
Avatar

Community saint

Hi,

I have created a simple html/php page that allows me to enter some form fields and submit the data to a MySQL database. I have this working well and wanted to get it working without the complexities of OCP, making sure I understand the base technology first !

I am now going to fit this in to the OCP framework.

To avoid SQL Injection amongst other vulnerabilities I shall use the database abstraction layer described here:
ocPortal Developer's Guide: Database access and manipulation - ocPortal.com

One of the features that I used in my initial design is that some of the fields are stored as ENUMs in the database, I cannot see how I can get the ENUM list out using the abstraction layer - although I think that one of the functions lets you pass straight SQL through - is that the way to go ?

I will use Comcode pages for the html and a miniblock for the php - can anyone see an issue with doing that ?

I know that it would be good to use templates and languages but I think that will come much later.

Your thoughts greatly appreciated.

Cheers
Ade
Back to the top
 
Posted
Rating:
#101604
Avatar

Hi,

So, we haven't used ENUMs as they're not standard SQL, but that shouldn't stop you (everyone here uses MySQL anyway to be honest).

According to the MySQL manual:

MySQL manual said

To determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE 'enum_col' and parse the ENUM definition in the Type column of the output.

You're right, our query method allows direct SQL. The actual data types coming out of / going into, MySQL for select/insert/update queries, will be strings or integers, so there's no complexity in terms of data values.

In terms of asking MySQL for the possible enum values…

The query method is smart enough to know that SHOW queries return a result, so you'll get MySQL rows right back. We try and keep the APIs very simple.

The clunky thing here is the parsing of what MySQL gives out!

I did a test inside data_custom/execute_temp.php which is our official playground file.

I created a test table:


I queried the SHOW stuff:



I wrote a quick parser:



Here's my code…

PHP code

function execute_temp()
{
    
$r=$GLOBALS['SITE_DB']->query("SHOW COLUMNS FROM test_tbl LIKE 'test_col'");
    
$enum_vals=array_map('quote_trim',explode(',',preg_replace('#^.*\((.*)\).*$#','$1',$r[0]['Type'])));
    @
print_r($enum_vals);
}

function 
quote_trim($in)
{
    return 
trim($in,"'");
}



Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#101606
Avatar

Part 2 - running regular queries

I should have actually had the table prefix on the start of my table name, so I am renaming test_tbl to ocp9_test_tbl (my prefix is ocp9_.

This allows me to use other parts of ocPortal's query API, as consistent table prefixing is assumed (i.e. the prefix is auto-added to any supplied table names) when you're not writing manual SQL.

PHP code

$GLOBALS['SITE_DB']->query_delete('test_tbl'); // Wipe current table contents

// Put some stuff in, trying with both the enum values, and enum indexes
$GLOBALS['SITE_DB']->query_insert('test_tbl',array('test_col'=>'a'));
$GLOBALS['SITE_DB']->query_insert('test_tbl',array('test_col'=>2)); // 2nd index relates to 'b' (the second enum value)

// Should the contents
@print_r($GLOBALS['SITE_DB']->query_select('test_tbl'));




Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#101617
Avatar

Community saint

Hi Chris,

Thanks for taking the time to run through that.

I had managed to get something similar working but not as neat as yours !!

I would never of thought of using array_map and trimming all at once.

What I need to do achieve is starting to come together !

Cheers
Ade

Back to the top
 
There are too many online users to list.
Control functions:

Quick reply   Contract

Your name:
Your message: