0
$query = mysql_query(
  "insert into users (id,email,password,firstname,lastname) 
  values ('','$email','$password','$firstname','$lastname')"
) or die(mysql_error());

echo $userid = mysql_insert_id();

$q_query = mysql_query(
  "insert into qualification (q_id) values ('$userid')"
) or die(mysql_error());

echo $userid shows perfectly but the row is not inserted into the qualification table. Why not?

erik258
  • 14,701
  • 2
  • 25
  • 31
Irfan
  • 58
  • 11
  • Does it error? What is the structure of the `qualification` table? – Michael Berkowski Jan 02 '15 at 22:36
  • 1
    Are you sure that first query is even working? You're trying to insert an `id` of `''` into the `users` table; that makes no sense to me. Edit: obviously it is, or else the `echo` wouldn't show right. But why it would work I still don't know ( see below); – erik258 Jan 02 '15 at 22:36
  • 1
    Does `or die(mysql_error())` give you a error message? – Sean Jan 02 '15 at 22:36
  • 2
    @DanFarrell Assuming `auto_increment` for `id`, an empty string `''` or `null` will both just cause it to use the next auto-inc as normal. (personally I prefer to leave it out of the list though) – Michael Berkowski Jan 02 '15 at 22:37
  • 1
    Is `q_id` an integer? if so, change `('$userid')` to `($userid)` – Gil Jan 02 '15 at 22:38
  • 2
    Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). – Jay Blanchard Jan 02 '15 at 22:39
  • Don't use `mysql_*` functions. The [`mysql`](http://php.net/manual/en/intro.mysql.php) extension is deprecated since PHP 5.5 and it will be removed in the future (possibly on the next major version which is PHP 7). Use [`mysqli`](http://php.net/manual/en/book.mysqli.php) or [`PDO MySQL`](http://php.net/manual/en/ref.pdo-mysql.php) instead. – axiac Jan 02 '15 at 22:40
  • @Gil, MySQL will type-cast the string value to an integer, so it is irrelevant. – Federkun Jan 02 '15 at 22:41
  • Hey guys thanx for greate feed back i use all these option when you suggest me but still is not insert qualification table – Irfan Jan 02 '15 at 22:45
  • @DanFarrell its working per insert for users table and show my last insert id but same issue useid not insert in qualification table – Irfan Jan 02 '15 at 22:46
  • At this point, it seems likely that the schema is relevant here. Can you please edit your question to contain the output of "SHOW CREATE TABLE uesrs; SHOW CREATE TABLE qualification;" ? – erik258 Jan 02 '15 at 22:48

1 Answers1

1

I suspect @Gil has the right answer. Using the mysql function last_insert_id() is a more straightforward approach however:

mysql> create temporary table users (id int unsigned not null auto_increment primary key, email text, password text, firstname text, lastname text);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users (id,email,password,firstname,lastname) values ('','dan@site.com', 'password','dan','f');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

This depends on whether you're in strict mode (thanks to comment below )

mysql> insert into users (id,email,password,firstname,lastname) values (null,'dan@site.com', 'password','dan','f');
Query OK, 1 row affected (0.00 sec)

mysql> create table qualification (q_id int unsigned not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into qualification( q_id) values (last_insert_id() );
Query OK, 1 row affected (0.00 sec)

mysql> select * from users,qualification;
+----+--------------+----------+-----------+----------+------+
| id | email        | password | firstname | lastname | q_id |
+----+--------------+----------+-----------+----------+------+
|  1 | dan@site.com | password | dan       | f        |    1 |
+----+--------------+----------+-----------+----------+------+
1 row in set (0.00 sec)
erik258
  • 14,701
  • 2
  • 25
  • 31
  • 2
    "I don't understand why it's working for you": It depends on strict SQL mode (http://bugs.mysql.com/bug.php?id=61375) – Federkun Jan 02 '15 at 22:56