...

View Full Version : Unable to execute create database query.



hornsby100
03-13-2007, 11:40 AM
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

timgolding
03-13-2007, 01:45 PM
Are you connecting to the database first?
Does the database user you are connecting with have sufficient privallages to create tables?

Fumigator
03-13-2007, 10:38 PM
And to add more questions, where is the mysql_query() call, and are you checking to see that it succeeded?

ItsMe
03-14-2007, 07:50 PM
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.

Fumigator
03-14-2007, 08:18 PM
Make your error message more informative:



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

guelphdad
03-14-2007, 09:11 PM
The problem is most likely the single quotes around the table name and column names. they are unnecessary.

ItsMe
03-15-2007, 11:19 AM
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

hessodreamy
03-15-2007, 06:18 PM
remove the quotes around the column name in the primary key specification.
You are referring to a column, not a string
PRIMARY KEY(id),
UNIQUE id_2(id)

hornsby100
03-16-2007, 11:15 AM
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

hessodreamy
03-16-2007, 12:22 PM
remove the comma after your UNIQUE id_2(id)

hornsby100
03-18-2007, 12:04 AM
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

ole90
03-18-2007, 12:24 AM
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
}

Nightfire
03-18-2007, 12:27 AM
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>";
}
?>

Fumigator
03-18-2007, 12:36 AM
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.

Nightfire
03-18-2007, 12:51 AM
Well spotted with the mysql_close, rushed through it and missed that out



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum