Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re^6: extract column data

by bigup401 (Pilgrim)
on May 24, 2017 at 15:58 UTC ( [id://1191105]=note: print w/replies, xml ) Need Help??


in reply to Re^5: extract column data
in thread extract column data

thanks, am now getting all lastnames printed but comes in array format with comma, single quots brackets and am not good in regex to remove [', and make it well printed like

John Doe Smith

any idea

my $sth = $dbh->selectall_arrayref("SELECT lastname FROM dbase"); $dbh->disconnect; @data = Dumper $sth; print @data;

Replies are listed 'Best First'.
Re^7: extract column data
by Mr. Muskrat (Canon) on May 24, 2017 at 17:08 UTC

    You can't just cobble together various examples and hope to make it work. You need to read the documentation and understand what you are working with.

    Start by reading the documentation for DBI related to selectall_arrayref as others have already instructed. Then read the documentation for Perl references.

Re^7: extract column data
by thanos1983 (Parson) on May 24, 2017 at 18:57 UTC

    Hello again bigup401,

    I found some time and I put together a small example that replicates your script and also the test case.

    #!/usr/bin/perl use DBI; use strict; use warnings; use Data::Dumper; use Config::Simple; #package LSPDB; $|=1; #flush every time the program my $path = 'conf.ini'; my %config = (); my $checkExist = ""; sub mysql { Config::Simple->import_from("".$path."", \%config) or die Config::Simple->error(); my $dbh = DBI->connect("dbi:mysql::".$config{'MySQL.host'}.":".$co +nfig{'MySQL.port'}."", "".$config{'MySQL.user'}."", "".$config{'MySQL.pass'}."", { 'PrintError' => 1, 'RaiseError' => 1 , 'AutoInactiveD +estroy' => 1 } ) or die "Could not connect to ". $config{'MySQL.host'} .": ". $DB +I::errstr ."\n"; my $databases = $dbh->do("SHOW DATABASES LIKE '".$config{'MySQL.db +'}."'") or die "Error: " .dbh->errstr. "\n"; if ($databases eq 1) { printf "Database: ". $config{'MySQL.db'} ." exists not creating: " +. $config{'MySQL.db'} ."\n"; } else { printf "Database: ". $config{'MySQL.db'} ." does not exist creatin +g: ". $config{'MySQL.db'} ."\n"; $checkExist = $dbh->do("CREATE DATABASE IF NOT EXISTS `".$config{' +MySQL.db'}."`") or die "Could not create the: ".$config{'MySQL.db'}." error: " +. $dbh->errstr ."\n"; } # End of else $dbh->do("USE ".$config{'MySQL.db'}."") or die "Error: " .dbh->errstr. "\n"; my $tables = $dbh->do("SHOW TABLES FROM `".$config{'MySQL.db'}."` WHERE Tables_in_".$config{'MySQL.db'}." LIKE '".$config{'MySQL.table'}."'") or die "Error: ".dbh->errstr. "\n"; if ($tables eq 1) { printf "Table: ".$config{'MySQL.table'}." exists not creating: ".$ +config{'MySQL.table'}."\n"; } else { printf "Table: ".$config{'MySQL.table'}." does not exist creating: + ".$config{'MySQL.table'}."\n"; $checkExist = $dbh->prepare("CREATE TABLE ".$config{'MySQL.table'} +." ( `ID` INT NOT NULL AUTO_INCREMENT, `lastname` VARCHAR(25) CHARACTER SET utf8 NOT NULL + UNIQUE, PRIMARY KEY(`ID`) );"); if (!$checkExist->execute()) { die "Error: ". $checkExist->errstr ."\n"; } } # End of else $checkExist = $dbh->prepare("INSERT IGNORE INTO `".$config{'MySQL. +table'}. "` (`lastname`) VALUES ('John'), ('Doe'), ('Jones') +, ('Smith')"); if (!$checkExist->execute()) { die "Error: ". $checkExist->errstr ."\n"; } my $statement = "SELECT * FROM `".$config{'MySQL.table'}."` WHERE +1"; my $hash_ref = $dbh->selectall_hashref($statement, 'lastname'); $checkExist->finish(); $dbh->disconnect() or warn "Error disconnecting: $DBI::errstr\n"; return $hash_ref; } # End of mysql sub my $output_ref = mysql(); my @lastnames = keys %$output_ref; print Dumper $output_ref, \@lastnames; __DATA__ $ perl mysql.pl Database: PerlMonks exists not creating: PerlMonks Table: Data exists not creating: Data $VAR1 = { 'Doe' => { 'ID' => 2, 'lastname' => 'Doe' }, 'John' => { 'ID' => 1, 'lastname' => 'John' }, 'Smith' => { 'lastname' => 'Smith', 'ID' => 4 }, 'Jones' => { 'ID' => 3, 'lastname' => 'Jones' } }; $VAR2 = [ 'Doe', 'John', 'Smith', 'Jones' ];

    The conf.ini file:

    [MySQL] user=user pass=password host=localhost port=3306 db=PerlMonks table=Data

    On your next question it would be easier for us to provide your minimal working example for replicating your problem.

    Hope this helps.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
    A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1191105]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2024-03-28 08:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found