DZone Snippets: database code

PHP Dynamic Checkbox Table Creator (data retrieved from MySQL DB)

dneuma (Daniel Neumann) — Fri, 02 May 2008 12:09:34 GMT

Hi All.
This is a function for Dynamically Create a Checbox Table retrieving information for from a MySQL Database.
As it is quite commented, it's also good for learning how this things work :)
Hope you find it useful.
Feedback is welcome.
Cheers
Dan



function dynamic_checkbox_table ($sql_str, $col_label, $col_name, $val_checked="S", $cant_cols_tbl=3){

/*

	by Daniel Neumann

	this script creates dynamically permite a table containing checkboxes 

	getting the data for the checkboxes from a MySQL DB

	$sql_str, SQL select string to retrieve data from DB (see example in last comment line)

	$col_label, DB column that has values for the checkbox label 

	$col_name, DB column that has values for the checkbox name

	$val_checked="S", value when checked (value="" attribute) it uses the same value for all of them. If you whish to use a dynamic value from a DB, you should comment the line (it´s explained next to the code in the middle of the function) and de-comment the other line (check the code,. you'll understand what I mean). Also, you should use this parameter to specify the column name for the values

	$cant_cols_tbl=3, quantity of columns for the table, it defaults to 3

	usage example: dynamic_checkbox_table("SELECT * FROM keywords", "Keyword", "ID_Keywrd");

*/

	

	//connect DB and run query

	$db="MyDB";

	$db_user="MyUser";

	$pass="MyPass";

	$host="localhost";

	@mysql_connect($host,$db_user,$pass);

	@mysql_select_db($db) or die ("cannot connect to DB");

	$q_resultado = mysql_query($sql_str);

	mysql_close();

	if (mysql_num_rows($q_resultado)==0) exit("no rows returned");

	

	$next_row = mysql_fetch_array($q_resultado); //fetch first row

	

	$output = "\n"; //open table tag

	do {

		$output .= "\n"; //open row tag

		for ($i=1 ; $i <= $cant_cols_tbl ; $i++ ){ //loops as many times as $cant_cols_tbl

			$row=$next_row; //assign $row, next row will be checking next one, that avoids starting a new row when it's gonna be empty

			$output .= "\n"; //close TD

		} //close for loop

		$output .= "\n"; //close row

	} while ($next_row); //close do-while (and checks if there's another row)

	$output .= ""; //open TD tag

			$output .= (!$row) ? "" : ''.$row[$col_label]; //create checkbox and data from $row (**** you should comment this line if you whish to use dynamic $val_checked****)

//			echo (!$row) ? "" : ''.$row[$col_label]; //create checkbox and data from $row (**** you should de-comment this line if you whish to use dynamic $val_checked****)

			$next_row = mysql_fetch_array($q_resultado); //retrieve next row

			$output .= "\n"; //close table

	return $output; 

}

Rails Array#add_condition Method

timmorgan (Tim Morgan) — Mon, 18 Feb 2008 02:54:44 GMT

Lets you add a condition to a set of ActiveRecord conditions easily like this:



  conditions = ['active = ? and type = ?', true, 2]

  conditions.add_condition ['person_id = ?', 345]



class Array

  def add_condition(condition, conjunction='and')

    if condition.is_a? Array

      if self.empty?

        (self << condition).flatten!

      else

        self[0] += " #{conjunction} " + condition.shift

        (self << condition).flatten!

      end

    elsif condition.is_a? String

      self[0] += " #{conjunction} " + condition

    else

      raise "don't know how to handle this condition type"

    end

    self

  end

end

How to produce daily database table dumps in CSV format

ejboy (Fyodor Kupolov) — Fri, 16 Nov 2007 21:08:12 GMT

The following code demonstrates how to produce CSV files with dynamic
file name pattern based on a current day. The produced files have the following naming format:
TABLE_NAME_MM_DD_YYYY.csv







     

        table_name=test

    

          classpath="ojdbc14.jar" user="scott" password="tiger"/>

    

     

        SELECT * FROM ${table_name}

Use Scriptella ETL to run the example.

See How to execute an ETL file from command line, Ant or directly from Java .

Project Schema

tmcw () — Tue, 13 Nov 2007 21:41:43 GMT

// description of your code here



CREATE TABLE passenger (id serial NOT NULL, nickname character varying(255) NOT NULL, description text);



CREATE TABLE location (id serial NOT NULL, name character varying(255), latitude double precision, longitude double precision);

Project Schema

tmcw () — Tue, 13 Nov 2007 21:41:19 GMT

// description of your code here



