insert that seems to work half the time

m3rajk posted this at 21:41 — 22nd January 2004.

Joined: Jul 2003

i have an insert statement that seems to only work half the time. it's importtant because it's called by a signup script. i'm far from a db expert and would love to know what others think might be going on since i'm out of ideas on how to fix it.

function that calls the db (note: interests table works half the time. bio table always works)

<?php
function bntrstadd(){ # add the bio and interests
  include(\"/home/dcfydllc/includes/fyd.altincs.php\"); # includes file (precautionary measure)
  $uid=$_POST['uid']; # set the uid so this part is synched
  # variables for bio/interests adding
  $bq1=clean($_POST['bq1']); $bq2=clean($_POST['bq2']); $bq3=clean($_POST['bq3']); 
$bq4=clean($_POST['bq4']); $auth=clean($_POST['auth']); $bio=clean($_POST['bio']); 
$iiif='uid'; $iiiv=\"'$uid'\"; # answers to bio questions 1-4, an author for the quote, 
#and a space to write a open bio || two variable for interests (Insert Into Interest 
#Fields/Values)
  
  /* if the interest is filled out, add to the insert statement section */
  if(isset($_POST['books'])){ // if any catagories of books were selected
    $books=clean(join(';',$_POST['books'])); // clean the entry
    $iiif.=', books'; $iiiv.=\", '$books'\"; } // add it to the insert statement
  if(isset($_POST['hobbies'])){ // if any catagories of hobbies were selected
    $hobbies=clean(join(';',$_POST['hobbies'])); // clean the entry
    $iiif.=', hobbies'; $iiiv.=\", '$hobbies'\"; } // add it to the insert statement
  if(isset($_POST['misc'])){ // if any catagories of misc were selected
    $misc=clean(join(';',$_POST['misc'])); // clean the entry
    $iiif.=', misc'; $iiiv.=\", '$misc'\"; } // add it to the insert statement
  if(isset($_POST['movies'])){ // if any catagories of movies were selected
    $movies=clean(join(';',$_POST['movies'])); // clean the entry
    $iiif.=', movies'; $iiiv.=\", '$movies'\"; } // add it to the insert statement
  if(isset($_POST['music'])){ // if any catagories of music were selected
    $movies=clean(join(';',$_POST['music'])); // clean the entry
    $iiif.=', music,'; $iiiv.=\", '$music'\"; } // add it to the insert statement
  if(isset($_POST['pets'])){ // if any catagories of pets were selected
    $pets=clean(join(';',$_POST['pets'])); // clean the entry
    $iiif.=', pets'; $iiiv.=\", '$pets'\"; } // add it to the insert statement
  if(isset($_POST['sports'])){ // if any catagories of sports were selected
    $sports=clean(join(';',$_POST['sports'])); // clean the entry
    $iiif.=', sports'; $iiiv.=\", '$sports'\"; } // add it to the insert statement
  if(isset($_POST['vacation'])){ // if any catagories of vacations were selected
    $vacation=clean(join(';',$_POST['vacation'])); // clean the entry
    $iiif.=', vacation'; $iiiv.=\", '$vacation'\"; } // add it to the insert statement
  
  # connect to db and add bio and interest table entries
  $db=mysql_connect($host, $login1, $pass1) or die(\"cannot access mysql\"); # connect
  $fyd=mysql_select_db($dbname, $db) or die(\"cannot access db\"); # select the db
  $addbio=mysql_query(\"INSERT INTO bio (uid, bq1, bq2, bq3, bq4, auth, bio) VALUES 
('$uid', '$bq1', '$bq2', '$bq3', '$bq4', '$auth', '$bio')\", $db); 
# insert bio info into bio table
  $addinterests=mysql_query(\"INSERT INTO interests ($iiif) VALUES ($iiiv)\", $db); 
# insert interests into interests table
}
?>

function that creates the page that submits that (note: all it's values that require something in the $_POST array have that. that much was tested for)

<?php
function bntrst($uid){ # get the bio and interests
  include(\"/home/dcfydllc/includes/fyd.altincs.php\"); 
# includes file (precautionary measure)
  $bookopts=arraysToOptions($books, array('','')); 
$miscopts=arraysToOptions($misc, array('',''));
  $movieopts=arraysToOptions($movies, array('','')); 
$musicopts=arraysToOptions($music, array('',''));
  $petopts=arraysToOptions($pets, array('','')); 
$sportopts=arraysToOptions($sports, array('',''));
  $vacaopts=arraysToOptions($vacations, array('','')); 
$hobbyopts=arraysToOptions($hobbies, array('',''));
  echo <<<END
    <p>Thank you for your interest in FindYourDesire.com.  
The questions will be displayed in your profile.  (X)HTML is not 
supported in the answers to the questions.  The interests are used in the
 advanced search.  Both are optional.</p>
    <form action=\"{$_SERVER['PHP_SELF']}\" method=\"POST\">
      <input id=\"step\" name=\"step\" type=\"hidden\" value=\"2\" />
      <input id=\"uid\" name=\"uid\" type=\"hidden\" value=\"$uid\" />
      <input id=\"un\" name=\"un\" type=\"hidden\" value=\"{$_POST['un']}\" />
      $tsw750
        <tbody>
          <tr><td colspan=\"2\">What are your hobbies?<td></tr>
          <tr><td colspan=\"2\"><input id=\"bq1\" maxlength=\"250\" name=\"bq1\" size=\"87\" 
type=\"text\" /></td></tr>
          <tr><td colspan=\"2\">What are your future goals?<td></tr>
          <tr><td colspan=\"2\"><input id=\"bq2\" maxlength=\"250\" name=\"bq2\" size=\"87\" 
type=\"text\" /></td></tr>
          <tr><td colspan=\"2\">Your friends describe you as:<td></tr>
          <tr><td colspan=\"2\"><input id=\"bq3\" maxlength=\"250\" name=\"bq3\" size=\"87\" 
type=\"text\" /></td></tr>
          <tr><td colspan=\"2\">what is your favorite quote?<td></tr>
          <tr><td colspan=\"2\"><input id=\"bq4\" maxlength=\"250\" name=\"bq4\" size=\"87\" 
type=\"text\" /></td></tr>
          <tr><td colspan=\"2\">who said it?<td></tr>
          <tr><td colspan=\"2\"><input id=\"auth\" maxlength=\"50\" name=\"auth\" size=\"87\" 
type=\"text\" /></td></tr>
          <tr><td colspan=\"2\">open bio<td></tr>
          <tr><td colspan=\"2\"><textarea cols=\"75\" id=\"bio\" name=\"bio\" rows=\"10\">
write your answer here</textarea></td></tr>
          <tr><td>&nbsp;</td><td>&nbsp;</td></tr>
          <tr>
            <td colspan=\"2\" class=\"center\"><!-- inner table for interests -->
              $tsw100b1
                <tbody>
                  <tr><td colspan=\"3\" class=\"center\">To select multiple items, 
hold down the CONTROL key (often abbreviated 
to Ctrl).<br />Choose as many as you'd<!--'--> like, there is NO limit.</td></tr>
                  <tr>
                    <th><span class=\"under\">Genres of Books</span></th>
                    <th><span class=\"under\">Types of Hobbies</span></th>
                    <th><span class=\"under\">Miscellaneous Interests</span></th>
                  </tr>
                  <tr>
                    <td class=\"center\">
                      <select id=\"books\" name=\"books\" multiple size=\"10\">
$bookopts
                      </select>
                    </td>
                    <td class=\"center\">
                      <select id=\"hobbies\" name=\"hobbies\" multiple size=\"10\">
$hobbyopts
                      </select>
                    </td>
                    <td class=\"center\">
                      <select id=\"misc\" name=\"misc\" multiple size=\"10\">
$miscopts
                      </select>
                    </td>
                  </tr>
                  <tr> 
                    <th><span class=\"under\">Genres of Movies</span></th>
                    <th><span class=\"under\">Genres of Music</span></th>
                    <th><span class=\"under\">Kinds of Pets</span></th>
                  </tr>
                  <tr>
                    <td class=\"center\">
                      <select id=\"movies\" name=\"movies\" multiple size=\"10\">
$movieopts
                      </select>
                    </td>
                    <td class=\"center\">
                      <select id=\"music\" name=\"music\" multiple size=\"10\">
$musicopts
                      </select>
                    </td>
                    <td class=\"center\">
                      <select id=\"pets\" name=\"pets\" multiple size=\"10\">
$petopts
                      </select>
                    </td>
                  </tr>
                  <tr> 
                    <th><span class=\"under\">Types of Sports</span></th>
                    <th><span class=\"under\">Vacation Spots</span></th>
                    <th><span class=\"under\"></span></th>
                  </tr>
                  <tr> 
                    <td class=\"center\">
                      <select id=\"sports\" name=\"sports\" multiple size=\"10\">
$sportopts
                      </select>
                    </td>
                    <td class=\"center\">
                      <select id=\"vacation\" name=\"vacation\" multiple size=\"10\">
$vacaopts
                      </select>
                    </td>
                    <td class=\"center\">
                    </td>
                  </tr>
                </tbody>
              </table>
            </td>
          </tr>
          <tr><td class=\"center\"><input type=\"reset\" value=\"Restart This Step\" /></td>
<td class=\"center\"><input type=\"submit
\" value=\"Go To The Next Step\" /></td></tr>
        </tbody>
      </table>
    </form>
END;
}
?>

i know the un is in the post becasue it's attained from the previous page which puts the un into a db and retrieves the uid that it passes to this one. so both are there for the next page. does anyone have any ideas why the interests only work half the time and the bio works 100% of the time?

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

druagord posted this at 22:01 — 22nd January 2004.

He has: 335 posts

Joined: May 2003

try it like this and see what is the error whe it doesn't work

<?php
 # connect to db and add bio and interest table entries
  $db=mysql_connect($host, $login1, $pass1) or die(\"cannot access mysql\"); # connect
  $fyd=mysql_select_db($dbname, $db) or die(\"cannot access db\"); # select the db
  $addbio=mysql_query(\"INSERT INTO bio (uid, bq1, bq2, bq3, bq4, auth, bio) VALUES
('$uid', '$bq1', '$bq2', '$bq3', '$bq4', '$auth', '$bio')\", $db);
# insert bio info into bio table
  $SQL = \"INSERT INTO interests ($iiif) VALUES ($iiiv)\"
  if(!$addinterests=mysql_query($SQL, $db))
  {
        echo mysql_error().\"<br \>\".$SQL;
  }
# insert interests into interests table
}
?>

like this you should be able to view the query whe it fails and see if you have a logic error in building $iiif and $iiiv

IF , ELSE , WHILE isn't that what life is all about

antoshka posted this at 05:37 — 23rd January 2004.

They have: 30 posts

Joined: Aug 2003

i am not sure what the function 'clean' does, but you might need to escape values before putting them into a query. like this:

$bq1=mysql_escape_string(clean($_POST['bq1']));
$bq2=mysql_escape_string(clean($_POST['bq2']));

etc...

if a user enters ' mark somewhere, your query will break. SQL will treat it as the end of the current value (since u use ' marks to denote text in the query).

m3rajk posted this at 04:24 — 26th January 2004.

They have: 461 posts

Joined: Jul 2003

that's actually what clean does. =o)

<?php
function clean($userInput){ # preps for db entry by \"cleaning\" html
  $a=addslashes(htmlentities(trim(stripslashes(rawurldecode($userInput))), ENT_QUOTES));
  return $a;
}
?>

tha t has al the ' and " get converted to the html entities and then incase there's anything else, it gets a \, so i probalby do overkill, but it's definitely covered

and it hasn't failed since i put in the debug statement.. i still have no clue why

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.