SQL Unknown column 'title' in 'field list'

[iRoot121]

Hi guys, I'm kinda new to PHP, and I started with developing a site. Now I've a piece of code (check below), it is suppost to edit a page in the database, but the output is "Error updating database1: Unknown column 'title' in 'field list'". But, the column exist!

 

All variables are set, and I don't know what I'm doing wrong, and I hope you guys can help me.

 

Code:

 

$query_update = "UPDATE `pagina` SET `title` = '".addslashes($title)."', `korting`='".$korting."', `volg`='".$volg."', `product` = '".$product."',`product_id`='".$product_id."', `visible` = '".$visible."', `metadescription` = '".addslashes($metadescription)."', `url` = '".$url."', `content` = '".addslashes($content)."', `headerpicture`='".addslashes($headerpicture)."',`picture`='".addslashes($picture)."', form_id = '".$form_id."',`related`='".$related_string."', `datum` = NOW() WHERE `id` = '".$id."'";

mysql_query($query_update)or die('Error updating database1: '.mysql_error());

 

Column:

 

 

Greetzz iRoot121.

[PravinS]

open table structure and check whether there is any space around "title" field

[iRoot121]

open table structure and check whether there is any space around "title" field

There aren't any spaces .

[Muddy_Funster]

echo out $query_update as part of the or die statement and post up the contents

[iRoot121]

echo out $query_update as part of the or die statement and post up the contents

Posted: UPDATE `pagina` SET `title` = 'Test2', `korting`='0', `volg`='0', `product` = '1',`product_id`='', `visible` = '1', `metadescription` = 'Test2', `url` = 'test2', `content` = 'Test2', `headerpicture`='',`picture`='', `form_id` = '',`related`='', `datum` = NOW() WHERE `id` = '1708'
Error updating database1: Unknown column 'title' in 'field list'

It isn't because of the empty values, is it?

Edited August 2, 2013 by iRoot121

[Muddy_Funster]

n/m mis-read something

Edited August 2, 2013 by Muddy_Funster

[Muddy_Funster]

your exact table name? (case sensitive)

[iRoot121]

your exact table name? (case sensitive)

pagina

Is the table name.

[jazzman1]

Try to run this to get a length of all columns, in case you have empty spaces somewhere.

