SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2013
    Location
    Phoenix, AZ & Pagosa Springs, CO
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Strange issue with front-end of site not populating new database entries from backend

    Hi there;

    I'm fairly new to MySQL and have a strange issue that I hope someone can assist me with:

    One of our company websites has four types of users(admin, agent, processor and enrollee); the processor inputs the enrollee profile information via the front-end of our site and the info is stored within our MySQL database. Normally after this happens, our admins, agents and processors are all able to view the new enrollee info on the front-end of the site.

    At the beginning of the year (Jan 2, 2013), any new enrollee info that was normally entered by our processors are now not showing up on the front-end at all. It's strange because all of our previous entries (prior to Jan 2, 2013) are still able to be viewed/edited via the front-end, so it's just happening to all new entries after Jan 2, 2013.

    Each enrollee entered has it's own id number that is auto-generated in the database for every new entry created. With this in mind, our accounts populate on the front-end as such:

    websitename.com/admin/admin_enrollments.php?id=150

    With the '150' example being the new entry unique id. Our front-end website still shows entries UP TO number 248, but right at 249 (ex: websitename.com/admin/admin_enrollments.php?id=249), I receive an error stating:

    Error in query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 31

    So, I check the syntax within the code and it shows as:

    $premiumInfo = enrollmentForms::getPremiumInfoByID($enrollment['planRef_id'],$enrollment['planName'],$enrollment['premiumMode']);

    To my limited knowledge, all looks good - and this command still works perfectly with all entries BEFORE id 249, so I'm stumped as to why the entries have just decided to stop showing up on the front-end. The SQL database shows all new entries being populated within the back-end tables just fine, but they are just not showing up on the front end for some reason.

    In addition, 100% of the files within the website have remained unedited/untouched for over three months, and every other category is showing and populating just fine, so this is really boggling my mind as to where the issue could be. Also, our processors upload PDFs simultaneously while entering enrollee info, and I noticed that the PDF the processor uploaded at the moment the site stopped populating new enrollees was the largest one ever uploaded (at araound 5MB: not too large, but largest one ever uploaded), so I think maybe there was a server timeout, error, or something out-of-the-ordinary that could have lent to there being a break, error or something that I can't figure out.

    We've tried contacting the original developer to no avail, so I'm stumped as to what I should do or where I should go to find the appropriate assistance would be greatly appreciated.

    I'm mostly a front-end developer, and have limited knowledge in back-end SQL knowledge, so that's where I'm coming from. If any other relevant information is needed to access the above scenario, I can provide; Thanks in advance for any help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are the entries for ids greater than 248 actually in the table?

    from your description, my guess is there's a flaw in your application logic somewhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2013
    Location
    Phoenix, AZ & Pagosa Springs, CO
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    are the entries for ids greater than 248 actually in the table?

    from your description, my guess is there's a flaw in your application logic somewhere
    Hi r937- Thanks for your response and question.

    There are entries greater than 248 that are actually in the table, and continue to be populated by our processors [I](I've enclosed an image of the whole problem at the bottom of this post for visual reference.

    The pic has three parts: from top to bottom, it shows the following: #1: The last item to show up on front-end (248), #2: The following item (249) & resulting error on the front-end and #3: A snapshot of the database table showing all items entered previous and following 248 are being populated within the database. Again, not showing up front-end.

    Also, you mentioned that there may be a flaw in the application logic somewhere: after viewing the image I've enclosed, would there be anything I could look for and/or provide that could help with searching the correct area?

    Thanks again in advance for your response; I hope I've made sense in all this - Have a great day!
    Roger

    SCRX_001.jpg

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try to find the actual query that's generating the error

    then insert an echo statement so that you can see the actual sql that's being passed to mysql

    chances are, the entire sql statement is flawed (hence, the error message doesn't point to a particular part of the query as it normally would) likely due to a missing php variable value

    for example...
    Code:
    SELECT *
      FROM enrolments
     WHERE id =
    this type of error always generates the "near '' at line ..." message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2013
    Location
    Phoenix, AZ & Pagosa Springs, CO
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937;

    Thanks for the idea you provided. Within our database, we have 18 different tables housed there; one of them being the one with the problem (`enrollment`); and I have looked at the SQL Statement from that particular table, and it's as follows:
    SELECT * FROM `enrollment` WHERE 1

    Also, as a reference , the PHP being executed for this within the php file is as follows:
    $premiumInfo = enrollmentForms::getPremiumInfoByID($enrollment['planRef_id'],$enrollment['planName'],$enrollment['premiumMode']);
    (NOTE: I should mention the php above shows $enrollment['planName'], but 'planName' is actually not located within the enrollment table, but rather located in a different table called planRef, but when I change it to $planRef, there's no change - may not be important, but thought I'd mention it)

    The interesting thing is that all our other tables are working as normal, and their SQL is similar to the `enrollment` one (examples below):
    SELECT * FROM `planRef` WHERE 1
    SELECT * FROM `planType` WHERE 1
    SELECT * FROM `userTypeRef` WHERE 1
    etc.


    I'm learning along the way so I apologize in advance, but how would I go about inserting an echo statement within SQL to see the actual sql being passed to mysql? When I go to the SQL tab and hit 'Go' to run the SQL Query on database, it generates the entire table with all entries entered.

    Thanks again for your help with this.
    Roger

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Copyright1977 View Post
    ... how would I go about inserting an echo statement within SQL
    echo is a php command

    (disclaimer: i don't do php, so don't ask me for help)

    the idea is, if the sql query is constructed by php, then there are some php variables in it (e.g. the id which comes off the dynamic url), so you want to echo the sql string that php constructed just before php passes it to mysql for execution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2013
    Location
    Phoenix, AZ & Pagosa Springs, CO
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937;

    Thanks for the help. I will be working on coming up with a solution to the method you've pointed out.

    Again, much appreciated & have a great day!
    Roger


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
  •