mysql insert problem

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 开发者_StackOverflow社区0 0 0 0. There's a lot of AJAX on my page so it was tricky to find it...sorry my bad!

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.

$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.