SELECT LENGTH(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_SCHEMA='DB_NAME' and TABLE_NAME='pagina';

Substitute the name of your database.

[iRoot121]

I don't get any output.

 

I've installed Plesk on my server, maybe you need another method for that to reach INFORMATION_SCHEMA?

[jazzman1]

Are you sure that your database and table's name is correct?

 

Try:

SHOW COLUMNS FROM `db_table` FROM `db_name`;

[iRoot121]

Test page:
<?php
include('../includes/config.php');
mysql_query("SHOW COLUMNS FROM `pagina` FROM `cms`")or die('Error: '.mysql_error());
?>

Still no output .

[jazzman1]

Well, there are no columns inside the table pagina

 

Also, you should output the result of the query in php, I think.

Edited August 2, 2013 by jazzman1

[jazzman1]

Try,

$query = "SELECT LENGTH(COLUMN_NAME)
           FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_SCHEMA='DB_NAME' and TABLE_NAME='pagina'";

$result = mysql_query($query) or die(mysql_error());

$output = array(); 

while ($row = mysql_fetch_assoc($result)) {
$output[] = $row;    
}
echo '<pre>'.print_r($output, true).'</pre>'; 

[iRoot121]

Array
(
    [0] => Array
        (
            [LENGTH(COLUMN_NAME)] => 2
        )

    [1] => Array
        (
            [LENGTH(COLUMN_NAME)] => 5
        )

    [2] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [3] => Array
        (
            [LENGTH(COLUMN_NAME)] => 4
        )

    [4] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [5] => Array
        (
            [LENGTH(COLUMN_NAME)] => 13
        )

    [6] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [7] => Array
        (
            [LENGTH(COLUMN_NAME)] => 3
        )

    [8] => Array
        (
            [LENGTH(COLUMN_NAME)] => 8
        )

    [9] => Array
        (
            [LENGTH(COLUMN_NAME)] => 15
        )

    [10] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [11] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [12] => Array
        (
            [LENGTH(COLUMN_NAME)] => 4
        )

    [13] => Array
        (
            [LENGTH(COLUMN_NAME)] => 5
        )

    [14] => Array
        (
            [LENGTH(COLUMN_NAME)] => 10
        )

    [15] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [16] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [17] => Array
        (
            [LENGTH(COLUMN_NAME)] => 7
        )

    [18] => Array
        (
            [LENGTH(COLUMN_NAME)] => 5
        )

)

The 1st 2 arrays are 'id' and 'title', so the lenght seems to be right.

[jazzman1]

Hm....it looks just fine.

 

Now, try this:

$query = "SELECT LENGTH(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='DB_NAME' AND TABLE_NAME='pagina' AND column_name ='title'";

[iRoot121]

Array
(
    [0] => Array
        (
            [LENGTH(COLUMN_NAME)] => 5
        )

)

Seems still to be good .

[jazzman1]

Does the name of the database is the same in your first post?

[iRoot121]

Yes it is.

[jazzman1]

Can I see how you connect to the database and set the table name?

[iRoot121]

I think you mean it like this?

 

I've a file called 'config.php', above each page I set an include('config.php');

 

Config.php:

mysql_connect('localhost', '<user>', '<mypass>');
mysql_select_db('cms');

I import the DB structure into the Database:

CREATE TABLE `pagina` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(254) default NULL,
  `content` text,
  `volg` int(11) NOT NULL default '0',
  `form_id` int(2) default NULL,
  `headerpicture` varchar(128) default NULL,
  `picture` varchar(128) NOT NULL,
  `url` varchar(50) default NULL,
  `titeltag` varchar(254) default NULL,
  `metadescription` varchar(254) default NULL,
  `visible` int(1) unsigned default '1',
  `menutop` int(1) default '0',
  `sort` int(10) default NULL,
  `datum` date NOT NULL default '0000-00-00',
  `product_id` int(11) NOT NULL default '0',
  `product` int(2) NOT NULL default '0',
  `korting` int(11) NOT NULL,
  `related` varchar(128) NOT NULL,
  `ORGID` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `url` (`url`),
  KEY `related_2` (`related`),
  KEY `related_3` (`related`),
  KEY `related_4` (`related`),
  KEY `product` (`product`),
  KEY `product_2` (`product`),
  KEY `product_3` (`product`),
  KEY `product_4` (`product`),
  FULLTEXT KEY `index` (`title`,`content`),
  FULLTEXT KEY `related` (`related`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1707 ;

And by fillin' in the form on editproduct.php, I click submit, and then edit.php defines the vars, and updates the DB:

 

Piece of edit.php:

$query_update = "UPDATE `pagina` SET `title` = '".addslashes($title)."', `korting`='".$korting."', `volg`='".$volg."', `product` = '".$product."',`product_id`='".$product_id."', `visible` = '".$visible."', `metadescription` = '".addslashes($metadescription)."', `url` = '".$url."', `content` = '".addslashes($content)."', `headerpicture`='".addslashes($headerpicture)."',`picture`='".addslashes($picture)."', `form_id` = '".$form_id."',`related`='".$related_string."', `datum` = NOW() WHERE `id` = '".$id."'";

	mysql_query($query_update)or die('Posted: '.$query_update.'<br />Error updating database1: '.mysql_error());

[jazzman1]

Just for test, get rid addslashes() off of your query string.

 

Run this:

<?php

$db_server = mysql_connect('localhost','userName', 'userPass') or die(mysql_error());

mysql_select_db('cms') or die("Unable to select database: " . mysql_error());

$query = "UPDATE `pagina` SET `title` = '".$title."', `korting`='".$korting."', `volg`='".$volg."', `product` = '".$product."',`product_id`='".$product_id."', `visible` = '".$visible."', `metadescription` = '".$metadescription."', `url` = '".$url."', `content` = '".$content."', `headerpicture`='".$headerpicture."',`picture`='".$picture."', `form_id` = '".$form_id."',`related`='".$related_string."', `datum` = NOW() WHERE `id`=$id";

$result = mysql_query($query) or die(mysql_error());

$output = array(); 

while ($row = mysql_fetch_assoc($result)) {
$output[] = $row;    
}
echo '<pre>'.print_r($output, true).'</pre>';

[iRoot121]

Got an empty Array(), but he updates the database .

[lemmin]

Can you post the exact values of your variables in the query?

[jazzman1]

Got an empty Array(), but he updates the database .

Yes, b/s you need to use a sql select statement to retrieve the data from the db server.