SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calling by 'id number' how do you know what the content is with out a name?

    Hi all

    Having a little problem understanding how I can recognize my 'id number' when calling from my table? I was calling from my table by the 'venue name' so I could easily recognize what the content was about, but after a bit of reading and a previous thread it seems the best bet is to call from the 'id number'. ?

    Below is the php code which I currently have and works, but I have no clue what each 'id number' stands for until it's shown on my page?

    PHP Code:
    <?php
        
    require_once('includes/mysql_connect.inc.php');

        
    // The id should be numerical, not a word. It should be an auto-increment index in the venues table. ( left by some one on a previous thread )
        
        
    $sql "SELECT venue,comments,address FROM venues WHERE id=" mysql_real_escape_string($_GET['id']);
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        while (
    $row mysql_fetch_array ($result)) {
        echo 
    '<h1>' $row['venue'] . '</h1><br><em>' $row['comments'] . '</em><p>' $row['address'] .'</p>';
        }
    ?>
    What I previously had:

    PHP Code:
    <?php
        
    require_once('includes/mysql_connect.inc.php');

        
    // The id should be numerical, not a word. It should be an auto-increment index in the venues table. ( left by some one on a previous thread )
        
        
    $sql "SELECT venue,comments,address FROM venues WHERE venue=" mysql_real_escape_string($_GET['venue']);
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        while (
    $row mysql_fetch_array ($result)) {
        echo 
    '<h1>' $row['venue'] . '</h1><br><em>' $row['comments'] . '</em><p>' $row['address'] .'</p>';
        }
    ?>
    So what the address bar looks like:

    /venue/9873 - using id

    or

    /venue/somevenue - using venue name - which is really what I want for SEO reasons but doesn't matter if the id is the right way to do it

    So my main question is, how do you no what content each 'id number' has?
    What are the issues if i use name instead of id?
    Should I always use id?

    Making sense? Please help, Thank You.

  2. #2
    SitePoint Evangelist Andrewaclt's Avatar
    Join Date
    Dec 2003
    Location
    Raleigh, NC
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Typically the IDs are set when you create the database, aka a primary key depending on how the database is set up. Typically they are set to unique and auto increment, at least that's one theory. Whoever created the database can explain exactly how the IDs are created, can you provide more detail?

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes cheers, I created the database myself and have been adding the content via php admin or a submit form, but mainly php admin with a updated .txt file.

    My ID field is auto_increment, PRIMARY, but if I have 100 venues which have been added in one way or the other to my database how will I no what each number represents??

    Example of my homepage

    this is SOME VENUE from my site ... blar blar ..

    SOME VENUE has a link <a href="venue/some venue">some venue</a>

    or how it should be?

    SOME VENUE has a link <a href="venue/5467">some venue</a>

    So how will I no what number to use with out searching through my database first? Thats why I thought of using the venue name which i could easily remember.

    Cheers

  4. #4
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First off, I'm going to counter-argue that using a string for your primary key is not as bad as some claim. If you doubt me, time it yourself - you'll see no appreciable difference between integer keys and varchar or char keys. (Do not, however, use TEXT keys!) I've run benchmarks in MySQL on all three types of keys, using MyISAM, InnoDB, and even MEMORY (aka HEAP) tables, and every time I come up with similar query times for SELECT, DELETE, INSERT, and UPDATE statements ("similar times" means similar within an engine; I've compared various engines as well, but that's a separate matter).

    There are many cases where a numerical surrogate key is needed (a "surrogate key" is a key that uses data that is not truly part of the row's data, such as the ID created by an auto_increment column in MySQL), but your case seems to be one where a natural key (i.e. the venue name) can be used (that is, of course, provided that venue names are unique). In database design theory, it is generally accepted that natural keys are better because they have a stronger association with the data they represent; surrogate keys are only used when necessary.

    Okay, now that all that's said, here's how you fix your current problem:
    I'm assuming that when you generate your list of venue links, you're doing so by pulling them from the database and iterating through the resultset ("SELECT venue FROM venues"). If this is the case, then it's a simple matter of also selecting the ID when you do so:
    Code PHP:
    $query = "SELECT id,venue FROM venues";
    $res = mysql_query($query);
    while($row = mysql_fetch_assoc($res))
        echo "<a href=\"venue/{$row['id']}\">{$row['venue']}</a><br />\n";
    PHP questions? RTFM
    MySQL questions? RTFM

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quality! Just shed some serious light on where I was heading next, thank you!

    There are many cases where a numerical surrogate key is needed (a "surrogate key" is a key that uses data that is not truly part of the row's data, such as the ID created by an auto_increment column in MySQL), but your case seems to be one where a natural key (i.e. the venue name) can be used (that is, of course, provided that venue names are unique).
    I was thinking about something like this early, mainly because some venue names have 2-3 words, and i was thinking of using 1 main word as the id name i'll call for, easier again if you get what i mean?

    example: venue/venue has three
    but the address bar will have one: venue/something

    the venue will be shown as a header on the page, so i couldn't really break that down to one word.

    I'm assuming that when you generate your list of venue links, you're doing so by pulling them from the database..
    I wasn't but i will be doing now thanks to your example, i will still need to type links myself to specific links when not calling from the database though, so if i add a new column (surrogate key) - (iam i understand this properly - as above) with a single word id for that venue could i then call it by that id name?

    big thanks

  6. #6
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the case of multi-word keys, for URL purposes, you would have to do something else... maybe you could replace spaces with underscores '_' for the links, and then before you look them up in your database you replace the underscores with spaces. This would maintain "pretty", SEO-friendly URLs while still allowing you to use the natural keys in your database.

    Another option along similar lines would be to use underscores in the database directly, and only replace them with spaces when displaying as headers/link names/etc. I like this one myself, but I guess I'm just strongly biased against spaces except for print meant to be read by humans (coming as I do from a *nix background).

    Thus "Venue With Four Words" would appear in your database as "Venue_With_Four_Words", would have the link "venue/Venue_With_Four_Words", and when you display it for the user you run it through str_replace("_", " ", $venuename).

    Off Topic:

    Just to illustrate my anti-space bias, Windows drives me absolutely bonkers because "My Computer" and "My Documents" and "Program Files" and so many others have spaces!! I'd be so much happier with camel-case ("MyComputer", "MyDocuments").
    PHP questions? RTFM
    MySQL questions? RTFM

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    kromey, excellent advice regarding surrogate/natural keys

    the only thing i have to add is that for seo purposes, it would be better to use dashes than underscores

    venue/venue-with-four-words

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, really got me thinking in different ways now, more advance that is.. lol cheers
    A couple more things then if you don't mind:

    1. If i use venuename instead of venue_name, will that have any seo differences?
    2. Also you say use venue_name_three, my venue names are first char capital and i really wanted just a lower-case one word for the venue/lower?
    3. my php code, which works fine by the way. just wondering with your code in place do i need to keep the previous bit of code in place for everything to function properly?

    Also calling by category now as well,

    PHP Code:
    <?php
        
    require_once('includes/mysql_connect.inc.php');

        
    $query "SELECT id,venue FROM venues WHERE category=4";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"venue/{$row['id']}\">{$row['venue']}</a><br>\n";
        
        
    $sql "SELECT venue,comments,address FROM venues WHERE id=" mysql_real_escape_string($_GET['id']);
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        while (
    $row mysql_fetch_array ($result)) {
        echo 
    '<h5>' $row['venue'] . '</h5><h4><strong>' $row['comments'] . '</strong></h4><p></p><h3>Location: ' $row['address'] .'</h3>';
        }
    ?>
    4. Can i use this set up with galleries etc? just changing the table info etc?

    Thank You

  9. #9
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    kromey, excellent advice regarding surrogate/natural keys
    See? That database class I took did do me some good. I just wish I had learned more concrete SQL instead of spending an entire semester getting drilled in database theory.

    Quote Originally Posted by r937 View Post
    the only thing i have to add is that for seo purposes, it would be better to use dashes than underscores

    venue/venue-with-four-words
    Don't mean to question your wisdom, Rudy, but why are dashes better than underscores for SEO? I'll admit I'm not really up to par on SEO, but I do want to learn.
    PHP questions? RTFM
    MySQL questions? RTFM

  10. #10
    SitePoint Zealot
    Join Date
    Mar 2007
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey View Post
    (Do not, however, use TEXT keys!)
    Thanks for the tip kromey, it looks like I will be editing some fields in my database to varchar.
    Kayzio - We don't hesitate, we accelerate.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    venuename instead of venue-name or even venue_name is much worse for seo purposes

    search engines will not be able to parse the component words successfully

    what words do you see in www.expertsexchange.com? expert sex change or experts exchange?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by computerbarry View Post
    thanks, really got me thinking in different ways now, more advance that is.. lol cheers
    A couple more things then if you don't mind:

    1. If i use venuename instead of venue_name, will that have any seo differences?
    Edit: See Rudy's post above.
    Also, keep in mind that whatever you use needs to be easily translatable into keys for your database, so compacting into a single word when in your database you have 2 or 3 or more words is probably not your best bet.

    Quote Originally Posted by computerbarry View Post
    2. Also you say use venue_name_three, my venue names are first char capital and i really wanted just a lower-case one word for the venue/lower?
    You can run your venue name through strtolower to print the URL, but remember you'll need to re-upper-case the first letter of each word if that's what it is in your database. See the user comments in ucfirst for a function that will do that (or similar, can't quite recall).

    Quote Originally Posted by computerbarry View Post
    3. my php code, which works fine by the way. just wondering with your code in place do i need to keep the previous bit of code in place for everything to function properly?

    Also calling by category now as well,

    PHP Code:
    <?php
        
    require_once('includes/mysql_connect.inc.php');

        
    $query "SELECT id,venue FROM venues WHERE category=4";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"venue/{$row['id']}\">{$row['venue']}</a><br>\n";
        
        
    $sql "SELECT venue,comments,address FROM venues WHERE id=" mysql_real_escape_string($_GET['id']);
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        while (
    $row mysql_fetch_array ($result)) {
        echo 
    '<h5>' $row['venue'] . '</h5><h4><strong>' $row['comments'] . '</strong></h4><p></p><h3>Location: ' $row['address'] .'</h3>';
        }
    ?>
    My PHP code was meant to help you use surrogate keys. If you instead use your natural keys (which I very strongly recommend) then just adapt your own previous code and ignore mine.

    Quote Originally Posted by computerbarry View Post
    4. Can i use this set up with galleries etc? just changing the table info etc?
    There's no limit to where you can use this method. That's the true beauty of programming - finding nifty solutions and then using them everywhere they fit.

    Quote Originally Posted by computerbarry View Post
    Thank You
    You're welcome.
    PHP questions? RTFM
    MySQL questions? RTFM

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kromey View Post
    why are dashes better than underscores for SEO?
    see this post, which quotes the search engine optimization FAQ

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thankee, much obliged Rudy!
    PHP questions? RTFM
    MySQL questions? RTFM

  15. #15
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the seo advice and links r937.

    My PHP code was meant to help you use surrogate keys. If you instead use your natural keys (which I very strongly recommend) then just adapt your own previous code and ignore mine.
    let me get this right, a 'surrogate key' is something you create to replace your id when calling to the database for what given reasons, maybe the venue name issue? and the natural key is a column/id which has your orgianal data?

    kromey - my code above with yours seems to work fine, so why change it? your bit of code select the links and my bit shows the data?

    surrogate key:
    If i add another column to my table for the one word lowercase which will show in my address bar? correct? and just show the original venue name on the page, but have a one word id instead for the seo in address bar?

    Please correct me if am wrong here, and thanks guys for getting involved.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would not bother with an additional column, just use the actual venue name -- swap spaces with hyphens when creating urls or searching for them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers r937, so back to the surrogate keys and natural keys, just need to get an understanding of this, am i understanding them in what ive been saying?

    and kromey or r397 - from above
    my code above with yours seems to work fine, so why change it? kromey bit of code selects the links for me and my bit shows the venue/id content where i want it?

    thanks a lot guys

    and back to the id int or name var, just use which suits, personal site - int or seo based - var?

  18. #18
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Surrogate keys are keys that are composed of data that is added to the row for the sole purpose of being keys; in general they have no inherent connection with the data itself. An example would be a MySQL auto_increment column.

    Natural keys are keys that use part of the data itself. These are preferred because of the inherent strong connection between the key and the data (due to the fact that the key is in fact a part of the data). Examples would be a date (for e.g. a database of holidays) or the name of a venue (in your case).

    Rudy and I are both (correct me if I'm wrong, Rudy) suggesting that you ditch any form of surrogate key from your database schema and use your venue names. For the purpose of links, replace spaces with hyphens '-'. My code that I provided is only necessary when a surrogate key is being used; if you follow our suggestion to use the natural key of your venue name, my code is unnecessary as written, but could be modified to pull your list of venues from the database; to do so, drop the id column from the SELECT statement and replace $row['id'] with str_replace(" ", "-", $row['venue']).
    PHP questions? RTFM
    MySQL questions? RTFM

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,244
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    good synopsis, kromey

    i will almost never use an auto_increment id when the table has no child tables

    i will almost always use an auto_increment id when the table does have child tables and the natural keys (there might be more than one, called "candidate" keys) are all long, e.g. VARCHAR

    some database guys will suggest that you should always use a surrogate, but any rule which says "always" should be looked upon with deep suspicion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what your saying is, delete the auto int id from every database I create and use a natural id instead 'venue name' as example, but use surrogate keys when needed or when it doesn't really matter?

    Thanks, just starting to understand this properly now.

    Just about my code then please:

    I have the links showing in <div id=links> and the venue and details in another <div id=venue>

    So could you please show me how I can structure the code now, thanks

    The below does work

    <div venue>
    PHP Code:
    <?php
        
    require_once('includes/mysql_connect.inc.php');

        
        
    $sql "SELECT venue,comments,address FROM venues WHERE id=" mysql_real_escape_string($_GET['id']);
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        while (
    $row mysql_fetch_array ($result)) {
        echo 
    '<h5>' $row['venue'] . '</h5><h4><strong>' $row['comments'] . '</strong></h4><p></p><h3>Location: ' $row['address'] .'</h3>';
        }
    ?>
    </div>

    blar.. blar..

    <div links>
    PHP Code:
    <?php

        $query 
    "SELECT id,venue FROM venues WHERE category=1";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"venue/{$row['id']}\">{$row['venue']}</a><br>\n";
        
    ?>
    </div>


    big thanks agin

  21. #21
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a natural & surrogate example would be great !

    Thanks

  22. #22
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its ok, probably the best way to learn and do it myself, thanks again guys and booked marked your site r937.com and thanks kromey.

  23. #23
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first block of PHP looks great. If you're going for the natural keys, however, the second block should instead look like this:
    Code PHP:
     
    <?php
     
        $query = "SELECT venue FROM venues WHERE category=1";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        echo "<a href=\"venue/".str_replace(" ", "-", $row['venue'])."\">{$row['venue']}</a><br>\n";
        ?>
    Just be sure to run that back through str_replace to make the '-' all be ' '!

    An example of a surrogate key would be the ID column you were previously advised to use. The number the database assigned to each of your venues had no inherent connection to the venues - what the hell is venue #14??

    A natural key, on the other hand, would be the venue name itself. Since each venue has a name that uniquely identifies it, this serves as a great primary key for the table. The key value itself, then, not only references the row where you can find more information about that venue, it also logically identifies the venue itself ("Carlson Center - I know that venue! That's where such-and-such an event was held last week!").

    The general rule of thumb for telling the difference between a surrogate and natural key is whether or not you can look at the key and associate it in your own mind with the data it represents. If that key is e.g. "Carlson Center", well I know that that happens to be a local venue in my town. Thus it's most likely a natural key for the venue table. If, however, I'm looking at 3872, well, what the hell is that? It may in fact reference the "Carlson Center", but looking at it on its own you have no idea.

    Here's one: "20071225". Can you discern whether that's a natural or surrogate key? How about if I phrase it this way: "December 25th, 2007". Can you figure it out now? Here's another hint: the table name is "holidays". Holidays are a great example of (potentially) numerical keys that are in fact natural keys. For example, 20070528 would represent the US's observance of Memorial Day in 2007 (2007-05-28 = May 28th, 2007).

    Keep in mind that natural keys strongly relate to their respective data, while surrogate keys only make sense in the context of the database itself. With that in mind, you should have no trouble telling the difference.
    PHP questions? RTFM
    MySQL questions? RTFM

  24. #24
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm looking at 3872, well, what the hell is that?
    lol.. exactly

    Great examples, thanks again kromey

  25. #25
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just having a little trouble with the below:

    The first block of PHP looks great. If you're going for the natural keys, however, the second block should instead look like this:
    PHP Code:

    PHP Code:
    <?php
     
        $query 
    "SELECT venue FROM venues WHERE category=1";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"venue/".str_replace(" ""-"$row['venue'])."\">{$row['venue']}</a><br>\n";
        
    ?>
    Just be sure to run that back through str_replace to make the '-' all be ' '!
    How? and were do I run back through str_replace for everything to work?

    Thanks Guys
    Last edited by computerbarry; Jun 9, 2007 at 06:21.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •