CODEWITHSUNDEEP
phpmysqlubuntu
Toni
Toni

Reputation: 35

Can't do mysql insert on ubuntu

I recently uploaded my site from my pc to ubuntu with ssh.
Before I uploaded it worked fine with xampp.
Now everything else is working but mysql insert query.
Connect is working and mysql update. Nothing in mysql error.log.

Select is working too.

This is not working

$user_query = $db->prepare("INSERT INTO users (id, userid, name) VALUES (:id, :userid, :name)");
if($url_query->execute(array(":id" => "", ":userid" => $userid, ":name" => $name))){
    header("location: /");
}

This is working

$user_query = $db->prepare("UPDATE users SET name=:name WHERE userid =:userid");
if($user_query->execute(array(":name" => $name, ":userid" => $userid))){
    header("location: /");
}

I don't see any difference between these two querys.

Table

+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| userid   | text    | NO   |     | NULL    |                |
| name     | text    | NO   |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

My host is from digitalocean and I used this when I installed my apache and mysql

$user_query = $db->prepare("SELECT id FROM users WHERE userid=:userid");
    if($user_query->execute(array(":userid" => $userid))){
        if($user_query->rowCount()){

            $user_query = $db->prepare("UPDATE users SET name=:name WHERE userid =:userid");
            if($user_query->execute(array(":name" => $name, ":userid" => $userid))){
                header("location: /");
            }

        } else {

            $user_query= $db->prepare("INSERT INTO users (id, userid, name) VALUES (:id, :userid, :name)");
            if($user_query->execute(array(":id" => "", ":userid" => $userid, ":name" => $name))){
                header("location: /");
            }
        }
    }

Upvotes: 1

Views: 1365

Answers (1)

M. Eriksson
M. Eriksson

Reputation: 13645

Since the id-field is set to auto_increment, remove it from your insert statement. MySQL will handle that for you. In your current query, you're actually trying to set the id-field to an empty string (which should result in an error, since it's a non nullable, unique primary key set as an auto incremented integer).

Try:

$user_query= $db->prepare("INSERT INTO users (userid, name) VALUES (:userid, :name)");
if($user_query->execute(array(":userid" => $userid, ":name" => $name))){
    header("location: /");
}

If you get it to work on one server and not another, it might depend on the MySQL version. Newer MySQL-versions are much more picky about the integrity of the values.

Upvotes: 1

Related Questions