Learning MySQL

[Descendant]

wow I just got it working on my XAMPP and I go to a tutorial and I already get stumpped..... Here is the tutorial I'm trying to do: http://www.tizag.com/mysqlTutorial/mysqldatabase.php

now this is what I'm trying to do. Making a database named "test" and making a username and password for it and I guess a table lol but I'm stuck on the username/password part.

I can make the database just fine but now where do i go to make the usernames...

sorry I'm really newb, any help is appericated!

[Izzy]

There is already a database created in phpMyAdmin called test.

So I suggest you use a different name for your test database like mytest etc.

I posted a how to create a Wordpress blog database that may help you, as it applies in principle to any database creation in phpMyAdmin.

Try and follow this tutorial and change the names to anything you like or use the names as is, but don't use the test database already created by default in phpMyAdmin.

http://community.apachefriends.org/f/viewtopi ... 258#112258

[Descendant]

omg... really cause i just deleted that one!!! uhhh, how do i get it back

[Izzy]

Too late now so don't worry about it but don't use the test database you replaced it with but rather follow my tut and start over.

From now on get used to making a backup before changing or deleting anything that is already contained within XAMPP or any of it's modules and in particular .conf and .ini files.

Then if you make a mistake you can always rely on the backup you made of the original files to get you out of trouble.

[Descendant]

sweet, easy enough lol but is it ok that the username "root" has access to it also?

also do you have any other tuts for mysql?

[Izzy]

The user root is the MySQL super user and has access to everything - in other words the user root is the MySQL administrator and is why you should give the user root a password using the Security page from the Welcome Page menu item which helps to make your server one notch more secure.

Sorry I don't have more for you, but the Internet, as you found out may have many tuts that will help you.

I am pleased also that you could follow my little tut and, as I pointed out, you can use that method to create any database ready for populating with tables and data, usually when installing a script like Wordpress and phpBB etc. that creates tables and adds data from within the script's install procedure.

[Descendant]

I'm planning to code something like facebook/myspace in the future what type of database structure do they have? OR what would I have to look into for allowing users to create there own acc's and editing it like something in phpBB or w/e facebook uses.

Really sorry I'm old school html going into php and this is all magic to me right now haha

[Descendant]

ok so I'm going through a tutorial and it was getting me to make atable for my database by using php.

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("localhost", "wp", "pw") or die(mysql_error());
mysql_select_db("wordpress") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE example(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
 name VARCHAR(30), s
 age INT)")
 or die(mysql_error()); 

echo "Table Created!";

?>


so when i tryed this code it gave me an error

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'age INT)' at line 5

sorry, I really don't know what this error message is saying

[KallistaAEnvarou]

It's age INT (##), not age INT.

The best way to learn MySQL is to get your desired result in phpMyAdmin first and then follow the syntax you see after your command is run.

For using the create command, though, use CREATE {TABLE/DATABASE/WHATEVER} IF NOT EXISTS. The IF NOT EXISTS part will prevent any future errors upon you running that script again. Also, read up on minimal permissions. Using your root do everything is not very secure.

As well, you should ALWAYS include a specific die message when you do mysql_query commands. If you simply do die(mysql_error()) and you have 30 queries running, you won't know where the error is coming from. I have a function that tells me the line number, file name, and mysql error of any failed mysql_query. If you want it, I'll post it.

[Descendant]

ok so i tryed it out

Code: Select all

 age INT(##)")

gives me the same error but points at "age INT(##)" I'm trying to do this tutorial here: http://www.tizag.com/mysqlTutorial/mysqltables.php

and it would be nice to have that function =)

[edit]
totally didn't see that s I had after a comma that f'd up my code!
[/edit]

[KallistaAEnvarou]

That was supposed to be a variable. So you want to use INT(1) or INT(7) or INT(30). You need to specify how many digits the database needs to accept.

As for the functions:

Code: Select all

function con($user,$database,$line)
{
   /**
   Function statement: connects to a database on localhost using a given user and database, and errors with a unique, descriptive method if no connection is possible
   Input
      $user:
         parameter passed to the function
         describes which user is supposed to connect to the server
      $database:
         parameter passed to the function
         describes which database $user is supposed to select
      $line
         parameter passed to the function with __LINE__
         describes the line which produced an error when an error occurs
      $file
         variable brought in using the global keyword
         defined in the file to point to which file where any error occurred
   Process
         $GLOBALS['con']=@mysql_connect(localhost', $user, $password) or die("Connection of $user on $line in $file failed: " . mysql_error()); //$GLOBALS['con'] allows the $con variable to be used in the originating file after the function has processed; the @ suppresses any warnings //password can be defined here directly or, if different users have different passwords, an if statement can be used; alternatively, you can add a formal parameter to define the password
         $GLOBALS['db']=@mysql_select_db($database) or die("Selection of $database on $line in $file failed: " . mysql_error()); //$GLOBALS['db'] allows the $db variable to be used in the originating file after the function has processed; the @ suppresses any warnings
   Output
      $GLOBALS['con'], passed as $con to the file, to return the connection on success
      $GLOBALS['db'], passed as $db tot he file, to return the database selection upon success
      Error message returned on failure, and the script exits
   */

   global $file;
   $GLOBALS['con']=@mysql_connect('localhost',$user,$password) or die("Connection of $user on $line in $file failed: " . mysql_error());
   $GLOBALS['db']=@mysql_select_db($database) or die("Selection of phplearn_makingalyriaeasy$database on $line in $file failed: " . mysql_error());
}

function sql($query,$line,$toomany="")
{
   /**
   Function statement: Performs an SQL query and returns the location of the error with the line number and file name.
   Input
      $query
         Passed to the function
         Signifies the query the mysql_query() function is supposed to parse
      $line
         Passed to the function via the __LINE__ constant
         Signifies the line number of the function call
      $GLOBALS['file']
         Defined outside the function via the $file= command (called via the superglobal $GLOBALS variable)
         Signifies the file name of the function call (when $file= is used at the top of the file)
   Process
      $result=mysql_query($query) or die("Query on $line in $file failed: " . mysql_error());
   Output
      Return $result //returning the process function directly will not work
   Function call
      sql($query,__LINE__) // $query may be passed as a variable or as a direct definition [ex: sql("SELECT * FROM selecttable",__LINE__)]
   Assumptions
      Established connection to the database with proper user permissions
   */

   global $file;
   $result=mysql_query($query) or die("Query on $line in $file failed: " . mysql_error());
   return $result;
}

function closesql($line,$toomany="")
{
   /**
   Function statement: closes the current mysql connection
   Input
      $line
         Passed to the functioin via __LINE__
         Signifies the line number where any
      $con
         Passed to the function via the global keyword
         Signifies the variable where the connection information is stored
      $file
         Passed to the function via the global keyword
         Signifies the file name where an error might occur
   Process
      return (mysql_close($con) or die("Closure of mysql connection on $line in $file failed. Make sure that it was opened and that it hasn't already been closed."));
   Return
      The closed connection
   Function call
      closesql(__LINE__);
   */

   global $con;
   global $file;
   return (mysql_close($con) or die("Closure of mysql connection on $line in $file failed. Make sure that it was opened and that it hasn't already been closed.");
}


If you need more information on the global keyword, see php.net. You'll be able to understand the functions better if you have a colour-coded text editor like jEdit. If you need me to be more detailed on anything, just let me know.