[RESOLVED] Errors with php query to mysql db

[cSean]

Hi All,

I'm really about to lose it on this one. I have a set of scripts that retrieve and send data to a mySQL DB, and they work fine on my server, but on my client's server, they are not functioning, and I've been at it all night for several nights just trying to get ONE single query to work, but I just can't!! I'm hoping someone can see something I can't.

mysql version is 5.0.21 and PHP Version is 5.1.4.

Here's part of the file (the queries):

        $thedate = gmdate("Y-m-d", $timestamp);
    $thetime = gmdate("g:i:s", $timestamp);

    $connection = mysql_connect ("localhost", $username, $userpass) or die ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ($myDB);

    $myQuery = "INSERT INTO clock (`id`, `eid`, `date`, `in`, `out`, `notes`) VALUES ('', '0',\'$thedate\', \'$thetime\', '0', '$_POST[notes]')";
    
    //$myQuery2 = 'INSERT INTO `ngenmedi_employees`.`clock` (`id`, `eid`, `date`, `in`, `out`, `notes`) VALUES (NULL, \'0\', '$thedate', '$thetime', \'00:00:00\', \'\')';

    $success = mysql_query ($myQuery) or die("can't execute query " . mysql_error()); 

myQuery outputs this in php:

can't execute query 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 '\'2007-10-29\', \'5:07:15\', '0', '')' at line 1

myQuery2 outputs this:

Parse error: syntax error, unexpected T_VARIABLE in /home/ngenmedi/public_html/employees/scripts/clockin.php on line 49

Where line 49 is the line myQuery = ....

And I have no idea what's going wrong, and I believe I've tried every single variation of these queries, adding and removing \'s, '', all sorts of things, I just can't figure it out!!! I even once got the script to execute, but instead of my date and time strings defined in php, it just inserted "0000-00-00".

Let me know if you come up with anything please!

-csean

[3PRIMATES]

Errors are commented and fixed in code:

$thedate = gmdate("Y-m-d", $timestamp);
    $thetime = gmdate("g:i:s", $timestamp);
    
// make sure to secure data before using in within a query.
//This will help to prevent a SQL injection
$notes = mysql_real_escape_string($_POST['notes']);

    $connection = mysql_connect ("localhost", $username, $userpass) or die ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ($myDB);

// You were using backticks ` here instead of  single quotes
// thedate and thetime did not need to be escaped, and I used a more desirable format for the variable inclusion
    $myQuery = "INSERT INTO clock ('id', 'eid', 'date', 'in', 'out', 'notes') VALUES ('', '0','".$thedate."', '".$thetime."', '0', '".$notes."')";
    
    $success = mysql_query ($myQuery) or die("can't execute query " . mysql_error()); 

Hope it helps,

3P

[cSean]

Hi 3P,

Thanks a lot for the response! I'm still getting an error I'm afraid:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user: 'root@localhost' (Using password: NO) in /home/ngenmedi/public_html/employees/scripts/clockin.php on line 61

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/ngenmedi/public_html/employees/scripts/clockin.php on line 61

can't execute query 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 ''id', 'eid', 'date', 'in', 'out', 'notes') VALUES ('', '0','200

The last one is the one I'm really worried about. To get rid of the warnings I just changed the line as follows:

//from:
//$notes = mysql_real_escape_string($_POST['notes']);
//to:
$notes = $_POST['notes']; 

I figure I'll worry about one problem at a time and try to fix that real_escape issue after I figure out why the query won't execute.

Any ideas?

[sstalder]

The username and password you are passing needs to be set according to the client's server database username/password. They are not all the same.

[cSean]

Hi sstalder,

The code I've posted isn't the entire file, I'm able to connect without problems to the server and database, and I have the login details stored at the beginning of my script as strings, so I don't think that could be causing a problem.

Is that what you meant?

[3PRIMATES]

Hey there, sorry there was an error in my last code.
Its been awhile and made the same mistake you did, LOL

replace the line below.
You you should not have quotes around the row names.

$myQuery = "INSERT INTO clock (id, eid, date, in, out, notes) VALUES ('', '0','".$thedate."', '".$thetime."', '0', '".$notes."')"; 

3P

[sstalder]

The query should be this:

$myQuery = "INSERT INTO clock (`id`, `eid`, `date`, `in`, `out`, `notes`) VALUES (NULL, 0,'".$thedate."', '".$thetime."', 0, '".$notes."')"; 

Auto-increment fields should return null.
Number fields should insert as numbers, not strings.
Hopefully that helps.

[3PRIMATES]

Hi, in the above I would remove the back ticks

3P

[cSean]

Okay, the first query from 3P still returned errors, but the last one from sstalder did actually work!!! I'm not sure if its because the back ticks are necessary, or if it was because I was attempting to insert strings into an integer type, but the working code is:

$myQuery = "INSERT INTO clock (`id`, `eid`, `date`, `in`, `out`, `notes`) VALUES (NULL, 0,'".$thedate."', '".$thetime."', 0, '".$notes."')"; 

Thank you both very very much!

[sstalder]

"back ticks" are actually recommended in querys with PHP/SQL, other languages that use SQL such as ASP, etc does not matter.

[3PRIMATES]

Odd, I have never seen them used outside of an actual command line query to a database. As in running a query straight through the sql admin console.

[cSean]

I was under the same impression 3P, but this new version must have changed something, it's so frustrating having to keep 3 different versions of these scripts now!

[sstalder]

Well trust me they change daily The best thing to do is just read up on standards and try your best to follow them (that day) because they change so much and are very different depending on the server/os/php version/mysql version, etc as you will run into that in your future projects.

Im glad it works for you and feel free to pm me at anytime with questions.