Won't insert to db properly!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Cyphonic
Forum Newbie
Posts: 13
Joined: Sun Aug 18, 2002 1:29 pm

Won't insert to db properly!

Post by Cyphonic »

I've just made a simple guestbook, and it just won't insert the values into the MySQL database!

What's wrong?

Code: Select all

<head>
<title>Jabben</title>
</head>
<? 
// db info
$server = "localhost";
$user = "xxxx";
$password = "xxxx";
$dbname = "xxxx";
// ------

if((isset($comment)) && (!empty($comment))&& ($loggeduser != "Nickname") && (!empty($loggeduser))) {
$comment = stripslashes($comment);
$comment = htmlspecialchars($comment);
$comment = str_replace("\n","<br>",$comment);


@ $db = mysql_pconnect($server, $user, $password);

mysql_select_db($dbname);

$datestamp = date("Y-m-d H:i:s");

$htuser = $_SERVERї'PHP_AUTH_USER'];

$query = "INSERT INTO messages (poster,datestamp,message,ipaddress) VALUES('$htuser','$datestamp','$comment','$REMOTE_ADDR')";

$result = mysql_query($query);
}


$connection = mysql_connect($server,$user,$password);
 $db = mysql_select_db($dbname, $connection);
 $sql = "select * from messages order by id desc limit 30";
 $result = mysql_query($sql, $connection);
 while($res=mysql_fetch_array($result)) {
 $dbcommentї]=$resї"message"];
 $dbauthorї]=$resї"poster"];
 $dbdatestampї]=$resї"datestamp"];
 }
 $loop = count($dbauthor);

?>
<table bgcolor="#000000" cellspacing="0" cellpadding="1" width="300" border="1" align="center">
  <tr>
    <td height="5"> <table bgcolor="#ffffff" cellspacing="0" cellpadding="2" width="300" border="0">
        <tr>
          <td><table width="300" border=0 align=center cellpadding=1 cellspacing=0 bordercolor="#A2C4FD">
              <form id="twall" name="twall" action="index.php" method="post">
                <tr>
                  <td align="center" bgcolor="#D2C8BD">
                    <div align="center">
                      <p><b> <font color="#000000">Jabben</font></b></p>
                      <table width="300" border="0" cellspacing="1">
                        <tr>
                          <td><b>Navn :</b></td>
                          <td>
                            <input type="text" size="30" maxlength="15" name="loggeduser" disabled value="<? echo $_SERVERї'PHP_AUTH_USER']; ?>">
                          </td>
                        </tr>
                        <tr>
                          <td><b>blah:</b></td>
                          <td>
                            <textarea cols="30" name="comment" rows="3" wrap="VIRTUAL"></textarea>
                          </td>
                        </tr>
                        <tr>
                          <td colspan="2">
                            <div align="center">
                              <input type="submit" name="Submit" value="Schmeck inn!">
                            </div>
                          </td>
                        </tr>
                      </table>
                      <p> </p>
                    </div>
                  </td>
                </tr>
                <tr>
                  <td> <table cellpadding=2 cellspacing=0 border=0 align="center" width="300">
                      <tr>
                        <td nowrap align=left width="210" class="datetxt"></td>
                        <td nowrap align=left></td>
                      </tr>

					  <? for($i=0;$i<$loop;$i++) {
						  if($i%2 == 0) { $tgwcolor = "#D2C8BD"; }
						  else { $tgwcolor = "#FFFFFF"; }
						  $dbcomment&#1111;$i] = ereg_replace ("<span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>", "^%&amp;^$@#&amp;", $dbcomment&#1111;$i]);
						  $dbcomment&#1111;$i] = ereg_replace ("<span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>", "^%&amp;^$@#&amp;", $dbcomment&#1111;$i]);
						  $dbcomment&#1111;$i] = ereg_replace ("ass", "^%&amp;^$@#&amp;", $dbcomment&#1111;$i]);


						  ?&gt;
					  &lt;tr bgcolor="&lt;?echo $tgwcolor;?&gt;"&gt;
                        &lt;td width="210" align=left nowrap class="datetxt"&gt;
                          &lt;b&gt;&lt;?echo $dbauthor&#1111;$i];?&gt;
                          @

                          &lt;?echo $dbdatestamp&#1111;$i];?&gt; &lt;/b&gt;
                          &lt;/td&gt;
                        &lt;td align=left nowrap&gt;
                          &lt;p&gt;
                        &lt;? $dbcomment&#1111;$i] = wordwrap($dbcomment&#1111;$i], 20, "&lt;br&gt;", 1);
                        echo $dbcomment&#1111;$i]; ?&gt;&lt;/p&gt;
                        &lt;/td&gt;
                      &lt;/tr&gt;
                     &lt;? } ?&gt;

                    &lt;/table&gt;&lt;/td&gt;
                &lt;/tr&gt;
                &lt;tr&gt;
                  &lt;td&gt; &lt;table cellpadding=0 cellspacing=0 border=0 align="left" width="100%"&gt;
                      &lt;tr&gt;
                        &lt;td colspan="2" bgcolor="#D2C8BD"&gt;
&lt;div align="center"&gt;&lt;/div&gt;
                        &lt;/td&gt;
                      &lt;/tr&gt;
                      &lt;tr&gt;
                        &lt;td colspan=2&gt;&lt;input name="image" type="image" value="images/pixel_black.gif" width="0" height="0"&gt;&lt;/td&gt;
                      &lt;/tr&gt;
                    &lt;/table&gt;&lt;/td&gt;
                &lt;/tr&gt;
              &lt;/form&gt;
            &lt;/table&gt;&lt;/td&gt;
        &lt;/tr&gt;
      &lt;/table&gt;&lt;/td&gt;
  &lt;/tr&gt;
&lt;/table&gt;
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

RE: Won't insert to db properly!

Post by MeOnTheW3 »

Your problem is with the quotes used in the INSERT statement. PHP offers the use of single and double quotes, but not at the same time nor interchangably <- is that even a word?

Crash course on PHP and quotes

Content residing in Single quotes is not parsed.
Contetn residing in Double quotes is.

Example:

Code: Select all

&lt;?php
   $a = 'abcdefg';
   $b = "$ahijklmnopqrstuvwxyz";     // abcdefghijklmnopqrstuvwxyz
   $c = '$ahijklmnopqrstuvwxyz';      // $ahijklmnopqrstuvwxyz
   $d = $a . 'hijklmnopqrstuvwxyz';   // abcdefghijklmnopqrstuvwxyz
   $e = $a . "hijklmnopqrstuvwxyz";  // abcdefghijklmnopqrstuvwxyz
?&gt;


YOUR CODE:

Code: Select all

&lt;?php
$query = "INSERT INTO messages (poster,datestamp,message,ipaddress) VALUES('$htuser','$datestamp','$comment','$REMOTE_ADDR')"; 
?&gt;
REVISED CODE;

Code: Select all

&lt;?php
$query = "INSERT INTO messages (poster,datestamp,message,ipaddress) VALUES('" . $htuser . "','" . $datestamp . "','" . $comment . "','" . $REMOTE_ADDR . "')"; 
?&gt;
OR

Code: Select all

&lt;?php
$query = 'INSERT INTO messages (poster,datestamp,message,ipaddress) VALUES(
('''.$htuser.''','''.$datestamp.''','''.$comment.''','''.$REMOTE_ADDR.''')'; 
?&gt;
The second of the revised would use less system resources as it would require no string processing.


MeOnTheW3 - Canada
Last edited by MeOnTheW3 on Thu Nov 14, 2002 11:39 am, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

the line

Code: Select all

$query = "INSERT INTO messages (poster,datestamp,message,ipaddress) VALUES('$htuser','$datestamp','$comment','$REMOTE_ADDR')";
is ok.
If there are string values (in opposite to numerical values) in VALUE(....) it is necessary to quote them for mysql with ' which means the ' character must reach the mysql-server.
and $string = "abc'de" is valid, it's just the string literal abc'de

Where does $REMOTE_ADDR come from? maybe $_SERVER['REMOTE_ADDR'] since you already use $_SERVER['PHP_AUTH_USER']

try

Code: Select all

$result = mysql_query($query) or die($query.' :'.mysql_error());
to see what mysql complains about ;)
Cyphonic
Forum Newbie
Posts: 13
Joined: Sun Aug 18, 2002 1:29 pm

Post by Cyphonic »

I've just tried all of your methods, but nothing seems to work! It just returns the same "posting" page!

Have a look: http://www.faltins.net/test.php

The username field is disabled here.

NOTICE, the script is in norwegian.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If this information is coming from a form have you read this:
viewtopic.php?t=511

Mac
Cyphonic
Forum Newbie
Posts: 13
Joined: Sun Aug 18, 2002 1:29 pm

Post by Cyphonic »

Yeah, I've read it.. The problem seems to be that no variables are getting through to the database!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Have you tried echoing out all the variables that are supposed to be coming from the form to make sure that they are there?

Mac
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

Post by MeOnTheW3 »

Yeah, test your variables. If you do not have "register_globals" set to "on" in your php.ini you will have to grab the variables like:

Code: Select all

&lt;?php
$comments = $HTTP_POST_VARS&#1111;'comments'];  // if you use POST method
$comments = $HTTP_GET_VARS&#1111;'comments'];  // if you use GET method
?&gt;
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you are using PHP 4.1 or above you can use $_POST instead of $HTTP_POST_VARS and $_GET instead of $HTTP_GET_VARS.

Mac
Post Reply