problem i can't resolve

Mr Blockeel · 01-05-2009, 04:04 PM

have been looking at this problem for 2 hours, and can't figure it out

i have a problem inserting data to my database
it's for a pm system

Database

Code:

CREATE TABLE IF NOT EXISTS `private` (
  `id` int(11) NOT NULL auto_increment,
  `to` varchar(100) NOT NULL,
  `from` varchar(100) default NULL,
  `subject` varchar(100) default NULL,
  `message` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

PHP Code:

  <?php include ("../includes/db_connect.php") ; ?>

<?php 

 
if (isset($_POST['submit'])) { 

        

    // check if username exists in database.

 
    if (!get_magic_quotes_gpc()) {

        $_POST['sendto'] = addslashes($_POST['sendto']);

    }

 
    $qry = "SELECT username FROM users WHERE username = '".$_POST['sendto']."'";

    $name_check = $db_object->query($qry);

 
    if (DB::isError($name_check)) {

        die($name_check->getMessage());

    }

 
    $name_checkk = $name_check->numRows();

 
    if ($name_checkk = 0) {

            header('Location: compose.php?error=Sorry, the user: <strong>'.$_POST['sendto'].'</strong>'

          . ' does not exist.');

          exit;

    }

    

        // no HTML tags in username, website, location, password

 
    $_POST['sendto'] = strip_tags($_POST['sendto']);

    $_POST['from'] = strip_tags($_POST['from']);

    $_POST['subject'] = strip_tags($_POST['subject']);

    $_POST['message'] = strip_tags($_POST['message']);

    

    

    if (!get_magic_quotes_gpc()) {

        $_POST['subject'] = addslashes($_POST['subject']);

        $_POST['message'] = addslashes($_POST['message']);

    }

    

 
 
    $insert = "INSERT INTO pm (

            from, 

            to, 

            subject, 

            message) 

            VALUES (

            '$_POST['from']', 

            '$_POST['sendto']', 

            '$_POST['subject']', 

            '$_POST['message']')";

 
    $send_message = $db_object->query($insert);

 
    if (DB::isError($send_message)) {

        die($send_message->getMessage());

    }

 
    $db_object->disconnect();

    

    header('Location: ../index.php');

    }

?>

even this won't work, with fixed values

PHP Code:

  <?php require('db_connect.php'); ?>

<?php

 
    $insert = "INSERT INTO pm ( from, sendto, subject, message) 

            VALUES ( '1', '2', 'test', 'message')";

 
    $send_message = $db_object->query($insert);

 
    if (DB::isError($send_message)) {

????: NamePros.com http://www.namepros.com/code/548209-problem-i-cant-resolve.html
        die($send_message->getMessage());

    }

 
    $db_object->disconnect();

    

    header('Location: ../index.php');

????: NamePros.com http://www.namepros.com/showthread.php?t=548209
    

?>

pixelhero · 01-05-2009, 04:07 PM

swap around the sql statement for INSERT so that it follows order of the table.

Code:

 $insert = "INSERT INTO pm (
            to,
            from,
            subject,
            message)
            VALUES (
            '$_POST['sendto']',
            '$_POST['from']',
            '$_POST['subject']',
            '$_POST['message']')";

fixed mistakes. sorry

Peter · 01-05-2009, 04:10 PM

the 2nd code snippet will not work as you are trying to insert a value in a field called sento yet there is no field called that, in fact it is called simply to.

pixelhero · 01-05-2009, 04:15 PM

fixed mistake on mine.

Mr Blockeel · 01-05-2009, 04:30 PM

is it normal that with this as a database

Code:

