Behçet Mutlu
Archive for category MySQL Database
Building the Best SQL Manager With PHP (Part 2)
Posted by bekco in Data Querying, Database Administration, MySQL Database, PHP, Programming Languages, Software Engineering, SQL on May 31, 2011
After a long day work on a complicated class I finally finished my requiered querying algorithm. The final output of my SQL Manager class is like
select `adproperties`.*, `properties`.* FROM `adproperties` left join (select * FROM `advertisementproperties` WHERE ((`advertisementproperties`.`advertisement_id` = '113'))) `properties` on (`properties`.`adproperty_id` = (`adproperties`.`id`)) WHERE ((`adcategory_id` = (select `advertisements`.`adcategory_id` as adcategory FROM `advertisements` WHERE (((`advertisements`.`id` = '113') and (`advertisements`.`account_id` = '1'))))))
This worked for me perfectly. Even I add some more conditions on it. Later I’ll have time to fix the paranthesis problem but for the beginning, I’ll keep using this code instead of the old one.
I must finaly point that, I have not tried it with other kind of configuration array but after I finish the whole it will be the perfect SQL Manager Class written in PHP ever.
Building the Best SQL Manager With PHP
Posted by bekco in Data Querying, Database Administration, MySQL Database, PHP, Programming Languages, Software Engineering, SQL on May 29, 2011
Today I decided to improve one of my developed SQL Manager class to be able to create easy sql queries but complicated enough not to be able ease work on defining the complicated queries. With my old solution I could not be able to complete a query like below.
SELECT `adproperties`.*, `properties`.* FROM `adproperties` LEFT JOIN (SELECT * FROM `advertisementproperties` WHERE `advertisementproperties`.`advertisement_id` = 110) `properties` ON `properties`.`adproperty_id` = `adproperties`.`id` WHERE `adcategory_id` = (SELECT `advertisements`.`adcategory_id` as `adcategory` FROM `advertisements` WHERE `advertisements`.`id` = 110)
It is something right? So, what I need to do is adding inner queries up on my old manager even on the Joining phrases and Where clauses. It is not easy to build a complete solution for SQL because you would always think on the current problem to be fixed not the whole. Even though, I could have been able to build a generalization to create search queries on the relational queries of which is also used in many projects of mine.
Now what I can do with it is like:
SELECT `advertisementproperties`.`id`, `advertisementproperties`.`advertisement_id`, `advertisementproperties`.`value`, `adproperties_rel`.`options`, `adproperties_rel`.`type`, `adproperties_rel`.`adcategory_id`, `adproperties_rel`.`id` as `adproperty_id`, `adproperties_rel`.`name` FROM `advertisementproperties` RIGHT JOIN `adproperties` `adproperties_rel` ON `adproperties_rel`.`id` = `advertisementproperties`.`adproperty_id` WHERE ( (`adproperties_rel`.`account_id` = '1') or ISNULL (`adproperties_rel`.`account_id`)) and ( (`advertisement_id` = 113) or ISNULL (`advertisement_id`)) and (`adproperties_rel`.`adcategory_id` = '22')
This code above is not able to do what I needed and it is not a good SQL query either. What I really need to do is to create better querying solution for it.
I started from the end to internal core of the code and firstly defined the object that I would like it to create my query:
$sqlMgr = new SQLManager(array( "method" => "select", "selections" => array("adproperties" => "*", "properties" => "*"), "tableName" => "adproperties", "relations" => array( "properties" => array( "join" => "left", "tableName" => array( "query" => true, "method" => "select", "tableName" => "advertisementproperties", "conditions" => array("advertisementproperties" => "advertisement_id", "=" => 113) ), "conditions" => array("properties" => "adproperty_id", "=" => array("adproperties" => "id")) ) ), "conditions" => array( "adcategory_id", "=" => array( "query" => true, "method" => "select", "tableName" => "advertisements", "selections" => array("advertisements" => "adcategory_id"), "fieldAliases" => array( array( "table" => "advertisements", "name" => "adcategory_id", "alias" => "adcategory" ) ), "conditions" => array(array("advertisements" => "id", "=" => 113), "and" => array("advertisements" => "account_id", "=" => 1)) ) ) ));
This code should definitely do what I would like to do with my SQL Manager Class. Now is the time to get this configuration in to action.
With my old SQL Helper I’m no where close
SELECT `*`.*, `*`.*, `l_rel`.`l` as `l`, `adproperties`.`_rel` FROM `adproperties` L JOIN `l` `l_rel` ON `l_rel`.`l` = `adproperties`.`l` JOIN `` `_rel` ON `_rel`.`` = `adproperties`.`` WHERE ( (`advertisements`.`adcategory_id`) and (`_rel`.`Array`)) and ()
Some of the generated query is right how it supposed to be. I’ll start from the selections.
-
You are currently browsing the archives for the MySQL Database category.
Get Premium Web Templates Now!
Navigator
What am I doing?
-
iPad Mini mi yoksa iPad 4 mü almal? - http://t.co/Zuf4N7eu3n Bilgi http://t.co/FMEe40FFOk 9 years ago
-
Tag Cloud
Post Calendar
May 2022 M T W T F S S « Jul 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Support This Blog!