Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Query sometimes fails

    Hi all,

    I have a query, which when run from within phpMyAdmin, it works, however when integrated within a website written in Perl, it fails and throws errors.
    Both are run with a connection to the same database, and from coding/formatting side when integrated into the Website, all is correct and the same as other queries.

    Any help with this would be much appreciated - Thanks!

    MySQL Query:

    Code:
    CREATE TEMPORARY TABLE tmp_lecture_days (
    timeslot_id int(50)
    );
    INSERT INTO tmp_lecture_days (timeslot_id)
    SELECT DISTINCT tab_appointment.timeslot_id
    FROM tab_appointment WHERE lecture_id = '1115';
    SELECT COUNT(timeslot_id)
    FROM tmp_lecture_days;
    Error Log:

    Code:
    7.3.2011   10:14:12   error        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 ';
    7.3.2011   10:14:12   error             INSERT INTO tmp_lecture_days (timeslot_id)
    7.3.2011   10:14:12   error             SELECT DISTINCT tab_appoi' at line 3
    Last edited by schuhmi2; 03-08-2011 at 07:29 AM. Reason: Resolved

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    7.3.2011 10:14:12 error 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 ';
    print the sql statement in that file and check whether there's any error in the syntax.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi abduraooft,

    This probably will sound (and probably is) a stupid question,
    but what exactly do you mean?

    I know it works in phpMyAdmin, its just when Its integrated into my perl site, it throws errors.

    Perl Code is so:

    Code:
    				$query
    					= &statement_database(
    						"CREATE TEMPORARY TABLE tmp_lecture_days (
    						timeslot_id int(50)
    						);
    						INSERT INTO tmp_lecture_days (timeslot_id)
    						SELECT DISTINCT tab_appointment.timeslot_id
    						FROM tab_appointment WHERE lecture_id = '1115';
    						SELECT COUNT(timeslot_id)
    						FROM tmp_lecture_days;");
    						
    				 my ($days) = $query->fetchrow_array;
    The rest of my SQL is formatted exactly the same as this about, and all function.

    Thanks,

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,871
    Thanks
    2
    Thanked 164 Times in 159 Posts
    What is the code in your statement_database subroutine?

    You're passing 3 separate sql statements as a single string. How is your subroutine handling that string?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Generally, with PHP, you can *NOT* send more than one query to the DB at a time. (Silly, but true; other libraries in Java and C++ and even ASP.NET have no problems with doing so.)

    Try cutting that into three separate queries and see if the problem doesn't indeed go away.

    And not to ask a dumb question, but WHY are you using 3 queries, in the first place???
    Code:
    SELECT COUNT(DISTINCT timeslot_id)
    FROM tab_appointment WHERE lecture_id = '1115';
    Were you perhaps not aware that MySQL supports COUNT( DISTINCT xxx)?

    http://dev.mysql.com/doc/refman/5.1/...count-distinct
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    schuhmi2 (03-08-2011)

  • #6
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks @Old Pedant, It looks like when combining it all into one statement, I somehow managed to make a minor error (stupid brackets)

    Thanks for the help

    RESOLVED


  •  

    Tags for this Thread

    Posting Permissions

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