php and pdo data inserting problem

[Eagle.Avik]

hi, i am building a cms, when a user(already logged in) post anything, the script will check if the user is already exist or not if not it will create a new account(only save username and useremail) for the cms, so i can keep track of users.
ok, so i already managed to get this part.

at first i used mysql extension but a daniweb member suggest me to use pdo, so i read some documents on pdo and write down the code below, but it is not working as expected, it do not save the data post by user. though it do save userdata if not exist, but for some reason the second query does seem to working.

<?php
$pdo_table = 'post';
$pdo_usertable = 'author';
$success_page = 'postsuccess.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'save')
{
try
{
$pdo = new PDO('mysql:host=localhost;dbname=blog', 'Avik', '');
}
catch (PDOException $e)
{
$output = 'Unable to connect to the database server.';
echo $output;
exit();
}

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

try
{
$sql = "SELECT * FROM ".$pdo_usertable." WHERE authorname = '".$post_username."' AND  authoremail = '".$post_useremail."'";
$result = $pdo->query($sql);
      if (!$data = $result->fetch())
      {
      $sql = "INSERT `".$pdo_usertable."` (`id`, `authorname`, `authoremail`) VALUES (NULL, '$post_username', '$post_useremail')";
      $s = $pdo->prepare($sql);
      $s->execute();
      }
}
catch (PDOException $e)
{
$error = 'error getting and iserting data';
echo $error;
exit();
}

$post_title = $_POST['title'];
$post_topic = $_POST['topic'];
$post_tags = $_POST['tags'];
$post_content = $_POST['elm1'];
try
{
    $sql = "INSERT `".$pdo_table."` (`title`, `topicid`, `tags`, `postdate`, `content`) VALUES ('$post_title', '$post_topic', '$post_tags', CURDATE(), '$post_content')";
    $s = $pdo->prepare($sql);
    $s->execute();
}
catch (PDOException $e)
{
$error = 'error getting and iserting data';
echo $error;
exit();
}
 header('Location: '.$success_page);
}

?>

need help!

[jkon]

Hello. The main reason isn’t working is that in line 25 you make a check with a variable ($data) that is not declared.

Although you use PDO you don’t use prepared statements that is really a bad idea, with those in mind lines 23 through 30 could be (You have and sql syntax error with INSERT) :

    $sql = "SELECT * FROM ".$pdo_usertable." WHERE authorname = ? AND authoremail = ?";
    $statement = $pdo->prepare($sql);
    $statement->execute(array($post_username,$post_useremail));
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    if (count($result) == 0)
    {
      $sql = "INSERT INTO ".$pdo_usertable." (authorname,authoremail) VALUES (?,?)";
      $statement = $pdo->prepare($sql);
      $statement->execute(array($post_username,$post_useremail));
    }

Few notices that have nothing to do with PDO but with MySQL and logic. In INSERT you should define to what table you inserting with the INTO clause. If you want only to check that a “user” exists you could just SELECT COUNT(ID) AS COUNTER and check that the COUNTER is greater than 0.

I see that in the author table you insert and an id with null value. Why isn’t it autoincrement? Also I can’t see where a password is in the author table. Should they been authorized before posting?. And finally in the posts table I can’t see also the author, shouldn’t each post have also the author_id ?

[Eagle.Avik]

thanx for mentioned out those gaps.... actually this is my first time with pdo, mostly i use mysql, i write this code in mysql, but messed up in using pdo.

I see that in the author table you insert and an id with null value. Why isn’t it autoincrement? Also I can’t see where a password is in the author table. Should they been authorized before posting?. And finally in the posts table I can’t see also the author, shouldn’t each post have also the author_id ?

ok, so there is no password in the author table, bcoz user have to be looged in to view the cms page, so the user is already looged in. i want to save user info one more time in the current db, thats why the author table is only for user info, not for authorization.

and for the author_id, yes it should have i am figuring that out. in my real code i have author id, i am facing some problem with that code, thats why i didnt post that here to make things more complicated.

here is the updated code.

<?php
$pdo_table = 'post';
$pdo_usertable = 'author';
$success_page = 'postsuccess.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'save')
{
try
{
$pdo = new PDO('mysql:host=localhost;dbname=blog', 'Avik', '');
}
catch (PDOException $e)
{
$output = 'Unable to connect to the database server.';
echo $output;
exit();
}

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

try
{

    $sql = "SELECT * FROM ".$pdo_usertable." WHERE authorname = '".$post_username."' AND authoremail = '".$post_useremail."'";
    $statement = $pdo->prepare($sql);
    $statement->execute(array($post_username,$post_useremail));
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    if (count($result) == 0)
    {
      $sql = "INSERT INTO ".$pdo_usertable." (authorname, authoremail) VALUES ('$post_username', '$post_useremail')";
      $statement = $pdo->prepare($sql);
      $statement->execute(array('$post_username','$post_useremail'));
    }
}
catch (PDOException $e)
{
$error = 'error getting and iserting data';
echo $error;
exit();
}

$post_title = $_POST['title'];
$post_topic = $_POST['topic'];
$post_tags = $_POST['tags'];
$post_content = $_POST['elm1'];
try
{
    $sql = "INSERT INTO ".$pdo_table." (`title`, `topicid`, `tags`, `postdate`, `content`) VALUES ('$post_title', '$post_topic', '$post_tags', CURDATE(), '$post_content')";
    $s = $pdo->prepare($sql);
    $s->execute();
}
catch (PDOException $e)
{
$error = 'error getting and iserting data';
echo $error;
exit();
}
 header('Location: '.$success_page);
}

?>

[simplypixie]

So what problem do you have now (you don't say)? Also you don't need to declare an array of variables in your execute statement unless you are using placeholders, just use execute()

[Eagle.Avik]

thnx guys, i appreciate the help,
as i mentioned above if user not exist it will create a new user, and if exist it wont do anything.

so i want to get the current user id from the table. so that it will get the current user id and will save it on authorid column.
so how do i get it.
i was thinking about

SELECT id FROM ".$pdo_usertable." WHERE authorname = '".$post_username."' AND authoremail = '".$post_useremail."'

but i dont know how to get the id in php code as a variable.

[simplypixie]

If it is an existing user then from your first query it would be

$sql = "SELECT * FROM ".$pdo_usertable." WHERE authorname = '".$post_username."' AND authoremail = '".$post_useremail."'";
    $statement = $pdo->prepare($sql);
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    $user_id = $result['id'];

If it is a newly inserted user then (as long as the user ids are auto incremented) then you retrieve that last inserted id
return $this->db->lastInsertId();

if (count($result) == 0)
    {
      $sql = "INSERT INTO ".$pdo_usertable." (authorname, authoremail) VALUES ('$post_username', '$post_useremail')";
      $statement = $pdo->prepare($sql);
      $statement->execute();
      $user_id = $pdo->lastInsertId();
    }