e_i_pi's Profile User Rating: *****

Reputation: 823 Master
Group:
Mentors
Active Posts:
1,757 (0.73 per day)
Joined:
30-January 09
Profile Views:
53,344
Last Active:
User is offline Aug 30 2015 05:52 PM
Currently:
Offline

Previous Fields

Country:
AU
OS Preference:
Windows
Favorite Browser:
FireFox
Favorite Processor:
AMD
Favorite Gaming Platform:
Playstation
Your Car:
Mitsubishi
Dream Kudos:
75
Expert In:
Databases

Latest Visitors

Icon   e_i_pi has set their status

Posts I've Made

  1. In Topic: Group By And Concatenate Rows

    Posted 13 Aug 2015

    Tough problem, but thankfully there is a solution. Since I don't have your database in front of me, this solution may not work, but it could. It uses something called FOR XML PATH, which is a way of converting rows to concatenated string data. I won't go into the gory details, you can research that for yourself. I think this will work:

    Spoiler


    Now for the explanation:

    The WITH statement up the top is used to declare a CTE (Common Table Expression). CTEs can be used to define a subquery that is used throughout the remainder of your SQL script. You can declare multiple CTEs, and CTEs can reference each other or even themselves (handy for mapping out hierarchies). So, I have wrapped your original query in a CTE, as we'll need it multiple times later on. Note that I have removed the ORDER BY clause on the CTE - we won't need it. Note also that I have named the CTE simply "CTE". You could name it Bob, Harry, MyAwesomeCodez, whatever.

    Immediately after the CTE, we have our SELECT statement, which generates the final results. The basic statement is:
    SELECT
    	SchoolName,
    	LocationName,
    	/* some code */ AS Counties,
    	SUM(SchoolCount) AS SchoolCount,
    	IRN,
    	/* some code */ AS GradYears
    FROM CTE AS CTE2
    GROUP BY
    	SchoolName,
    	LocationName,
    	IRN
    ORDER BY LocationName ASC
    
    

    Let's go through that code to start with. We're adding in the ORDER BY clause here, as this is our final output, where we need it. We're also SUMming up the SchoolCount, which means we need a GROUP BY clause at the end. Note that we have only used the GROUP BY clause on SchoolName, LocationName and IRN. We haven't used it on Counties or GradYears, as they are using the FOR XML PATH code, which like other aggregates doesn't need to be grouped.

    Now let's look at the code for Counties, piece by piece. You'll notice the code looks like this:
    STUFF(
    	(
    		SELECT ', ' + CTE1.County  AS [text()]
    		FROM (
    			SELECT DISTINCT
    				SchoolName,
    				LocationName,
    				County,
    				IRN
    			FROM CTE
    		) AS CTE1
    		WHERE CTE1.SchoolName = CTE2.SchoolName
    		AND CTE1.LocationName = CTE2.LocationName
    		AND CTE1.IRN = CTE2.IRN
    		ORDER BY CTE1.County
    		FOR XML PATH ('')
    	), 1, 2, ''
    ) AS Counties,
    
    

    WOW, what an eyesore! So let's break it down piece by piece.

    Firstly, we'll look at the innermost SELECT DISTINCT query:
    SELECT DISTINCT
    	SchoolName,
    	LocationName,
    	County,
    	IRN
    FROM CTE
    
    

    This query should be pretty self-explanatory, but why are we selecting just this data and omitting SchoolCount and GradYears? Well, we don't need those for this part of the query. Here we are just trying to generate a unique list of counties by SchoolName/LocationName/IRN. We'll use this query to join against the CTE later on, and concatenate the counties together. In order to join "uniquely" we need to use the unique identifier of the outermost query by using the same GROUP BY columns.

    Next, let's look at this part:
    SELECT ', ' + CTE1.County  AS [text()]
    FROM (
    	/* subquery */
    ) AS CTE1
    WHERE CTE1.SchoolName = CTE2.SchoolName
    AND CTE1.LocationName = CTE2.LocationName
    AND CTE1.IRN = CTE2.IRN
    ORDER BY CTE1.County
    FOR XML PATH ('')
    
    


    Hmm, we have that FOR XML PATH thing happening here. So what is it doing? Well, it's ensuring that we only bring back one row of data, and concatenating that data together as a string separated by the string , . The WHERE clause ensures that we are selecting distinctly over the SchoolName/LocationName/IRN, and the ORDER BY clause ensures that the counties will be listed in alphabetical order once concatenated. So what does this concatenation look like? Well, if we had the counties "Delaware" and "Franklin", then the concatenation would look like this:

    , Delaware, Franklin

    ...which brings us to:
    STUFF(
    	(
    		/* subquery */
    	), 1, 2, ''
    ) AS Counties,
    
    

    The bizarrely named function STUFF is very handy for changing the front end of a string. It takes four arguments: 1) the string, 2) start position, 3) number of characters, 4) replacement string. So here, we are using the STUFF function to simply remove the first two characters of the concatenated string, which is that leading , .

    Now, as I said I can't guarantee that this will work, there may be a syntax error somewhere, or the output might be a bit screwy, but that's just because I don't have your database in front of me. Give it a go, and see how it pans out for you.
  2. In Topic: Triggers in postgreSQL and pgAdmin

    Posted 13 Aug 2015

    Just to be clear, are you talking about pgAdmin, or phpPgAdmin? In phpPgAdmin, you expand the following:
    Schemas >
     {SchemaName} >
       Tables >
        {TableName} >
          Triggers
    
    

    I'm not at home right now, so can't check pgAdmin, but I'll have a look in a few hours.
  3. In Topic: The merits of pdo and prepared statements.

    Posted 11 Aug 2015

    CTphpnwb said:

    I haven't used Postgre, but I believe it's also vulnerable to injection.

    Yes it is, but it's not vulnerable to the old '; DELETE EVERYTHING YEEHAW;--' trick, so I'd put it in the same category as MySQL in that regard.

    View Postastonecipher, on 12 August 2015 - 02:21 PM, said:

    The slow downs generally come when querying our automotive databases. The kinds that have every make, model, year, engine, transmission, and part# for every vehicle made since 1950.

    Some of the queries are a good 60 lines with several joins and his argument, that I see valid, is hitting the database twice with a prep statement slows it down when it may not be needed. Where as prepared statements are used when making several hits for changing values.

    So this comes down to poor querying. I used to write monstrous SQL, and my application still has some monsters in it (50-150 lines). Then I started working at my new workplace, and found they used temporary tables almost all the time. It's a great technique, and the basic pattern is this:
    • Drop the temporary table if it exists
    • Create the temporary table
    • SELECT INTO / INSERT INTO the temporary table from the monster table, using as many filters as possible
    • INDEX the temporary table
    • Use the temporary table in lieu of the actual table for the remainder of the query
    • Drop the temporary table (usually not required if your SQL is correct - see the CREATE TABLE statement below in the spoiler)

    Sounds like a pain in the neck, but once you get used to INDEX and CREATE TABLE syntax, it's quite easy. It also makes it quite easy to debug. I've seen this technique cut query times by 70-90% time and time again. With PostgreSQL, since you cannot include ';' characters in a prepared statement, you need to put the script in a stored procedure and call it that way. Here's an example of the contents of the stored proc:

    Spoiler


    Of course, this is how I think database connections and querying should be approached and handled. It sounds like you have a difficult boss on your hands.

    Tell him that binding variables in prepared statements requires you to pass the data type, which helps the PostgreSQL engine determine the best indices to use during execution planning, which in turn helps speed up query times. That's kind of a half-truth, but from the sounds of it, he won't have the nous to refute the statement.
  4. In Topic: Attempting to add a object to an array in another class.

    Posted 29 Jun 2015

    You're clobbering the class variable with the parameter you're passing, rather than adding it as a new array element. Try this instead:
    function addStudent($student) {
        $this->registered_students[] = $student;
    }
    
    
  5. In Topic: Find all elements/child elements that have a class (not className)

    Posted 1 Jun 2015

    For future readers / searchers who are wondering how to do this with jQuery, you use the .find() method, like so:
    var $foundClassNameElements = $('#InitialSelector').find('.foundClassName');
    
    