CREATE TABLE IF NOT EXISTS `pm` (
  `id` int(11) NOT NULL auto_increment,
  `from` varchar(100) default NULL,
  `sendto` varchar(100) default NULL,
  `subject` varchar(100) default NULL,
  `message` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

even this won't work
????: NamePros.com http://www.namepros.com/showthread.php?t=548209

PHP Code:

  $query = "INSERT INTO pm (from, sendto, subject, message)

VALUES ('from', 'sendto', 'subject', 'message')";

 
    mysql_query($query) or die('Error ,query failed');

it returns Error ,query failed

pixelhero · 01-05-2009, 04:50 PM

Ignore this a sec.

Mr Blockeel · 01-05-2009, 05:03 PM

PHP Code:

 
<form action="send.php" method="post" class="register_form">

 
<input name="from" type="hidden" value="<?php echo $_SESSION['username']; ?>">

 
<label>Send to : </label> 

<input type="text" name="sendto" value="<?php print $_GET["sendto"] ; ?>" class="txtBoxWide">

????: NamePros.com http://www.namepros.com/showthread.php?t=548209
<label>Subject : </label>

<input type="text" name="subject" maxlength="100" class="txtBoxWide"><br>

 
<label>Message :</label>

<textarea name="message" id="txtAreaWide" cols="" rows=""></textarea>

 
    <input type="submit" name="submit" value="Verstuur" />

 
<br class="spacer" />

</form>

pixelhero · 01-05-2009, 05:12 PM

Try changing this again to the following:

Code:

 $insert = "INSERT INTO pm (
            to,
            from,
            subject,
            message)
            VALUES (
            '$_POST["sendto"]',
            '$_POST["from"]',
            '$_POST["subject"]',
            '$_POST["message"]')";

You had single quotes between where they should be

Mr Blockeel · 01-05-2009, 05:17 PM

no; that's not necessary, because i used this script i wrote and adjusted it
and here the single quotes are no problem

PHP Code:

  <?php require('db_connect.php'); ?>

????: NamePros.com http://www.namepros.com/showthread.php?t=548209
<?php

 
if (isset($_POST['submit'])) { // if form has been submitted

 
 
    if (!$_POST['uname'] || !$_POST['passwd'] ||

        !$_POST['passwd_again'] || !$_POST['email']) {

            header('Location: ../register.php?error=You did not fill in a required field.');

            exit;

    }

 
    // check if username exists in database.

 
    if (!get_magic_quotes_gpc()) {

        $_POST['uname'] = addslashes($_POST['uname']);

    }

 
    $qry = "SELECT username FROM users WHERE username = '".$_POST['uname']."'";

    $name_check = $db_object->query($qry);

 
    if (DB::isError($name_check)) {

        die($name_check->getMessage());

    }

 
    $name_checkk = $name_check->numRows();

 
    if ($name_checkk != 0) {

            header('Location: ../register.php?error=Sorry, the username: <strong>'.$_POST['uname'].'</strong>'

          . ' is already taken, please pick another one.');

          exit;

    }

 
    // check passwords match

 
    if ($_POST['passwd'] != $_POST['passwd_again']) {

            header('Location: ../register.php?error=Passwords did not match.');

            exit;

    }

 
    // check e-mail format

 
    if (!preg_match("/.*@.*..*/", $_POST['email']) ||

         preg_match("/(<|>)/", $_POST['email'])) {

            header('Location: ../register.php?error=Invalid e-mail address.');

            exit;

    }

 
    // no HTML tags in username, website, location, password

 
    $_POST['uname'] = strip_tags($_POST['uname']);

    $_POST['passwd'] = strip_tags($_POST['passwd']);

    $_POST['location'] = strip_tags($_POST['location']);

 
    // check show_email data

 
    if ($_POST['show_email'] != 0 & $_POST['show_email'] != 1) {

        die('Nope');

    }

 
 
    // now we can add them to the database.

    // encrypt password

 
    $_POST['passwd'] = md5($_POST['passwd']);

????: NamePros.com http://www.namepros.com/showthread.php?t=548209
 
    if (!get_magic_quotes_gpc()) {

        $_POST['passwd'] = addslashes($_POST['passwd']);

        $_POST['email'] = addslashes($_POST['email']);

        $_POST['location'] = addslashes($_POST['location']);

    }

 
    $regdate = date("Y-m-d"); 

 
    $insert = "INSERT INTO users (

            username, 

            password, 

            regdate, 

            email, 

            location, 

            show_email, 

            last_login) 

            VALUES (

            '".$_POST['uname']."', 

            '".$_POST['passwd']."', 

            '$regdate', 

            '".$_POST['email']."', 

            '".$_POST['location']."', 

            '".$_POST['show_email']."', 

            'Never')";

 
    $add_member = $db_object->query($insert);

 
    if (DB::isError($add_member)) {

        die($add_member->getMessage());

    }

 
    $db_object->disconnect();

    

    header('Location: ../reg_succes.php');

    }

?>

pixelhero · 01-05-2009, 05:21 PM

Your using ". and ." to escape the clashes in quotes in that example. Which you are not using in the problematic version.

Mr Blockeel · 01-05-2009, 06:06 PM

re-wrote it for the biggest part and now it works,
i added someting to the querry and seems that that makes a big diffrence
don't knwo why, because in the similar script it isn't necessery

PHP Code:

      $insert = "INSERT INTO `pm` ( `id` , `from` , `to` , `subject` , `message` )

        VALUES ( NULL , '".$_POST['from']."', '".$_POST['to']."', '".$_POST['subject']."', '".$_POST['message']."'

????: NamePros.com http://www.namepros.com/showthread.php?t=548209
        )";

the id >> NULL sems to make the diffrence

NVD, thanks for the help

pixelhero · 01-05-2009, 06:43 PM

Strange that it requires that.... but glad its working

Dave · 01-06-2009, 07:59 AM

I have noticed how you are using type varchar for the to and from field. This is poor practice. They should be foreign keys that reference the id of your members table. Also if the id of the private table is set to auto increment there is no need to include it as part of the sql query.

Peter · 01-06-2009, 02:20 PM

he is using MyISAM tables. Foerign keys are not supported in MyISAM, if he requires foreign keys he would require innodb

However I do agree the to and from should have the id's assigned to the user in the users table if such a table exists.

Dave · 01-07-2009, 07:04 AM

Originally Posted by Peter

he is using MyISAM tables. Foerign keys are not supported in MyISAM, if he requires foreign keys he would require innodb

However I do agree the to and from should have the id's assigned to the user in the users table if such a table exists.

Ah yes well spotted Peter.

01-05-2009, 04:04 PM	THREAD STARTER #1 (permalink)
Mr Blockeel NamePros Regular Join Date: Oct 2006 Location: Ghent, Belgium Posts: 489	problem i can't resolve have been looking at this problem for 2 hours, and can't figure it out i have a problem inserting data to my database it's for a pm system Database Code: CREATE TABLE IF NOT EXISTS `private` ( `id` int(11) NOT NULL auto_increment, `to` varchar(100) NOT NULL, `from` varchar(100) default NULL, `subject` varchar(100) default NULL, `message` longtext, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; PHP Code: <?php include ("../includes/db_connect.php") ; ?> <?php if (isset($_POST['submit'])) { // check if username exists in database. if (!get_magic_quotes_gpc()) { $_POST['sendto'] = addslashes($_POST['sendto']); } $qry = "SELECT username FROM users WHERE username = '".$_POST['sendto']."'"; $name_check = $db_object->query($qry); if (DB::isError($name_check)) { die($name_check->getMessage()); } $name_checkk = $name_check->numRows(); if ($name_checkk = 0) { header('Location: compose.php?error=Sorry, the user: <strong>'.$_POST['sendto'].'</strong>' . ' does not exist.'); exit; } // no HTML tags in username, website, location, password $_POST['sendto'] = strip_tags($_POST['sendto']); $_POST['from'] = strip_tags($_POST['from']); $_POST['subject'] = strip_tags($_POST['subject']); $_POST['message'] = strip_tags($_POST['message']); if (!get_magic_quotes_gpc()) { $_POST['subject'] = addslashes($_POST['subject']); $_POST['message'] = addslashes($_POST['message']); } $insert = "INSERT INTO pm ( from, to, subject, message) VALUES ( '$_POST['from']', '$_POST['sendto']', '$_POST['subject']', '$_POST['message']')"; $send_message = $db_object->query($insert); if (DB::isError($send_message)) { die($send_message->getMessage()); } $db_object->disconnect(); header('Location: ../index.php'); } ?> even this won't work, with fixed values PHP Code: <?php require('db_connect.php'); ?> <?php $insert = "INSERT INTO pm ( from, sendto, subject, message) VALUES ( '1', '2', 'test', 'message')"; $send_message = $db_object->query($insert); if (DB::isError($send_message)) { ????: NamePros.com http://www.namepros.com/code/548209-problem-i-cant-resolve.html die($send_message->getMessage()); } $db_object->disconnect(); header('Location: ../index.php'); ????: NamePros.com http://www.namepros.com/showthread.php?t=548209 ?> __________________ www.mrblockeel.com

01-05-2009, 04:07 PM	#2 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	swap around the sql statement for INSERT so that it follows order of the table. Code: $insert = "INSERT INTO pm ( to, from, subject, message) VALUES ( '$_POST['sendto']', '$_POST['from']', '$_POST['subject']', '$_POST['message']')"; fixed mistakes. sorry __________________ pixelhero Web Development Studios UK Last edited by pixelhero; 01-05-2009 at 04:14 PM.

01-05-2009, 04:10 PM	#3 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,069	the 2nd code snippet will not work as you are trying to insert a value in a field called sento yet there is no field called that, in fact it is called simply to. __________________ Manage your portfolio using my new Domain Portfolio Management script. Securing Your Domain Name From Theft

01-05-2009, 04:15 PM	#4 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	fixed mistake on mine. __________________ pixelhero Web Development Studios UK

01-05-2009, 04:30 PM	THREAD STARTER #5 (permalink)
Mr Blockeel NamePros Regular Join Date: Oct 2006 Location: Ghent, Belgium Posts: 489	is it normal that with this as a database Code: CREATE TABLE IF NOT EXISTS `pm` ( `id` int(11) NOT NULL auto_increment, `from` varchar(100) default NULL, `sendto` varchar(100) default NULL, `subject` varchar(100) default NULL, `message` longtext, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; even this won't work ????: NamePros.com http://www.namepros.com/showthread.php?t=548209 PHP Code: `$query = "INSERT INTO pm (from, sendto, subject, message) VALUES ('from', 'sendto', 'subject', 'message')"; mysql_query($query) or die('Error ,query failed');` it returns Error ,query failed __________________ www.mrblockeel.com Last edited by Mr Blockeel; 01-05-2009 at 04:46 PM.

01-05-2009, 04:50 PM	#6 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	Ignore this a sec. __________________ pixelhero Web Development Studios UK

01-05-2009, 05:03 PM	THREAD STARTER #7 (permalink)
Mr Blockeel NamePros Regular Join Date: Oct 2006 Location: Ghent, Belgium Posts: 489	PHP Code: <form action="send.php" method="post" class="register_form"> <input name="from" type="hidden" value="<?php echo $_SESSION['username']; ?>"> <label>Send to : </label> <input type="text" name="sendto" value="<?php print $_GET["sendto"] ; ?>" class="txtBoxWide"> ????: NamePros.com http://www.namepros.com/showthread.php?t=548209 <label>Subject : </label> <input type="text" name="subject" maxlength="100" class="txtBoxWide"><br> <label>Message :</label> <textarea name="message" id="txtAreaWide" cols="" rows=""></textarea> <input type="submit" name="submit" value="Verstuur" /> <br class="spacer" /> </form> __________________ www.mrblockeel.com

01-05-2009, 05:12 PM	#8 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	Try changing this again to the following: Code: $insert = "INSERT INTO pm ( to, from, subject, message) VALUES ( '$_POST["sendto"]', '$_POST["from"]', '$_POST["subject"]', '$_POST["message"]')"; You had single quotes between where they should be __________________ pixelhero Web Development Studios UK

01-05-2009, 05:17 PM	THREAD STARTER #9 (permalink)
Mr Blockeel NamePros Regular Join Date: Oct 2006 Location: Ghent, Belgium Posts: 489	no; that's not necessary, because i used this script i wrote and adjusted it and here the single quotes are no problem PHP Code: <?php require('db_connect.php'); ?> ????: NamePros.com http://www.namepros.com/showthread.php?t=548209 <?php if (isset($_POST['submit'])) { // if form has been submitted if (!$_POST['uname'] \|\| !$_POST['passwd'] \|\| !$_POST['passwd_again'] \|\| !$_POST['email']) { header('Location: ../register.php?error=You did not fill in a required field.'); exit; } // check if username exists in database. if (!get_magic_quotes_gpc()) { $_POST['uname'] = addslashes($_POST['uname']); } $qry = "SELECT username FROM users WHERE username = '".$_POST['uname']."'"; $name_check = $db_object->query($qry); if (DB::isError($name_check)) { die($name_check->getMessage()); } $name_checkk = $name_check->numRows(); if ($name_checkk != 0) { header('Location: ../register.php?error=Sorry, the username: <strong>'.$_POST['uname'].'</strong>' . ' is already taken, please pick another one.'); exit; } // check passwords match if ($_POST['passwd'] != $_POST['passwd_again']) { header('Location: ../register.php?error=Passwords did not match.'); exit; } // check e-mail format if (!preg_match("/.@.../", $_POST['email']) \|\| preg_match("/(<\|>)/", $_POST['email'])) { header('Location: ../register.php?error=Invalid e-mail address.'); exit; } // no HTML tags in username, website, location, password $_POST['uname'] = strip_tags($_POST['uname']); $_POST['passwd'] = strip_tags($_POST['passwd']); $_POST['location'] = strip_tags($_POST['location']); // check show_email data if ($_POST['show_email'] != 0 & $_POST['show_email'] != 1) { die('Nope'); } // now we can add them to the database. // encrypt password $_POST['passwd'] = md5($_POST['passwd']); ????: NamePros.com http://www.namepros.com/showthread.php?t=548209 if (!get_magic_quotes_gpc()) { $_POST['passwd'] = addslashes($_POST['passwd']); $_POST['email'] = addslashes($_POST['email']); $_POST['location'] = addslashes($_POST['location']); } $regdate = date("Y-m-d"); $insert = "INSERT INTO users ( username, password, regdate, email, location, show_email, last_login) VALUES ( '".$_POST['uname']."', '".$_POST['passwd']."', '$regdate', '".$_POST['email']."', '".$_POST['location']."', '".$_POST['show_email']."', 'Never')"; $add_member = $db_object->query($insert); if (DB::isError($add_member)) { die($add_member->getMessage()); } $db_object->disconnect(); header('Location: ../reg_succes.php'); } ?> __________________ www.mrblockeel.com*

01-05-2009, 05:21 PM	#10 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	Your using ". and ." to escape the clashes in quotes in that example. Which you are not using in the problematic version. __________________ pixelhero Web Development Studios UK

01-05-2009, 06:06 PM	THREAD STARTER #11 (permalink)
Mr Blockeel NamePros Regular Join Date: Oct 2006 Location: Ghent, Belgium Posts: 489	re-wrote it for the biggest part and now it works, i added someting to the querry and seems that that makes a big diffrence don't knwo why, because in the similar script it isn't necessery PHP Code: $insert = "INSERT INTO `pm` ( `id` , `from` , `to` , `subject` , `message` ) VALUES ( NULL , '".$_POST['from']."', '".$_POST['to']."', '".$_POST['subject']."', '".$_POST['message']."' ????: NamePros.com http://www.namepros.com/showthread.php?t=548209 )"; the id >> NULL sems to make the diffrence NVD, thanks for the help __________________ www.mrblockeel.com

01-05-2009, 06:43 PM	#12 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	Strange that it requires that.... but glad its working __________________ pixelhero Web Development Studios UK

01-06-2009, 07:59 AM	#13 (permalink)
Dave Senior Member Join Date: Jun 2007 Location: NamePros.com Posts: 1,400	I have noticed how you are using type varchar for the to and from field. This is poor practice. They should be foreign keys that reference the id of your members table. Also if the id of the private table is set to auto increment there is no need to include it as part of the sql query.

01-06-2009, 02:20 PM	#14 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,069	he is using MyISAM tables. Foerign keys are not supported in MyISAM, if he requires foreign keys he would require innodb However I do agree the to and from should have the id's assigned to the user in the users table if such a table exists. __________________ Manage your portfolio using my new Domain Portfolio Management script. Securing Your Domain Name From Theft

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)