Gossamer Forum: General: Perl Programming: SQL Query problem

Jan 18, 2002, 10:17 AM

Wil

Veteran / Moderator (4108 posts)

Jan 18, 2002, 10:17 AM

Post #1 of 9

Shortcut

SQL Query problem

Hi

Can anyone spot any errors with the below code? For some reason it doesn't want to print anything where it shoudl print $member->('name') - nothing appears although I know something should.

Thanks for any help you can give me.

Code:
sub generate_offer_index { 

	$dbh = $mysql->connect_to_db; 

	$numrec = $dbh->selectrow_array("SELECT COUNT(*) FROM ata_offers"); 

	$sth = $dbh->prepare("SELECT * FROM ata_offers"); 
	$sth->execute(); 

	while (my $ref = $sth->fetchrow_hashref()) 
	{ 
			my $id = $ref->{'mem_id'}; 

			$sql = $dbh->prepare("SELECT name FROM ata_members WHERE id = '$id'"); 
			$sql->execute(); 
			$member = $sth->fetchrow_hashref(); 
			$sql->finish; 

		$offer_data .= qq|<tr bgcolor="#F4EFF7">  
                      <td class="text-black" width="25%">$member->{'name'}</td> 
                      <td class="text-black" width="57%">$ref->{'offer'}</td> 
                      <td class="text-black" width="18%">$ref->{'offer_end'}</td> 
					  </tr> 
						|; 


	} 

	$sth->finish; 
	$dbh->disconnect; 

}

- wil

Jan 18, 2002, 10:30 AM

Paul

Veteran (19537 posts)

Jan 18, 2002, 10:30 AM

Post #2 of 9

Shortcut

Re: [japh] SQL Query problem In reply to

Either the mem_id column is NULL/empty or the query isn't executing.

Try adding:

or die $DBI::errstr;

to make sure it isn't the query.

Jan 18, 2002, 10:34 AM

Wil

Veteran / Moderator (4108 posts)

Jan 18, 2002, 10:34 AM

Post #3 of 9

Shortcut

Re: [RedRum] SQL Query problem In reply to

Hm. mem_id is not null and I've modifed my sub to look like the following - still produces nothing.

Code:
sub generate_offer_index { 

	$dbh = $mysql->connect_to_db; 

	$numrec = $dbh->selectrow_array("SELECT COUNT(*) FROM ata_offers"); 

	$sth = $dbh->prepare("SELECT * FROM ata_offers"); 
	$sth->execute() or die("Can't execute SQL query:<BR><BR>[$DBI::err] $DBI::errstr"); 

	while (my $ref = $sth->fetchrow_hashref()) 
	{ 
			my $id = $ref->{'mem_id'}; 

			$sql = $dbh->prepare("SELECT name FROM ata_members WHERE id = '$id'"); 
			$sql->execute() or die("Can't execute SQL query:<BR><BR>[$DBI::err] $DBI::errstr"); 
			$member = $sth->fetchrow_hashref(); 
			$sql->finish; 

		$offer_data .= qq|<tr bgcolor="#F4EFF7">  
                      <td class="text-black" width="25%">$member->{'name'}</td> 
                      <td class="text-black" width="57%">$ref->{'offer'}</td> 
                      <td class="text-black" width="18%">$ref->{'offer_end'}</td> 
					  </tr> 
						|; 


	} 

	$sth->finish; 
	$dbh->disconnect; 

}

- wil

Jan 18, 2002, 10:37 AM

Wil

Veteran / Moderator (4108 posts)

Jan 18, 2002, 10:37 AM

Post #4 of 9

Shortcut

Re: [japh] SQL Query problem In reply to

OK. How can I just grab the value of one single field in one query string. Maybe that's my solution?

Maybe something like:

Code:
		$field = "SELECT name FROM ata_members WHERE id = '$id'"; 
		$name->do($field) or die("Can't query database:<BR><BR>[$DBI::err] $DBI::errstr");

Would that return the value of "field" to $name?

- wil

Last edited by:

japh: Jan 18, 2002, 10:39 AM

Jan 19, 2002, 2:01 AM

lanerj

User (304 posts)

Jan 19, 2002, 2:01 AM

Post #5 of 9

Shortcut

Re: [japh] SQL Query problem In reply to

my $query = qq!
SELECT name FROM ata_members WHERE id = '$id'
!;
my $sth = $dbh->prepare($query) or &cgierr("Unable to query database. Reason: $DBI::errstr.");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
my $name = $sth->fetchrow_array;
$sth->finish;

