sql syntax problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Meteo
Forum Newbie
Posts: 24
Joined: Sun Jan 18, 2004 10:19 am
Contact:

sql syntax problem

Post by Meteo »

I usually dont have much trouble with sql, but on a friend's linux server, i am working with php and mysql, and all of a sudden my normal syntax isn't working anymore.

Code: Select all

$check_query = "select * from active where ip='$ip' and username='$username'";
$check_result = mysql_query($check_query, $connection) or die(mysql_error());
this returns:
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 '* ip='ip address' and username='guest'' at line

and other sql statements return errors saying the syntax is incorrect and in the error says the check for proper syntax and starts the statement right in the middle of it.

i checked the online manual for hours, and couldn't find much of anything except for sql statements that look much the same way that i write my own.

the server uses mysql 4.0.15. anyone know what could be going on or can shed some light on this?
kevin7
Forum Commoner
Posts: 96
Joined: Fri May 21, 2004 6:54 am

Post by kevin7 »

i don't seen any error in ur sql command...
perhaps u've using some reserve words in the sql as the table name... (i'm not so sure)... try the below, hope its helps...

Code: Select all

$check_query = "select * from `active` where ip='$ip' and username='$username'"; 
$check_result = mysql_query($check_query, $connection) or die(mysql_error());
User avatar
Meteo
Forum Newbie
Posts: 24
Joined: Sun Jan 18, 2004 10:19 am
Contact:

Post by Meteo »

i tried to rename the table to activity to make sure active wasn't some kind of a reserved keyword. it turns out that's not the case, i get the same errors.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

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 '* ip='ip address' and username='guest'' at line
according to that error report, it's saying that you have a * before ip='$ip'

in other words, did you just type up what you thought the query was, or did you actually copy/paste it?
User avatar
Meteo
Forum Newbie
Posts: 24
Joined: Sun Jan 18, 2004 10:19 am
Contact:

Post by Meteo »

i copied and pasted everything, i tried rewriting it many different times, and got pretty much the same error, just maybe a little different based on the words i used.

sorry, i just double checked the error i get now, and it's:

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 '* from active where ip='ip' and username='guest'' at

sorry, i must have copied a different error before uploading the file or something.
kevin7
Forum Commoner
Posts: 96
Joined: Fri May 21, 2004 6:54 am

Post by kevin7 »

how bout this...?

їphp]
$check_query = "select * from `active` where `ip`='$ip' and `username`='$username'";
ї/php]
?>
User avatar
Meteo
Forum Newbie
Posts: 24
Joined: Sun Jan 18, 2004 10:19 am
Contact:

Post by Meteo »

i noticed it'll work if i do it a little differently, but then i have a small problem, i can't get the data into a variable.

i'm used to doing this type of thing...

Code: Select all

$query = 'select * from table';
$result = mysql_query($query);
$row = mysql_fetch_array($result);
here's the only way i can get it to work now...

Code: Select all

$query = 'select column1, column2, column3 from table';
$result = mysql_query($query);
// now i dont know which function to use to grab that data
does anyone know?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Code: Select all

$row = mysql_fetch_array($result);
is rather useless, if not placed within a loop.

Code: Select all

$query = 'select * from table';
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
 print_r($row);
}
...might be more interesting...
User avatar
Meteo
Forum Newbie
Posts: 24
Joined: Sun Jan 18, 2004 10:19 am
Contact:

Post by Meteo »

i only used that as a primary example when selecting strictly one row of data. with that mysql_fetch_array creates a nice array with the data of one row, without the need of a while loop.

i need to know how to get the contents using this kind of sql...

Code: Select all

$result = mysql_query("select column1, column2 from table where id=1");
// i dont know what function to use to get the column1 and column2 data
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

you would simply use the same thing, or fetch_row or any number of fetch's...

$result = mysql_query("select column1, column2 from table where id=1");
$row = mysql_fetch_assoc($result);
echo $row['column1'].'<br />';
echo $row['column2'];
Serberus
Forum Newbie
Posts: 5
Joined: Fri Aug 13, 2004 6:31 pm
Location: Hertfordshire, UK

Post by Serberus »

Presumably ID is unique (primary key, autoincrement, not null?) so there should only be a single row matching ID 1? If so...

$result = mysql_query('select column1, column2 from table where id=1');
list($col1, $col2) = mysql_fetch_row($result);
echo $col1.'<br />'.$col2;
Post Reply