[SOLVED] Cant insert into database...

[xhelle]

Hi to all,

 

I have this code in php that need to insert the data into database, the data came from xml files, the problem is during i run my code i receive an error. Can you please check my code where do i go wrong. Thanks in advance.

 

Here's my code:

$xml = simplexml_load_file("KtextContentCatcherSample.xml");

foreach ($xml->children() as $child) 
{
if ($child->getName() == 'params')
{
	foreach ($child->children() as $params) 
	{
		if ($params->getName() == 'ArtistName')
			$name = $params;

		if ($params->getName() == 'ServiceName')
			$sname = $params;

		if ($params->getName() == 'encoding')
			$encoding = $params;

		if ($params->getName() == 'Message')
			$message = $params;

		if ($params->getName() == 'Filename')
			$fname = $params;

		if ($params->getName() == 'Data')
			$data = base64_decode($params);

		$query = "SELECT * FROM inbox where artist_id = '";
	}
}
}

//write file
$file = '/var/www/ktext/public/images/content/' .$fname;
$fh = fopen($file, 'w') or die("Can't open file");
fwrite($fh, $data);
echo "Data Written";
fclose($fh);
echo $file;

//DATABASE CONFIGURATION
$DBhost = 'localhost';
$DBuser = 'root';
$DBpass = 'password';
$DBName = 'ktext';

$aname = $_POST['ArtistName'];
$amessage = $_POST['Message'];
$bmessage = $_POST['Message'];
$fname = $_POST['Filename'];

$conn = mysql_connect($DBhost, $DBuser, $DBpass) or die('Cannot connect to the database because: ' .mysql_error());
mysql_select_db($DBName) or die('Database not exist. ' .mysql_error());

$query = "INSERT INTO inbox ('id', 'artist_id', 'date_in, content', 'preview', 'picture') VALUES ('$aname', '$amessage', '$bmessage', '$fname')";

$result = mysql_query($query) or die('Error updating database');

echo "Database Updated";

 

Hope you can help me....

[MasterACE14]

what's the error you're getting?

[Daniel0]

Maybe you could share that error message with us? We aren't psychic.

 

Also, see: mysql_real_escape_string

[xhelle]

Oh sorry for not including the error message. Here the message I get: "Error updating database". Basically the error it show came from this line ->$result = mysql_query($query) or die('Error updating database');

[Dathremar]

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

 

print that please

[xhelle]

Thanks for the reply, but I'm asking if my code or query is correct, because I can't update it's database. The data I need is came from a xml file, in that part I can save the file into it's designated path, but cannot save on database. Hope somebody can help me. Thanks in advance. >_____<

[Daniel0]

Well, maybe try to read your query:

 

INSERT INTO inbox ('id', 'artist_id', 'date_in, content', 'preview', 'picture') VALUES ('$aname', '$amessage', '$bmessage', '$fname')

 

First I notice you're doing like 'id', that is not the same as `id` or id. The last two are identifiers, the first one is a string. It's like $test vs. 'test' in PHP if that helps you understand it.

 

Then I notice this: 'date_in, content'. See anything wrong there (besides the incorrect quote character)?

 

I also notice that you have 5 (or 6 if you fix the above) identifiers, but only 4 values.

[xhelle]

I already correct this part and remove the id and date_in, since I don't know how to get this part.

 

Then I notice this: 'date_in, content'. See anything wrong there (besides the incorrect quote character)?

 

This is the error I get, when I run my code:

Data Written/var/www/ktext/public/images/content/KrisTest.jpgError updating databaseYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''artist_id', 'content', 'preview', 'picture') VALUES ('Kris', 'Test Message Test' at line 1

 

Thanks again to your asap responced ^_______^

[Daniel0]

You also need to fix this:

 

First I notice you're doing like 'id', that is not the same as `id` or id. The last two are identifiers, the first one is a string. It's like $test vs. 'test' in PHP if that helps you understand it.

[xhelle]

Hi again it seems the my problem is in this part

$query = "INSERT INTO inbox ('artist_id', 'content', 'preview', 'picture') VALUES ('$name', '$message', '$message', '$fname')";

I remove this '' on 

$query = "INSERT INTO inbox (artist_id, content, preview, picture) VALUES ('$name', '$message', '$message', '$fname')";

 

and now I have a new error:

Data Written/var/www/ktext/public/images/content/KrisTest.jpgCannot add or update a child row: a foreign key constraint fails (`ktext/inbox`, CONSTRAINT `FK_inbox` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`id`))

 

Can somebody help me...Thanks again.

[Daniel0]

Could you post the output of these queries?

 

SHOW CREATE TABLE inbox;
SHOW CREATE TABLE artist;

[xhelle]

Show create table inbox:

| inbox | CREATE TABLE `inbox` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `artist_id` int(11) unsigned NOT NULL,
  `date_in` datetime NOT NULL,
  `content` text NOT NULL,
  `preview` varchar(100) NOT NULL,
  `picture` varchar(150) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_inbox` (`artist_id`),
  CONSTRAINT `FK_inbox` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC | 

 

for artist:

| artist | CREATE TABLE `artist` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `icon` varchar(150) default NULL,
  `sub_count` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC |

 

To Daniel0 thanks for helping me out ^^

[Daniel0]

You have a constraint on inbox.artist_id to artist.id. This means that the value you use for a row in inbox.artist_id must correspond to a row in artist that has the same value in id.

[xhelle]

can you show me how to do it. im really new php and sql.

 

Thanks in advance..

[xhelle]

Hi Daniel,

 

My problem in inserting onto database, was already solve, thanks for your guide.