Updating profile date php/mysql is not working

Question

I'm making a profile updating page, and it doesn't work. To do it, I check if the table user_info has already information; if yes, I update the info with the mysql UPDATE query, else, I insert the new information.

I get the error "Error" all the time.

P.S:

This is not a registration page, registration is made on another table.
The $new_name variable I'm planning to answer it in another table, the basic table.

<?php

session_start();
include('include/connection.php');
if (isset($_SESSION['id']))

    {
    $id = $_SESSION['id'];
    $new_name = (isset($_POST['name']) ? $_POST['name'] : null);
    $location = (isset($_POST['location']) ? $_POST['location'] : null);
    $bio = (isset($_POST['bio']) ? $_POST['bio'] : null);
    $job = (isset($_POST['job']) ? $_POST['job'] : null);
    $birthday = (isset($_POST['birthday_year']) ? $_POST['birthday_year'] : null) . '-' . (isset($_POST['birthday_month']) ? $_POST['birthday_month'] : null) . '-' . (isset($_POST['birthday_day']) ? $_POST['birthday_day'] : null);
    $phone = (isset($_POST['phone']) ? $_POST['phone'] : null);

    $result_info = mysql_query("SELECT * FROM user_info WHERE(id='".$id."')");  
    $n_info = mysql_num_rows ($result_info);
    if ( $n_info = 0 )
    {
    $q = mysql_query ("INSERT INTO user_info (id , dateOfBirth , phoneNumber , bio , location , work) VALUES ('$id' , '$birthday' , '$phone' , '$bio' , '$location' , '$job' )")
    ;
    }
    else
    {
    $q = mysql_query ("UPDATE user_info (id  , dateOfBirth , phoneNumber , bio , location , work) VALUES ('$id' , '$birthday' , '$phone' , '$bio' , '$location' , '$job' )")
    ;
    }
    $query = $q;
    $req = $cnx->prepare($query);
    $req->execute();
    if ($query){
            echo 'Yes';
    }else{
            echo 'Error';
    } 
    }

else{
header('Location: index.php');
}

?>

Are you using PDO and mysql_ together? It looks like `$q` is assigned as the result of running your query through `mysql_query`; and you're then passing that into PDO to be run again. — andrewsi, Jul 17 '13 at 11:10

score 0 · Answer 1 · answered Jul 17 '13 at 11:13

0

Your UPDATE query syntax is wrong, you're using the INSERT syntax. The correct syntax for an UPDATE is:

UPDATE table SET field1 = 'val1', field2 = 'val2', field3 = 'val3'

You also probably want a where clause in there so it only updates the target record and not all records.

It looks like you're mixing mysql_* and PDO or MySQLi. You're passing a mysql result resource to PDO::prepare() or MySQLi::prepare() which is wrong. PDO:prepare() should be given the SQL string, and you should use the placeholders and bind your inputs instead of concatenating them into the SQL.

answered Jul 17 '13 at 11:13

MrCode

63,975
10
90
112

concatenating the inputs is not going to work ? It's obligatory to bind them when we use PDO ? – Y1NL30 Jul 17 '13 at 11:33
If you bind them then you prevent any possibility of SQL Injection, so this is always the recommended method. – MrCode Jul 17 '13 at 11:34

score 0 · Answer 2 · answered Jul 17 '13 at 11:36

Change your UPDATE query

From:
$q = mysql_query ("UPDATE user_info (id  , dateOfBirth , phoneNumber , bio , location , work) VALUES ('$id' , '$birthday' , '$phone' , '$bio' , '$location' , '$job' )")

To:
$q = mysql_query ("UPDATE user_info SET id='$id' , dateOfBirth = '$birthday', phoneNumber = '$phone' , bio = '$bio', location = '$location', work = '$job'");

Try running your queries with substitute values on your variables directly on your database for a better view of the errors.

Updating profile date php/mysql is not working

2 Answers2