Query clean-up ..

[tyson.crouch]

I'm sitting my CMA next week, and am rather embarrassed that i have to ask this.

i have some java code, that i'm looking to clean up. and in it is my SQL queries. I've tried running joins on my tables but none quite get the effect that i've achieved with my following code.

If you could please help with the MySQL it would be much appreciated.

try {
            Connection con = new database().getConnection();
            Statement stmt = con.createStatement();

            /* SELECT ALL PACKAGE DETAILS */
            String packageDetails = "SELECT * FROM packages";
            ResultSet res = stmt.executeQuery(packageDetails);

            while(res.next()) {
                count++;
            }

            /* CREATE NEW attractionPackage array */
            packageList = new attractionPackage[count];
            res.beforeFirst();
            while (res.next()) {
                packageList[i] = new attractionPackage();
                packageList[i].setPackageID(res.getInt("pacID"));
                packageList[i].setPackagePrice(res.getDouble("packagePrice"));
                
                try{
                    Connection connect = new database().getConnection();
                    Statement statement = connect.createStatement();
                    /* SELECT ALL DETAILS FROM attractionpackages WHICH CORRESPOND TO THE CURRENT pacID */
                    String query = "SELECT * FROM attractionpackages WHERE pacID = '"+packageList[i].getPackageID()+"' ORDER BY pacID ASC";
                    ResultSet results = statement.executeQuery(query);
                    int c  = 0;
                    int x = 0;
                    while(results.next()){
                        c ++;
                    }

                    String[] attractions = new String[c];
                    results.beforeFirst();

                    while(results.next()){
                        attractions[x] = results.getString("attID");
                        x++;
                    }
                    packageList[i].setAttractions(attractions);

                }catch (SQLException e) {
                    e.printStackTrace();
                }
                
                i++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

My table structure is as follows;

CREATE TABLE IF NOT EXISTS `packages` (
  `pacID` int(11) NOT NULL AUTO_INCREMENT,
  `packagePrice` double NOT NULL,
  PRIMARY KEY (`pacID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

CREATE TABLE IF NOT EXISTS `attractionpackages` (
  `attID` varchar(255) NOT NULL,
  `pacID` int(11) NOT NULL,
  PRIMARY KEY (`attID`,`pacID`),
  FULLTEXT KEY `attID` (`attID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[pritaeas]

Do you mean you want to do this:

SELECT * FROM packages p,attractionpackages a WHERE p.pacID = a.pacID ORDER BY pacID ASC

[tyson.crouch]

actually, yes lol.

I was thinking that i might need to join the tables, but this works.
From here, how do i iterate through the individual items, for example;

If pacID = 19, and there are 4 items which correspond to the given pacID how do i iterate through those?

My final query:

SELECT `attID`, packagePrice, packages.pacID
FROM attractionpackages, packages
WHERE attractionpackages.pacID = packages.pacID
ORDER BY pacID ASC

[pritaeas]

You'll have to use current_id/previous_id in your loop to determine at what point the pac_id changes. Something like (pseudocode):

previous_id = -1
while () {
  current_id = pac_id
  if (current_id <> previous_id) {
    // here starts a new pac_id, process the first
    previous_id = current_id
  }
  else {
    // process the next (same) pac_id with a different att_id
  }
}