Page 1 of 1

SQL Syntax error

Posted: Sat Nov 13, 2004 2:39 pm
by davidjwest
Before you ask yes I have RTFM, in fact I read two separate manuals and asked on another forum and nobody seems to be able to come up with an answer for my problem.

I am new to PHP/SQL so it may be something obvious, apologies if it is!

Here's the code, I've commented out various bits as I use the code to do all db stuff, connect, creat tables, insert and retrieve and I don't want to create tables etc over and over.

Also I've found 2 different syntaxes for some stuff, one from the "manual" and one from my hosts website.

Code: Select all

<?php
/*database operations*/
/*connect to databse*/
/*$dbh = DBI->connect("DBI:mysql:qhwslos_pitstop:localhost","qhwslos_pitstop","password");*/
/*error_reporting(E_ALL);*/
/*$dbh=mysql_connect ("localhost", "qhwslos_pitstop", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("qhwslos_pitstop");*/
mysql_connect ("localhost", "qhwslos_pitstop", "password") or die (mysql_error());
@mysql_select_db ("qhwslos_pitstop") or die (mysql_error());
/*create drivers table*/
/*$query="CREATE TABLE test (id int(6) NOT NULL auto_increment,driver_fname varchar(15) NOT NULL,driver_sname varchar(15) NOT NULL,age int(2) NOT NULL,nation varchar(20) NOT NULL,stamina int(2) NOT NULL,ability int(2) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);*/

/*insert driver*/
$query = "INSERT INTO drivers VALUES ('','John','Smith','21','Cypriot','99','99',)";
mysql_query($query) or die (mysql_error());
mysql_close();
/*get data from database*/
/*$query = "SELECT * FROM drivers";

mysql_query($query);
$result = mysql_query($query) or die (mysql_error());

/*$num = mysql_numrows($result);*/
/*mysql_close();/*

echo $result;

/*$i=1;
while ($i < 3){
$id = mysql($result,$i,"id");
$driver_fname = mysql($result,$i,"driver_fname");
$driver_sname = mysql($result,$i,"driver_sname");
$age = mysql($result,$i,"age");
$nation = mysql($result,$i,"nation");
$stamina = mysql($result,$i,"stamina");
$ability = mysql($result, $i,"ability");
echo"Driver ID: $id | $driver_fname $driver_sname | Age: $age<br>";
echo"Nationality: $nation | Ability: $ability | Stamina: $stamina";
$i++;
}*/

?>
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 ')' at line 1
Considering the code I'm using is out of a manual I've no idea what's wrong. It does seem to connect to the database and creat tables, I can see two tables showing in MyPHPAdmin. Any ideas?

I'm beginning to think this might be a host issue?

I do have 5 databases with my host package and I'm only using two of them (plus this one) and they work fine, PHPbb forums actually!

I set up the databse and permissions through PHPMyAdmin, one odd thing is I couldn't create tables as I get a Page Cannot be Displayed error if I try to do that.

Thanks for looking!

Posted: Sat Nov 13, 2004 3:06 pm
by magicrobotmonkey
the data base you create is named test, the data base you insert into and select from is named driver. Also, /* */ comments can be used across multiple lines! and to comment out a single line, or even part of one, just use // and it will comment from there through the end of the line

Posted: Sat Nov 13, 2004 3:38 pm
by davidjwest
Thanks for your input but that isn't the problem, I should have explained better or posted the right code!

I added the "test" table to see if it was working and according to MyPHPAdmin it did create the table. Even when I change that to "drivers" it's the same error.

I've commented out various lines as I've been tinkering around so much, I'll post a better example of code when I get a chance.

Re: SQL Syntax error

Posted: Sat Nov 13, 2004 6:02 pm
by timvw

Code: Select all

$query = "INSERT INTO drivers VALUES ('','John','Smith','21','Cypriot','99','99',)";
Either add a last value after the last , or get rid of it.

INSERT INTO drivers VALUES ('','John','Smith','21','Cypriot','99','99', 0)
INSERT INTO drivers VALUES ('','John','Smith','21','Cypriot','99','99')

Posted: Sun Nov 14, 2004 4:28 pm
by davidjwest
Thanks very much timvw, that certainly is a bug on my part, well spotted cos I didn't!

It's still not working 100% but that's because I messed around with the code so much trying to find another error which wasn't there that I think some of my data got entered incorrectly so I can't retrive it - some data is coming back so I think that's the problem.

I'm going to wipe all the data and start over, there's only a load of test data so no biggie, if I get any more problems I'll post again.

Many thanks!

Posted: Mon Nov 15, 2004 3:26 pm
by davidjwest
I got it working, thanks a lot.

As well as the bug you spottted, I'd entere " instead of ''

double quotes instead of two single quotes.

I also type mysql rather than mysql_result on lines 31 to 37.

Whoops!

Hopefully someone else will learn from my mistakes!