I can't update table's columns in MySQL database

Question

I have created a tabe with values using this command :

 CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `price` text NOT NULL,
  `link` text NOT NULL,
  `ppcode` text NOT NULL,
  `type` text NOT NULL,
  PRIMARY KEY  (`id`)
 )

And when i use this php codes i cant update any value of the columns :

 if (isset($_POST['edit'])){
 $delsql = "UPDATE news SET title='$newsubject',content='$newdisc',link='$newlink',
            price='$newprice',ppcode='$newppcode' WHERE id = '$id'";
 $result = mysql_query($delsql) or die(mysql_error());
 echo 'OK';
 }

Note : the version of MySQL is 3.5.2.2 and the version of PHP is 5.3

Firstly, do not use the `mysql_*` functions, they are deprecated, use either `PDO` or the `mysqli_*` functions. Secondly, output the mysql error to find out what is wrong, see `mysqli_error` — Geoffrey, May 21 '16 at 04:17
Yes.Your query may not be executing due to the fact that `mysql_*` is deprecated instead replace it with `mysqli_*` and also return your `mysqli_error()`..! — Umair Shah, May 21 '16 at 04:20
one more thing your id column is int so no need pass in single quote pass without single quote — Denis Bhojvani, May 21 '16 at 04:25
@UmairShahYousafzai - It would fail with a function not found error, it is not the cause of this fault, just bad practice. — Geoffrey, May 21 '16 at 04:28
@DineshBhojvani - It may not need quoting in this instance, but it causes no harm and does not prevent it from functioning. — Geoffrey, May 21 '16 at 04:29
Ok msg appeared , but the problim is not in the php code because when i tried to create the table and set valuse manual ( From PhpMyAdmin ) i got ok and the values changed but the `id` is still a constant No. it still 0 in all news — ßł Ặck Ĥặwk, May 21 '16 at 04:30
You can see this image : `http://up.dev-point.com/uploads1/1aab9ffafdb61.png` this is what i mean in 'manual method' — ßł Ặck Ĥặwk, May 21 '16 at 04:37
When i run the code in `localhost apache server` i get ok in all statements of mysql_* or PDO and the columns data changed — ßł Ặck Ĥặwk, May 21 '16 at 04:40
FYI, to put links `[text](http://url.com)` [**test**](http://www.google.com) — Juan Carlos Oropeza, May 21 '16 at 04:43

score 0 · Answer 1 · answered May 21 '16 at 04:41

0

Beside all the suggestion on the comments

You have

 WHERE id = '$id'";

but id is integer not text

CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,

answered May 21 '16 at 04:41

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Accepted Answer · answered May 21 '16 at 04:45

0

Use mysqli_* functions or PDO!!!!

//db connection

global $conn;

$servername = "localhost";  //host name

$username = "username"; //username

$password = "password"; //password

$mysql_database = "dbname"; //database name

//mysqli prepared statement 

$conn = mysqli_connect($servername, $username, $password) or die("Connection failed: " . mysqli_connect_error());

mysqli_select_db($conn,$mysql_database) or die("Opps some thing went wrong");

$stmt = $conn->prepare("UPDATE news SET title=?,content=?,link=?,price=?,ppcode=? WHERE id =?");

            $stmt->bind_param('sssdii',$newsubject,$newdisc,$newlink,$newprice,$newppcode,$id);

 //   i corresponding variable has type integer
 //  d  corresponding variable has type double
//  s   corresponding variable has type string
//  b   corresponding variable is a blob and will be sent in packets

            $stmt->execute();

            $row_count= $stmt->affected_rows;
            $stmt->close();
             $conn->close();

answered May 21 '16 at 04:45

JYoThI

11,977
1
11
26

what is `'sssdii'` in your example ? – ßł Ặck Ĥặwk May 21 '16 at 04:51
and waht is the questions marks in `$conn->prepare` ? – ßł Ặck Ĥặwk May 21 '16 at 04:52
'sssdii' data type of your parameter and ? it denote the parameters – JYoThI May 21 '16 at 05:04
OK, but what is the problem here ? http://up.dev-point.com/uploads1/f949370d79c61.png – ßł Ặck Ĥặwk May 21 '16 at 05:44
you dont need to change the ? this one it automatically taking parameters from this line $stmt->bind_param('sssdii',$newsubject,$newdisc,$newlink,$newprice,$newppcode,$id); sequence should be same order – JYoThI May 21 '16 at 05:45
$result = $stmt->get_result(); while($row=$result->fetch_assoc()) { $new_column[]=$row; } @ßłẶckĤặwk – JYoThI May 21 '16 at 05:52
1

You mean i can get the data of a column like this : `$row['content'];` ? @jothi – ßł Ặck Ĥặwk May 21 '16 at 07:36
but how can i insert a new data to the columns ? – ßł Ặck Ĥặwk May 21 '16 at 07:43
you need insert query ah ? – JYoThI May 21 '16 at 08:18

score 0 · Answer 3 · answered May 21 '16 at 04:53

0

In image of phpmyadmin that you provided in comments you can see that A_I (auto_increment) is not active. Also primary key is not set. Set A_I to true for id and set it as primary key (under Index) in phpmyadmin. Then test it with your code.

Of course first insert new data which will have auto incremented id's.

answered May 21 '16 at 04:53

Jakob

3,493
4
26
42

When i save I get this msg `This is not a number!` then the phpmyadmin want me to add a value to 'title' column !! – ßł Ặck Ĥặwk May 21 '16 at 05:04
i got that when i press ok ( to create the table with the columns ) – ßł Ặck Ĥặwk May 21 '16 at 05:11
You probably didn't set Lenght/Values column. Set it for INT 11 and VARCHAR 255 for example. – Jakob May 21 '16 at 05:14

I can't update table's columns in MySQL database

3 Answers3