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 -


$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

Are you connecting to the database first?
Does the database user you are connecting with have sufficient privallages to create tables?

And to add more questions, where is the mysql_query() call, and are you checking to see that it succeeded?

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:
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.

Make your error message more informative:


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

The problem is most likely the single quotes around the table name and column names. they are unnecessary.

Hi, we have tried the suggestions.

Enhancing the error message gave us the following:
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

remove the quotes around the column name in the primary key specification.
You are referring to a column, not a stringPRIMARY KEY(id),
UNIQUE id_2(id)

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

remove the comma after your UNIQUE id_2(id)

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
$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

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
}

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
$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>";
}
?>

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() (http://us3.php.net/manual/en/function.mysql-fetch-array.php)? 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.


$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 :p
SECOND EDIT: You can't close the connection before using mysql_fetch_array() though.

Well spotted with the mysql_close, rushed through it and missed that out