insert that seems to work half the time

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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

insert that seems to work half the time

Post by m3rajk »

insert that seems to work half the time

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)

Code: Select all

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)

Code: Select all

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?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Debug it?

Code: Select all

$addbio=mysql_query("INSERT INTO bio (uid, bq1, bq2, bq3, bq4, auth, bio) VALUES ('$uid', '$bq1', '$bq2', '$bq3', '$bq4', '$auth', '$bio')", $db);  $addinterests=mysql_query("INSERT INTO interests ($iiif) VALUES ($iiiv)", $db) or die(mysql_error());
It also helps debugging to put the query in a var, like,
$query = 'INSERT INTO ... etc..etc..";
mysql_query($query) or die(mysql_error());

That way you can also echo $sql; to make sure it's what you expect.
Post Reply