Sorry I'm just really happy because I've written some sexy code to contruct SQL queries and I thought I'd share my news LOL

Snippet:


Hehe, you can try it out with something like:

print foo('Some_Table', { Col1 => 'Val1', Col2 => 'Val2' }, ['Col1', 'Col2', 'Col3'], 'AND' );

Until you want to do Foo LIKE '%bar%', or Foo > bar. Or left joins, or functions.. fun fun fun! =)

Good start though!

Cheers,

Alex
--
Gossamer Threads Inc.

It was only a snippet. I'm working on it now.

Please don't think I'm copying GT::SQL either because I really am not. That is a waste of time for me as I only get satisfaction from writing my own code.

Anyway I hope you can see from the example above that it is nothing like it.

I just shifted the order of the arguments so it now looks something like:

my $sth = $DB->select( ['ID','Name'], 'Forums', { SubCat => 0, ID => '1' }, 'AND' );

...but I can also do:

my $foo = $DB->select( ['ID','Name'], 'Forums', { SubCat => 0, ID => '1' }, 'AND' )->fetchrow_hashref;

....and so on as the select method returns $sth.

I find that order easier as it is in the order of the query.

SELECT Cols FROM Table WHERE BLA='Bla' AND BLA='Bla'

No problems, I'm only trying to give you some tips now of things you will run into. May want to make sure your calling interface is flexible enough to handle it later.

Cheers,

Alex
--
Gossamer Threads Inc.

I definitely appreciate any advice. I accept what you are saying - just have to get my thinking cap on about how to accomodate every possible query :)

What are your thoughts on this?

It will now support queries like:

SELECT Cols FROM Table WHERE Col1='Foo' AND Col2='Bar'

or

SELECT Cols FROM Table WHERE Col1='Foo' AND Col2 LIKE '%Bar%'

or

SELECT Cols FROM Table WHERE Col1 LIKE '%Foo' AND Col2 LIKE 'Bar%'

Does the code look ok or messy? Hmm this should probably be in the perl forum now...


Something like:

$DB->select( ['ID','Name'], 'Forums', { SubCat => 'Hello', Foo => '%bar%' }, 'AND', { LIKE => ['Foo'] } );


...would create.......

SELECT ID,Name FROM Forums WHERE SubCat='Hello' AND Foo LIKE '%bar%'

Forget that, it's crap - I've redone it.

Hi

Can I ask what the purpose of such a sub is? Wouldn't it be easier just to write out your query, instead of defining it and then getting a script to rewrite it. Surely this takes more time?

- wil

Exactly what I was thinking. I am guessing its cause some DB's use other SQL formats so that you can make diff subs arrange it how you like and don't need to change the query? I don't have much experience with a lot of DB's.
Cheers,
Michael Bray

Well when your code becomes more advanced you realise the benefits.

When you have about 10+ modules most of which need to use SQL queries then you'll find that requiring DBI in all and having loads of SQL queries all over the place looks ugly/messy and is in fact _slower_.

Doing it this way keeps all SQL code in one central module meaning you can just require the $DB object into your other modules on demand speeding things up.

Just think, if you have 100+ queries in your entire script, preparing, executing and disconnecting every query would add about 300 lines to your script. Doing it this way requires 3 lines.

OK. No, I don't get this at all. Why would writing


be faster than writing


????

- wil

Well errr because when do you ever just write:

SELECT ID,Name FROM Forums WHERE SubCat='Hello' AND Foo LIKE '%bar%'

....in a perl script

I think you'll find you need...


I want my code to be professional looking, neat and organized, not in a big pile with queries and functions hiding all over the place. That would be impractical to manage and just ugh....

No. You can have all your connection routines in a sub, and then just pass the following line to the sub to get your results:


Why put it into a new data strucutre, just to untangle it at the other end? You're making more work for yourself for no reason.

- wil

That is completely the opposite of reality.

Anyway don't have time to explain it to you. Gotta get my damn car serviced.

What car 'ya got?

- wil

Well in my PHP scripts I have a connection sub routine. I assign the db connection to $DB and pass that along.

so if i have a function that needs to do something I just require the $DB


Having it that format allows me to access the result in a variety of different formats. I can pass the result into num_rows or get the result in an array etc.

Its handy being able to passaround the $DB connection, but keeping the rest seperate is a lot easier.
Cheers,
Michael Bray

>>Well in my PHP scripts I have a connection sub routine. I assign the db connection to $DB and pass that along.

so if i have a function that needs to do something I just require the $DB
<<

Well thats exactly the same principal as mine.

I use:

use Loader qw/$DB/;

...to load the database object and use $self->{dbh} for the database handle.

>>Having it that format allows me to access the result in a variety of different formats. I can pass the result into num_rows or get the result in an array etc.
<<

Mine will do the same......

my @array = $DB->select(['Col'], 'Table', { ID => 1 } )->fetchrow_array;

-OR-

my $hashref = $DB->select(['Col'], 'Table', { ID => 1 } )->fetchrow_hashref;

-OR-

my @array = $DB->select(['Col'], 'Table', { ID => 1 } )->fetchrow;

-OR-

my $arrayref = $DB->select(['Col'], 'Table', { ID => 1 } )->fetchall_arrayref;

-OR-

my $sth = $DB->select(['Col'], 'Table', { ID => 1 } );
while (my ($tags) = $sth->fetchrow_hashref) {
print $tags->{ID};
}


...much nicer IMO.

But your way has to do the query twice if you want to do a num_row and fetchrow_array.

My way should store the result in memory and allow me to access it in 2 ways from the one query. Not sure if it does store in memory actually... but I am fairly sure it does.


Cheers,
Michael Bray

A num_row?

Is that a COUNT in php?

Well,

Count(*) is an SQL feature. mysql_num_rows gives you the ammount of rows in an SQL query. So you can say.


The actual function name is mysql_num_rows. I just made a basic DB wrapper I use for my scripts so I can easily switch to Postgres later on. Using count(*) would be faster if you only want the number of results - but I think this way saves me doing a query which is better.
Cheers,
Michael Bray

You can do that with mine without extra queries. Using something like:

my $sth = $DB->select(['Col'], 'Table', { ID => 1 } );

..returns $sth. So anything you can normally do with $sth will work eg...

$sth->rows;

$sth->finish;

etc

Oh OK. So basically what we are doing is exactly the same except for rearranging the query which I still don't get why you are doing it :-)
Cheers,
Michael Bray

Hi,

Well, with perl using ->rows is not a reliable way to get the number of rows returned. From DBI:


Cheers,

Alex
--
Gossamer Threads Inc.

>>rearranging the query which I still don't get why you are doing it :-)<<

Because it means all the SQL code is kept in one module and it shortens the code as I don't need to write full queries. If I did it the way you are suggesting I'd have to write whole queries and pass them as arguments to the SQL module whereas this way I can just send the necessary arguments and the query is built dynamically.

It is also more flexible. Doing it your way (or Wil's) by passing the full query to your database sub like....

&somesub("SELECT Bar FROM Foo ORDER BY Bla LIMIT 1");

....how would you tell the sub to use fetchrow_array or fetchrow_hashref etc?

What if you needed to fetchall but wanted to format the data? ....it couldn't be done, or would sure take a lot of pissing about.