unable to save values in mysql

Question

I am trying to save data in two different database tables , when i run two query it save 0 in the integer field.

     $sql = 'insert into photo_album( user_id, photo_id, album_id )
            values(". $user_id.",".$photo_id.",". $album_id.")';

          $query = $this->db->query($sql);

but when i run query as hard coded values it work fine.

      INSERT INTO
       photo_album( photo_id, user_id, album_id )
        VALUES(8299,214,316)

can some one tell what is wrong ?

You started your string with single quotes `'` and you want to concat it with double quotes `"`? :D don't think that's going to work — Rizier123, Dec 03 '14 at 07:13
echo result is the INSERT INTO photo_album( photo_id, user_id, album_id ) VALUES(8299,214,316) — Sohail Yasmin, Dec 03 '14 at 07:14
First go through : http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php then change your query to `INSERT INTO photo_album( photo_id, user_id, album_id )VALUES (?, ?, ?)` — Hanky Panky, Dec 03 '14 at 07:17
@SohailYasmin: Is this the second Query(to second database)?. And what about first? — MMK, Dec 03 '14 at 07:24
@MMK database is only one table are two in the second table , i am facing issue , while inserting data same time in both table — Sohail Yasmin, Dec 03 '14 at 07:38

CaPs LoCk · Answer 1 · 2014-12-03T07:36:02.020

1

$data = array(
   'user_id' => '$user_id' ,
   'photo_id' => '$photo_id' ,
   'album_id' => '$album_id'
);

$this->db->insert('photo_album', $data);

edited Dec 03 '14 at 07:36

answered Dec 03 '14 at 07:29

CaPs LoCk

150
4

Rizier123 · Answer 2 · 2014-12-03T07:49:54.167

0

This should work for you:

(Don't mix single and double quotes! They are different :D)

$sql = "INSERT INTO `photo_album`(photo_id, user_id, album_id)
        VALUES('" . $photo_id . "', '" . $user_id . "', '" . $album_id . "')";

EDIT after comment:

Then try it to put ' around the values. Maybe the are stored as strings.

Also don't change the order of the column's to insert :D

edited Dec 03 '14 at 07:49

answered Dec 03 '14 at 07:14

Rizier123

58,877
16
101
156

also try the same but failed to save value – Sohail Yasmin Dec 03 '14 at 07:15
@SohailYasmin Updated my answer! Maybe you store the id's as strings so put `'` around them – Rizier123 Dec 03 '14 at 07:17
@Hanky웃Panky Ah you got me again xD (Think OP's problem is the order of the column's and the difference between single and double quotes) – Rizier123 Dec 03 '14 at 07:20
@SohailYasmin Think you have a wrong order of the columns! – Rizier123 Dec 03 '14 at 07:26
columns are not the issue at least it has to save some thing in the table – Sohail Yasmin Dec 03 '14 at 07:44

Dexter · Answer 3 · 2014-12-03T07:22:00.560

0

try like this in codeigniter format

$data = array(
                'user_id' => $user_id,
                'photo_id' => $photo_id,
                'album_id' => $album_id
             );
            $this->db->insert('photo_album', $data);

edited Dec 03 '14 at 07:22

answered Dec 03 '14 at 07:16

Dexter

1,804
4
24
53

after this can you do this echo $this->db->last_query(); – Dexter Dec 03 '14 at 07:43
INSERT INTO `photo_album` (`user_id`, `photo_id`, `album_id`) VALUES (214, 8323, 316) – Sohail Yasmin Dec 03 '14 at 07:52
its means query is right..then there is no chance insert 0 values – Dexter Dec 03 '14 at 07:54

zref · Answer 4 · 2014-12-03T07:40:38.803

0

Try this

 $sql="INSERT INTO photo_album (user_id,photo_id,album_id)
                VALUES('$this->user_id','$this->photo_id','$this->album_id')";

edited Dec 03 '14 at 07:40

answered Dec 03 '14 at 07:21

zref

37
10

Suchit kumar · Answer 5 · 2014-12-03T09:00:26.473

0

since you are using codeigniter then try like this:

$dataArr = array(
    'photo_id' => $photo_id,
   'user_id' => $user_id,
   'album_id' => $album_id
);

$this->db->insert('photo_album', $dataArr);

edited Dec 03 '14 at 09:00

answered Dec 03 '14 at 07:30

Suchit kumar

11,809
3
22
44

score 0 · Answer 6 · answered Dec 03 '14 at 07:33

0

Try This Separate variables

$sql = 'insert into photo_album( `user_id`, `photo_id`, `album_id` )
        values("' .$user_id. '","' .$photo_id. '","' .$album_id. '")';

answered Dec 03 '14 at 07:33

Arun Kumar

1,607
1
18
33

score 0 · Accepted Answer · answered Dec 03 '14 at 13:09

finely i manage to solve this issue , It is very strange you people will laugh at me / not believe me , I rename the field to some other name it start saving the value again , then i again rename to original name , I saw every this is working perfectly.

Thank you for your help.

please tell me if this is bug in mysql or not

unable to save values in mysql

7 Answers7