Custom serial display help

[Zlatko.Lakisic]

Ok here is the version:

Zen Cart 1.3.6
Database Patch Level: 1.3.6

And here is my serials function:

function getSetial($productID)
  {
      global $currencies, $order_totals, $db; 
    $dbres = $db->Execute("SELECT codeID, code FROM zen_codes WHERE viewed = 0 AND itemID = " . $productID . " LIMIT 1");
    
    $code = $dbres->fields['code'];

    $db->Execute("UPDATE zen_codes SET viewed = 1 WHERE codeID = " . $dbres->fields['codeID']);
    
    return $code;
  } 

[DrByte]

Try moving your custom serial script call down 2 lines ... below the 2 lines with } braces.

[Zlatko.Lakisic]

yep that did the trick, looks like it was just inside the wrong for loop! :)

i must say i am an asp.NET coder and havent liked many php apps in the past but zencart seems really well put together!

thanks again!

[Zlatko.Lakisic]

hey drByte another conundrum with the script, everything was working great until my customer added a whole bunch of serials to the database, i think 3000+. now as far as i know this shouldnt be a problem because im doing a search by productID but it times out (30 seconds plus when i hit the confirm order button) here is an error

Fatal error: Maximum execution time of 30 seconds exceeded in /home/aero/public_html/shop/includes/classes/db/mysql/query_factory.php on line 104

[DrByte]

What's the table structure?
What indexes have you set on the table?

[Zlatko.Lakisic]

no indexes lol

the table is like this

codeID (PK, int)
productID (FK, varchar)
code (varchar)
viewed (bit, default 0)

[Zlatko.Lakisic]

but even with no indexes it shouldnt have a problem with an SQL statement like this

SELECT codeID, code FROM zen_codes WHERE productID = 'c12s1d2s' AND viewed = 0 LIMIT 1

(c12s1d2s is a dummy productID)

[DrByte]

By "FK" do you mean "foreign key" ?
How exactly is that enforced? Is that something you've got the database trying to do? or do you mean it only as a reference?
If it's doing some sort of join back to another table, you may need to be doing some indexing to aid with that.

Is the delay coming from the SELECT or from the UPDATE?

Regardless of your FK situation, I would probably suggest putting a combined index on "productID, viewed"

[Zlatko.Lakisic]

hey i made an index on productID and viewed but its still timing out...

[Zlatko.Lakisic]

seems like this code:

function getSetial($productID)
  {
      global $db; 
    $code = 'No serial required for this product. If you do need a serial and did not recieve one please contact our staff.';
    $dbres = $db->Execute("SELECT codeID, code FROM zen_codes WHERE viewed = 0 AND itemID = '" . $productID . "' LIMIT 1");
    
    while(!$dbres->EOF)
    {
        $code = $dbres->fields['code'];
    
        $db->Execute("UPDATE zen_codes SET viewed = 1 WHERE codeID = " . $dbres->fields['codeID']);
    }
    
    return $code;
  } 

was causing an infinite loop so i recoded it with this:

function getSetial($productID)
  {
      global $db; 
    $code = 'No serial required for this product. If you do need a serial and did not recieve one please contact our staff.';
    $codeRes = $db->Execute("SELECT codeID, code FROM zen_codes WHERE viewed = 0 AND itemID = '" . $productID . "'");
    $i=1;
    while(!$codeRes->EOF && $i==1)
    {
        $code = $codeRes->fields['code'];
    
        $db->Execute("UPDATE zen_codes SET viewed = 1 WHERE codeID = " . $codeRes->fields['codeID']);
        
        $i++;
    }
    
    return $code;
  } 

but now its not putting out any serial numbers.... WIERD