need help

[djclewes]

hi

i got this so far

users

CREATE TABLE IF NOT EXISTS users (
user_id int(5) NOT NULL AUTO_INCREMENT,
user_name varchar(25) NOT NULL,
user_email varchar(35) NOT NULL,
user_pass varchar(255) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY user_email (user_email)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

–
– Dumping data for table users

INSERT INTO users (user_id, user_name, user_email, user_pass) VALUES
(myid, ‘myusername’, ‘[email protected]’, ‘mypassword’);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

but i need to add the following tables and don’t know how

images for profile pic and users pictures
date of birth
first name
last name
member since
comments
profile

[ErnieAlex]

Welcome back DJ… PS: Please place your code inside the PHP tags! Thanks!

It is easy to add fields to a table… You would do it something loosely like this. Note that I usually change one
field at a time so that I get a separate error message if it does not work. Since you normally would only change
your structure once, this seems best for me. You can of course go into your hosting control panel and edit the
table directly there which is easier for most programmers, but, here is how you do it in code…

[php]
// Add field to database…
$query = “ALTER TABLE users ADD dob DATETIME”;
$result = mysqli_query($db_connect, $query) or die(“Error adding field to table, error value=” . mysqli_error($db_connect));

// Add another field to database…
$query = “ALTER TABLE users ADD first_name VARCHAR(256)”;
$result = mysqli_query($conn, $query) or die(“Error adding field to table, error value=” . mysqli_error($conn));
[/php]
As you see, you just change the name of the field and the field’s data type. The above was for date of birth which I called “dob” and first name. I set the dob to a datetime format which is best for dates and the first name to 256 characters max for name. Adjust as needed… Also, you can combine them into one huge query, but, it is easier to do one at a time. The mysqli_query uses $conn for the connection, you need to change that to yours. (I was assuming that you meant add new fields not add new tables…)

[benanamen]

In 20+ years of DB admin I have never added columns to a DB with a script. At worst, I used phpmyadmin, at best, navicat. There are numerous Mysql managers available. I dont know why anyone would want to manage the DB structure with a php script. One might possibly do it at the Mysql console, but a script? I think not.

[ErnieAlex]

I realize it is not your way, but, DJ does not seem to have access to the control panel of the server, nor any
access to admin controls like “phpmyadmin”, so I gave him a work-around so he could move on… He only
needed to alter the table one time to get his fields in place… Not the best way, but…

[benanamen]

Dang, Dj needs to find a new host.

[ErnieAlex]

Kevin, maybe you can suggest a good app for him to use. A simple one…

By the way, with the wind-chill factor, it is -36degrees here right now!

[benanamen]

So, DJ needs a new host and Ernie needs to move. No man should live in a place that gets below zero.

Simple free? Simple paid? Navicat is by far the best Mysql GUI. It has a trial download but it is a paid app. Well worth the money and simple to use.

HeidiSQL interface resembles navicat and it is free. I have never used it though.

[ErnieAlex]

Interesting, that Navicat ! It’s only $19.99 for an iPad (iOS), but, $149 for Windows version?
I doubt DJ will want to pay that when he can just run the one time script to do it for him… Hmmmm…

Well, yep, I should move south! LOL I do like my New England, though…

[jay7981]

I have been using HeidiSQL for a while now and just recently switched to navicat … I have to say im really digging the navicat better but HeidiSQL is easy to use and full of options

PS im not picking on the OP but “Need Help” … not the best of subject titles…

[JimL]

In 20+ years of DB admin I have never added columns to a DB with a script. At worst, I used phpmyadmin, at best, navicat. There are numerous Mysql managers available. I dont know why anyone would want to manage the DB structure with a php script. One might possibly do it at the Mysql console, but a script? I think not.

A lot of companies rely on modifying the DB through scripts. DB migration scripts allow you to programatically upgrade/downgrade servers based on which version of the code you have and which “version” of the schema you want. It’s priceless, though of course thinking about migrations scripts and automatically building/deploying apps might be overkill for smaller projects. I love the automation of it though so I usually spend the minutes required setting it up - even for small client projects.

I also find it horrible when people ftp/ssh/sql into production and change stuff, setting up proper deployment means we should get the exact same environment in production as in development. The moment people start messing with stuff (only) on either side is when very interesting bugs are born.

Read more on (awesome) migrations:

DoctrineMigrationsBundle (Symfony Bundles Docs)

How to work with Doctrine migrations in Symfony

[djclewes]

no i have phpadmin via my cpanel on my shared hosting account

[ErnieAlex]

DJ, I was under the assumption you did not have access to that. So, that is where you can add fields.
AND, that is where you should do it. Simply click the table on the left side and then add the fields in the right
side under structure. Simple. Let us know if you do not understand how to do that…

[djclewes]

its when it asks these question i get confused

Name Type Length/Values Default Collation Attributes Null Index

[JimL]

Default
Optional default value of the column

Collation
Which collation (character set) should be used, it’s optional, if left blank the default character set for the database will be used (recommended)

Attributes
One tip is to choose UNSIGNED for numbers that should be > 0. The reason behind this is that ie an INT can normally only contain numbers between -2147483648 and 2147483648. If you know you will never use negative numbers changing the column to UNSIGNED moves the possible data span from 0 to 4294967295. Which means you get twice the possible numbers in the same type.

Null
Can the field be a NULL value (hold no value).

Index
For performance reasons you can tell the database to index specific columns - or even combinations of them. This (along with db design) is a study of its own. But if you query on data (where username = ?) you should probably (as a general rule) consider indexing it.

Why choose limited types? Because smaller types take less space to store, less space means less time while querying, and with databases timing is “everything”.

[djclewes]

what one should i use for images then?

[JimL]

Well that depends, normally you store the name/path to the image as a string in the database - so you just need to select a string type that is long enough for your file names.

[ErnieAlex]

It also depends on your site folder structure a bit. If you have a separate folder for each user to hold files,
then you only need to save the name of the image. For a profile image, I normally assign a new name to it
when stored using the user’s ID number. It is faster and easy to find that way. I have seen some code that
saves it under the username or even the name of user, but, I like the ID number for this.

You never save images in a database. If you encode the name of the image using the ID, you never need any
item stored in the database for images. But, if you need several images per user, you would need to rename
them with something that assigns the image to that user or create a folder for each user. I prefer to just add
their ID number to the front of the image name if more than one is needed.

[member=71845]JimL[/member], nice explanation for DJ on the options in his panel. Am sure that will help him.