为什么插入在MYSQL中不起作用？

Question

我正在尝试使用jquery函数编写新记录：

$.post("insertuser.php",$("#rohanStart").serialize(),function(data){ 
    alert(data);
});

这似乎可行，我的确回响了statmenet。但问题是值未写入数据库，查询语句中有问题吗？

mysql_query("INSERT INTO ajax_demo1( FirstName,LastName,Unit,Group,photo)
        VALUES (
            '".$arr['FirstName']."',
            '".$arr['LastName']."',
            '".$arr['Unit']."',
            '".$arr['Group']."',
            '".$arr['photo']."'
        )");

echo $arr['Group'];

Answer 1

First don't use jQuery or any frameworks, they rely on the proprietary Microsoft JScript innerHTML method which does not work correctly with the DOM thus adding huge amounts of ambiguity in scripting.

Secondly you're NOT correctly escaping data going in to the database, that is a serious security issue.

Thirdly your approach to database queries is not taking error handling in to account, you're just dumping queries directly in and hoping for the best.

You should ALWAYS number your queries and enclose them as I have below. Note that besides errors it is good to fail conditions up-front however with database structure you should execute if successful first and THEN fail to increase your indentation (by a single space, not this tab waste where you have five screens to horizontally scroll) so you can visualize where you are in your own code.

$query1 = "SELECT * FROM table_name WHERE something='value'";
$result1 = mysql_query($query1);

if ($result1)
{
 $row1 = mysql_fetch_assoc($result1);
}
else {mysql_error_report($query1,mysql_error(),__FUNCTION__);}

If your main header includes (you DO have a main header being included for all requests except AJAX correct?) you should have a universal MySQL error handling function that you can use to log SQL errors.

The following is the universal database error handler. You should have administrative error logs for HTTP, JavaScript, PHP and SQL errors so you can review and correct issues that your visitors encounter instead of if they only inconvenience you.

function mysql_error_report($q,$e,$f)
{
 if (isset($_SESSION['database']))
 {
  if (isset($_SESSION['id_member'])) {$id = $_SESSION['id_member'];} else {$id = 0;}
  if (isset($_SESSION)) {$session = mysql_real_escape_string(session_id());} else {$session = 0;}
  $ip = mysql_real_escape_string(getenv('REMOTE_ADDR'));

  $query = mysql_real_escape_string($q);
  $error = mysql_real_escape_string($e);
  $function = mysql_real_escape_string($f);
  if (isset($_SESSION['type'])) {$type = mysql_real_escape_string($_SESSION['type']);} else if (isset($_SESSION['cms_browser'])) {$type = 'Browser';} else {$type = 'Unknown';}
  if (isset($_SERVER['REQUEST_URI'])) {$url = $_SERVER['REQUEST_URI'];} else {$url = '';}
  if (isset($_SERVER['HTTP_USER_AGENT'])) {$ua = mysql_real_escape_string($_SERVER['HTTP_USER_AGENT']);} else {$ua = '';}

  $query1 = "INSERT INTO log_errors_sql (id_session, type, id_user, date, ip, function, mysql_error, mysql_query, url, user_agent) VALUES ('$session', '$type', '$id', NOW(), INET_ATON('$ip'), '$function', '$error', '$query', '$url', '$ua')";
  $result1 = mysql_query($query1);

  if (!$result1) {mysql_error_report_mail($q,$e,$f,$ua);}
 }
 else {mysql_error_report_mail($q,$e,$f);}
}

By using that approach you'll strengthen your coding practices to be much stricter. You don't want ambiguity, you want to be a total tightass about your code because the less subjectivity there is in your coding the more your code will be able to handle.

Also your white-space is very loose.

This...

INSERT INTO ajax_demo1( FirstName,LastName,Unit,Group,photo)

Should be formatted like this...

INSERT INTO ajax_demo1(FirstName, LastName, Unit, Group, photo)

You might ask why keeping your white-space like that is important, if you haven't spent a ton of time with find and replace (look up "Advanced Find & Replace", it works on wine/Linux and blows the crap away out of the native Linux console command performance wise and it's dirt cheap, supports regex, etc) you'll find yourself making mass site-wide edits in the blink of an eye because even your white-space is uniformly the same strict approach.

Should you heed my advice use a AFR (Advanced Find and Replace) to search for (but not replace) all instances of "mysql_query" and correct the formatting of everything you've written. Mix in a little AJAX notifications and you can see the errors instantly while you're still in the browser without a single alt-tab. That's how I roll.

...and of course doing this will make your debugging much easier. This isn't a fish, this is fishing and I hope it helps.