Home Tutorials Forums Articles Blogs Movies Library Employment Press
Old 03-24-2009, 12:26 AM   #1
Vagabond
Senile Member
 
Join Date: Feb 2008
Location: Washington State, USA
Posts: 169
Send a message via Yahoo to Vagabond
Default mySQL syntax error

I've never had these problems till my client started using a "discount" web host that actually costs more than most of the quality hosts, and for less features.

Anyway, here's what I send:

Code:
// assemble variables to send
$email = $_GET['email'];
$name = $_GET['name'];
// for temp process, dob is irrelevant
$location = $_GET['location'];
$ip = "myIP";
$pass = "myPass";

// register new account
echo "Registering Account... <br>";

// open connection to database
include "dbc.php";

// set up query for registration
$reg_query = "ENTER INTO players (email, name, ip, loc, dob, dor, pass) VALUES ('$email', '$name', '$ip', '$location', NOW(), NOW(), PASSWORD('$pass'))";

// run registration query
$reg_result = @mysql_query ($reg_query, $dbc);

// if reg_result is FALSE...
if ($reg_result == FALSE) {
	
	echo "<br> Error Unable to register account! " . mysql_error($dbc);
	
	// close sql connection
	mysql_close($dbc);
	
// else, if result is NOT FLASE...
} else {
	
	echo "<br> Registration complete...";
	
	// close sql connection
	mysql_close($dbc);
	
} // end if ($reg_result == FALSE)
and here's what it gives me back: "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 'ENTER INTO players (email, name, ip, loc, dob, dor, pass) VALUES ('2', '1', 'myI' at line 1"

any idea what I'm doing wrong here?
Vagabond is offline   Reply With Quote
Old 03-24-2009, 06:02 AM   #2
yell0wdart
jordanrift.com
 
Join Date: Sep 2007
Location: Phoenix, AZ
Posts: 366
Default

You sure you're not looking for the INSERT statement? ENTER is not a valid SQL statement. That'd be your most glaring syntax issue, right there.

Beyond that, I'd make sure you're inserting data that's able to be implicitly typed to SQL column types, etc. Don't have my dev environment fired up ATM to check your SQL out, but after you fix the ENTER problem, if you're still getting errors, I'd start with column types and mapping data.
__________________

bad developer

Jordan Rift
yell0wdart is offline   Reply With Quote
Old 03-24-2009, 04:57 PM   #3
Vagabond
Senile Member
 
Join Date: Feb 2008
Location: Washington State, USA
Posts: 169
Send a message via Yahoo to Vagabond
Default

Well I feel utterly ridiculous now -_-

It worked, but how about this one? It's a function I'm using to check if data already exists on a database before I enter it

PHP Code:
// validateData
// Input: db field name as $field and data being checked as $data
// Output: (echoes) 0 (data valid), 1 (data invalid)
function validateData($field$data){
    
    echo 
'validating data: "' $data '" as "' $field '" <br>';
    
    
// escape input
    
mysql_real_escape_string($data);
    
    
// open connection to database
    
include "dbc.php";
    
    
// set up query for $input
    
$query "SELECT '$field' FROM players WHERE '$field'='$data' LIMIT 1";
    
    echo 
$query;
    
    
// run query for $input
    
$result = @mysql_query ($query$dbc) or die(mysql_error($dbc));
    
$row mysql_fetch_array($result);
    
    echo 
"<br>" $result;
    
    echo 
"<br>Results = " $row[0];
        
    
// if result is FALSE...
    
if ($row[0] == NULL) {
        
        echo 
"<br> No Data Found! Returning 0 <br>";
        
        
// free results
        
mysql_free_result ($result);
        
// close sql connection
        
mysql_close($dbc);
        
        
// name is VALID (no match was found)
        // return 0
        
return(0);
        
    
// else, if $result is TRUE...
    
} else {
        
        echo 
"<br> Data Found! Returning 1 <br>";
        
        echo 
$resultArray[0] + "<br>";
        
        
// free results
        
mysql_free_result ($result);
        
// close sql connection
        
mysql_close($dbc);
        
        
// name is INVALID (match was found)
        // return 1
        
return(1);
        
    } 
// end if (!result)
    
// end validateData($field, $input) 
If the data already exists (usually name or email), then I need this to return 1, but it always returns 0, and when I echo the $result, it gives me "Resource id #5" or "Resource id #8" depending on if it was name or email, respectively. When I echo the fetched array ($row) it prints nothing, just "Results = "

I've even tried $row = mysql_fetch_row($result); and still it echoes nothing...
Vagabond is offline   Reply With Quote
Old 03-24-2009, 05:36 PM   #4
yell0wdart
jordanrift.com
 
Join Date: Sep 2007
Location: Phoenix, AZ
Posts: 366
Default

Why not do all that in your SQL? Do you have access to create stored procedures on your database instead of writing it dynamically in your PHP?

You could just create a stored procedure (or a PHP function to validate inputs and wrap this SQL statement if you don't have access to create stored procs) and do all that on the database.

Code:
-- first check to see if the record exists or not

-- We want to check a record's existance vs the primary key column in
-- your table because it's a unique value. Name and email will not always 
-- be unique once you're dealing with a significant amount of data.

IF NOT EXISTS (
    SELECT * FROM `my_table`
    WHERE `my_pimary_key` = @PrimaryKey
)
BEGIN

-- if it's not there, we insert

    INSERT INTO `my_table`
    (
        `column_1`
        `column_2`
        `column_3`
    )
    VALUES
    (
        @Value1
        @Value2
        @Value3
    )

END

ELSE

BEGIN

-- if it IS there, we update it

    UPDATE `my_table` SET
        `column_1` = @Value1
        `column_2` = @Value2
        `column_3` = @Value3
    WHERE `my_primary_key` = @PrimaryKey

END
__________________

bad developer

Jordan Rift
yell0wdart is offline   Reply With Quote
Old 03-24-2009, 06:06 PM   #5
Vagabond
Senile Member
 
Join Date: Feb 2008
Location: Washington State, USA
Posts: 169
Send a message via Yahoo to Vagabond
Default

that sounds great... but I have no idea how to implement that ><

I'm a game developer, and for the project I'm working on I need to be able to register players and keep their game data. I'm used to working with major web hosts, but the one I'm working with now is really kind of crappy. They have a really poor interface and damn near no tools what so ever.

All I really know if the basic stuff, inserting fields and selecting data. I'm buried in game design stuff, but my client wants to start registering users (even though we don't have anything for them to DO yet). I've done simple registration and log in script before, but for some reason they're not working here and it's driving me nuts...

edit: looks like I have admin privileges, I just don't know how to do any of that *sighs* I'm gonna need to find someone who does, this is a bit beyond my scope

I think the major problem is I don't know enough sql to do this without aid, but the tools I'm given require me to actually know what I'm doing >< what I got is like one step above just a command prompt
Vagabond is offline   Reply With Quote
Old 03-24-2009, 06:57 PM   #6
Vagabond
Senile Member
 
Join Date: Feb 2008
Location: Washington State, USA
Posts: 169
Send a message via Yahoo to Vagabond
Default

Holy crap I got it work ><

I decided to to directly to the server and run my query on the command prompt and it returned an error. Eventually I worked the query out and now this works:

PHP Code:
// open connection to database
include "dbc.php";

// set up query for $input
$query 'SELECT `'.$field.'` FROM `players` WHERE `'.$field.'`="'.$data.'" LIMIT 1';

// run query for $input
$result = @mysql_query ($query$dbc) or die(mysql_error($dbc));
    
// if result is FALSE...
if ($result == false) {
    
    echo 
"<br> No Data Found! Returning 0 <br>";
    
    
// free results
    
mysql_free_result ($result);
    
// close sql connection
    
mysql_close($dbc);
    
    
// name is VALID (no match was found)
    // return 0
    
return(0);
    
// else, if $result is TRUE...
} else {
    
    echo 
"<br> Data Found! Returning 1 <br>";
    
    
// free results
    
mysql_free_result ($result);
    
// close sql connection
    
mysql_close($dbc);
    
    
// name is INVALID (match was found)
    // return 1
    
return(1);
    
// end if (!result) 
rather than play around with replacing the variables within the quotations, I just went ahead and broke the string up to insert the variables, making sure to use quotes around the final variable in the where clause. Every other method seemed to give me the error "Failed to execute SQL : SQL SELECT name FROM players WHERE name=vagabond LIMIT 1 failed : Unknown column 'vagabond' in 'where clause'"

This works, that's good enough for alpha stage. I'll bring in someone who actually knows what they're doing to relieve some of my load later on. Thanks so very much for the help, yell0wdart, I appreciate the time you gave me.
Vagabond is offline   Reply With Quote
Old 03-24-2009, 07:13 PM   #7
Vagabond
Senile Member
 
Join Date: Feb 2008
Location: Washington State, USA
Posts: 169
Send a message via Yahoo to Vagabond
Default

Strike that T_T it lied to me. instead of telling me there is no data when there is, it's telling me there is data even when there isn't...

edit; strike -that-... I'm just working too fast and not paying attention, it works now that I fixed all the variable names
Vagabond is offline   Reply With Quote
Old 03-24-2009, 08:52 PM   #8
yell0wdart
jordanrift.com
 
Join Date: Sep 2007
Location: Phoenix, AZ
Posts: 366
Default

Glad you got it working. If you do get a developer in there who knows his/her SQL, I'd recommend getting as much of that sort of thing out of your PHP as you can. This is exactly what databases are engineered to do, and will generally do it faster than your server-side code can.
__________________

bad developer

Jordan Rift
yell0wdart is offline   Reply With Quote
Old 03-27-2009, 10:17 AM   #9
vks87
Registered User
 
Join Date: Mar 2009
Posts: 8
Default

I can't solve it because i can't understand this code..




Web site design / Entertainment forum / Web design
vks87 is offline   Reply With Quote
Old 03-27-2009, 05:11 PM   #10
Vagabond
Senile Member
 
Join Date: Feb 2008
Location: Washington State, USA
Posts: 169
Send a message via Yahoo to Vagabond
Default To summarize

1) ENTER is not a valid SQL query, what I needed was INSERT

2) I ended up restructuring my SELECT query string, breaking it to supply my variables in dot syntax, rather than relying on PHP to replace them at run time. (see below)

PHP Code:
// set up mySQL query
// (SELECT `$field` FROM `players` WHERE `$field` = "$data" LIMIT 1)
$query 'SELECT `' $field '` FROM `players` WHERE `' $field  .'` = "' $data '" LIMIT 1'
3) I had a few other errors in my php and actionscript codes. As I ran through different tries, I had changed a few variable names and I had failed to change them all back. Once I fixed them all, it ran perfectly.

edit: In the future, vks87, please don't post unless you can give something -to- the thread, or you need something -from- the thread. I'm trying to be as polite as I can, but posting essentially "I don't know" doesn't help anyone, and if you're asking me to clarify something, I can't tell what it is because you didn't say.
Vagabond is offline   Reply With Quote
Reply


Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
1084: Syntax error Petros34 ActionScript 3.0 6 05-15-2011 01:54 PM
[AS3] 1084: Syntax error: expecting rightbrace before leftbrace [email protected] Gaming and Game Development 12 04-04-2010 12:10 AM
Calculating the pixel size of a text sambamac ActionScript 3.0 8 02-06-2009 01:08 AM
Error 1086: Syntax Error daz4126 ActionScript 3.0 3 01-05-2009 12:13 PM
syntax error help? - preloader ginob ActionScript 2.0 3 04-03-2008 09:02 PM


All times are GMT. The time now is 06:58 PM.

///
Follow actionscriptorg on Twitter

 


Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Ad Management plugin by RedTyger
Copyright 2000-2013 ActionScript.org. All Rights Reserved.
Your use of this site is subject to our Privacy Policy and Terms of Use.