Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How do you make a table in mysql

  1. #1
    Join Date
    Mar 2015
    Location
    Second Life
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question How do you make a table in mysql

    Edit, I know how to make a db in mysql, I mean how do you make the tables in the mysql db. I was gonna fix the title of post but can't

    ----------------------------
    K, yeah this looks like a dumb question BUT what do you do in mysql database?

    I mean, if you want to put up a cms or forum & it deals with mysql db making tables there. I go blank when I go in there. So I end up not using it lol unless I do a auto install of a script lol

    Can someone explain how to make tables etc; in the mysql db please Lol
    Last edited by xhilaration; 03-15-2015 at 12:45 AM. Reason: Tried fixing title of post but can't so I put a edited title in post itself!

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,875
    Thanks
    92
    Thanked 97 Times in 95 Posts

    Default

    MySQl is much like a spreadsheet storage area used for fast sorting and organizing of your data sets such as user names and date registered or cataloging your inventory.

    The first thing I need to ask is do you have a database you can access where you have a host, password, username, and database name?
    Last edited by james438; 03-15-2015 at 04:21 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Mar 2015
    Location
    Second Life
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by james438 View Post
    MySQl is much like a spreadsheet storage area used for fast sorting and organizing of your data sets such as user names and date registered or cataloging your inventory.

    The first thing I need to ask is do you have a database you can access where you have a host, password, username, and database name?
    I have a hosting & yes I can get into the phpadmin sql db area but when I go into it, Im like uhhhh nuuuu & leave. Because I have the foggest of what & how to do things in there lol

    Its like my heart startes pounding bc there is no better explaination on how to make table & all.

    But I found some info how it looks

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,875
    Thanks
    92
    Thanked 97 Times in 95 Posts

    Default

    dev.mysql has the official information, but I have never been a big fan of their website's layout. Here is the link for the page for creating a table:

    http://dev.mysql.com/doc/refman/5.7/...ate-table.html

    Code:
    CREATE TABLE tablename( 
    (ID INT NOT NULL AUTO_INCREMENT,
    summary TEXT,
    date DATETIME,
    PRIMARY KEY(ID))
    Here the ID is the unique identifier. You need to have one column where the values are always unique. I usually use ID, but it can be anything. "summary" and "date" are just two examples of a couple of columns you could have.


    Here is an example of how you would connect to your database:
    Code:
    $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
    mysqli_select_db($conn,"my_db") or die(mysqli_error());
    put it together and you have:

    Code:
    $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
    mysqli_select_db($conn,"my_db") or die(mysqli_error());
    $query = "CREATE TABLE tablename( 
    (ID INT NOT NULL AUTO_INCREMENT,
    summary TEXT,
    date DATETIME,
    PRIMARY KEY(ID))";
    $result = mysqli_query($conn,$query) or die ("Couldn't execute query.");
    The above will connect to your database and create a table named "tablename". You will need to add some rows of data to it before you can do much with it other than see if it exists or add a column to the table.

    It should be pointed out that no security has been used to protect the code at his stage.
    To choose the lesser of two evils is still to choose evil. My personal site

  5. #5
    Join Date
    Mar 2015
    Location
    Second Life
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default

    Awesome, ty James
    Appreciate it greatly for the pages & the fix on my thread title too

  6. #6
    Join Date
    Mar 2015
    Location
    Second Life
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default

    K, i made a db but

    I am trying to put a cms & in the setup.php it gives me the stuff to make the tables but I need help how to set up the tables

    Here is the info from the setup.php:
    Code:
    CREATE TABLE IF NOT EXISTS `members` (
      `memberID` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) NOT NULL DEFAULT '',
      `password` varchar(32) NOT NULL DEFAULT '',
      PRIMARY KEY (`memberID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
    
    INSERT INTO `members` (`memberID`, `username`, `password`) VALUES
    (1, 'admin', '21232f297a57a5a743894a0e4a801fc3');
    
    CREATE TABLE IF NOT EXISTS `pages` (
      `pageID` int(11) NOT NULL AUTO_INCREMENT,
      `pageTitle` varchar(255) DEFAULT NULL,
      `isRoot` int(11) NOT NULL DEFAULT '1',
      `pageCont` text,
      PRIMARY KEY (`pageID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
    
    INSERT INTO `pages` (`pageID`, `pageTitle`, `isRoot`, `pageCont`) VALUES
    (1, 'Home', 0, '<p>Sample Sample content</p>'),
    (2, 'About', 1, '<p>Sample Sample content</p>'),
    (3, 'Services', 1, '<p>Sample Sample content</p>'),
    (4, 'News', 1, '<p>Sample Sample content</p>'),
    (5, 'Contact', 1, '<p>Sample Sample content</p>');
    And when I go into my db, I see this:
    Code:
    Name Type Length/Values Default Collation Attributes	 Null Index A_I Comments MIME type Browser transformation Transformation options
    I am guessing A_I is this: AUTO_INCREMENT

    In the db & that is where I go blank & confused

    Can anyone help me out what goes where in the 'create a table' part
    Last edited by xhilaration; 03-22-2015 at 06:40 PM. Reason: Fixing the wording

  7. #7
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,875
    Thanks
    92
    Thanked 97 Times in 95 Posts

    Default

    Honestly, your tables look pretty good at first glance. You have even populated both. Don't worry about going into your database directly. You should be able to access it using php. php and MySQL were practically made for each other as far as code interaction.

    Just to make sure you are connecting to the database correctly can you post the php you are using in your setup.php file? The code you posted was all pure MySQL, but I didn't see any php in there.

    When you do post your code don't your connection info. Just post XXXXX for your database name, password, etc. so that others don't have the necessary info to hack your database.
    Last edited by james438; 03-23-2015 at 12:44 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #8
    Join Date
    Mar 2015
    Location
    Second Life
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default

    Go here to look at the setup.php on my server:
    http://www.crueltycouture.allalla.com/cms/setup.php
    Main index page:
    http://www.crueltycouture.allalla.com/cms/index.php
    ---------------------
    Quote Originally Posted by james438 View Post
    Honestly, your tables look pretty good at first glance. You have even populated both. Don't worry about going into your database directly. You should be able to access it using php. php and MySQL were practically made for each other as far as code interaction.

    Just to make sure you are connecting to the database correctly can you post the php you are using in your setup.php file? The code you posted was all pure MySQL, but I didn't see any php in there.

    When you do post your code don't your connection info. Just post XXXXX for your database, password, etc. so that others don't have the necessary info to hack your database.
    Thats what was in the setup.php
    This here:

    CREATE TABLE IF NOT EXISTS `members` (
    `memberID` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL DEFAULT '',
    `password` varchar(32) NOT NULL DEFAULT '',
    PRIMARY KEY (`memberID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

    INSERT INTO `members` (`memberID`, `username`, `password`) VALUES
    (1, 'admin', '21232f297a57a5a743894a0e4a801fc3');

    CREATE TABLE IF NOT EXISTS `pages` (
    `pageID` int(11) NOT NULL AUTO_INCREMENT,
    `pageTitle` varchar(255) DEFAULT NULL,
    `isRoot` int(11) NOT NULL DEFAULT '1',
    `pageCont` text,
    PRIMARY KEY (`pageID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

    INSERT INTO `pages` (`pageID`, `pageTitle`, `isRoot`, `pageCont`) VALUES
    (1, 'Home', 0, '<p>Sample Sample content</p>'),
    (2, 'About', 1, '<p>Sample Sample content</p>'),
    (3, 'Services', 1, '<p>Sample Sample content</p>'),
    (4, 'News', 1, '<p>Sample Sample content</p>'),
    (5, 'Contact', 1, '<p>Sample Sample content</p>');

    Dave's github:
    https://github.com/daveismynamecom/s...ster/setup.php

    Where is saying to go into my phpmyadmin:
    https://daveismyname.com/building-a-...p#.VQ9h-Nm9Kc1
    Click the link there to look at it on github & here to read that he said to go into the phpmyadmin to set up the tables & when I go into my phpmyadmin to make the tables, I start to get like blinde sided if that makes since
    Last edited by xhilaration; 03-23-2015 at 12:50 AM. Reason: Forgot to add another url link & my site with setup.php & index.php

  9. #9
    Join Date
    Jul 2008
    Location
    Derbyshire, UK
    Posts
    3,035
    Thanks
    25
    Thanked 599 Times in 575 Posts
    Blog Entries
    40

    Default

    From memory, I believe there's an "SQL" tab in phpMyAdmin where you can run SQL queries on an existing database, so you copy the code there and run it.
    Focus on Function Web Design
    Fast Edit (A flat file, PHP web page editor & CMS. Small, FREE, no database!) | Fast Edit BE (Snippet Manager) (Web content editor for multiple editable regions!) | Fast Apps

  10. The Following User Says Thank You to Beverleyh For This Useful Post:

    xhilaration (03-23-2015)

  11. #10
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,875
    Thanks
    92
    Thanked 97 Times in 95 Posts

    Default

    Here is what it looks like if you want to create the tables directly and populate them directly as Beverly was referring to.
    Click image for larger version. 

Name:	sql1.jpg 
Views:	316 
Size:	89.3 KB 
ID:	5643
    Click image for larger version. 

Name:	sql2.jpg 
Views:	317 
Size:	91.4 KB 
ID:	5644

    To do using a file like setup.php you are on the right track, but you need to connect to the database first.

    Code:
    <?php
    $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
    mysqli_select_db($conn,"my_db") or die(mysqli_error());
    $query="your mysql code here.  make sure you do not use double quotes in your sql code unless you escape it with a \ first.";
    $result = mysqli_query($conn,$query) or die ("Couldn't execute query.");
    ?>
    To choose the lesser of two evils is still to choose evil. My personal site

  12. The Following User Says Thank You to james438 For This Useful Post:

    xhilaration (03-23-2015)

Similar Threads

  1. Store other mysql table data into another table.
    By udaybabu in forum MySQL and other databases
    Replies: 0
    Last Post: 12-29-2014, 07:12 AM
  2. Make a search in a mysql table.
    By chetanmadaan in forum MySQL and other databases
    Replies: 4
    Last Post: 06-20-2010, 10:17 PM
  3. PHP and MySQL table
    By calumogg in forum PHP
    Replies: 8
    Last Post: 10-24-2007, 05:48 PM
  4. How to make foreign key linking in PHP/MYSQL?
    By devil_vin in forum MySQL and other databases
    Replies: 1
    Last Post: 10-11-2007, 06:28 AM
  5. copy records from table to table in MySQL?
    By nephish in forum Other
    Replies: 0
    Last Post: 07-29-2005, 07:40 AM

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
  •