Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would continue your "convert number to string" approach. However you will realize that your proposed algorithm fails if your ID is a <em>prime and greater than 52</em>.</p> <h3>Theoretical background</h3> <p>You need a <a href="http://en.wikipedia.org/wiki/Bijection" rel="noreferrer">Bijective Function</a> <em>f</em>. This is necessary so that you can find a inverse function <em>g('abc') = 123</em> for your <em>f(123) = 'abc'</em> function. This means:</p> <ul> <li>There must be no <em>x1, x2 (with x1 ≠ x2)</em> that will make <em>f(x1) = f(x2)</em>,</li> <li>and for every <em>y</em> you must be able to find an <em>x</em> so that <em>f(x) = y</em>.</li> </ul> <h3>How to convert the ID to a shortened URL</h3> <ol> <li>Think of an alphabet we want to use. In your case that's <code>[a-zA-Z0-9]</code>. It contains <em>62 letters</em>.</li> <li><p>Take an auto-generated, unique numerical key (the auto-incremented <code>id</code> of a MySQL table for example).</p> <p>For this example I will use 125<sub>10</sub> (125 with a base of 10).</p></li> <li><p>Now you have to convert 125<sub>10</sub> to X<sub>62</sub> (base 62).</p> <p>125<sub>10</sub> = 2×62<sup>1</sup> + 1×62<sup>0</sup> = <code>[2,1]</code></p> <p>This requires use of integer division and modulo. A pseudo-code example:</p> <pre><code>digits = [] while num &gt; 0 remainder = modulo(num, 62) digits.push(remainder) num = divide(num, 62) digits = digits.reverse </code></pre> <p>Now map the <em>indices 2 and 1</em> to your alphabet. This is how your mapping (with an array for example) could look like:</p> <pre><code>0 → a 1 → b ... 25 → z ... 52 → 0 61 → 9 </code></pre> <p>With 2 → c and 1 → b you will receive cb<sub>62</sub> as the shortened URL.</p> <pre><code>http://shor.ty/cb </code></pre></li> </ol> <h3>How to resolve a shortened URL to the initial ID</h3> <p>The reverse is even easier. You just do a reverse lookup in your alphabet.</p> <ol> <li><p>e9a<sub>62</sub> will be resolved to "4th, 61st, and 0th letter in alphabet".</p> <p>e9a<sub>62</sub> = <code>[4,61,0]</code> = 4×62<sup>2</sup> + 61×62<sup>1</sup> + 0×62<sup>0</sup> = 19158<sub>10</sub></p></li> <li><p>Now find your database-record with <code>WHERE id = 19158</code> and do the redirect.</p></li> </ol> <h3>Some implementations (provided by commenters)</h3> <ul> <li><a href="https://gist.github.com/1073996" rel="noreferrer">Ruby</a></li> <li><a href="https://gist.github.com/778542" rel="noreferrer">Python</a></li> <li><a href="https://gist.github.com/1158171" rel="noreferrer">CoffeeScript</a></li> <li><a href="https://gist.github.com/4626401" rel="noreferrer">Haskell</a></li> <li><a href="https://metacpan.org/pod/Short::URL" rel="noreferrer">Perl</a></li> <li><a href="https://gist.github.com/9554733" rel="noreferrer">C#</a></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POHow to code a URL shortener?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDonut
    1. USMarcel Jackwerth
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to code a URL shortener?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODon't forget to sanitize the URLs for malicious javascript code! Remember that javascript can be base64 encoded in a URL so just searching for 'javascript' isn't good enough.j
      singulars
      1. PO
      1. USBjorn Tipling
    2. CO@apphacker: Could you shortly explain how to sanitize please? I thought it would be enough to use strip_tags() function in PHP. Or tell me if that cannot be explained shortly, then I'll post it as a new question here.
      singulars
      1. PO
      1. UScaw
    3. COA function must be bijective (injective *and* surjective) to have an inverse.
      singulars
      1. PO
      1. USGumbo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload