Data not making it into mysql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Data not making it into mysql database

Post by iankent »

Probably no need to post the info if you've got mysql working elsewhere. I'm really struggling to understand why it would work fine on one page and not on another lol... The only bits that would have been sensitive would be the default username/password bits (though often they're not set anyway).

Could we do one last test with this code in a file on its own :) if mysql is definately working elsewhere then this should almost certainly return something:

Code: Select all

<?php
mysql_connect('yourhost','youruser','yourpass');
mysql_select_db('yourdb');
$r = mysql_query('SELECT NOW();');
$l = mysql_fetch_assoc($r);
print_r($l);
mysql_close();
?>
Just tested the code on my dev server and it works so there's definately not a problem with the code - that'll at least rule out a code problem!

edit: actually, you could drop the mysql_select_db() line as its not required for that query
tinyang
Forum Newbie
Posts: 13
Joined: Mon Nov 23, 2009 3:35 pm

Re: Data not making it into mysql database

Post by tinyang »

Array ( [NOW()] => 2009-11-27 11:10:39 )

here (above) is the output from your script! But strangely enough, no output from the rating.php script. Here it is in it's current state:

Code: Select all

<?php
error_reporting(-1);
session_start();
 
$rating = $_POST['rating'];
$pic_id = $_POST['pic_id'];
 
//Connect and select database
// We'll do the mysql_error() bit on these two as well, the problem could be here
mysql_connect("192.168.000.000", "username", "password");
if(mysql_error()) {
    echo "mysql_connect error: ".mysql_error();
}
mysql_select_db("tutorial");
if(mysql_error()) {
    echo "mysql_select_db error: ".mysql_error();
}
 
if(!is_numeric($rating) || !is_numeric($pic_id)) {
    // either make them a number, or display an error and exit
    echo "Either rating or pic_id isn't a valid number";
    exit;
}
// Even though we know they're definately numbers, there's no harm in escaping them to be on the safe side!
$rating = mysql_real_escape_string($rating);
$pic_id = mysql_real_escape_string(pic_id);
 
// Personally, I'd escape $_SESSION['username'] too. Even though it shouldn't be able to be changed by the end-user, you can't guarantee it hasn't been, so best to play it safe
$username = mysql_real_escape_string($_SESSION['username']);
 
// Just noticed why your query might not be working... you've removed the wrong quote from around your numbers, instead of taking away the doubles you should have taken away the singles. Actually, in this case, you can take both of them away - because the whole query is inside double quotes, the variables can be parsed inline like this
$query="INSERT INTO comments (pic_id, user, rating) VALUES ($pic_id, '$username', $rating);";
 
mysql_query($query);
echo "testing";
 
if(mysql_error()) {
    echo "mysql_query error: ".mysql_error();
}
?>
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Data not making it into mysql database

Post by iankent »

Hmm.

Line 26 - should be $pic_id not pic_id
Line 30 - silly question I know, but is $_SESSION['username'] definately set?

Just tried it on my dev server using the following (slightly modified) code and it works, other than returning a mysql error that the table doesn't exist (as expected):

Code: Select all

<?php
error_reporting(-1);
session_start();
 
$rating = 1; // $_POST['rating'];
$pic_id = 2; // $_POST['pic_id'];
 
//Connect and select database
// We'll do the mysql_error() bit on these two as well, the problem could be here
mysql_connect("localhost", "root", "");
if(mysql_error()) {
    echo "mysql_connect error: ".mysql_error();
}
mysql_select_db("dev");
if(mysql_error()) {
    echo "mysql_select_db error: ".mysql_error();
}
 
if(!is_numeric($rating) || !is_numeric($pic_id)) {
    // either make them a number, or display an error and exit
    echo "Either rating or pic_id isn't a valid number";
    exit;
}
// Even though we know they're definately numbers, there's no harm in escaping them to be on the safe side!
$rating = mysql_real_escape_string($rating);
$pic_id = mysql_real_escape_string($pic_id);
 
// Personally, I'd escape $_SESSION['username'] too. Even though it shouldn't be able to be changed by the end-user, you can't guarantee it hasn't been, so best to play it safe
$_SESSION['username'] = 'test';
$username = mysql_real_escape_string($_SESSION['username']);
 
// Just noticed why your query might not be working... you've removed the wrong quote from around your numbers, instead of taking away the doubles you should have taken away the singles. Actually, in this case, you can take both of them away - because the whole query is inside double quotes, the variables can be parsed inline like this
$query="INSERT INTO comments (pic_id, user, rating) VALUES ($pic_id, '$username', $rating);";
 
mysql_query($query);
echo "testing";
 
if(mysql_error()) {
    echo "mysql_query error: ".mysql_error();
}
?>
outputs this:

Code: Select all

testingmysql_query error: Table 'dev.comments' doesn't exist
edit: also, my code definately returns errors, so your errors must be being diverted elsewhere for some unknown reason! if the line 26 change doesn't work and $_SESSION['username'] is definately set, it could be worth adding a custom error handler to trap any errors before Apache can redirect them? Something like this:

Code: Select all

 
function test_error_handler($errno, $errstr, $errfile, $errline, $errcontext) {
    print_r($errno); print_r($errstr); print_r($errfile); print_r($errline); print_r($errcontext);
    return true;
}
set_error_handler('test_error_handler');
 
tinyang
Forum Newbie
Posts: 13
Joined: Mon Nov 23, 2009 3:35 pm

Re: Data not making it into mysql database

Post by tinyang »

Whoo-hoo! I did get something to echo back out to the webpage this time!!! :D Thank you thank you thank you!

Here is my current rating.php:

Code: Select all

<?php
error_reporting(-1);
session_start();
 
$rating = 1; // $_POST['rating'];
$pic_id = 2; // $_POST['pic_id'];
 
//Connect and select database
// We'll do the mysql_error() bit on these two as well, the problem could be here
mysql_connect("localhost", "username", "password");
if(mysql_error()) {
    echo "mysql_connect error: ".mysql_error();
}
mysql_select_db("tutorial");
if(mysql_error()) {
    echo "mysql_select_db error: ".mysql_error();
}
 
if(!is_numeric($rating) || !is_numeric($pic_id)) {
    // either make them a number, or display an error and exit
    echo "Either rating or pic_id isn't a valid number";
    exit;
}
// Even though we know they're definately numbers, there's no harm in escaping them to be on the safe side!
$rating = mysql_real_escape_string($rating);
$pic_id = mysql_real_escape_string($pic_id);
 
// Personally, I'd escape $_SESSION['username'] too. Even though it shouldn't be able to be changed by the end-user, you can't guarantee it hasn't been, so best to play it safe
$_SESSION['username'] = 'test';
$username = mysql_real_escape_string($_SESSION['username']);
 
// Just noticed why your query might not be working... you've removed the wrong quote from around your numbers, instead of taking away the doubles you should have taken away the singles. Actually, in this case, you can take both of them away - because the whole query is inside double quotes, the variables can be parsed inline like this
$query="INSERT INTO comments (pic_id, user, rating) VALUES ($pic_id, '$username', $rating);";
 
mysql_query($query);
echo "testing";
 
if(mysql_error()) {
    echo "mysql_query error: ".mysql_error();
}
 
 function test_error_handler($errno, $errstr, $errfile, $errline, $errcontext) {
     print_r($errno); print_r($errstr); print_r($errfile); print_r($errline); print_r($errcontext);
     return true;
 }
 set_error_handler('test_error_handler');
 
?>
As you can see, it is your most recent posting of the rating.php script plus the error handling at the end. The error handling is what did it, I ran just your most recent posting of rating.php script and still no output. But with the error handling in there, now it outputs this:

testing

$_SESSION['username'] is definately set. So what is the next step?
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Data not making it into mysql database

Post by iankent »

Wooo we're getting somewhere :D

Try moving the set_error_handler() to the very top of the page (doesn't matter where the actual function declaration goes). Hopefully with set_error_handler() at the top it'll output some useful errors that'll tell you where its going wrong.
tinyang
Forum Newbie
Posts: 13
Joined: Mon Nov 23, 2009 3:35 pm

Re: Data not making it into mysql database

Post by tinyang »

No luck. I still just get "testing" echoed out. But I did add:

Code: Select all

if (!(isset($_SESSION['login']) && $_SESSION['login'] != '')) {
header ("Location: login.php");
}
to the top of the script and commented out the

Code: Select all

$_SESSION['username'] = 'test';
and also added

Code: Select all

echo $username;
and it is echoing out the correct username, so I now that $_SESSION['username'] is getting set correctly.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Data not making it into mysql database

Post by iankent »

I have no idea lol, unless you can find some kind of error logs containing PHPs errors I'm not sure what to suggest! Check your apache config and php.ini and look for anything mentioning error logs, and check all of them to search for any PHP warnings etc.

Failing that, you could remove the error handler, leave the 'echo "testing";' at the end, and comment out every line. uncomment them one by one until you no longer get testing output and you know which line is causing the problem.

Just for the sake of 'completeness', it might be worth echoing out the query to copy and paste it into mysql query browser or phpmyadmin, just to make sure the query is being created properly (there's no reason it shouldn't be, but no harm in making sure!)
tinyang
Forum Newbie
Posts: 13
Joined: Mon Nov 23, 2009 3:35 pm

Re: Data not making it into mysql database

Post by tinyang »

OK, so I checked my php.ini file, and error_reporting is turned off/commented out. log_errors is set to on, and error_log = php.log which doesn't exist on the web server because error_reporting is off (I think). So I read the php manual, and it is still not clear to me , what setting do I give to error_reporting so it is logging only bugs in my code?

I should add that my php version is 5.1.6
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Data not making it into mysql database

Post by iankent »

Have a look at this quick guide. It's a few years old now but the same principle still applies. As far as I know, having error reporting off shouldn't matter to log_errors, they should still be logged!
http://www.addedbytes.com/php/php-ini-g ... d-logging/

If you cant find the php.log file, try this on linux:

Code: Select all

find / -n php.log
and if that doesn't help, try having a look in your Apache error logs instead, usually /var/logs but you can check in your Apache config files to be sure.

If error_reporting is set to off and log_errors is set to on, unless there's a permission problem preventing Apache/PHP from writing to the log it almost certainly is logging the errors somewhere
tinyang
Forum Newbie
Posts: 13
Joined: Mon Nov 23, 2009 3:35 pm

Re: Data not making it into mysql database

Post by tinyang »

Here is a question. I've been reading the info on logs, and I've been logging into and out of my website, clicking on all kinds of things to use the php code on it, and it is not generating any php log errors where I would expect it to. How do I create this log file and make sure that the webserver group has access to write to it?
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Data not making it into mysql database

Post by iankent »

tinyang wrote:Here is a question. I've been reading the info on logs, and I've been logging into and out of my website, clicking on all kinds of things to use the php code on it, and it is not generating any php log errors where I would expect it to. How do I create this log file and make sure that the webserver group has access to write to it?
have you checked the apache error logs too? the errors could be going there instead.

if they're not, find out what permissions apache and php are running with (usually apache:apache) and check your logs directory can be written to by that group.

is this a server you've configured yourself? and is it a live server or development server?
Post Reply