php and mysql data insert problem

[Eagle.Avik]

hi i am building a cms system for my website, i am able to put data into a mysql database with the following code(look below).
i also have a login system for accessing the cms, everything worked so far. I am facing a problem now.
Ok so user signup info is stored in a differant database, and i am using another database to store the cms content

--blog
|-author
|-post

so my blog databse contains two tables. on the post section i store the content of a post.
the author section contains author name and author email columns.
whenever someone trying to post new content the following code will check for the username if it is already exists or not if not then it will insert the username and useremail. here lies the problem i can check whether the username already exist or not, but if not i am not able to insert new username and email.

my php code-

<?php
$mysql_server = 'localhost';
$mysql_username = 'Avik@localhost';
$mysql_password = '';
$mysql_database = 'blog';
$mysql_table = 'post';
$mysql_usertable = 'author';
$success_page = 'postsuccess.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'save')
{
$db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
if (!$db)
{
die('Failed to connect to database server!<br>'.mysql_error());
}
mysql_select_db($mysql_database, $db) or die('failed to connect to database<br>'.mysql_error());
$post_username = $_POST['username'];
$post_useremail = $_POST['useremail'];
$sql = "SELECT authorname FROM ".$mysql_usertable." WHERE authorname = '".$post_username."'";
$result = mysql_query($sql, $db);
      if (!$data = mysql_fetch_array($result))
      {
      $sql = "INSERT `".$mysql_usertable."` (`id`, `authorname`, `authoremail`) VALUES (NULL, '$post_username', '$post_useremail')";
      }

$post_title = $_POST['title'];
$post_topic = $_POST['topic'];
$post_tags = $_POST['tags'];
$post_content = $_POST['elm1'];

$sql = "INSERT `".$mysql_table."` (`title`, `topicid`, `tags`, `postdate`, `content`) VALUES ('$post_title', '$post_topic', '$post_tags', CURDATE(), '$post_content')";
      $result = mysql_query($sql, $db);
      mysql_close($db);
      header('Location: '.$success_page);
}

?>

am i doing something wrong? please help.

[broj1]

Test the sql staement by inserting this temporary debug code right after line 19:

die($sql);

This will display the insert query and stop the script. Now you can inspect the query or test it in phpmyadmin. You can also post it here.

Also use error checking:

$result = mysql_query($sql, $db) or die('ERROR!'); 

[broj1]

Now, another thing which is being repeated quite often: do clean the input that users enter in your form to avoid nasty things like sql injection. At least escape:

$post_username = mysql_real_escape_string($_POST['username']);
$post_useremail = mysql_real_escape_string($_POST['useremail']);

Even better, check if values contain expected data, blacklist characters, check for lenghts etc. And switch to pdo or mysqli, drop the mysql db extension.

[Eagle.Avik]

hi i have updated the the code with mysqli. but i think i messed up with mysqli. it does not give me any error but it does not save data to database

<?php
$mysqli_server = 'localhost';
$mysqli_username = 'Avik@localhost';
$mysqli_password = '';
$mysqli_database = 'blog';
$mysqli_table = 'post';
$mysqli_usertable = 'author';
$success_page = 'postsuccess.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'save')
{
$link = mysqli_connect($mysqli_server, $mysqli_username, $mysqli_password);
if (!$link)
{
die('Failed to connect to database server!');
}
$link->select_db($mysqli_database) or die('failed to connect to database');

$post_username = mysqli_real_escape_string($_POST['username']);
$post_useremail = mysqli_real_escape_string($_POST['useremail']);

$sql = "SELECT * FROM ".$mysqli_usertable." WHERE authorname = '".$post_username."' AND  authoremail = '".$post_useremail."'";
$result = mysqli_query($link, $sql);
      if (!$data = mysqli_fetch_array($result))
      {
      $sql = "INSERT `".$mysqli_usertable."` (`id`, `authorname`, `authoremail`) VALUES (NULL, '$post_username', '$post_useremail')";
      $result = mysqli_query($link, $sql);
      }


$post_title = $_POST['title'];
$post_topic = $_POST['topic'];
$post_tags = $_POST['tags'];
$post_content = $_POST['elm1'];

$sql = "INSERT `".$mysqli_table."` (`title`, `topicid`, `tags`, `postdate`, `content`) VALUES ('$post_title', '$post_topic', '$post_tags', CURDATE(), '$post_content')";
      $result = mysqli_query($sql, $link);
      mysqli_close($link);
      header('Location: '.$success_page);
}

?>

[broj1]

Sory, I didn't mean to push you into mysqli so quickly, since it was not the reason for your particular trouble. It is only a good idea to switch since mysql is going to be abandoned soon. But back to the insert problem.

Have you tried the die() solution form my first post? Please post the output here.

[Eagle.Avik]

yup i solved that problem, i forget to place

$result = mysql_query($sql, $db);

this code after the sql query. :) thnx. but seriously why mysqli and not mysql, i mean is there any risks involved in mysql than mysqli

[haolongvt]

mysql extra Scheduled PHP 4.1.3 return Down but now be used for the entire PHP Ver higher than 4.1.3
MySQLi: MySQL Improved Extension : as a added extensions for PHP 4.1 return up

[broj1]

There are at least 2 reasons:

1. The mysql extension is deprecated in the current version of PHP and will be removed in future (see http://si1.php.net/manual/en/intro.mysql.php). For now using it causes warnings (I think), in future it will fire errors.

2. Newer extensions support more mysql functionalities that add to security, such as prepared statements which greatly prevent sql injection attacks.

But, in my opinion it is also worth looking at PDO. It has a advantage over mysqli in that it is an abstraction layer, that is, it supports several databases. This might make your life easier if you swap database in a future.