just got mysql but I dont know what to do

I just got a MySQL database on my free account (not bad, huh?) and I also have PHP support but I don't know what to do with it. I don't really understand how it all works, so if someone could explain or point me towards a good tutorial...

I went to http://hotwired.lycos.com/webmonkey/99/21/index2a.html but it was a little too advanced. It wanted me to go to mysql.com and php.com and download something (???) and then move around all these files and rename some and change extensions and edit stuff, etc. Is it really all that complicated? Or do I need to do all this stuff to create a shoutbox or comments system or site stats system using PHP and MySQL?

tom

You need to create a database, give it a name (relevant is better.) assign a user to it, and give it a password.

Now you can use php to connect to it. Usually hosts stick to "localhost" as the database server name. But they should tell you that. If you can't create a database on your own (via like phpmyadmin or using ssh) then you'll have to contact the techs and give them the info you want for it.

Hopefully you have phpMyAdmin installed as part of your hosting plan, makes life real easy to set up mysql database tables, etc.


once you have it set up. Create the variables you need:

I put these in a file called dbConnect.inc.php and outside the public directory:

/home/yoursite/public_html/bad place to put this file

/home/yoursite/db/better place

Then when you include those variables you just use this path:
/home/mardala/db/yourfile.inc.php

It may be different on your host, but the idea is the same....

What you need in that file is:
database name, database user, database password, host.

so for instance:
[code]$dbName = your_db;
$dbUsr = SomeName;
$dbPass = SomePass;
$db = "localhost";[/code]

Now you can use these to connect. Now you can also create a connection include file (.inc.php ---> dont use .inc ). It's simple:

