mysql insert problem

Question

ive got a really weird problem. i have no clue why its not working. i create an user and get the id and insert a row based on that id in another table. the row gets inserted with that id but the other values however for that row are not inserted!

 $user_id = mysqli_insert_id($this->connection);

 $query = "INSERT INTO selections 
 (user_id, language_id, country_id, region_id, city_id, gender_id, age_id, category_id)
 VALUES ($user_id, 1, 1, 0, 0, 0, 20, 0)";

so the user_id gets inserted, but not the other values (they are all 0 in the table). i have really checked the columns and deleted all foreign keys to debug this problem. but i have no clue at all.

the columns are all INT. the weird part is sometime when i replace $user_id with a literal number it works, sometimes it doesnt. but the row is always created. and i have checked that $user_id is an integer.

i know this is a hard problem and that it can be caused of a lot of things, but i have tried to solve this tiny issue for 3 hours now. so would be great if someone just gave me something i could do to debug this problem.

UPDATE: even when i have set default values and just insert the first column (user_id) it doesnt work. every other field is 0. So weird!

| selections | CREATE TABLE `selections` (
  `user_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `country_id` int(11) NOT NULL DEFAULT '1',
  `region_id` int(11) NOT NULL DEFAULT '0',
  `city_id` int(11) NOT NULL DEFAULT '0',
  `gender_id` int(11) NOT NULL DEFAULT '0',
  `age_id` int(11) NOT NULL DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


  $query = "INSERT INTO selections
        (user_id)
        VALUES ('$user_id')";

the user_id shows 178 and other fields are 0:(

UPDATE:

It worked in the sql command line. but not in php. but mysqli generated no error and the row was indeed inserted but why are the other fields 0?

ANSWER: My fault. i had a jquery script that changed it back to 0 0 0 0 0 0 0. There's a lot of AJAX on my page so it was tricky to find it...sorry my bad!

Answer 1

$query = "INSERT INTO selections 
(user_id, language_id, country_id, region_id, city_id, gender_id, age_id, category_id)
VALUES ('$user_id', 1, 1, 0, 0, 0, 20, 0)";

Single quotes around $user_id might do it.

Answer 2

When you run into situations like this, print the query to screen before it is executed:

$query = "INSERT INTO ...";
echo $query

Try:

$query = "INSERT INTO selections 
            (user_id, language_id, country_id, region_id, city_id, gender_id, age_id, category_id)
          VALUES 
            ({$user_id}, 1, 1, 0, 0, 0, 20, 0)";

You need to wrap PHP variables in {} when referencing them in SQL string statements.

Use Your DEFAULT Constraints

---

If you have defaults then you don't need to set the values in your INSERT statement:

INSERT INTO selections 
  (user_id)
VALUES 
  ({$user_id})

Referencial Integrity

---

You're getting the last inserted id and using it in a subsequent insert into another table, but you don't have a foreign key defined on the user_id column to ensure that the value going into that column actually exists in the other table. If you provide the name of the table & column you are getting for your last insert id, I'll provide the ALTER TABLE statement.