Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Unable to execute create database query.

    I have this code which should allow me to create a mysql database. However when i try to run it online it comes up with the problem 'Unable to run query'.
    Here is the code -

    PHP Code:
    $query="CREATE TABLE 'hornpv886'.'clientdetails' (
      'id' INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
      'Date_Required_From' TEXT NOT NULL,
      'Date_Required_To' TEXT NOT NULL,
      'Firstname' TEXT NOT NULL,
      'Surname' TEXT NOT NULL,
      'Number_of_People' TEXT NOT NULL,
      'Names_of_People' TEXT NOT NULL,
      'Address_Line_1' TEXT NOT NULL,
      'Address_Line_2' TEXT NOT NULL,
      'Town_City' TEXT NOT NULL,
      'County' TEXT NOT NULL,
      'Postcode' TEXT NOT NULL,
      'Country' TEXT NOT NULL,
      'Phone_Number' TEXT NOT NULL,
      'Mobile_Number' TEXT NOT NULL,
      'Email_Address' TEXT NOT NULL,
      'Date_Required_From' TEXT NOT NULL,
      PRIMARY KEY('id'),
      UNIQUE 'id_2'('id')
    )"

    If anyone can help me it would be much appreciated.

    Ryan

  • #2
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Are you connecting to the database first?
    Does the database user you are connecting with have sufficient privallages to create tables?
    You can not say you know how to do something, until you can teach it to someone else.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    And to add more questions, where is the mysql_query() call, and are you checking to see that it succeeded?

  • #4
    New Coder
    Join Date
    Mar 2005
    Location
    UK
    Posts
    79
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi, i've been working with Ryan on this.

    In response to the questions, yes we are connecting to the database first and it's connecting ok.

    The mysql_query() is run after declaring the $query as in the first post as follows:
    PHP Code:
    mysql_query($query) or die("Unable to execute query"); 
    From this we get the die message telling us that the query hasn't been executed.

    I'm presuming there is something wrong with the SQL, but i cannot see anything.

    Any more help would be useful.

    Many Thanks.
    Andrew,
    AKA ItsMe

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Make your error message more informative:

    PHP Code:
    mysql_query($query) or die("SQL Error Occured!<br />Query text: $query<br />Error Text: ".mysql_error()); 

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The problem is most likely the single quotes around the table name and column names. they are unnecessary.

  • #7
    New Coder
    Join Date
    Mar 2005
    Location
    UK
    Posts
    79
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi, we have tried the suggestions.

    Enhancing the error message gave us the following:
    Code:
    Error Text: 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 'PRIMARY KEY('id'),  UNIQUE 'id_2'('id')' at line 19.
    And removing the single quotes also made no difference.

    Any further suggestions?

    Thanks
    Andrew,
    AKA ItsMe

  • #8
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts
    remove the quotes around the column name in the primary key specification.
    You are referring to a column, not a string
    Code:
    PRIMARY KEY(id), 
      UNIQUE id_2(id)

  • #9
    New Coder
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Tried your suggestions still comes up with an error. If you could have a look it would be much appreciated.

    http://www.villaincarvoeiro.com/createdb.php

    Thanks

    Ryan

  • #10
    New Coder
    Join Date
    May 2005
    Location
    Leeds, UK
    Posts
    83
    Thanks
    1
    Thanked 0 Times in 0 Posts
    remove the comma after your UNIQUE id_2(id)

  • #11
    New Coder
    Join Date
    Feb 2007
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for that it works now.
    I have another problem in my page that shows the mysql database. It will only show a comma www.villaincarvoeiro.com/showall.php.
    The code is -

    PHP Code:
    <?php
    $dbhost 
    '******';
    $dbuser '******';
    $dbpass '******';
    $dbname '******';

    mysql_connect($dbhost$dbuser$dbpass) or die ('Error connecting to mysql');

    mysql_select_db($dbname);

    $query "SELECT * FROM clientdetails";
    $outcome mysql_query($query);
    $number mysql_numrows($outcome);          
    mysql_close();
    $i=0;
    while(
    $i<$number) {
    $id=mysql_result($outcome,$i,"id");
    $Date_Required_From=mysql_result($outcome,$i,"Date_Required_From");
    $Date_Required_To=mysql_result($outcome,$i,"Date_Required_To");
    $Firstname=mysql_result($outcome,$i,"Firstname");
    $Surname=mysql_result($outcome,$i,"Surname");
    $Number_of_People=mysql_result($outcome,$i,"Number_of_People");
    $Names_of_People=mysql_result($outcome,$i,"Names_of_People");
    $Address_Line_1=mysql_result($outcome,$i,"Address_Line_1");
    $Address_Line_2=mysql_result($outcome,$i,"Address_Line_2");
    $Town_City=mysql_result($outcome,$i,"Town_City");
    $County=mysql_result($outcome,$i,"County");
    $Postcode=mysql_result($outcome,$i,"Postcode");
    $Country=mysql_result($outcome,$i,"Country");
    $Phone_Number=mysql_result($outcome,$i,"Phone_Number");
    $Mobile_Number=mysql_result($outcome,$i,"Mobile_Number");
    $Email_Address=mysql_result($outcome,$i,"Email_Address");

    echo 
    "id: $id,<br><hr> $Date_Required_From,<br><hr> $Date_Required_To,<br><hr> $Firstname,<br><hr> $Surname,<br><hr> $Number_of_People,<br><hr> $Names_of_People,<br><hr> $Address_Line_1,<br><hr> $Address_Line_2,<br><hr> $Town_City,<br><hr> $County,<br><hr> $Postcode,<br><hr> $Country,<br><hr> $Phone_Number,<br><hr> $Mobile_Number,<br><hr> $Email_Address,<br><hr>";



    $i++;
    }
    ?>
    Once again thanks for all your help.

    Ryan

  • #12
    Regular Coder
    Join Date
    Jan 2007
    Posts
    217
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I'm assuming all that code is to make the field names into variables, right? You could use the extract function which will do that for you.

    while($row =mysql_fetch_array($result)) {
    extract($row);
    $variables blah blah
    }

  • #13
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    Cleaned it up. If you're not getting results, then there's either a problem with the name of the fields, or there's no data in the db

    PHP Code:
    <?php
    $dbhost 
    '******';
    $dbuser '******';
    $dbpass '******';
    $dbname '******';

    mysql_connect($dbhost$dbuser$dbpass) or die ('Error connecting to mysql');

    mysql_select_db($dbname);

    $query "SELECT * FROM clientdetails";
    $outcome mysql_query($query);
    // The function is mysql_num_rows()
    //$number = mysql_numrows($outcome);   
    $number mysql_num_rows($outcome);
    mysql_close();

    while(
    $row mysql_fetch_array($outcome)){
        
    extract($row);
        echo 
    "id: ".$id.",<br><hr> ".$Date_Required_From.",<br><hr> ".$Date_Required_To.",<br><hr> ".$Firstname.",<br><hr> ".$Surname.",<br><hr> ".$Number_of_People.",<br><hr> ".$Names_of_People.",<br><hr> ".$Address_Line_1.",<br><hr> ".$Address_Line_2.",<br><hr> ".$Town_City.",<br><hr> ".$County.",<br><hr> ".$Postcode.",<br><hr> ".$Country.",<br><hr> ".$Phone_Number.",<br><hr> ".$Mobile_Number.",<br><hr> ".$Email_Address.",<br><hr>";
    }
    ?>

  • #14
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You're closing the connection before your calls to mysql_result(). You need that connection for all mysql-type functions.

    Have you looked into using mysql_fetch_array()? It would cut down on excessive coding, saving you typing time if nothing else.

    Also you should use mysql_num_rows(), not mysql_numrows(), although I think PHP is forgiving enough to recognize the misspelling.

    And you can use a for loop which handles your $i variable for you-- no need to initialize and increment manually.

    And as if I haven't nagged enough(!), one more nag-- ALWAYS check your query to see if it succeeded or failed, and provide some action if it fails. I realize this is a very simple query, but having a standard error catching routine is just good practice.

    PHP Code:
    $query "SELECT * FROM clientdetails";
    $outcome mysql_query($query);

    //check for a successful query
    if (!$outcome) {
        die(
    "Query Error!<br />Query text: $query<br />Error text: ".mysql_error());
    }

    for (
    $i 0$i mysql_num_rows(); $i++) {
        
    $clientData[$i] = mysql_fetch_array();
        foreach (
    $clientData[$i] as $fieldKey => $fieldVal) {
            print 
    "$fieldKey: $fieldVal,<br><hr>\n";
        }

    EDIT: I'm too slow... Nightfire beat me to it and now you have two variations to try
    SECOND EDIT: You can't close the connection before using mysql_fetch_array() though.
    Last edited by Fumigator; 03-17-2007 at 11:39 PM.

  • #15
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    Well spotted with the mysql_close, rushed through it and missed that out


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •