SQL debugging in large PHP app

Question

I am using CodeCharge Studio to finish a large PHP application. This question isn't really CCS related, but a bit more general. I have a web form that is supposed to allow CRUD capabilities with a certain SQL Server table, but the Inserts keep failing without throwing any errors. What would be the best way to debug this?

Answer 1

See what the actually query is and then try doing that query directly on the DB. Then you know whether it's a PHP issue or a DB issue.

Then go from there, depending on the outcome.

Answer 2

When I'm having trouble with dynamically generated SQL queries, I typically echo out the query and try running that query on the console for the DB. Or alternatively, you could write a simple PHP function that writes out strings to a file, and that way you don't have to display the query directly on your page, but instead in a log file.

Answer 3

You can monitor all sql queries in mysql as shown in this site, once you enable logging, run the query manually and see why its failing..this should be good starting point.

Answer 4

If the query looks OK, double check that the user running the query has insert rights to the database.

I've been caught out by that before.

Answer 5

You should try using FirePHP and log all the SQL to your Firebug:

An Example would be:


$sql = "SELECT * FROM table"
if (!mysql_query($sql)) {
// In un successfull log to FireBug
FB::error($data, "SQL error: ".mysql_error());
}

You can also implement the FB::error call from your own function, so you can later deactivate this behaviour modifying your function:


function log_error($data, $msg){
//dont forget to deactivate in case the application goes live!
FB::error($data, $msg);
}
if (!mysql_query($sql)) {
// In un successfull log to FireBug
log_error($data, "SQL error: ".mysql_error());
}

Answer 6

In addition to what's mentioned before, I can add my recent discovery:

trigger_error(print_r($your_var,1),E_USER_ERROR);

So you can output and debug your variable, even if it's a complex script with redirects, where simple echo would not help.

Dmitri.