Home » Database » DB » Bug #575

[2004-01-14 13:56 UTC] andreas dot schneider at scriptwriter dot ch

Description:
------------
Hi!
I Think there must be an error with the autoprepare. I'm getting my parameters by an formular and there is also an select-field. if there are no options in, then no value is set for my parameter "land". var_dump gives me NULL. But this NULL makes, that Query is not executed.

Reproduce code:
---------------
require_once 'DB.php';
$dsn = "mysql://$mysql_user:$mysql_pw@$mysql_host/$mysql_db";
$table_fields = array('name','strasse', 'land','plz');
$table_values = array($form->exportValue('name'),$form->exportValue('strasse'),$form->exportValue('land'),$_REQUEST['plz']);
$sth = $db->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_UPDATE, "id=gebaeude_id");

[2004-01-16 07:18 UTC] andreas dot schneider at scriptwriter dot ch

OK, sorry, but i will hope to give you all information, you will need. At first here the dump of the table from phpmyadmin. Next I will give you a script, so you don't have to know something about my system.

Thank you for your help.
#################################################
# phpMyAdmin SQL Dump
# version 2.5.2-rc1
# http://www.phpmyadmin.net
#
# Host: localhost
# Erstellungszeit: 16. Januar 2004 um 08:21
# Server Version: 4.0.12
# PHP-Version: 4.3.1
#
# Datenbank: `erp`
#

# --------------------------------------------------------

#
# Tabellenstruktur für Tabelle `rauv_gebaeude`
#
# Erzeugt am: 08. Januar 2004 um 18:22
# Aktualisiert am: 15. Januar 2004 um 14:50
#

CREATE TABLE `rauv_gebaeude` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`strasse` varchar(50) NOT NULL default '',
`land` varchar(5) NOT NULL default '',
`plz` varchar(6) NOT NULL default '',
`ort` varchar(20) NOT NULL default '',
`unterste_etage` int(11) NOT NULL default '0',
`oberste_etage` int(11) NOT NULL default '0',
`bemerkung` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=18 ;

[2004-01-16 07:43 UTC] andreas dot schneider at scriptwriter dot ch

At first here you get the script. in my config.inc.php only the access-dates for the mysql-database are set. that you can do for yours directly in the $dsn.

<?php
require_once 'DB.php';
include_once "config.inc.php";//here only variables for the dsn are set (user/pwd)

$dsn = "mysql://$mysql_user:$mysql_pw@$mysql_host/$mysql_db";
$db = DB::connect($dsn);
if (DB::isError($db)) {
die ($db->getMessage());
}

require_once 'DB.php';
$dsn = "mysql://$mysql_user:$mysql_pw@$mysql_host/$mysql_db";
$table_fields = array('name','strasse', 'land','plz');
// $table_values = array('testname','teststrasse','sss','testplz');
$table_values = array('testname','teststrasse',$_REQUEST['fehler'],'testplz');

var_dump($table_fields);
var_dump($table_values);
$table_name = 'rauv_gebaeude';

$sth = $db->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_INSERT);
$db->execute($sth, $table_values);
?>

Here is the output of the vardump, if nothing is given for the cgi-parameter land. it displays then NULL and the execute failed.

array(4) {
[0]=>
string(4) "name"
[1]=>
string(7) "strasse"
[2]=>
string(4) "land"
[3]=>
string(3) "plz"
}
array(4) {
[0]=>
string(8) "testname"
[1]=>
string(11) "teststrasse"
[2]=>
NULL
[3]=>
string(7) "testplz"
}

I hope, that you can reproduce the error with that informations and the former database-dump. Thanx for your help!

[2004-01-20 07:56 UTC] andreas dot schneider at scriptwriter dot ch

I'M very sorry to took your time!

I did a script with a testtable (you'll find bottom) with only 2 cols. And you opened my eyes, because I've seen, that the problem is the NOT NULL in the col I wanted to fill.

I hope, you can forgive me, but I learned something.

Best regards, Andreas

########### new testscript ################################

<?php
require_once 'DB.php';
//here only variables for the dsn are set (user/pwd)
include_once "config.inc.php";
$dsn = "mysql://$mysql_user:$mysql_pw@$mysql_host/$mysql_db";
//tablename for the tests
$table_name = 'test';
//connecting database
$db = DB::connect($dsn);
//create table
// $sql = "CREATE TABLE $table_name ( id int(11) NOT NULL auto_increment, name varchar(50) NOT NULL default '', PRIMARY KEY (id))";
$sql = "CREATE TABLE $table_name ( id int(11) NOT NULL auto_increment, name varchar(50) NULL default '', PRIMARY KEY (id))";
$result = $db->query($sql);
//prepare autoquery
$table_fields = array('name');
$table_values = array($_REQUEST['testname']);
var_dump($table_fields);
var_dump($table_values);
//make the autoquery
$sth = $db->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_INSERT);
$result=$db->execute($sth, $table_values);
//getting possible error-output
if (DB::isError($result)) {
print $result->getMessage();
}
//drop table
$sql = "DROP TABLE $table_name";
$result = $db->query($sql);

?>