-1

Here's my info table:

CREATE TABLE `info` (
  `id_info` int(10) NOT NULL auto_increment,
  `judul_info` varchar(50) collate latin1_general_ci NOT NULL,
  `konten` varchar(255) collate latin1_general_ci NOT NULL,
  `diubah_oleh` varchar(20) collate latin1_general_ci NOT NULL,
  `id_kategori` int(10) NOT NULL,
  `tgl_buat` timestamp NOT NULL default '0000-00-00 00:00:00',
  `tgl_ubah` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dibuat_oleh` varchar(20) collate latin1_general_ci NOT NULL,
  `id` int(10) NOT NULL,
   PRIMARY KEY  (`id_info`),
   KEY `id_kategori` (`id_kategori`),
   KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=62 ;

Here's my upload table 

CREATE TABLE `upload` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `deskripsi` text,
  `filetype` varchar(200) default NULL,
  `filedata` longblob,
  `filename` varchar(200) default NULL,
  `filesize` bigint(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

I'm using this query :

$sql1="INSERT INTO info VALUES('','$judul', '$konten','$diubah_oleh','$kategori',now(),'$tgl_ubah','$dibuat_oleh','')";
$sql2="insert into upload values ('','$keterangan','$tipe','$filedata','$nama_file',$ukuran)";
$sql3="UPDATE info SET id=last_insert_id()";
$result=mysql_query($sql1);
$result=mysql_query($sql2);
$result=mysql_query($sql3);

I want info.id has the same value as upload.id but with this query all of the value i get in info.id is the same as value i last inserted in upload.id.

Lorenz Meyer
  • 19,166
  • 22
  • 75
  • 121
aldy feraldy
  • 3
  • 4
  • 2
    Use MySQLi or PDO. MySQL is deprecated. – Webice Aug 14 '14 at 06:53
  • [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Phil Aug 14 '14 at 06:53
  • I have always wondered if mysql_* are deprecated, what is the equivalent for `mysql_real_escape_string` in mysqli and pdo? – Hussain Aug 14 '14 at 06:56
  • 1
    There is no need for that, cause of prepared Statements in both. But here: http://php.net/manual/de/mysqli.real-escape-string.php – Webice Aug 14 '14 at 06:57

5 Answers5

1
CREATE TABLE `upload` (
  `id` int(10) unsigned NOT NULL,
  `deskripsi` text,
  `filetype` varchar(200) default NULL,
  `filedata` longblob,
  `filename` varchar(200) default NULL,
  `filesize` bigint(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;



        $sql1="INSERT INTO info VALUES('','$judul', '$konten','$diubah_oleh','$kategori',now(),'$tgl_ubah','$dibuat_oleh','')";
        $result=mysql_query($sql1);

        $lastId = mysql_insert_id();

        $sql2="insert into upload values ('$lastId','$keterangan','$tipe','$filedata','$nama_file',$ukuran)";
        $result=mysql_query($sql2);
Webice
  • 592
  • 4
  • 15
  • The MySQL function is [`LAST_INSERT_ID()`](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id) – Phil Aug 14 '14 at 06:55
  • You originally had it as part of the INSERT statement in which case you would use the MySQL server function `LAST_INSERT_ID()`. – Phil Aug 14 '14 at 06:57
  • Yes, thats why i edit it. MySQL is deprecated. Its useless to discuss about that. – Webice Aug 14 '14 at 06:58
  • Also, I think OP wants to set the (badly named) `info.id` column with the last inserted id from the `upload` table. Your answer sets the `info.id_info` column though with those column names, I don't blame you – Phil Aug 14 '14 at 06:59
  • @Phil Why you dont yell, cause of the wrong order ? ! – Webice Aug 14 '14 at 06:59
  • You seemed to be confused about the MySQL server function `LAST_INSERT_ID()`. This has nothing to do with PHP. In any case, you removed that part – Phil Aug 14 '14 at 07:00
  • Okey i'am out. If you only complaining about my answer, i dont have to reply anymore. – Webice Aug 14 '14 at 07:03
  • Sorry, no offence intended. I only commented because your answer was *almost* right – Phil Aug 14 '14 at 07:07
1

Your last update statement below is updating all the rows in your info tables with the same id because there is no where statement.

Since you need the upload table id information inside the info table.

Follow these steps:

Run the $sql2 first. Then run the $sql1 inserting the last_insert_id() in info.id. This way you don't need to use update statement as well.

msdhami
  • 11
  • 2
0

There is an alternate php function to that mysql_insert_id() . You can use this to generate the ID inserted in the last executed query.

kartik
  • 583
  • 8
  • 24
0

You can do this by using mysql_insert_id(). This function returns the AUTO_INCREMENT ID generated from the previous INSERT operation. Your code should look like this

 $sql1="INSERT INTO info VALUES('','$judul', '$konten','$diubah_oleh','$kategori',now(),'$tgl_ubah','$dibuat_oleh','')";
        $result=mysql_query($sql1);

        $lastinsertedid= mysql_insert_id();

        $sql2="insert into upload values ('$lastinsertedid','$keterangan','$tipe','$filedata','$nama_file',$ukuran)";
        $result=mysql_query($sql2);

Hope this helps you

Utkarsh Dixit
  • 4,267
  • 3
  • 15
  • 38
  • Thanks a lot @Utkarsh. It works now. I just swap the sql1 to sql2 from your code and put a NULL in upload.id then put $lastinsertedid on info.id – aldy feraldy Aug 14 '14 at 07:18
-1

can you try to do this:

$sql1="INSERT INTO info VALUES('','$judul', '$konten','$diubah_oleh','$kategori',now(),'$tgl_ubah','$dibuat_oleh','')";

$sql2="insert into upload values ('','$keterangan','$tipe','$filedata','$nama_file',$ukuran)";

$last_id = last_insert_id();

$sql3="UPDATE info SET id=".$last_id;

$result=mysql_query($sql1);

$result=mysql_query($sql2);

$result=mysql_query($sql3);

user07192010
  • 44
  • 5