How do you make a table in mysql

[xhilaration]

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

[james438]

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?

[xhilaration]

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

[james438]

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/en/create-table.html


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:

$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:


$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.

[xhilaration]

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

[xhilaration]

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:

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:

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

[james438]

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.

[xhilaration]

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
---------------------

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/simple-cms/blob/master/setup.php

Where is saying to go into my phpmyadmin:
https://daveismyname.com/building-a-content-management-system-from-scratch-bp#.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 :(

[Beverleyh]

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.

[james438]

Here is what it looks like if you want to create the tables directly and populate them directly as Beverly was referring to.
5643
5644

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


<?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.");
?>

[xhilaration]

Great, thank you guys
I will try that & see if it will help, if I have anymore ?, will post more in this thread :)

[xhilaration]

When I try to make the tables
The database and the table making it's confusing and I try but it's hard

[james438]

It certainly is difficult or at least it was for me when I first took the time to learn it. I think I spent 8 hours a day on it for a month before I got any sort of grasp on how php and mysql worked together in order to make tables for the project was working on. I was working full time at another job at the time too.

For some people it may be easier and others more difficult, so just be diligent and patient with it and feel free to post here in the forums if you get stuck.