MySQL...erg....UH NO

[kvnmck18]

I know php pretty well, I know XML pretty well and I've never used MySQL...I've always avoided it but I would like to start using it now...I just don't really know how to go about it. Where do I start?I want to create a sign up/login page that goes to an individual page for each member. I don't even know what book to look at or where to go about, anyone want to help me out with some baby steps then lead up to long jumps?Thanks,-Kevin

[Akula_dude]

databases are just tables, really, but with a nice eay way of selecting entries.For example, if I had a table called "members" wich holds the Usernames and emailsof people and I wanted to see the list of usenames: my_sql_query( "SELECT Username FROM Members" );For more (alot) of info, read the tutorial. Keep on with it, databases are very useful.

[reportingsjr]

Yes they are! There is a very good tutorial explaining it on melonfire.com, ill pull the link for you in a second!Found it http://www.melonfire.com/community/columns...ticle.php?id=39Just read through that and you will understand it much more!

[yoshida]

databases are just tables, really, but with a nice eay way of selecting entries.For example, if I had a table called "members" wich holds the Usernames and emailsof people and I wanted to see the list of usenames: my_sql_query( "SELECT Username FROM Members" );For more (alot) of info, read the tutorial. Keep on with it, databases are very useful.

I especially like functions as "SELECT * FROM members WHERE name='$name'"The results obtained by that query can be used in a variable and re-used in new queries. I use it to let people store blogs and personalized stylesheets.

[Jack McKalling]

I use MySQL for holding simple general settings about my site, like how much pageviews I have, and for storing data about movies, which I list at my site.MySQL was not that complicated to learn for me, but then again, I started it with a very easy book called "PHP & MySQL for dummies" (Dutch translation) and it helped me A LOT

[boen_robot]

I think making the jump from XML must be done with an analogy if you all don't mind .Think of a MySQL database like a single XML file. Each table in it could be represented as a child element of the root one. Each record (row) in it, as another element, and a field (column), as a final element, or to make it in figures:

<database>  <table id="members">    <record>      <field id="username">Table 1, Row 1, Column1</field>      <field id="password">Table 1, Row 1, Column2</field>      <field id="something">Table 1, Row 1, Column3</field>    </record>    <record>      <field id="username">Table 1, Row 2, Column1</field>      <field id="password">Table 1, Row 2, Column2</field>      <field id="something">Table 1, Row 2, Column3</field>    </record>  </table>  <table id="customers">    <record>      <field id="name" >Table 2, Row 1, Column1</field>      <field id="adress">Table 2, Row 1, Column2</field>      <field id="phone">Table 2, Row 1, Column3</field>    </record>    <record>      <field id="name">Table 2, Row 2, Column1</field>      <field id="adress">Table 2, Row 2, Column2</field>      <field id="phone">Table 2, Row 2, Column3</field>    </record>  </table></database>

You know that with XPath you can select a certain data field. For example

/*/table[id='customers']/record/field[id='phone']

Will select all phones in the customers table.MySQL makes the database in a binary way and accesses them with an SQL query. Think of it like the XPath of MySQL. An equivalent to the avobe is (I think):

SELECT phone FROM customers

But SQL allows manipulation of data as well. Server side scripting languages do have XML writers though, but they don't use XPath, unlike MySQL which uses SQL syntax to manipulate data.Side note: XQuery is supposed to change that I think.Just learn the SQL syntax and PHPs MySQL functions. Scince you have some background in XML and understand the concept of a database, it will be easy to adopt it.

[kvnmck18]

Thank everyone I'm going to get on this. Good explinations.

[kvnmck18]

I can't get this to work...

<?php$mysql_host="myserveraddress";$mysql_user="myusername";$mysql_password="*****";$link = mysql_connect("$mysql_host", "$mysql_user", "$mysql_password")	or die("Could not connect : " . mysql_error());if (mysql_query("CREATE DATABASE my_base",$link))  {  echo "Database created";  }else  {  echo "Error creating database: " . mysql_error();  }mysql_close($link);?>

That should work but I keep getting error saying that my username does not have access. :\

[justsomeguy]

You probably need to set up users in the mysql server. Set up a mysql user with a password, and give the user access to the database that you are trying to use, or permission to create new databases (apparently you are trying to create a new database). If you are running this on a host you pay for, chances are you don't have permission to create a new database yourself.

[kvnmck18]

I have already done that but I get an error...and it is on my host... :\

[justsomeguy]

Well, there's something you haven't done. If you had done everything, then you wouldn't be getting the error. You need to create a user, with a password, and give the user access to do anything. Check the MySQL manual for instructions on how to do that.

[kvnmck18]

I've most of this working now, I just have some questions about settings...Number of fields is equal to...the number of boxes you want, like if you wanted age/Fname/Lname, you would say 3 fields...correct?How do know what "Type" to make each field? There are like 28 options How do you know long to make "Length/Values"? What should I set the "Collation", "Attributes", "Null/Not Null" to? What does "auto_increment" do?I'm still confused...I hope you can help

[justsomeguy]

This is more of a question for the SQL forum, but I'll try to take it one by one."Field" is the term for an item (column) in the database. A row (entry) is made up of several fields. So, yes, if you want 3 items to be stored in the database, then specify 3 fields.Datatype is determined by what you want the field to hold. Normal numbers are stored as int, decimal numbers are float, for character strings the best is probably varchar, unless you need international characters. If you need international characters, use nvarchar, but nvarchar takes up more space then varchar. Using varchar is better then using char because a field that is char(50) will always use up memory for 50 characters, regardless of how many are actually stored in the field. varchar allocates memory according to the number of characters that are actually being stored. varchar and nvarchar have a maximum length of 255 characters, so if you need more then that (like a big paragraph, or input from an HTML textarea box), then use the text datatype instead. The normal text type (not smalltext or bigtext) has a limit of something like 16MB of text. I think smalltext has a limit if 64KB. If you want to store binary data (like a file, an image or something) use blob (binary large object). If you only want to store a single bit value (1 or 0), use int with a size of 1, or bit if you see it there. You can use datetime for storing dates, but I prefer to store Unix datestamps, which are only integer numbers, so my date columns are int fields. You can get more information on all of the different datatypes in the MySQL documentation.http://mysql.org/doc/refman/4.1/en/data-types.htmlFor length, you can leave it to the default for most number fields. The text datatype should also be left to the default. For varchar, enter the maximum number of characters you would expect. Like, for storing an IP address, the length would be 15 (xxx.xxx.xxx.xxx). If you set a maximum length on a varchar field, make sure to validate the form input and make sure that the data is not too long.You can leave collation to be the defalt. I can't remember which options are in the attributes field, but I don't normally use them. Set a field to allow null values if you don't care if it is required. Fields that do not allow null values are required. The primary key for your table can never be null. Fields with the text datatype have to allow nulls. If you leave nulls off on a number field, and you don't specify a value, the value will be 0.auto_increment can only be used on an integer field that is the primary key. It will increment the field for each row that you add automatically. So, if you have a row called id that is an integer primary key, and it is set to auto_increment, you can insert three rows like this:insert into users (fname, lname) values ('John', 'Smith')insert into users (fname, lname) values ('Jane', 'Smith')insert into users (fname, lname) values ('Bob', 'Smith')You did not specify the id field in the insert, but since it is set to auto_increment, the first row will be id 1, the second will be id 2, and the third will be id 3. An auto_increment field will automatically take the next-highest value in the table.

[kvnmck18]

Thank you all very helpful.