CODEWITHSUNDEEP
mysqlsqlperldbi
Ahmad Bilal
Ahmad Bilal

Reputation: 382

Get only Column Name and number of items in that column using Perl+MySQL(DBI)

I'm using LAMP (Linux, Apache, MySQL, Perl). Using the DBI module from CPAN.

I'm trying to get COLUMN_NAME and COUNT at the same time, and store them into a Perl Hash.

so that I can print in HTML something like this:

Album: Selfies; #Column_Name
Photos: 0; #Count

For this I think I would need a for/foreach loop to print the key and corresponding value of the hash, one set at a time. I got the basics down as you see.. even successfully wrote 2 separate SQL statements, but I'm looking for a more efficient way. Also, there were some issues:

A. The first Column_Name/Count set was coming out as empty, though rest were fine. something like this.

Album: 
Photos:

Album: First
Photos: 2

B. In case of count being 0, It was returning blank line again, Something like:

Album: First_album
Photos:

C. To fix both, I tried to grep /^$/ .. but result was something like this:

Album: 1
Photos: 1

Pretty stuck at the moment. My questions are:

If there is a combined SQL statement to get COLUMN_NAME AND COUNT. I know how to prepare separate statements, but I hope there is a way to reduce it to one statement.

second question, I think the part I'm doing wrong is extracting the COLUMN_NAME is

$rv = $sth->fetchrow_array; #I think this is the wrong way maybe.

I tried the other things by looking at the DBI documentation, but so far, haven't figured out how to over come the first 2 problems above.

sample structure of MySQL database:

DB: Testing Table: firsttable

columns: Selfies, Panaroma Selfies contains 3 values, Panaroma contains 5 values.

I want to extract this information using DBI/Perl and present it, in the following manner:

Album: Selfies
Photos: 3

Album: Panaroma
Photos: 5

Sql statements

SELECT COUNT(column_name) FROM table_name;
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_name'

Upvotes: 0

Views: 288

Answers (1)

Barmar
Barmar

Reputation: 781096

If I understand what you're doing, this is the query:

SELECT "Selfies" AS Album, COUNT(DISTINCT Selfies) AS Photos
FROM table_name
UNION
SELECT "Panorama" AS Album, COUNT(DISTINCT Panorama) AS Photos
FROM table_name

Fetch the results and output them in a loop.

Upvotes: 1

Related Questions