[code]
include('/home/yoursite/db/yourfile.inc.php');
$objConnection = mysql_connect($db, $dbUsr, $dbPass) or die("Error: mysql_error());

mysql_select_db($dbName, $objConnection);[/code]

Now you can use these files to connect to send queries to the database and use them in your pages.

One thing you can do with images is 1. store them as binaries in the database, or 2. just have a path that is dynamically generated (better -> faster and easier to back up.)

SQL is pretty straightforward.

[code]
$strSQL = "SELECT some_field, another_field FROM some_table WHERE some_value = another_value";

or

$strSQL = "SELECT * FROM some_table WHERE some_value = another_value";

[/code]

Those are basic queries. You can use JOINS (INNERJOIN, LEFTJOIN, etc.) which puts two or more tables together in a specific manner. That gets into more detail and would require a bit more explanation. There is also INSERT, UPDATE, etc. queries where you can alter or add database data using forms and php.

Some databases (like SQL2k, SQL7) require you to "close" the connection after use. mySQL you don't need to.

You might be better off getting a book on mySQL, esp with some chapters on php and mysql. Because there is a lot more to it. You need to read a bit about normalising tables and using Keys which is a way to uniquely identify a row of data. Typically your first field (column) would be something like:
[code]
CREATE TABLE your_table(
table_id SMALLINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
table_name VARCHAR (25) NOT NULL,
etc...
);
[/code]

The id allows you to make fast searches and comparisons, plus its required in a relational database.

SQL queries work best when not having to find strings to compare. You can do searches like: find some string where the id number is equal to this id number. Like that, best to keep to simple numbers to identify data, but you can also use LIKE sql command. Which searches for a string of data.

But get a book, and read it. SQL is quite easy, but can get very complex. For making webpages you don't need to make it too complex (normally.)

When you create a page, you can use the mysql array function and a php loop to display all the data one something. So if you have a table with all of your "shouts" in it, you can loop through all of them and it will display all of them. Also you can limit what you want to your hearts content. That way you just make one page that displays all your data, or a function that can do that. Simplifies your pages alot.

I can get some php and mysql display examples in a bit if you want.

Alright thanks so much for your help. I didn't get too much information about the database from the e-mail I got. Here it is (almost) verbatim:

- - - - - - - - - - - - - - - - - - - -
Hi!
Here is your database.

Account: nacy1073
Pass: ********
DataBase: DB_nacy1073
- - - - - - - - - - - - - - - - - - - -

So I guess they've given me a name for my database which is fine. I don't have phpAdmin or any power user functions. I don't even have web-based upload (just to give you an idea of what kind of free host I have). I also don't understand which folder I should put files into.

My site is at http://trhaynes.ionichost.com so I guess I should put them under http://trhaynes.ionichost.com/db? Also what files should I make there? I don't understand this .inc extension. Does it matter what the first file (the one that defines database name/username/password/host) is called?

From what I understand, I'm making a .php file that links to/accesses my database. Then I can make more .php files that read or write to my database, after checking with the first .php file to see if the names and password are correct. Is this (very simplified) breakdown correct?

From there, I understand that I can make forms (like shoutboxes) that write to my database via a .php file. Then I can include a .php file which retrieves information from my database. If this is correct then I'm going to have a lot to learn about how all this works, huh?

You have posted some code snippets - I'm sure there is other information that needs to be added as code. Do I need to use or something? I guess that gives you an idea about how little I know about the whole php business.

I hope you can see that I'm working really hard at it though, so any help that you would give me would be excellent. Thank you so much.

tom

The database will store any data you put in it. Your files are just regular web page files. all you really need to do is make sure you have your database connection variables (user, password, etc.) stored in a safe place and that you do NOT have directory browsing enabled (user .htaccess if you have to.), otherwise if you can't put them above the publi_html directory, if you can browse you can see your files.

Wow marble great explaination because I don't know a jack about SQL and MySQL although I know they are databases and can be connected and used in a message board, etc.. But never know how to create or use them, thx a bunch..

Originally posted by trhaynes
So I guess they've given me a name for my database which is fine. I don't have phpAdmin or any power user functions. I don't even have web-based upload (just to give you an idea of what kind of free host I have). I also don't understand which folder I should put files into.

I would contact the tech's and see what you can do to access the database. If you don't have a client to access it you can connect from your own computer using a downloadable client. I am not 100% what is available for mySQL . Otherwise you can make your php page that will interact with the database. You could start off by making a simple form that you can send queries to your db from your site. You would be able to create the tables, etc. Then start filling some test data to get the php working. man it would be much easier if they had phpMyAdmin installed.
I know this is annoying to hear, but find some paid hosting. Its so cheap now and you get all the extras, including usually at least 2 databases.

I still don't understand what file I put my connection info into. Is it dbConnect.inc.php? And I make it like this:

[PHP]
$dbName = your_db;
$dbUsr = SomeName;
$dbPass = SomePass;
$db = "localhost";
?>
[/PHP]

??

Either that or you're saying that I can download a program that will manage it for me? Like PHPMyAdmin but not from my host? Or what?

Also, what actions do I put in my forms to make them write to the database? I need to get data into the database before I can post it on my pages.

Thanks again.

tom

phpMyAdmin has to be run from the web server. What that file above does is puts the connection info into variables that you can use for any page that needs a database connection. When you do need a database connection you use the mysql_connect() function and use those variables. The reason I put them into a seperate file and above the public_html directory is an added level of security. You can just use the info directly in the mysql_connect() function to connect.

Think of it like this:

1. Some connection info.
2. Use that to create a connection ($objConn)
3. Create an SQL statement ($strSQL).
4. use $strSQL and $objConn to run the query.
5. use php and some html to display the information.

I'm not completely sure what a public_html directory is. Do you just mean that I should hide it in a couple folders instead of putting it in the root directory?

Then I'll make a dbConnect.inc.php file with the above info.

Then I add $objConn to connect, but where do I add that? To a form of an HTML page?

tom

YOu are going to have to stop thinking html and start thinking php =)

Whenever you use any kind of php function or code, it has to be a php page. You can take all of your pages and change the extenstion to .php instead of .html and start from there. It parses html code as html code. Then when you add php code it will parse that as php code as well.

public_html directory is the root of your website. You would put your index.php or index.html page there. You cannot just browse above that directory. like this: http://../../index.php that isn't allowed. so if you put pages up there, then they are at least a little safer. Can you use an ftp client? like wsFtp or cuteFtp? You can create directories up there, etc.

That is where i put the dbConnect.inc.php file. Now $objConn is what you use to connect to the database. So if you make some code that needs to query the database, you open a connection first, then submit the query. I will get a sample page up for you, might make more sense. Give me a bit (maybe tomorrow?)