mySQL syntax error

[Vagabond]

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:

// 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?

[yell0wdart]

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.

[Vagabond]

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

[yell0wdart]

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.

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

[Vagabond]

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]

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]

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

[yell0wdart]

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.

[vks87]

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




Web site design / Entertainment forum / Web design

[Vagabond]

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.