[thelist] MySQL stored function looping problem

Phil Turmel pturmel-webdev at turmel.org
Mon Dec 8 21:26:10 CST 2008


Jeremy Weiss wrote:
> Okay, a while back I posted a question involving a very non-normalized
> db table that I'm dealing with.
> (http://lists.evolt.org/archive/Week-of-Mon-20081117/196289.html)
> I've made a bit of progress but I'm in new territory now. I've tried to
> fully explain the problem, so this email is a bit long.
> 
> The advice I received last time was to break this troublesome field out
> into its own table. So, with the help of others, I've managed to write
> a couple of stored functions to assist with this task.
> 
[snip /]

Hi Jeremy,

I think you've made this a little more complicated than necessary.  The
core of the normalization you are trying to perform is the parsing of
the pipe-delimited features column.  This is very much a scripting task
as opposed to a SQL task (not that it can't be done in SQL...)

In any case, I'd do something like this:

<?php
$cn = mysql_connect(....); // I'll leave the credentials for you...
$sql = "Select mls, features From Listings";
$res = mysql_query($sql, $cn);
if ($res === FALSE) {
  echo mysql_error($cn);
  exit;
}
while ($row = mysql_fetch_assoc($res)) {
  extract($row, EXTR_OVERWRITE);
  $featurelist = explode('|', $features);
  foreach ($featurelist as $fid) {
    if (strlen($fid1 = trim($fid))) {
      $isql = sprintf("Insert Into carmls_features (mls, fid) " .
        "Values ('%s', '%s')",
        mysql_escape_string($mls),
        mysql_escape_string($fid1));
      $ires = mysql_query($isql, $cn);
      if ($ires === FALSE) {
        echo mysql_error($cn);
        exit;
      }
    }
  }
}
?>

Yeah, there'll be a bunch of single-row inserts issued from PHP, but for
a one-time conversion script, I doubt you'll suffer too much.  You did
say there's only ~ 15K listings.  Figure 1ms per insert to estimate the
runtime.  (If I'm way off on that, I'd like to know what hardware you're
running on...)

HTH,

Phil



More information about the thelist mailing list