Bob
http://totallyfreeads.com

Jan 19, 2002, 10:14 AM

Wil

Veteran / Moderator (4108 posts)

Jan 19, 2002, 10:14 AM

Post #6 of 9

Shortcut

Re: [lanerj] SQL Query problem In reply to

Thanks for your help. Doesn't work though, for some reason? I've now got the code below. When I execute it, only one record appears and nothing appear where $name should be. If I omit the second database query (the one to find out $name) then all records appear.

Code:
sub generate_offer_index { 

	$dbh = $mysql->connect_to_db; 

	$numrec = $dbh->selectrow_array("SELECT COUNT(*) FROM ata_offers"); 

	$sth = $dbh->prepare("SELECT * FROM ata_offers"); 
	$sth->execute() or die("Can't execute SQL query:<BR><BR>[$DBI::err] $DBI::errstr"); 

	while ($ref = $sth->fetchrow_hashref()) 
	{ 
			my $id = $ref->{'mem_id'}; 

			my $sql = $dbh->prepare("SELECT name FROM ata_members WHERE id = '$id'"); 
			$sql->execute() or die("Can't execute SQL query:<BR><BR>[$DBI::err] $DBI::errstr"); 
			my $name = $sth->fetchrow_array(); 
			$sql->finish; 

		$offer_data .= qq|<tr bgcolor="#F4EFF7">  
                      <td class="text-black" width="25%">$name</td> 
                      <td class="text-black" width="57%">$ref->{'offer'}</td> 
                      <td class="text-black" width="18%">$ref->{'offer_end'}</td> 
					  </tr> 
						|; 


	} 

	$sth->finish; 
	$dbh->disconnect; 

}

- wil

Jan 19, 2002, 8:31 PM

lanerj

User (304 posts)

Jan 19, 2002, 8:31 PM

Post #7 of 9

Shortcut

Re: [japh] SQL Query problem In reply to

I,m not thinking all that well at the moment as I have a bad dose of the flue, but you should be able do this with a simple join.
try somthing like this -

$query = qq!
SELECT M.name, O.offer, O.offer_end FROM ata_members AS M, ata_offers AS O WHERE M.'id'=O.'mem_id'
!;
$sth = $dbh->prepare($query);
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
while (($name,$offer,$offer_end)=$sth->fetchrow_array) {
...whatever...
}
$sth->finish;
$dbh->disconnect;

That should pull all the names from ata_members that have a matching id in ata_offers, and the matching offer and offer_end fields from ata_offers I think.

Bob
http://totallyfreeads.com

Jan 20, 2002, 1:47 AM

lanerj

User (304 posts)

Jan 20, 2002, 1:47 AM

Post #8 of 9

Shortcut

Re: [lanerj] SQL Query problem In reply to

oops
WHERE M.'id'=O.'mem_id'
should read
WHERE M.id=O.mem_id (no single quotes.)

Bob
http://totallyfreeads.com

Jan 21, 2002, 4:12 AM

Wil

Veteran / Moderator (4108 posts)

Jan 21, 2002, 4:12 AM

Post #9 of 9

Shortcut

Re: [lanerj] SQL Query problem In reply to

Thanks for your help, Bob! I've never used joins before, and I'm pleased I've just started :-). This is the final code I used, which works perfectly. Thanks.

Code:
sub generate_offer_index { 

	$dbh = $mysql->connect_to_db; 

	$numrec = $dbh->selectrow_array("SELECT COUNT(*) FROM ata_offers"); 

	my $query = qq|SELECT M.name, O.offer_start, O.offer_end, O.offer, O.icon, O.additional FROM ata_members AS M, ata_offers AS O WHERE M.id=O.id|;  
	$sth = $dbh->prepare($query);  
	$sth->execute or die("Can't execute SQL query:<BR><BR>[$DBI::err] $DBI::errstr"); 

	while (my $ref = $sth->fetchrow_hashref()) 
	{ 

		$offer_data .= qq|<tr bgcolor="#F4EFF7">  
                      <td class="text-black" width="25%">$ref->{'name'}</td> 
                      <td class="text-black" width="57%">$ref->{'offer'}</td> 
                      <td class="text-black" width="18%">$ref->{'offer_end'}</td> 
					  </tr> 
						|; 
	} 

	$sth->finish; 
	$dbh->disconnect; 

}

- wil