php mysql insert

Question

I am pretty new in php programming and i'm facing with one trouble here. Probably it will be simple for all of you, but ok..

When i try to insert one row into mysql table named "novica" first time it works ok, but after that i'm unable to add any new row's. But when i delete this row, i can add one...but again, only one. I don't know what cause this.

here is my little php:

<?php
    $con = mysql_connect("localhost","root","password");
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("sola", $con);
    mysql_query("INSERT INTO novica (naslov, vsebina, avtor, ustvarjeno) VALUES('$_POST[address]', '$_POST[content]', 'Klemen', NOW())");

    mysql_close($con);
?>

And here is mysql export sql code:

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 22, 2012 at 06:41 PM
-- Server version: 5.5.28
-- PHP Version: 5.3.10-1ubuntu3.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sola`
--

-- --------------------------------------------------------

--
-- Table structure for table `novica`
--

CREATE TABLE IF NOT EXISTS `novica` (
  `id_novica` int(10) NOT NULL,
  `naslov` text COLLATE utf8mb4_bin NOT NULL,
  `vsebina` text COLLATE utf8mb4_bin NOT NULL,
  `avtor` text COLLATE utf8mb4_bin NOT NULL,
  `ustvarjeno` date NOT NULL,
  `posodobljeno` date DEFAULT NULL,
  PRIMARY KEY (`id_novica`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

--
-- Dumping data for table `novica`
--

INSERT INTO `novica` (`id_novica`, `naslov`, `vsebina`, `avtor`, `ustvarjeno`, `posodobljeno`) VALUES
(0, 'a', 'a', 'Klemen', '2012-11-22', NULL);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Kami · Accepted Answer · 2012-11-22 17:48:00Z

6

Your primary key is not set to auto increment. As such when you insert a new row, the id of zero is possibly assigned, but any subsequent insert fails as that id is already taken.

Either add the id into the insert statement or make the id_novica auto increment - http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

CREATE TABLE IF NOT EXISTS `novica` (
  `id_novica` int(10) NOT NULL AUTO_INCREMENT,
  `naslov` text COLLATE utf8mb4_bin NOT NULL,
  `vsebina` text COLLATE utf8mb4_bin NOT NULL,
  `avtor` text COLLATE utf8mb4_bin NOT NULL,
  `ustvarjeno` date NOT NULL,
  `posodobljeno` date DEFAULT NULL,
  PRIMARY KEY (`id_novica`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

answered Nov 22, 2012 at 17:48

Kami

19.5k4 gold badges53 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clem Over a year ago

Oh gosh :) thank a lot! I'we really checked everything, except this...ty again

Marcin Orlowski · Accepted Answer · 2012-11-22 17:48:31Z

1

It always god to check error (mysql_error()) to find out what the problem. If that is problematic, display your query, execute by hand in console and check what error you get.

In general your primary key id_novica have to be unique, hence failure of subsequent inserts. You should either change values or define your primari key as AUTO_INCREMENT, to tell mysql to take care of dealing with this. So instead of

`id_novica` int(10) NOT NULL,

you shall have

`id_novica` int(10) NOT NULL AUTO_INCREMENT,

and in your INSERTS do not provide id_novica at all.

You also should not be using mysql_ exitension - it is deprecated. Switch to mysqli_ but if you are starting, jump directly to PDO instead

answered Nov 22, 2012 at 17:48

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

Comments

mychalvlcek · Accepted Answer · 2012-11-22 17:51:39Z

0

add unique id to your insert or update your id column to be automatically incremented:

ALTER TABLE  `novica` CHANGE  `id_novica`  `id_novica` INT( 10 ) NOT NULL AUTO_INCREMENT

answered Nov 22, 2012 at 17:51

mychalvlcek

4,0762 gold badges23 silver badges36 bronze badges

Collectives™ on Stack Overflow

php mysql insert

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related