Data not going into SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

I've fixed the glitch in the observation, but I still get an error saying my query was empty, this is the output:

Code: Select all

SQL = INSERT INTO informes (`city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('Playa de Las Americas', '1', 'Palmeras Del Sur, Bloque C32', '00125', '006', '05698', 'Si', 'No', 'No', 'Si', 'Ninguno ', '83.53.199.156')
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Things have changed a bit... can you please repost your code as it stands now.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

Functions:

Code: Select all

function filled_out($form_vars) {
	
	foreach ($form_vars as $keys => $value) {
		if (!isset($key) || ($value == ''))
			return false;
		}
	return true;
}

function insert($city, $area, $street, $unit, $unitl, $incident, $property, $andiamos, $bbgs, $telxos, $observations, $ip) {
	
	include('variables.php');
	
	$conn = db_connect();
	if (!$conn)
		require_once('header.html');
		return '<div class="error">No se ha podido establecer una conexi&oacute;n a la base de datos. Por favor, int&eacute;ntelo m&aacute;s tarde.</div>';
		require_once('footer.html');
		
		$result = mysql_query("INSERT INTO informes (city, area, street, unit, unitl, incident, property, andiamos, bbgs, telxos, observations, ip) VALUES ('$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations', '$ip')");
		
		if (!$result)
		require_once('header.html');
		return '<div class="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
		$result = mysql_query($sql) or die(mysql_error());
		print $result;
		require_once('footer.html');
		return true;
	}
Processing script:
require_once('includes/global.php');

$city = $HTTP_POST_VARS['city'];
$street = $HTTP_POST_VARS['street'];
$unit = $HTTP_POST_VARS['unit'];
$unitl = $HTTP_POST_VARS['unitl'];
$incident = $HTTP_POST_VARS['incident'];
$property = $HTTP_POST_VARS['property'];
$andiamos = $HTTP_POST_VARS['andiamos'];
$bbgs = $HTTP_POST_VARS['bbgs'];
$telxos = $HTTP_POST_VARS['telxos'];
$observations = $HTTP_POST_VARS['observations'];
$ip = $REMOTE_ADDR;

$area = 1; /* Change this value! */

$insert_result = insert($city, $area, $street, $unit, $unitl, $incident, $property, $andiamos, $bbgs, $telxos, $observations, $ip);

if ($insert_result === true) {
require_once('header.html');
echo '<div class="error">&iexcl;La informaci&oacute;n que ha enviado ha sido a&ntilde;adida a la base de datos con &eacute;xito!</div>';
require_once('footer.html');
}

else {
require_once('header.html');
echo '<div class="error">No se ha podido guardar la informaci&oacute;n a la base de datos. Por favor, int&eacute;ntelo de nuevo.</div>';
mysql_query($sql) or die(mysql_error());
require_once('footer.html');
exit;
}
HTML of Form:
<div id="loginbox">
Rellene el formulario siguiente y a continuaci&oacute;n pulse agregar.
<form method="post" action="insert.php">
<div class="lalt">Cuidad:</div>
<div class="lalt"><input name="city" type="text" class="field" /></div>
<div class="lalt"> Direcci&oacute;n:</div>
<div class="lalt"><input name="street" type="text" class="field" /></div>
<div class="lalt">N&ordm; Soporte / Letra de Soporte :</div>
<div class="lalt"><input name="unit" type="text" class="field" size="10"/> / <input name="unitl" type="text" class="field" size="10"/></div>
<div class="lalt">Incidente:</div>
<div class="lalt"><input name="incident" type="text" class="field" /></div>
<div class="lalt">Propiedad:</div>
<div class="lalt"><select name="property"><option value="Si">Si</option><option value="No">No</option></select></div>
<div class="lalt">Pegatina de Andiamo :</div>
<div class="lalt"><select name="andiamos"><option value="Si">Si</option><option value="No">No</option></select></div>
<div class="lalt">Pegatina de BBG :</div>
<div class="lalt"><select name="bbgs"><option value="Si">Si</option><option value="No">No</option></select></div>
<div class="lalt">Pegatina de Telxo :</div>
<div class="lalt"><select name="telxos"><option value="Si">Si</option><option value="No">No</option></select></div>
<div class="lalt">Observaciones:</div>
<div class="lalt"><input name="observations" type="text" class="field" /></div>
<div class="lalt"><input type="image" src="images/agregar.gif" value="Autentificar" /></div>
</form>
</div>
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

I just submitted the form and I got the following output:
SQL = INSERT INTO informes (`city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('Playa de las Americas', '1', 'Palmeras Del Sur, Bloque C32', '001', '002', '003', 'Si', 'Si', 'No', 'No', 'Tiene una casas ', '83.53.199.156')
I went into phpMyAdmin and in the SQL option I put the following:
INSERT INTO informes (`city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('Playa de las Americas', '1', 'Palmeras Del Sur, Bloque C32', '001', '002', '003', 'Si', 'Si', 'No', 'No', 'Tiene una casas ', '83.53.199.156')
It successfuly went into the table, I don't see why this is causing such a problem... :x
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Code: Select all

$result = mysql_query("INSERT INTO `informes` (`id`, `city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('', '$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations ', '$ip');");
      if (!result)
      require_once('header.html');
      return '<div id="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
      require_once('footer.html');
try the following

Code: Select all

$result = mysql_query("INSERT INTO `informes` (`id`, `city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('', '$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations ', '$ip');");
      if (!mysql_affected_rows($conn)) {
        require_once('header.html');
        return '<div id="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
      } else {
        require_once('footer.html'); 
       ...
      }
OK change the brackets as needed to make it readable.
Key thing here is you are checking the number of rows affected by the insert (also works with update and delete) rather than the result. See what happens.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

CoderGoblin wrote:

Code: Select all

$result = mysql_query("INSERT INTO `informes` (`id`, `city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('', '$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations ', '$ip');");
      if (!result)
      require_once('header.html');
      return '<div id="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
      require_once('footer.html');
try the following

Code: Select all

$result = mysql_query("INSERT INTO `informes` (`id`, `city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('', '$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations ', '$ip');");
      if (!mysql_affected_rows($conn)) {
        require_once('header.html');
        return '<div id="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
      } else {
        require_once('footer.html'); 
       ...
      }
OK change the brackets as needed to make it readable.
Key thing here is you are checking the number of rows affected by the insert (also works with update and delete) rather than the result. See what happens.
It still doesn't work, I get an error saying the query was empty.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

I have uploaded all files in .zip format, you can download them from the following URL:

http://www.xirgo.net/portfolio/telerepo ... report.zip

In includes/global.php you have to edit the database settings. Here are the SQL quieries you can run:

Code: Select all

DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
  `id` tinyint(3) NOT NULL auto_increment,
  `user` tinytext NOT NULL,
  `pass` tinytext NOT NULL,
  `name` tinytext NOT NULL,
  `email` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `informes`;
CREATE TABLE `informes` (
  `id` tinyint(3) NOT NULL auto_increment,
  `city` tinytext NOT NULL,
  `area` tinytext NOT NULL,
  `street` mediumtext NOT NULL,
  `unit` tinytext NOT NULL,
  `unitl` tinytext NOT NULL,
  `incident` tinytext NOT NULL,
  `property` tinytext NOT NULL,
  `andiamos` tinytext NOT NULL,
  `bbgs` tinytext NOT NULL,
  `telxos` tinytext NOT NULL,
  `observations` tinytext NOT NULL,
  `ip` mediumtext NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=13 ;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` tinyint(3) NOT NULL auto_increment,
  `user` tinytext NOT NULL,
  `pass` tinytext NOT NULL,
  `region` tinytext NOT NULL,
  `name` tinytext NOT NULL,
  `email` tinytext NOT NULL,
  `dni` tinytext NOT NULL,
  `login` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

INSERT INTO `user` VALUES (1, 'demo', 'fe01ce2a7fbac8fafaed7c982a04e229', '', '', '', '', '0000-00-00 00:00:00');
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Code: Select all

function insert($city, $area, $street, $unit, $unitl, $incident, $property, $andiamos, $bbgs, $telxos, $observations, $ip) {
   
   include('variables.php');
   
   $conn = db_connect();
   if (!$conn)
      require_once('header.html');
      return '<div class="error">No se ha podido establecer una conexi&oacute;n a la base de datos. Por favor, int&eacute;ntelo m&aacute;s tarde.</div>';
      require_once('footer.html');
      
      $result = mysql_query("INSERT INTO informes (city, area, street, unit, unitl, incident, property, andiamos, bbgs, telxos, observations, ip) VALUES ('$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations', '$ip')");
      
      if (!mysql_affected_rows($result)) {
        echo("No result!");
        require_once('header.html');
        return '<div class="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
      } else {
        echo("SECOND SQL");
        $result = mysql_query($sql) or die(mysql_error());
        print $result;
        require_once('footer.html');
        return true;
     }
}
Are you sure the empty result is not from your second mysql_query (Cannot see you setting the $sql variable).

Unfortunately I am answering this at work and don't have MySQL to test any code.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

I'm not trying to insert 2 quieries, just one, this maybe causing the problem but I don't see where I have gone wrong in my code. All I'm trying to insert is the data that is in the form to the table 'informes'.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Is the error relating to the first or second SQL Request. As stated the second one will be empty as there is no $sql variable set. Is there any content in the table. (Manually check, not through PHP).

I simply want to check I am looking in the correct place. :wink:
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

No, there's no content in the table. Also, if I removed the second query, would my code look like this ?

Code: Select all

function insert($city, $area, $street, $unit, $unitl, $incident, $property, $andiamos, $bbgs, $telxos, $observations, $ip) {
   
   include('variables.php');
   
   $conn = db_connect();
   if (!$conn)
      require_once('header.html');
      return '<div class="error">No se ha podido establecer una conexi&oacute;n a la base de datos. Por favor, int&eacute;ntelo m&aacute;s tarde.</div>';
      require_once('footer.html');
      
      $result = mysql_query("INSERT INTO informes (city, area, street, unit, unitl, incident, property, andiamos, bbgs, telxos, observations, ip) VALUES ('$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations', '$ip')");
      
      if (!mysql_affected_rows($result)) {
        echo("No result!");
        require_once('header.html');
        return '<div class="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
        require_once('footer.html');
        return true;
     }
}
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

TRY

Code: Select all

function insert($city, $area, $street, $unit, $unitl, $incident, $property, $andiamos, $bbgs, $telxos, $observations, $ip) {
   
   include('variables.php');
   
   $conn = db_connect();
   if (!$conn) {
     echo("No DB connection !");
     return '<div class="error">No se ha podido establecer una conexi&oacute;n a la base de datos. Por favor, int&eacute;ntelo m&aacute;s tarde.</div>';
   } else {  
      $result = mysql_query($conn,"INSERT INTO informes (city, area, street, unit, unitl, incident, property, andiamos, bbgs, telxos, observations, ip) VALUES ('$city', '$area', '$street', '$unit', '$unitl', '$incident', '$property', '$andiamos', '$bbgs', '$telxos', '$observations', '$ip')");
     
      if (!mysql_affected_rows($result)) {
        echo("RESULT FAILED");
        return '<div class="error">No se ha podido agregar la informaci&oacture;n a la base de datos.</div>';
      } else {
        echo("RESULT OK");
        return "";
     }
  }
}
You notice I have removed the include of the header and footer. This is better in the calling section. Remember no code is executed within a function after return.

The calling section would then check the return value to decide what needs to be output.

Generally use if/else structures with brackets and it is always a good idea to have one return value type, don't mix boolean and strings.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

I get the following error now:
SQL = INSERT INTO informes (`city`, `area`, `street`, `unit`, `unitl`, `incident`, `property`, `andiamos`, `bbgs`, `telxos`, `observations`, `ip`) VALUES ('0125', '1', '0125', '0125', '0125', '0125', 'Si', 'Si', 'Si', 'Si', '0125 ', '83.53.199.156')
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/virtual/site117/fst/var/www/html/portfolio/telereport/includes/global.php on line 75

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/virtual/site117/fst/var/www/html/portfolio/telereport/includes/global.php on line 77
RESULT FAILED
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Can you post your db_connect function (replace db connection parameters with **** whatever as we don't need to know your dbhost/username/password etc).

It looks as though the return of the function is wrong but a value (possibly a string?)
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

Code: Select all

function db_connect() {
	
	$result = mysql_pconnect('localhost','*******','*********');
	
	if (!result)
		return false;
	if (!mysql_select_db('xirgo_net_-_telereport'))
		return false;
		
	return $result;
	}
Post Reply