My Information

Member Title:
= -1
Age:
41 years old
Birthday:
June 24, 1974
Gender:
Location:
Australia
Years Programming:
4
Programming Languages:
HTML, XML, jQuery, PHP, SQL, MySQL, VBA, VBScript, PostgreSQL

Contact Information

E-mail:
Private
Website URL:
Website URL  http://www.artofwar.cc/

Comments

Page 1 of 1
  1. Photo

    cupidvogel Icon

    09 Jun 2012 - 11:30
    Ha ha, I really liked your "if(navigator.appName == 'Microsoft Internet Explorer')" theme!
  2. Photo

    Duckington Icon

    04 Apr 2012 - 00:57
    It's not live yet, still developing and then going into beta test. I haven't actually decided on a name yet, so no domain for it.
  3. Photo

    modi123_1 Icon

    13 Mar 2012 - 06:56
    Ha.. well good luck with it.. it's always a good butt of a joke.
  4. Photo

    RudiVisser Icon

    08 Mar 2012 - 15:20
    You know, I only just worked out what your avatar is. Love it!
  5. Photo

    RudiVisser Icon

    06 Sep 2011 - 00:37
    Thank you very much :-D
  6. Photo

    RudiVisser Icon

    06 Sep 2011 - 00:37
    Thank you very much :-D
  7. Photo

    e_i_pi Icon

    05 Sep 2011 - 22:15
    Ah thank you :) I'm a fan of minimalism, so I wwanted something nice and simple, plus you just can't go past red and black!
  8. Photo

    no2pencil Icon

    05 Sep 2011 - 19:52
    Love your avatar!
  9. Photo

    Sayid Ahmed Icon

    13 Jul 2011 - 14:02
    thanks for the add.
  10. Photo

    Dormilich Icon

    17 Feb 2011 - 13:25
    just wanted to congratulate for the funny thread title "OOP, PDOs, pain, tears".
Page 1 of 1