www.webdeveloper.com
Results 1 to 10 of 10

Thread: mysql, is there an easier way?

  1. #1
    Join Date
    Feb 2005
    Location
    California
    Posts
    147

    mysql, is there an easier way?

    im starting out with databases. Funfun. a couple questions

    1)
    i am just setting up a database that stores usernames and passwords. I was told to set my primary column to be auto_increment and called ID. is this necessary for such a table? I see how it might be in others...

    2)
    i am saving user and pass like this:
    PHP Code:
    mysql_query("INSERT INTO $mysql_table (name, pass)
    VALUES ('gameguy43','nicetry')"
    )
    or die(
    'Query failed: ' mysql_error()); 
    i think that this is the simplest way. Its getting info from the database that is confusing.. Say i want to get the pass for a username. right now, im sending the query, saving the result to an array, and taking the first element.
    PHP Code:
    $query "SELECT pass FROM $mysql_table WHERE name = 'gameguy43'";
    $result mysql_query($query) or die('Query failed: ' mysql_error());
    $array mysql_fetch_array($resultMYSQL_NUM);
    echo 
    $array[0]; 
    is there a simpler way to do this?

    3)If i want to make a new table (i believe i know the sql) do i still send the sq with the mysql_query() function?

    4)I want to eventually make db-driven forums. Should i make a different table for each thread, each forum, or just put it all together with a bunch of columns? What if i want to make a seriesof photo albums? should i make a new table for each album's pics, or just stick them all in one and add ann "album" column? (I want these scripts to eventually be able to be administrated through an admin panel, so i will be making these tables and working with them on the fly)

    umm, i guess thats it, im just beginning so expect more questions. Thank god i finally started learning this stuff, using the file system was SO INEFFICIENT!
    Last edited by gameguy43; 06-29-2005 at 10:47 PM.

    `_ _ _
    | _ /\ |\/||_ | _| |\ /
    |_|/--\| ||_ |_||_| |

    http://parker.aldronan.com

  2. #2
    Join Date
    Jun 2005
    Location
    KCMO
    Posts
    93
    Quote Originally Posted by gameguy43
    1)
    i am just setting up a database that stores usernames and passwords. I was told to set my primary column to be auto_increment and called ID. is this necessary for such a table? I see how it might be in others...
    always a good idea to have a unique field with a controllable datatype (not necessary, but always a good idea)
    Quote Originally Posted by gameguy43
    2)
    i am saving user and pass like this:
    PHP Code:
    mysql_query("INSERT INTO $mysql_table (name, pass)
    VALUES ('gameguy43','nicetry')"
    )
    or die(
    'Query failed: ' mysql_error()); 
    i think that this is the simplest way. Its getting info from the database that is confusing.. Say i want to get the pass for a username. right now, im sending the query, saving the result to an array, and taking the first element.
    PHP Code:
    $query "SELECT pass FROM $mysql_table WHERE name = 'gameguy43'";
    $result mysql_query($query) or die('Query failed: ' mysql_error());
    $array mysql_fetch_array($resultMYSQL_NUM);
    echo 
    $array[0]; 
    is there a simpler way to do this?
    first, you should MD5 encrypt the password http://us4.php.net/md5
    second, something like this will work for the login, with md5 passwords (also sets $_SESSION['user']
    PHP Code:
    $query mysql_query("SELECT UserName FROM user WHERE UserName='$_POST[uname]' AND Password=MD5('$_POST[pword]')") or die(mysql_error());

    if (@
    mysql_result($query0) == $_POST['uname']){
        
    $_SESSION['user'] = $_POST['uname'];
        
    header("Location: login.php");
        } else {
        echo 
    'OOPs! Your login information is incorrect!  Please try again.    ';
        } 
    Quote Originally Posted by gameguy43
    3)If i want to make a new table (i believe i know the sql) do i still send the sq with the mysql_query() function?
    if you exited the database, then you need to reenter it
    Quote Originally Posted by gameguy43
    4)I want to eventually make db-driven forums. Should i make a different table for each thread, each forum, or just put it all together with a bunch of columns? What if i want to make a seriesof photo albums? should i make a new table for each album's pics, or just stick them all in one and add ann "album" column? (I want these scripts to eventually be able to be administrated through an admin panel, so i will be making these tables and working with them on the fly)
    why reinvent the wheel?
    http://simplemachines.org (forum)
    http://phpbb.com (forum)
    i also know there are several free photo albums out there
    Don't want a roundhouse kick to the head?
    http://chucknorris2012.com -- sign the petition!

  3. #3
    Join Date
    Mar 2005
    Location
    Adelaide, Australia (currectly in KL Malaysia)
    Posts
    25
    I think its fine to try build your own, re-inventing the wheel is a good way to learn

    in regards to your idea abt having a different table for each form i would say no need.

    simply have two tables that are referenced to each other. eg

    table name: forums
    fields: id, title, desc

    table name: posts
    fields: id, forum_id (forums.id), title, details

    i suggest if you want to take on such a task then sit down map out yout DB FIRST! not after otherwise you end up having to adjust your DB and potentially your code to suit.

    think of all the possible functions and how you can make them link together (as in the one above.

    for example you may wish to extend this again to something like this:

    table name: forums
    fields: id, title, desc

    table name: posts
    fields: id, forum_id (forums.id), in_reply_to (posts.id), title, details

    in regards to your question about the sql statement to create the table while it can be done (in fact all SQL statements can be run from PHP as I understand unless the user your connecting to doesnt have the rights to execute that particular command) I would suggest not doing it because it is best in my opinion to have a database structure that will not change based on how your users interact with the system (except for data of course). the reason for this is to ensure you have a 'basic structure' that wont change from system to system.

  4. #4
    Join Date
    Feb 2005
    Location
    California
    Posts
    147
    thenk you both for all of your help! that was one question i wanted to ask but, forgot, the thing about the encryption. you both seem to have misunderstood my question about the making tables on the fly, i was simply asking if the same function, mysql_query() is used? I was just looking for a quick yes or no, i could always just try it and see if it works :P. thanks again!

    PS, reinventing the wheel is the pwnage, especially if u reinvent it then make it better :P
    Last edited by gameguy43; 06-30-2005 at 01:00 AM.

    `_ _ _
    | _ /\ |\/||_ | _| |\ /
    |_|/--\| ||_ |_||_| |

    http://parker.aldronan.com

  5. #5
    Join Date
    Mar 2005
    Location
    Adelaide, Australia (currectly in KL Malaysia)
    Posts
    25
    in regards to your question about the sql statement to create the table while it can be done (in fact all SQL statements can be run from PHP as I understand unless the user your connecting to doesnt have the rights to execute that particular command) I would suggest not doing it because it is best in my opinion to have a database structure that will not change based on how your users interact with the system (except for data of course). the reason for this is to ensure you have a 'basic structure' that wont change from system to system.
    read :P

  6. #6
    Join Date
    Jan 2005
    Location
    Lithia Springs, GA USA
    Posts
    886
    Use sha1 or greater it is much more secure than md5.

    md5 has already been broken. http://en.wikipedia.org/wiki/SHA-1

    Also you absolutly want a "controlled" field such as id and I like to set it to primary & auto increment makes it easier on the indexing

  7. #7
    Join Date
    Jun 2005
    Posts
    1

    Why ID field?

    First of all, what's 'controlled' about an auto-increment field?
    Second, why would you want to index on an index field that has no functional use? If you're going to search the table for something you'll search on username. So put an index on that.

    In my opinion this is one of the cases where a functional field (username) is perfect to be the primary key. You don't want duplicate usernames, so by making it the primary key you'll be sure of that.

    Only if there are other tables that need to reference this one (by foreign key relation) then an index-field would be useful, but still I would index the username an put an unique-constraint on that column.

  8. #8
    Join Date
    Feb 2005
    Location
    California
    Posts
    147
    lol, still not answeringmy question is mysql_query the FUNCTION that is used or is there a different one? lol, i bet its that one, whatever. where do i get more info on this "sha"?
    oh, and what exactly is indexing? i noticed an index thing on myadmin, and i used it and it screwed things up. do i ever need to use this? what about that other option... individual or something

    `_ _ _
    | _ /\ |\/||_ | _| |\ /
    |_|/--\| ||_ |_||_| |

    http://parker.aldronan.com

  9. #9
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,222
    mysql_query is the funciton you use any time you want to send an SQL statement to a mysql database, whether it's a SELECT, INSERT, UPDATE, CREATE, etc. statement.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  10. #10
    Join Date
    Jan 2005
    Location
    Lithia Springs, GA USA
    Posts
    886
    First of all, what's 'controlled' about an auto-increment field?
    It is controlled because YOU control where it starts and what is put into it.

    Second, why would you want to index on an index field that has no functional use? If you're going to search the table for something you'll search on username. So put an index on that.

    Your last statement answers this question

    In my opinion this is one of the cases where a functional field (username) is perfect to be the primary key. You don't want duplicate usernames, so by making it the primary key you'll be sure of that.
    You can make it unique it doesn't need to be primary. You can use the id field for that. You can also make the username field an index if you want to.


    Only if there are other tables that need to reference this one (by foreign key relation) then an index-field would be useful, but still I would index the username an put an unique-constraint on that column.
    So you want to build for now not thinking to the future when there may need to be another table that will need to be related to this one? This is not very good planning for the future IMO
    Last edited by BeachSide; 07-01-2005 at 12:30 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles