Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

A database record pointer

I haven't seen anything that's the equivalent of a file pointer for databases and I seem to need such a  thing.
I will be selling barcode numbers, which have been purchased in bulk and will now be sold off one at a time. My thought was to fill a "barcode table"  with, say, 10,000 new bar codes. Other columns in  the table would hold the buyer's information, to be filled in when the barcode was sold. So after PayPal approved payment, the next unsold barcode would be pulled from the barcode table and issued to the buyer, and the buyer's information would be filled into the information fields of that barcode's record in  the table. A table pointer would then be incremented to point to the next unsold barcode in the table.  So for each sale I need something that gets the row in the barcode table of the  next unsold bar code, i.e., reads the table pointer.  Is there an accepted way of doing this?

Thanks for any ideas.
Steve
0
steva
Asked:
steva
  • 9
  • 7
  • 4
  • +2
4 Solutions
 
Amar BardoliwalaCommented:
Hello steva,

One way of doing this with database table is that you need to have 2 fields in bar code table

1. serial_no for each bar code like 1,2,3 ..
2. is_sold field which can hold value = 1 for sold bar codes and value = 0 for unsold bar codes.

using this 2 fields you can fetch next unsold bar code.

You will need to fetch from your query like following

$sql = "select min(serial_no) from barcode_table where is_sold = 0"

Above will give you next unsold barcode

Hope this will help you.

Thank You.

Amar.
0
 
stevaAuthor Commented:
Thanks.  Yes, I can see that that would work.  But I can also see that the server is going to have look at every row until it finds the first one with these conditions.  It seems like there ought to be a way to store a pointer with the table so there's just one table access to get the right row.
0
 
Amar BardoliwalaCommented:
Hello Steva,

I think you can create and index on serial_no and is_sold which will give you quick result.

On the other hand you might need to store last sold or next available serial_no in some other table or text file and from there you can get direct reference to unsold bar code table.

Other solution can be to move sold bar code record to another table and in that situation you will always have first record as unsold in your bar code table.

These are some possible ways that you can try.

Hope this will help.

Thank You.

Amar

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Dave BaldwinFixer of ProblemsCommented:
No matter how you do it, getting the pointer and accessing the table are going to be two different operations.  It is not uncommon to use a very small table that contains the pointer and other info.  The catch to that is that SQL tables are not kept in a specific order except by using the ORDER BY clause.  There is no other guarantee of order.  SQL databases reuse space in tables and order the tables for access but not in a linear fashion like a spreadsheet.

Since your bar codes must be unique, they can be their own index and your 'pointer' can simply be the next one in the list.  The index is kept sorted even if the data isn't.  Having an 'is sold' field can help you make sure that you don't have an error that will cause you to sell a bar code twice.
0
 
xtermCommented:
This is easy - populate barcode_table1 as normal with a numerical ID field and your codes.

Then insert the barcode you just sold into clone table "sold" which has an autoincrement field "id" you leave blank in the insert.

Use the mysql_insert_id function (http://us3.php.net/manual/en/function.mysql-insert-id.php) to tell you what # you just inserted into the sold table.

It returns the id of the next available barcode in barcode_table1, which is a simple linear SELECT.

Write this value into a spare field, or to a text file, and just overwrite with any new sale.
0
 
Ray PaseurCommented:
Couple of things that are important here.  First of all, a table of 10,000 rows is nothing, so don't worry about performance.  Just index every column used in WHERE, JOIN, ORDER, or GROUP.  Second, you need to be concerned about "race" conditions which can arise if there are two requests occurring at the same time.  When you get ready to allocate a barcode to "is sold" status, you want to LOCK TABLES before selecting the next barcode, then you can make the update, then UNLOCK TABLES.
0
 
stevaAuthor Commented:
After more thought, it seems like there may be a better solution to all of this.  I'm going to have to work with a directory of 10,000 files anyway, where each file is the jpg for the barcode. So the files will be 965438674.jpg , 953478512.jpg, etc. where the barcodes being issued are 965438674 and 953478512.  So there won't be an unsold table, just a sold table. I'll do a PHP  readdir() to get the next file in the folder, strip off '.jpg'  in  the file name to get the barcode number, put that in the sold table with the buyer information, and attach the jpg file  to the Thank You email.  Then delete that file from the directory.

That seems pretty clean.

Sorry I got off in the wrong direction.

0
 
xtermCommented:
Your way will certainly work, but that readdir() with 10k files in there might be a bit painful until you've sold several thousand barcodes...  Unix doesn't do as well with megadirectories (especially if its on a mirrored array) as say NT which automatically does btree hashing.  Hopefully the performance is adequate for you, but if not, you have several fallback solutions here.  Good luck!
0
 
stevaAuthor Commented:
Won't the readdir() just get the first file in  the directory, no matter how deep the directory is?  I'm not looking for a particular file.  Whichever one is next is the one I'll sell next.
0
 
Dave BaldwinFixer of ProblemsCommented:
What will you do when someone wants a consecutive block of barcodes?
0
 
xtermCommented:
It will, but I still think it has to see everything before it returns the one file - all my scripts which read directories have an uncomfortable pause at the beginning.  You could test it out by just writing a little script to touch 10000 files in a directory:
$>  mkdir foo
$>  cd foo
$>  COUNT=0
$>  while [ $COUNT -le 10000 ]; do touch bar.$COUNT; COUNT=`expr $COUNT + 1`; done

Open in new window

0
 
xtermCommented:
I actually just created a folder on my Ubuntu machine with 10000 files (all 0 bytes in size) and ran the attached script using both my new directory, and /usr/bin (which only has about 1000 files in it) and the performance was really good, and about the same in either directory.

I think it's because readdir() makes no effort to sort in any way, and its access order is totally indeterminate - for instance, when I run it, the files are not returned anywhere near the order I created them:

xterm@dellxps:~/ee$ ./readdir.php
file is bar.4510
file is bar.7556
file is bar.6858
file is bar.6486
file is bar.8210
file is bar.5484
file is bar.1117
file is bar.920
file is bar.4335
file is bar.9806

#!/usr/bin/php -q
<?php

$count=0;
$limit=10;

$handle=opendir("./foo");
while (false!==($filename=readdir($handle))) {
        if ( ($filename!="..")&&($filename!=".") ) {
                echo "file is $filename\n";
                $count++;
                if($count==$limit) { exit; }
        }
}

?>

Open in new window

0
 
stevaAuthor Commented:
Dave,


What will you do when someone wants a consecutive block of barcodes?

Good question but there's a good answer. I can't guarantee consecutive numbers, but there's no reason for anyone to need them either.  Bar codes are just 12-digit numbers guaranteed to be unique in the barcode world. They have no intrinsic meaning. They're just indexes used by retailers to access the item in  their database.
0
 
xtermCommented:
Point being, maybe your readdir() idea will work just as well as any other :)  Have fun.
0
 
Dave BaldwinFixer of ProblemsCommented:
I thought you'd say that.  I can almost guarantee that if you do that business long enough someone will insist on consecutive barcodes for their products.

I would be looking at this as an inventory control problem.  I would use the database with enough info to tell what has been sold and what is available.
0
 
stevaAuthor Commented:
xterm,

Thanks for running the test.


readdir() makes no effort to sort in any way, and its access order is totally indeterminate - for instance, when I run it, the files are not returned anywhere near the order I created them:

Hmmm.  The PHP manual for readdir() says:

Returns the filename of the next file from the directory. The filenames are returned in the order in which they are stored by the filesystem.   http://php.net/manual/en/function.readdir.php 
0
 
xtermCommented:
> Returns the filename of the next file from the directory. The filenames are returned in the order in which they are stored by the filesystem.

Yep, but the filesystem stores them by inode numbers and not necessarily the order you wrote them.  So you're not going to get any niceties such as alphanumeric sorting or by last modified time, evidently.  In looking back at my previous scripts, I see they're slow because I pull the files into an array and sort them for my needs before iterating through them.
0
 
Dave BaldwinFixer of ProblemsCommented:
@xterm is right, files get stored where there is room for them in both the directory table and disk space.  If you delete a file, that opens up a space in the directory table for the next file.  That is true for all file systems I've seen.
0
 
stevaAuthor Commented:
I think we've worn this out.  Thanks to everyone for their input.  I split the points as best as I could .  Sorry there aren't more to go around.

Steve
0
 
Ray PaseurCommented:
Sorry I was away this afternoon. Here is what I would do if I were you.  Use this little stopwatch timer class to give yourself a sense of the time required to run parts of a script, including the time required to access the data base.  Get started with these preparatory steps to create your test data.

Create 10,000 rows in the data base with barcode numbers and a "sold" flag of some sort (keep an auto_increment key, too).  Put a UNIQUE index on the barcode numbers.  Run a query to "sell" 3,000 of them.  Just for fun, randomize the query so that they are NOT sold in any numeric order.

Now start the timer and test the time it takes to find a barcode and complete a sale.  You would use something like this sequence.
LOCK TABLES barcodes
SELECT barcode, id FROM barcodes WHERE sold = 0 ORDER BY barcode LIMIT 1
UPDATE barcodes SET sold = 1 WHERE id = $id LIMIT 1
UNLOCK TABLES barcodes
echo $barcode

It would be interesting if you would post the values you get from the stopwatch timer.  I think all the stuff about keeping separate tables, etc., will go by the wayside as soon as you see how little time this takes.
<?php // RAY_class_Stopwatch.php
error_reporting(E_ALL);


// DEMONSTRATE A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+
// MAN PAGE http://php.net/manual/en/function.microtime.php


class StopWatch
{
    protected $a; // START TIME
    protected $s; // STATUS - IF RUNNING
    protected $z; // STOP TIME

    public function __construct()
    {
        $this->a = array();
        $this->s = array();
        $this->z = array();
    }

    // A METHOD TO REMOVE A TIMER
    public function reset($name='TIMER')
    {
        // RESET ALL TIMERS
        if ($name == 'TIMER')
        {
            $this->__construct();
        }
        else
        {
            unset($this->a[$name]);
            unset($this->s[$name]);
            unset($this->z[$name]);
        }
    }

    // A METHOD TO CAPTURE THE START TIME
    public function start($name='TIMER')
    {
        $this->a[$name] = microtime(TRUE);
        $this->z[$name] = $this->a[$name];
        $this->s[$name] = 'RUNNING';
    }

    // A METHOD TO CAPTURE THE END TIME
    public function stop($name='TIMER')
    {
        $ret = NULL;

        // STOP ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                // IF THIS TIMER IS STILL RUNNING, STOP IT
                if ($this->s[$name])
                {
                    $this->s[$name] = FALSE;
                    $this->z[$name] = microtime(TRUE);
                }
            }
        }

        // STOP ONLY ONE OF THE TIMERS
        else
        {
            if ($this->s[$name])
            {
                $this->s[$name] = FALSE;
                $this->z[$name] = microtime(TRUE);
            }
            else
            {
                $ret .= "ERROR: CALL TO STOP() METHOD FOR '$name' IS NOT RUNNING";
            }
        }

        // RETURN AN ERROR MESSAGE, IF ANY
        return $ret;
    }

    // A METHOD TO READ OUT THE TIMER(S)
    public function readout($name='TIMER', $dec=3, $m=1000, $eol=PHP_EOL)
    {
        $str = NULL;

        // GET READOUTS FOR ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                $str .= $name;

                // IF THIS TIMER IS STILL RUNNING UPDATE THE END TIME
                if ($this->s[$name])
                {
                    $this->z[$name] = microtime(TRUE);
                    $str .= " RUNNING ";
                }
                else
                {
                    $str .= " STOPPED ";
                }

                // RETURN A DISPLAY STRING
                $lapse_time = $this->z[$name] - $start_time;
                $lapse_msec = $lapse_time * $m;
                $lapse_echo = number_format($lapse_msec, $dec);
                $str .= " $lapse_echo";
                $str .= $eol;
            }
            return $str;
        }

        // GET A READOUT FOR ONLY ONE TIMER
        else
        {
            $str .= $name;

            // IF THIS TIME IS STILL RUNNING, UPDATE THE END TIME
            if ($this->s[$name])
            {
                $this->z[$name] = microtime(TRUE);
                $str .= " RUNNING ";
            }
            else
            {
                $str .= " STOPPED ";
            }


            // RETURN A DISPLAY STRING
            $lapse_time = $this->z[$name] - $this->a[$name];
            $lapse_msec = $lapse_time * $m;
            $lapse_echo = number_format($lapse_msec, $dec);
            $str .= " $lapse_echo";
            $str .= $eol;
            return $str;
        }
    }
}



// DEMONSTRATE THE USE -- INSTANTIATE THE STOPWATCH OBJECT
$sw  = new Stopwatch;

// SET A STOPWATCH NAME THAT REFLECTS THE PARTS OF THE SCRIPT WE WANT TO TIME
$g_timer = 'GOOGLE TIMER';

// START A TIMER TO GET ELAPSED TIME FOR A CALL TO GOOGLE
$sw->start($g_timer);

// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME (READS GOOGLE WEB PAGE)
$page = 'http://google.com';
$html = file_get_contents($page);

// GET A READOUT OF THE TIMER WHILE IT IS STILL RUNNING
echo nl2br($sw->readout($g_timer));
echo "<br/>" . PHP_EOL;

// PERFORM SOME OTHER ACTIVITY (READS GOOGLE WEB PAGE AGAIN)
$page = 'http://google.com';
$html = file_get_contents($page);

// STOP THE TIMER AND GET A READOUT WITH SHORT DECIMALS
$x = $sw->stop($g_timer);
echo nl2br($sw->readout($g_timer, 1));
echo "<br/>" . PHP_EOL;




// START A SECOND TIMER
$y_timer = 'YAHOO TIMER';
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT (ONE IS STOPPED AND ONE IS STILL RUNNING)
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// STOP ALL OF THE STOPWATCHES
$sw->stop();

// REPORT THE STOPWATCHES CONTENT AGAIN
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// TRY TO STOP A TIMER THAT IS NOT RUNNING
$x = $sw->stop($g_timer);
var_dump($x);
echo "<br/>" . PHP_EOL;
echo "<br/>" . PHP_EOL;



// START THIS TIMER OVER AGAIN
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://weather.yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ONE OF THE STOPWATCHES
$sw->reset($g_timer);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ALL OF THE STOPWATCHES
$sw->reset();
echo "ALL STOPWATCHES HAVE BEEN REMOVED";

// REPORT THE STOPWATCHES CONTENT (SHOWS NOTHING)
echo nl2br($sw->readout());

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

Open in new window

0
 
stevaAuthor Commented:
Ray,

Thanks.  I'll tuck your code away for future use but I don't have time now to set it up and run what you suggest.  The current course, anyway, is to only have a "Sales" table and to pull the next  unsold barcode from a directory of files, not a database table, where each file is the jpg image of that barcode.  So the only database work to do is to store that barcode number in the Sales table, along with the customer information.   The last issue was how long it would take to pull the next file from the directory of jpg files with PHP's  readdir() and whether or not  this time depended on how many files were in the directory.

Steve
0
 
stevaAuthor Commented:
Ray,

I wonder if I could ask a question about the MySQL code you posted.  Where exactly would you execute

LOCK TABLES barcodes
SELECT barcode, id FROM barcodes WHERE sold = 0 ORDER BY barcode LIMIT 1
UPDATE barcodes SET sold = 1 WHERE id = $id LIMIT 1
UNLOCK TABLES barcodes
echo $barcode

It seems to be PHP since it has the echo at the end, but I don't think PHP will take LOCK, SELECT, etc.  I especially like the way the id from the SELECT got used immediately in the UPDATE as $id.

I'm going to assume that this was just a shorthand you were using to illustrate what would actually have to be executed in PHP as something like

$query = "SELECT barcode, id FROM barcodes WHERE sold = 0 ORDER BY barcode LIMIT 1";
$results = mysqli_query($db, $query)

and then you'd have to dig $id out of $results.

Is that right?

Steve
0
 
xtermCommented:
Steve,

Yes, you are correct, he was just using shorthand to illustrate the sequence of events.
0
 
stevaAuthor Commented:
xterm,

Thanks.
0
 
Ray PaseurCommented:
assume that this was just a shorthand...

Yep!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 7
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now