SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Continuing to develop my "help wanted" site, in the "edit job opportunity script" I come up with the error message "Error in SQL syntax near "OpenJobs" Where OJID=6' at line 1."

    Here is the URL of the job opportunity management system page:
    http://<a href="http://www.kripalush...rtnty.php3</a>

    In the search box, key in a word like "and" or "test."

    Hit the edit button on the resulting page and you get the error message.

    Here is the code for the page this button is supposed to bring up (patterned after Kevin's PHP MySQL toturial). Any clues?

    PHP Code:
     <?php

    $dbcnx 
    = @mysql_connect("db1.5points.net""kripalu",
    "qu1ck5tARt");
    mysql_select_db("kripalu");

      if (
    $submit): // The job opportunity's details have
                    // been updated.

      
    $sql "UPDATE OJText SET " .
             
    "Text='$jobtext', " .
             
    "WHERE OJID=$id";
      if (
    mysql_query($sql)) {
        echo(
    "<P>Job opportunity details updated.</P>");
      } else {
        echo(
    "<P>Error updating job opportunity details: " .
             
    mysql_error() . "</P>");
      }

    ?>

    <P><A HREF="show_mng_job_oprtnty.php3">Return to Job Opportunity Management</A></P>

    <?php
      
    else: // Allow the user to edit the job opportunity
            // with OJID=$id

      
    $jobtext=mysql_query("SELECT OJText" .
                          
    "FROM OpenJobs " .
                          
    "WHERE OJID=$id");
      if (!
    $jobtext) {
        echo(
    "<P>Error fetching job opportunity details: " .
          
    mysql_error() . "</P>");
        exit();
      }

      
    $jobtext mysql_fetch_array($jobtext);

      
    $text $jobtext["Text"];

      
    // Add slashes to database
      // values for use as HTML attributes
      
    $text addslashes($text);

    ?>

    <FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>
    <H1>Edit Job Opportunity:</H1>
    Text: <INPUT TYPE=TEXT NAME="text" VALUE="<?php echo($text); ?>" SIZE=20 MAXLENGTH=100><BR>
    <INPUT TYPE=HIDDEN NAME="id" VALUE="<?php echo($id); ?>">
    <INPUT TYPE=SUBMIT NAME="submit" VALUE="SUBMIT"></P>
    </FORM>

    <?php endif; ?>
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  2. #2
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Paul,

    Sorry, don't know the answer. But next time you post a script somewhere you should be sure to blank out your MySQL connection information. User name and password especially, and even the host address would be worthwhile.

    I'll let the guru's answer your real question. :-)
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  3. #3
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    OOOps re connection information!

    Greetings RKuhle,

    Many thanks for that tip re the connection information. Is there any way you or I can blank out that infomation now?

    Regards,

    Paul C.
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  4. #4
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    posting this first before i check your prob:

    just "edit" your post to change the login info

    going to look into the prob now

    .:: UPDATE ::.

    Looks like you have the problem fixed m'man!
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  5. #5
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On the right hand side of your post, at the top of the cell, there are several small images. Click the "Edit" button and I believe you'll be able to edit your post. You might consider changing your password now, as anyone could access your database remotely.
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  6. #6
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    edit post and strange error message

    Greetings Alarion,

    I am logged on and tried to edit my post. Came up with a message that either I am not logged on or do not have permission to edit that page. Straaaaange!

    On the strange error message, nope, I have not fixed it yet. But digging around, I find that the OJID='6 has to do with the id I am passing to the edit script (see first post) from the page that links to it.

    http://<a href="http://www.kripalush...rtnty.php3</a> The '6 is apparently the id of the job opportunity I am trying to edit. There may be an error in this part of the show_list_job oprtnty.php3 page script.

    PHP Code:
     <?php

    $dbcnx 
    = @mysql_connect();
    mysql_select_db();

    // The basic SELECT statement
    $select "SELECT DISTINCT OJID, OJText";
    $from   " FROM OpenJobs";
    $where  " WHERE OJID > 0";


    if (
    $searchtext != "") { // Some search text was specified
      
    $where .= " AND OJText LIKE '%$searchtext%'";
    }
    ?>

    <!-- Table #4 View, edit, delete job opportunities list -->
    <TABLE WIDTH="446" BORDER=1>
    <TR><TH>Content</TH><TH>Options</TH></TR>

    <?php
    $result 
    mysql_query($select $from $where);
    if (!
    $result) {
      echo(
    "</TABLE>");
      echo(
    "<P>Error retrieving content from database!<BR>".
           
    "Error: " mysql_error());
      exit();
    }

    while (
    $jobs mysql_fetch_array($result)) {
      echo(
    "<TR>\n");
      
    $id       $jobs["OJID"];
      
    $jobtext $jobs["OJText"];
      echo(
    "<TD>$jobtext</TD>\n");
      echo(
    "<TD>[<A HREF='human_resources/job_opportunities/edit_job_oprtnty.php3?id=$id'>Edit</A>|".
           
    "<A HREF='human_resources/job_opportunities/delete_job_oprtnty.php?id=$id'>Delete</A>]</TD>\n");
      echo(
    "</TR>\n");
    }
    ?>

    </TABLE>
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  7. #7
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Greetings RKuhle,

    For some reason those edit, etc icons do not appear at the top right of my first message. I must have tried using the ones below belonging to the cel below me.

    I have initiated a change in my password.
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  8. #8
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, just went back to your site and it seem mysql is down

    anyhow, last time I went there I forgot you wanted help with the 'edit' part. I thought you were just having trouble displaying the records after a search Anyhow, as soon as I see you mysql back up, or the page functioning I will see what I can do
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  9. #9
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    strange error message

    Greetings Alarion:

    Site was down to change password. Is back up now.
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  10. #10
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    looking at this line of code from your first post:
    PHP Code:
    $jobtext=mysql_query("SELECT OJText" .
                          
    "FROM OpenJobs " .
                          
    "WHERE OJID=$id"); 
    put a space after "OJText".. currently it looks like that your SQL text is:
    "SELECT OJTextFROM OpenJobs WHERE OJID=$id"

    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  11. #11
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    strange error gone, but no text to edit

    Greetings Alarion,

    Adding that space made the error message go away! Thanks!

    However, when the "edit job opportunity" page comes up, it does not include the text from the item to be edited from the previous page.

    For example,

    1) starting with the management page http://<a href="http://www.kripalush...rtnty.php3</a> if I key in the word "test," and click on "Search",

    2) I go to the "View, edit, delete" page http://<a href="http://www.kripalush...rtnty.php3</a>. Here, if I click on "Edit" next to the first 'test' item,

    3) I go to the "Edit job opportunity" page http://<a href="http://www.kripalush....php3?id=6</a>. I notice that the "id=6" is passed in the url, but for some reason the corresponding content or text, does not show up in the text box on the edit page.

    I figure there must be something wrong either in the code of the "View, edit, delete" page or the "edit page." Here is the code for both.

    "View, edit, delete" page code:

    PHP Code:
    <?php

    $dbcnx 
    = @mysql_connect();
    mysql_select_db();

    // The basic SELECT statement
    $select "SELECT DISTINCT OJID, OJText";
    $from   " FROM OpenJobs";
    $where  " WHERE OJID > 0";


    if (
    $searchtext != "") { // Some search text was specified
      
    $where .= " AND OJText LIKE '%$searchtext%'";
    }
    ?>

    <!-- Table #4 View, edit, delete job opportunities list -->
    <TABLE WIDTH="446" BORDER=1>
    <TR><TH>Content</TH><TH>Options</TH></TR>

    <?php
    $result 
    mysql_query($select $from $where);
    if (!
    $result) {
      echo(
    "</TABLE>");
      echo(
    "<P>Error retrieving content from database!<BR>".
           
    "Error: " mysql_error());
      exit();
    }

    while (
    $jobs mysql_fetch_array($result)) {
      echo(
    "<TR>\n");
      
    $id       $jobs["OJID"];
      
    $jobtext $jobs["OJText"];
      echo(
    "<TD>$jobtext</TD>\n");
      echo(
    "<TD>[<A HREF='human_resources/job_opportunities/edit_job_oprtnty.php3?id=$id'>Edit</A>|".
           
    "<A HREF='human_resources/job_opportunities/delete_job_oprtnty.php?id=$id'>Delete</A>]</TD>\n");
      echo(
    "</TR>\n");
    }
    ?>

    </TABLE>
    "Edit" page code:

    PHP Code:
    <?php

    $dbcnx 
    = @mysql_connect();
    mysql_select_db();

      if (
    $submit): // The job opportunity's details have
                    // been updated.

      
    $sql "UPDATE OJText SET " .
             
    "Text='$jobtext', " .
             
    "WHERE OJID=$id ";
      if (
    mysql_query($sql)) {
        echo(
    "<P>Job opportunity details updated.</P>");
      } else {
        echo(
    "<P>Error updating job opportunity details: " .
             
    mysql_error() . "</P>");
      }

    ?>

    <P><A HREF="show_mng_job_oprtnty.php3">Return to Job Opportunity Management</A></P>

    <?php
      
    else: // Allow the user to edit the job opportunity
            // with OJID=$id

      
    $jobtext=mysql_query("SELECT OJText " .
                          
    "FROM OpenJobs " .
                          
    "WHERE OJID=$id ");
      if (!
    $jobtext) {
        echo(
    "<P>Error fetching job opportunity details: " .
          
    mysql_error() . "</P>");
        exit();
      }

      
    $jobtext mysql_fetch_array($jobtext);

      
    $text $jobtext["Text"];

      
    // Add slashes to database
      // values for use as HTML attributes
      
    $text addslashes($text);

    ?>

    <FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>
    <H1>Edit Job Opportunity:</H1>
    Text: <INPUT TYPE=TEXT NAME="text" VALUE="<?php echo($text); ?>" SIZE=20 MAXLENGTH=100><BR>
    <INPUT TYPE=HIDDEN NAME="id" VALUE="<?php echo($id); ?>">
    <INPUT TYPE=SUBMIT NAME="submit" VALUE="SUBMIT"></P>
    </FORM>

    <?php endif; ?>
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  12. #12
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, in your "edit" page.. you have this:

    PHP Code:
    $text $jobtext["Text"]; 
    shouldn't that be:
    PHP Code:
    $text $jobtext["OJText"]; 
    ?
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  13. #13
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Greetings Alarion,

    Great! That fixed the problem of not getting the text to edit. However, when I hit the "SUBMIT" button, I get that pesky message again "Error updating job opportunity. Details: you have a error in your SQL syntax near "WHERE OJID=6' at line 1. I looked over the code again and find that the spacing that was giving me the problem last time is still OK. Is there some other problem in there?

    PHP Code:
    <?php

    $dbcnx 
    = @mysql_connect("");
    mysql_select_db("");

      if (
    $submit): // The job opportunity's details have
                    // been updated.

      
    $sql "UPDATE OJText SET " .
             
    "Text='$jobtext', " .
             
    "WHERE OJID=$id ";
      if (
    mysql_query($sql)) {
        echo(
    "<P>Job opportunity details updated.</P>");
      } else {
        echo(
    "<P>Error updating job opportunity details: " .
             
    mysql_error() . "</P>");
      }

    ?>

    <P><A HREF="human_resources/job_opportunities/show_mng_job_oprtnty.php3">Return to Job Opportunity Management</A></P>

    <?php
      
    else: // Allow the user to edit the job opportunity
            // with OJID=$id

      
    $jobtext=mysql_query("SELECT OJText " .
                          
    "FROM OpenJobs " .
                          
    "WHERE OJID=$id ");
      if (!
    $jobtext) {
        echo(
    "<P>Error fetching job opportunity details: " .
          
    mysql_error() . "</P>");
        exit();
      }

      
    $jobtext mysql_fetch_array($jobtext);

      
    $text $jobtext["OJText"];

      
    // Add slashes to database
      // values for use as HTML attributes
      
    $text addslashes($text);

    ?>

    <!-- Begin edit job form -->

    <FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD="post">
    <H1>Edit Job Opportunity:</H1>

    <!-- Textarea for editing the content -->

    <TEXTAREA 
    NAME="text" 
    ROWS=15 COLS=45 WRAP>
    <?php echo($text); ?>
    </TEXTAREA>

    <!-- Submit button -->

    <P><INPUT TYPE=HIDDEN NAME="id" VALUE="<?php echo($id); ?>">
    <INPUT TYPE=SUBMIT NAME="submit" VALUE="SUBMIT"></P>
    </FORM>

    <!-- End form for editing job -->

    <?php endif; ?>
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  14. #14
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I see what the problem is.

    First off, here is your update SQL statement:
    PHP Code:
    $sql "UPDATE OJText SET " .
             
    "Text='$jobtext', " .
             
    "WHERE OJID=$id "
    but on your form you have this:
    Code:
    <TEXTAREA 
    NAME="text" 
    ROWS=15 COLS=45 WRAP>
    <?php echo($text); ?>
    </TEXTAREA>
    so, in your SQL statement, change "Text='$jobtext', " to "Text='$text', " <---- you also have an un-needed comma in there, remove that - that is what is giving you there error, but even if you didn't get the error, the data would be blanked out on a successfull update if you don't change $jobtext to $text

    that should fix you right up

    an easy way to help you while debugging is that if your SQL statement is screwing up (as in this case), then start assigning your SQL to a variable, then pass that variable to mysql_query. This way, you can "echo" your SQL statement as it appears to mysql_query if you need to.. for example:

    PHP Code:
    $sql "update OJText SET Text='$text' where OJID = $id";
    mysql_query($sql) or die("Failed on this update: $sql"); 
    Enjoy
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  15. #15
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    strange error message

    Your suggestions worked! Many thanks!
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  16. #16
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    glad it's working now. Good luck in finishing that sucker up!
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  17. #17
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    No more strange error messages.

    Well she's all done, even with a primitive MySQL and PHP user authentication on the HR data management link http://www.kripalushop.org/kripalu/k...s/main_hr.php3.

    HR is delighted. Check out the end result http://www.kripalushop.org/kripalu/k..._oprtntys.php3

    Notice "Updated daily" and I'm no longer responsible!

    Again, thanks for your help.

    Onward to a more sophisticated user authentication process. Not sure whether to go with MySQL and PHP (seems primitive, one database fits all pages) or to go with Apache. Reading up on it now. Any thoughts?
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  18. #18
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Congrats

    Hmm, well a certain Oracle consultant I know swears by database user authentication. Meaning, instead of having one master user/password for PHP to connect to the database with, you make a new username and password for each user with the privileges you want that user to have. This way, you let the database enforce user privileges and you don't have to enforce them on the client side.

    If you went this route, it would be more secure to setup these accounts for your HR people manually- rather than to have your script do it. If you can avoid using your master "user" db account, then you have secured the site that much more

    just an idea

    if you don't go that way, but instead use a "users" table in your db, then make sure to encrypt the password inside the database. MySQL has a Password() function, and PHP has a md5() function

    good luck!
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  19. #19
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that on security.

    I think the little database program I am using is something like the Oracle program you describe. It is encripted, but it isn't as sophisticated as I would like it to be. I think I'll start a new user thread to see who's out there on this subject.

    Thanks again,

    Paul C.
    Paul C.
    ClickBasics
    http://www.clickbasics.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •