how to hash this?

[bazz]

I need to make the following MySQL query only output a specific thing once. so as more records are entered to the DB, Accommodation (and the other fields values), will only output once whether there is one record or 1000's.

I am trying to build a triple combo but I need to wokr this out before I try to write to it.

Please try to point me in the right direction.

Ooops here's the query so far


my $sth = $dbh->prepare("SELECT
Business_Type_ID,
Business_Type,
Business_Sub_Type_ID,
Business_Sub_Type,
Business_Category_ID,
Business_Category
FROM
$table
left outer join tbl_businessType
ON
$table.Business_Type_ID = tbl_businessType.Type_ID
left outer join tbl_businessSubType
ON
$table.Business_Sub_Type_ID = tbl_businessSubType.Sub_Type_ID
left outer join tbl_businessCategory
ON
$table.Business_Category_ID = tbl_businessCategory.Category_ID

");
$sth->execute;
while (my @fields = $sth->fetchrow_array) {

print qq(@fields\n);
# <option value="$fields[0]">$fields[1]</option>
# );
}
$dbh->disconnect;



The output from that is this:

00000000001 Accommodation 00000000001 Bed and Breakfast 00000000005 Approved


bazz

[FishMonger]

I'm not 100% sure what you're needing to accomplish, but it sounds like you're going to want to use the LIMIT clause. You may also need the DISTINCT or UNION clause.

[bazz]

just seen FishMonger's post so I shall look into those

Worked this out some more but is this the efficient way to do it. I am trying to ensure that each value/field from Db is shown once irrespective of how many records are (for eample) of one businessType.


my $sth = $dbh->prepare("SELECT
Business_Type_ID,
Business_Type,
Business_Sub_Type_ID,
Business_Sub_Type,
Business_Category_ID,
Business_Category
FROM
$table
left outer join tbl_businessType
ON
$table.Business_Type_ID = tbl_businessType.Type_ID
left outer join tbl_businessSubType
ON
$table.Business_Sub_Type_ID = tbl_businessSubType.Sub_Type_ID
left outer join tbl_businessCategory
ON
$table.Business_Category_ID = tbl_businessCategory.Category_ID

");
$sth->execute;
while (my @fields = $sth->fetchrow_array) {
push (@businessType, $fields[1]);
push (@businessSubType, $fields[3]);
push (@businessCategory, $fields[5]);

}
$dbh->disconnect;

foreach my $bizType (@businessType) {
$hash{$newBusinessType} = $bizType; #builds the hash
}

print qq( hash=$hash{$newBusinessType}); #outputs the value





bazz

[bazz]

OK, one step at a time bazz...

Guys I am trying to build a hash from the following MySQL array, @fields.

Surely this is not the best way? I am at a loss as to any other though so your advice would be most welcome.



... after the initial query,

while (my @fields = $sth->fetchrow_array) {
print qq(
af=@fields<br />
);
# build the hash here
$enteredBusinessData{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{$fields[6]}{$fields[7]};


bazz

[FishMonger]

Do you simply want a hash or do you want a more complex structure i.e., a multidimensional hash? Your last try builds a HoHoHoHoHoHoH (hash of hashes of hashes of hashes etc).

Read these parts of the DBI doc to see if it's what you want.
fetchrow_hashref
http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchrow_hashref

fetchall_hashref
http://search.cpan.org/~timb/DBI-1.601/DBI.pm#fetchall_hashref

[FishMonger]

Maybe you want an AoH (array of hashes). If so, use fetchrow_hashref and as you loop through the returned data, push the hash onto the array.
while ( my $hash_ref = $sth->fetchrow_hashref ) {
push @AoH, $hash_ref;
}

[bazz]

Thanks FishMonger I 'll look into those.

While you seem still to be about, I'll clarify what I want incase I need to look into something else as well.

in an array, I can output all this:


Accommodation Bed & Breakfast Approved London England
Accommodation Guesthouse 3 Star London England
Eateries Bistro Mediterranean London England
Eateries Restaurant Michelin London England


I need to get it like this (or something along these lines)



Accommodation Bed&Breakfast Approved London England
Guesthouse 3Star
Eateries Bistro Mediterranean
Restaurant Michelin


so that my select boxes for BusinessType, SubType and Category each have a value outputted only once.

bazz

[FishMonger]

It's unclear from the depiction of your array output how those values correspond with the db fields, but I'd say your database isn't completely or properly normalized, which is why you're having a hard time extracting the data in the manor you need.

[FishMonger]

Another sql clause you will be needing is order by and you'll probably want to use that field as the key for the fetchall_hashref.

[bazz]

re. normalisation, I think it is normalised. However, instead of me gathering data from the tables 'tbl_Type' 'tbl_SubType, 'tbl_Category', I have been trying to get it from tbl_businessDetails because I want only to show those Types subtypes and categories, which already have relevant data in the Db.

so my tables are like this (some of them).


tbl_Type
|id | Accommodation|

tbl_SubType
|id | Guest House |

tbl_Category
|id | 3 Star|

tbl_BusinessDetails
|id | businessType_ID | business_SubType_ID | business_Cat_ID|



bazz

[FishMonger]

Ok, that structure makes more sense.

So, now onto building the hash. Without having the ability to work directly with your data, I can't be sure, but I think this might be what you are wanting.

use Data::Dumper; # for testing

my %enteredBusinessData;
while (my @fields = $sth->fetchrow_array) {


# I'm assuming that the 'Accommodation' and 'Eateries' values are in $fields[0]
# and will be the keys to the hash

my $key = shift @fields;
push @{$enteredBusinessData{$key}}, [@fields];
}

print Dumper \%enteredBusinessData;

If that is close, you might look at fetching the data as an array_ref or hash_ref.

[bazz]

Thank you FishMonger it seems to be closer :)

the output ois shown like this

$VAR1 = { '00000000003' => [ [ 'Dining Out', '00000000007', 'Bistro', '00000000013', 'Mediterranean', '00000000008', 'Shropshire' ] ], '00000000001' => [ [ 'Accommodation', '00000000001', 'Bed and Breakfast', '00000000005', 'NITB Approved', '00000000008', 'Down' ], [ 'Accommodation', '00000000002', 'Guest House', '00000000002', '3 Star', '00000000008', 'Shropshire' ] ] };



I shall keep going with the reading you sent me.

bazz

[bazz]

OK, I have tried a has_ref but it fails and even though I have read up on it, I donlt fully understand. especially the numbers showing




$sth->execute;


while ($hash_ref = $sth->fetchall_hashref('Business_Type')) {
print "Name for id 00000000001 is $hash_ref->{00000000001}->{Business_Type}\n";
}
}


The error shows as this

DBD::mysql::st fetchall_hashref failed: fetch() without execute() at tripleCombo line 371


:scratches head til it bleeds:

bazz

[FishMonger]

If you use fetch_all then it fetches all requested data without needing to use a while loop.

Try this:
use Data::Dumper;

my $sth = $dbh->prepare(your select statement);
$sth->execute;
my $hash_ref = $sth->fetchall_hashref('Business_Type');

print Dumper $hash_ref;

[bazz]

That looked promising but it doesn't bring in everything. I did amend your code however to use Type_ID instead of Business_Type so maybe I did something wrong which I still cannot see. (double check.. irrepsective of what value the hash builds on, it still only brings in two records).


use Data::Dumper;

my $sth = $dbh->prepare(your select statement);
$sth->execute;
my $hash_ref = $sth->fetchall_hashref('Type_ID');

print Dumper $hash_ref;



It brings in all the data but only for records 00000000001 and 00000000003. record ID 00000000002 doesn't get a look in.

Isn't the flow on this like this:
1. bring in all the data from that table - (either to an array or a hash),
2. manipulate it so that each field is only outputted once irrespective of how many times it has been retrieved from the table.
3. use the $count++ method to build the array values for the JS?

FYI this should give a flavour of what I am trying to compile so I need also to build the incremental values for the arrays.



secondGroup[0][0][0]=new Option("-- Select Category --"," ");
secondGroup[1][0][0]=new Option("-- Select Category --"," ");
secondGroup[3][0][0]=new Option("-- Activity Categories --"," ");
secondGroup[4][0][0]=new Option("-- DiningOut Categories --"," ");
secondGroup[5][0][0]=new Option("-- Hostelries Categories --"," ");

secondGroup[1][1][0]=new Option(" Bed and Breakfast Categories "," ");
secondGroup[1][1][1]=new Option(" Approved ","put value here");

secondGroup[1][2][0]=new Option(" Guest House Categories"," ");
secondGroup[1][2][1]=new Option(" GH One Star ","put value here");
secondGroup[1][2][2]=new Option(" GH Two Star ","put value here");
secondGroup[1][2][3]=new Option(" GH Three Star ","put value here");

secondGroup[1][3][0]=new Option(" Country House Categories "," ");
secondGroup[1][3][1]=new Option(" CH 1 Star ","put value here");
secondGroup[1][3][2]=new Option(" CH 2 Star ","put value here");

secondGroup[1][4][0]=new Option(" Hotel Categories "," ");
secondGroup[1][4][1]=new Option(" H One Star ","put value here");
secondGroup[1][4][2]=new Option(" H One Star ","put value here");

secondGroup[2][0][0]=new Option(" Attraction Category "," ");
secondGroup[2][1][0]=new Option(" ATT S/T 1 Categories"," ");
secondGroup[2][1][1]=new Option(" ATT S/T 1 Category 1 ","put value here");
secondGroup[2][1][2]=new Option(" ATT S/T 1 Category 2 ","put value here");
secondGroup[2][1][3]=new Option(" ATT S/T 1 Category 3 ","put value here");

secondGroup[2][2][0]=new Option(" ATT S/T 2 Categories "," ");
secondGroup[2][2][1]=new Option(" ATT S/T 2 Category 1 ","put value here");
secondGroup[2][2][2]=new Option(" ATT S/T 2 Category 2 ","put value here");
secondGroup[2][2][3]=new Option(" ATT S/T 2 Category 3 ","put value here");
secondGroup[2][2][4]=new Option(" ATT S/T 2 Category 4 ","put value here");

secondGroup[2][3][0]=new Option(" ATT S/T 3 Categories "," ");
secondGroup[2][3][1]=new Option(" ATT S/T 3 Category 1 ","put value here");
secondGroup[2][3][2]=new Option(" ATT S/T 3 Category 2 ","put value here");


secondGroup[3][1][0]=new Option(" Act S/T 1 Categories "," ");
secondGroup[3][1][1]=new Option(" Act S/T 1 Category 1 ","put value here");
secondGroup[3][1][2]=new Option(" Act S/T 1 Category 2 ","put value here");
secondGroup[3][1][3]=new Option(" Act S/T 1 Category 3 ","put value here");

secondGroup[3][2][0]=new Option(" Act S/T 2 Categories "," ");
secondGroup[3][2][1]=new Option(" Act S/T 2 Category 1 ","put value here");
secondGroup[3][2][2]=new Option(" Act S/T 2 Category 2 ","put value here");

secondGroup[3][3][0]=new Option(" Act S/T 3 Categories "," ");
secondGroup[3][3][1]=new Option(" Act S/T 3 Category 1 ","put value here");
secondGroup[3][3][2]=new Option(" Act S/T 3 Category 2 ","put value here");

[FishMonger]

Lets take a step back and take Perl out of the picture. Run your select statement from the mysql CLI and see if it outputs the desired data. If need be, tweak the select statement until it only returns the desired data. Form that point, we should be able to see how to build the hash.

If you're willing, it would help me if you could post the CLI output of the select statement. If you prefer, you can send it as an attachment in a PM to me.