[tony2getit]

Hi, please help as patience is wearing. I am receiving the following error message when running script to connect to MySQL database and then create some tables. I am using XAMPP and Notepad to write scripts if this info helps.

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 'EXIST moviesite' at line 1

For the life of me I can't see where the error is and have a feeling it lies somewhere else.
This is my first attempt at a database in PHP and I am eager to move on from here as well as learn where I am messing up. The script is here:

PHP Code:

<?php
//connect to MySQL



$db = mysql_connect ('localhost', 'bp6am', '*******') or
    die ('Unable to connect. Check your connection parameters.');

//create the main database if it doesn't already exist
$query = 'CREATE DATABASE IF NOT EXIST moviesite';
mysql_query($query, $db) or die(mysql_error($db));

//make sure our recently created datbase is the active one 
mysql_select_db('moviesite', $db) or die(mysql_error($db));

//create the movie table
$query = 'CREATE TABLE movie (

    movie_id    INTEGER UNSIGNED   NOT NULL AUTO_INCREMENT,
    movie_name    VARCHAR (255)       NOT NULL,
    movie_type    TINYINT           NOT NULL DEFAULT 0,
    movie_year    SMALLINT UNSIGNED  NOT NULL DEFAULT 0,
    movie_leadactor INTEGER UNSIGNED   NOT NULL DEFAULT 0,
    movie_director  INTEGER UNSIGNED   NOT NULL DEFAULT 0,

    PRIMARY KEY (movie_id),
    KEY movie_type (movie_type, movie_year)

    )
    
    ENGINE=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

//create the movietype table
$query = 'CREATE TABLE movietype (
    movietype_id    TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    movietype_label VARCHAR(100)     NOT NULL,
    PRIMARY KEY (movietype_id)
    )
    ENGINE=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

//create the people table
$query = 'CREATE TABLE people (
    people_id      INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
    people_fullname   VARCHAR (225)        NOT NULL,
    people_isactor    TINYINT (1) UNSIGNED    NOT NULL DEFAULT 0,
    people_isdirector TINYINT (1) UNSIGNED  NOT NULL DEAFULT 0,

    PRIMARY KEY (people_id)

    )
    ENGINE=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

echo 'Movie database successfully created!';
?>

User name and password have been set in PHPMyAdmin and I assume these are fine. Tired of checking code over and over and not finding the error. A solution to this would enhance my day immensely as I have looked on various web links for an answer without success.

[Nico]

Code:

CREATE DATABASE IF NOT EXISTS moviesite

... you missed an S.

[tony2getit]

Thanks for prompt reply Nico and what can I say but DOH! However, I did have a feeling that I may have other problems and now have the error message:

Access denied for user 'bp6am'@'localhost' to database 'moviesite'

I am assuming that this has to do with permissions for password etc. and set up folders and the like which I have not had a lot of experience of using XAMPP or WAMP as I have only had to worry about programme code previously. Was sure that setting it in XAMPP PHPMyAdmin was enough. Is there somewhere else I should look to change in another part of XAMPP.

I originally tried using the 'root' option which is installed by XAMPP as default but could not get any of the example script ****ions on the web to work. Sorry if this should be obvious but it's not to me at the moment and am trying desperately to get past this hurdle and crack on with working on database for real. On a real learning curve here.

[tony2getit]

Thanks for your prompt reply Nico and all I can say is DOH! In future will check with a fine tooth comb but was assuming a connection error which is what I now have with the following message:

Access denied for user 'bp6am'@'localhost' to database 'moviesite'

I have as far as my limited knowledge of admin procedures/permissions set the user and password correctly in PHPMyAdmin but maybe I need to change elsewhere such another Xampp program folder. Must admit this all a bit fiddly for me at the moment and not obvious as I am just used to getting on with coding in the past. Is there something else to look for here so that I can leap this hurdle and get down to the more satisfying task of creating my actual database.

I'm on a good learning curve here however, and really should learn all about administration of MySql for the future but only have books and knowlege of programming concepts.

[Nico]

The default usename for XAMPP is "root", with no password. Have you tried that?

Although, since you're still learning, I suggest you move straight away from the mysql_* library. It's very old, slow, and insecure. Instead, look at the improved MySQLi version or PDO. It does look more complicated at the beginning, but you won't regret it.

[tony2getit]

Hi Nico, had tried using root with no password previously and got the following error:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' (using password: YES) in C:\Program Files\xampp\htdocs\db_ch03-1.php on line 6
Unable to connect. Check your connection parameters.

The script I tried was this:

$db = mysql_connect ('localhost', 'root', ' ') or
die ('Unable to connect. Check your connection parameters.');

I found this example posted by other users but doesn;t work for me.

Obviously I have not done this correctly somewhere and not sure exactly how to change as I have played around with leaving password completely blank and using double quotes for password i.e " " as empty string all to no avail.

As for MySQLi, this sounds like a good idea but how would I now use this with my XAMPP already installed or should I do a new install package? Sorry if I'm not seeing what I should be and am waiting for that moment when it all falls into place.

[Nico]

I'm unable to help you, then. XAMPP doesn't have a default password, so you must have changed it somehow. You could see if you find something in your php.ini file.

As for MySQLi, it's enabled by default too, so it shouldn't be a problem.

[stoopkid]

Nico is correct, there is no password as a default setting. You can change it though in the config file, or by going to the security area locally: http://localhost/security/

I use XAMPP on a daily base at work so post back if you still encounter problems or check here:
apache friends - xampp for windows

[tony2getit]

Hi Nico

Been working on some other stuff and then came back to fix this error. Thanks for the advice and I looked for the settings in MySql in case they had been changed. I installed Wamp server and still had issues but then found a tutorial on how to re-set user name and password back to defaults using MySql console and was then able to connect.

[Nico]

Awesome! Mind posting that tutorial, in case someone else here has the same problem?