Topic: insert into problem

[iarnazca]

mysql version 5.1.30

this is the query that fails
It is

Code: [Select]

mysql_query('INSERT INTO bugs (id, reporter, title, desc) VALUES ('', '$username', '$title', '$desc')') or die('Could not insert data because '.mysql_error());

It does not return an error code.  Here is the entire script.

Code: [Select]

<?php 
session_start();
require_once("config.php"); 

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

$username = mysql_real_escape_string($_POST['reporter']);



// insert the data

$title = $_POST['title'];
$desc = $_POST['desc'];

$query = "mysql_query('INSERT INTO bugs (id, reporter, title, desc) VALUES ('', '$username', '$title', '$desc')') or die('Could not insert data because '.mysql_error())";
echo "Sent...";



?>

I get the "Sent..." output but no error and the info has not been inserted.

table structure

Code: [Select]

************************************** 1. row ***********************************
            Table: bugs
Create Table: CREATE TABLE 'bugs' (
     'id' int(32) NOT NULL AUTO_INCREMENT,
     'reporter' varchar(32) COLLATE utf8_unicode_ci NOT NULL,
     'title' varchar(32) COLLATE utf8_unicode_ci NOT NULL,
     'dec' varchar(32) COLLATE utf8_unicode_ci NOT NULL,
     PRIMARY KEY ('id')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00)



[sader]

$query = "mysql_query('INSERT INTO bugs (id, reporter, title, desc) VALUES ('', '$username', '$title', '$desc')') or die('Could not insert data because '.mysql_error())" ;

remove qoutes

[sader]

o I just saw that u will need change even more
like so

$query = mysql_query("INSERT INTO bugs (id, reporter, title, desc) VALUES ('', '$username', '$title', '$desc')") or die('Could not insert data because '.mysql_error());

[iarnazca]

thank you sader.

I used your line.

and now it reports the error.

Code: [Select]

Could not insert data because You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc) VALUES ('', 'nazca', 'test', 'test')' at line 1

Notice how it responded with the variables.

[iarnazca]

okay its fixed  what I did was use phpmyadmin to insert into mysql then copied the code it used.  I'll post them both here in comarison.  Not sure WHY it worked just that it did.  I think it was because it defined the database and the table. or declared NULL instead of '',

Code: [Select]

//saders code that helped it return the error.  Thanks agains sader.
$query = mysql_query("INSERT INTO bugs (id, reporter, title, desc) VALUES ('', '$username', '$title', '$desc')") or die('Could not insert data because '.mysql_error());

//phpmyadmin code that worked for some strange reason.
$query = mysql_query(" INSERT INTO `nazca_game`.`bugs` (`id` , `reporter` , `title` , `desc` ) VALUES ( NULL , '$username', '$title', '$desc' )") or die('Could not insert data because '.mysql_error()) ;


[gizmola]

The reason saders code didn't work is because of the first VALUES for 'id' where he passed  --  ''.  This is an empty string, but the id column is an integer type.  A string does not match an integer, so you got the error.  When you pass the NULL keyword as phpMyAdmin did, then it works ok, but the truth is you can omit the id entirely from the insert statement both in the column list and the values.  So this should work... (just small rwork of saders's code).


$query = mysql_query("INSERT INTO bugs (reporter, title, desc) VALUES ('$username', '$title', '$desc')") or die('Could not insert data because '.mysql_error());

[iarnazca]

it works perfectly now.  Thanks gizmola.