Re: DBI and MySQL issue

I get concerned when I see... (and I'll guess that you are not using 'strict')

  $immh->execute .....;
  $imm = $immh->fetchrow_array;

  ## as opposed to 
  $immh->execute() ....;
  $imm = $immh->fetchrow_array();
[download]

That said, you might consider using this instead.

# Open cursor
$imm_qy = "SELECT pv.value FROM profile_values pv, profile_fields pf
            WHERE pf.name = 'profile_magazine' AND pv.uid = $uid AND p
+v.fid = pf.fid";
print "\$imm_qy is $imm_qy .\n";
$immh = $dbh->selectrow_array($imm_qy) or die "Cannot prepare magazine
+ query: " . $dbh->errstr . "\n";
[download]

Note that I changed the order of the where clause that may reduce the number of lines processed and speed up the query. You also may want to index the pf.name column. Using text in a where clause is generally slow.

Comment on Re: DBI and MySQL issue Select or Download Code

Replies are listed 'Best First'.
Re^2: DBI and MySQL issue by chromatic (Archbishop) on May 02, 2007 at 23:48 UTC
I get concerned when I see... (and I'll guess that you are not using 'strict') `strict` has nothing to do with leaving parentheses off of method calls. The method names are not barewords; they're unambiguously method calls.	[reply] [d/l]
Re^2: DBI and MySQL issue by dokkeldepper (Friar) on May 03, 2007 at 07:29 UTC
And while we are speaking about the query design: Avoid cross joins if possible. Cross joins explode the tables first and apply the "where" clauses afterwards. There is no inherent reason for a cross join as far as I can see.	[reply]
Re^3: DBI and MySQL issue by tbone1 (Monsignor) on May 03, 2007 at 12:38 UTC
I have to do it because of the database design (like I said, it's a drupal-based web site), and because, well, because it shouldn't be a big deal to do that. The tables aren't big, they're indexed, and it's probably less overhead than multiple queries. Is there something in MySQL, or not in MySQL, that I should know about? I've been doing this in Ingres/Informix/Oracle for longer than I'd care to admit, so I don't see why it's a big deal. -- tbone1, YAPS (Yet Another Perl Schlub) And remember, if he succeeds, so what. - Chick McGee	[reply]
Re^3: DBI and MySQL issue by herveus (Prior) on May 04, 2007 at 11:27 UTC
Howdy! What are you talking about? On the face of it, the query looks like a simple join with suitable criteria. There is no cartesian product begin generated. How would you rephrase the query to avoid the problem you allege exists? yours, Michael	[reply]