CREATE TABLE passenger (id serial NOT NULL, nickname character varying(255) NOT NULL, description text);



CREATE TABLE location (id serial NOT NULL, name character varying(255), latitude double precision, longitude double precision);

Project Schema

tmcw () — Tue, 13 Nov 2007 21:41:08 GMT

// description of your code here



CREATE TABLE passenger (id serial NOT NULL, nickname character varying(255) NOT NULL, description text);



CREATE TABLE location (id serial NOT NULL, name character varying(255), latitude double precision, longitude double precision);

disable SQL / MySQL in rails logging in development mode

jm3 (john manoogian III) — Fri, 02 Nov 2007 00:09:50 GMT

// description of your code here



ActiveRecord::Base.logger = Logger.new("#{RAILS_ROOT}/log/#{RAILS_ENV}_database.log")

goes at end of config/env

Select DataBase Schema

dubby (Dave) — Wed, 17 Oct 2007 11:30:21 GMT

// Select database schema.
//This could be used to recreate or test for existence of columns/tables
//or could also be used to create a database template system to enable the writing //of database schema into txt template file to be recreated again by reading the //txt file via an application
//
//You can also use SELECT * instead of defining each schema property (column)



SELECT TABLE_CATALOG

, TABLE_SCHEMA

, TABLE_NAME

, ORDINAL_POSITION

, COLUMN_DEFAULT

, IS_NULLABLE

, DATA_TYPE

, CHARACTER_MAXIMUM_LENGTH

, COLLATION_NAME 

FROM 

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (N'Persons')

C#: Execute A Query & Return A Reader

cornerblue (CornerBLUE, Inc.) — Thu, 19 Jul 2007 22:02:34 GMT



public static SqlDataReader GetReader(string Query)

{

	string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["CONNECTION_STRING_NAME"].ConnectionString;

	SqlConnection con = new SqlConnection(ConnectionString);

	SqlCommand command = new SqlCommand();



	command.Connection = con;

	command.Connection.Open();

	command.CommandText = Query;

	return command.ExecuteReader();

}

dbms database class

nerdcoder (Dan Larsen) — Tue, 03 Jul 2007 21:46:51 GMT

I'm at my first attempt at creating something for the PyS60 and I needed a simple sql wrapper class...
I'm an experienced programmer, but has never programmed in python for s60 and it's been a while since I've used Python... So I hope this will help somebody, not annoy ;-)



# Filename: db.py save in your Python dir

import e32db



class db:

    def __init__(self, dbpath):

	self.db = e32db.Dbms()

	self.dbv = e32db.Db_view()

	self.reset_counters()

	try:

	    self.db.open(unicode(dbpath))

	except:

	    self.db.create(unicode(dbpath))

	    self.db.open(unicode(dbpath))



    def reset_counters(self):

	self.affected_rows = 0

	self.num_rows = 0

	self.__internal_counter = 0



    def query(self, sql):

	self.reset_counters()

	if sql.lower().startswith('select'):

	    self.dbv.prepare(self.db, unicode(sql))

	    self.dbv.first_line()

	    self.num_rows = self.dbv.count_line()

	else:

	    self.affected_rows = self.db.execute(unicode(sql))



    def next(self):

	row = {'id': 0}

	if self.num_rows < 1:

	    self.reset_counters()

	    raise StopIteration

	elif self.__internal_counter < self.num_rows:

	    self.dbv.get_line()

	    for i in range(self.dbv.col_count()):

		row[i] = self.dbv.col(i+1)

	    self.dbv.next_line()

	    self.__internal_counter += 1

	    return row

	else:

	    self.reset_counters()

	    raise StopIteration



    def __iter__(self):

	return self

Now to create and fill db, create a file in the same dir as the db.py with this content:



# Change __exec_path to the path of your python script dir

__exec_path = "E:\\Python\\"

dbname = "test"



import sys

sys.path.append(__exec_path)

from db import db



# This will open E:\\Python\test.db - it will be created first, if not existing

mydb = db(__exec_path+dbname+'.db')

mydb.query("create table testing (id counter, name varchar)")

mydb.query("insert into testing (name) values ('test 1')")

mydb.query("insert into testing (name) values ('test 2')")

Now go ahead and have fun:



# Again change __exec_path to the path of your python script dir

__exec_path = "E:\\Python\\"

dbname = "test"



import sys

sys.path.append(__exec_path)

from db import db



# Opens E:\\Python\test.db

mydb = db(__exec_path+dbname+'.db')

mydb.query("select * from testing")

for row in mydb:

    print "-> ",row[0]," ",row[1]," <-"

Have fun!
Dan