How to insert new row into a already created database PHP?

Question

LATER EDIT: It woorks to insert a new row, but ONLY one! After inserting one user to my Users table, I cannot insert others :D it (again) keeps alerting "Could not enter data". Why?

I already have a database named "problema_curs_1" and I want to insert a new row into my Users table. This is the code:

 $db['user'] = 'root';
 $db['pass'] = '';
 $db['server'] = 'localhost';
 $conn = mysql_connect($db['server'], $db['user'], $db['pass']);
        if(! $conn )
        {
            die(json_encode(array("mesaj" => "'Could not connect: ")));
        }
        $sql = "INSERT INTO Users ('nume', 'password', 'rol') VALUES($numereg,$parolareg,'user')";
        mysql_select_db('problema_curs_1');
        $retval = mysql_query( $sql, $conn );
        if(! $retval )
        {
            die(json_encode(array("mesaj" => "Could not enter data:")));
        }
        echo "Entered data successfully\n";
        mysql_close($conn);

It pops up "Could not enter data:". My table has 4 columns: ID, "nume", "password" and "rol". ID is set to autoincrement, and this is why I don't want to insert it manually.

*This I will use in my register() function. *If it helps, I can put here the way I wrote the login() part, which seems to work:

function login($nume,$parola)
{
    $db['user'] = 'root';
    $db['pass'] = '';
    $db['server'] = 'localhost';
    $conn = mysql_connect($db['server'], $db['user'], $db['pass']);
    $sql = "SELECT id FROM `Users` WHERE nume ='".$nume."' ANY password = '".md5($parola)."'";
    $q = mysql_query($sql);

    if(!$q)
        die(json_encode(array("mesaj" => "Invalid")));
    else{
        $x = mysql_fetch_array($q);
        if (empty($x))
            die(json_encode(array('mesaj' => 'User does not exist')));
        else {
        //    $user = new User($x['id']);
            session_start();
            $_SESSION['user_id'] = $x['id'];
            $_SESSION['loggedin'] = "yes";
            die(json_encode(array("mesaj"=>"Congrats :)")));
        }

        }
    //nu prea are cum sa ajunga aici
    die(json_encode(array("mesaj"=>"You were not logged")));
}

Danyal Sandeelo · Answer 1 · 2015-04-28T09:06:33.377

1

you are passing varchar type variables as integers.

INSERT INTO Users ('nume','password','rol') VALUES($numereg,$parolareg,user)

to

INSERT INTO Users (nume,password,rol) VALUES('$numereg','$parolareg','user')

You are passing values directly which can lead to SQL INJECTION, I hope you would clean the params or pass them as secure params.

edited Apr 28 '15 at 09:06

answered Apr 28 '15 at 08:54

Danyal Sandeelo

12,196
10
47
78

Thanks! I changed it to $sql = "INSERT INTO Users ('nume', 'password', 'rol') VALUES('$numereg','$parolareg','user')"; and still not gettin' data :( – Lulu Apr 28 '15 at 09:00
@Lulu unquote the column names aswell – gbestard Apr 28 '15 at 09:00
INSERT INTO Users (nume,password,rol) VALUES('$numereg','$parolareg','user') – Danyal Sandeelo Apr 28 '15 at 09:01
It wooorks! Now I have another problem: after inserting one user to my Users table, i cannot insert others :D it (again) keeps alerting "Could not enter data" – Lulu Apr 28 '15 at 09:04
@Lulu check all the unique constraints, check the value and see if it works or not... write down SHOW CREATE TABLE Users; and run this query..paste the output here.. – Danyal Sandeelo Apr 28 '15 at 09:05
I put it into an alert and it printed ""SHOW TABLES FROM problema_curs_1" – Lulu Apr 28 '15 at 09:17

score 1 · Answer 2 · answered Apr 28 '15 at 09:06

You were missing `` symbol in insert query . I have checked this code .Try this code ,hope it will solve your problem.


<?php 
     $db['user'] = 'root';
     $db['pass'] = '';
     $db['server'] = 'localhost';
     $conn = mysql_connect($db['server'], $db['user'], $db['pass']);
            if(! $conn )
            {
                die(json_encode(array("mesaj" => "'Could not connect: ")));
            }
            $sql = "INSERT INTO Users (`name`, `password`, `rol`)     VALUES('dfghfg','dfghdf','45456345')";
            mysql_select_db('problema_curs_1');
            $retval = mysql_query($sql);
            if(! $retval )
            {
                die(json_encode(array("mesaj" => "Could not enter data:")));
            }
            echo "Entered data successfully\n";
            mysql_close($conn);
    ?>

score -1 · Answer 3 · edited May 23 '17 at 12:17

$sql = "INSERT INTO Users ('nume', 'password', 'rol') VALUES($numereg,$parolareg,'user')";

In this line, you just do an insert of your variables. This will not work, because you do not have quotes, and it also very insecure (SQL injection for example).

How should you do it? This is an way:

$sql = "INSERT INTO `Users` (`nume`, `password`, `rol`) VALUES('" .mysql_real_escape_string($numereg) . "', '" . mysql_real_escape_string($parolareg) ."','user')";

Always use backticks for your table names, and your field names (instead of quotes).

Also: don't use mysql_* function, but PDO or Mysqli_* function.
Refer to Why shouldn't I use mysql_* functions in PHP? for more information.

More references for this answer:
SQL injection
mysql_real_escape_string function

score -2 · Answer 4 · answered Apr 28 '15 at 09:01

-2

$sql = "INSERT INTO Users set name='". $numereg ."', password= '". $parolareg ."', rol = '". $user."' ";

answered Apr 28 '15 at 09:01

Puneet Sharma

66
8

@Blaatpraat actually it is, for inserts you can also use `SET` – Daan Apr 28 '15 at 09:11
1

@Daan It is valid as a MySQL extension, but it is not valid as a SQL query. It would work though (because OP is using MySQL)... – Blaatpraat Apr 28 '15 at 09:13
I agree it's not standard SQL. – Daan Apr 28 '15 at 09:14
The question is tagged as [tag:mysql], OP is using `mysql_` and this syntax is compatible with [MySQL INSERT syntax](http://dev.mysql.com/doc/refman/5.6/en/insert.html), so I don't see any problem here. – kenorb Apr 28 '15 at 11:07

How to insert new row into a already created database PHP?

4 Answers4