Converting a simple mySQL query to operate via PHP

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Rik Brown
    Senior Member
    • May 2005
    • 345
    • 4.2.X

    Converting a simple mySQL query to operate via PHP

    I have a mysql query that I would like to execute from within PHP script. However, its not working and I believe its related to the proper usage of PHP variables.

    Here is the mysql query that works fine by itself:

    Code:
    SET @DaysPastMax = 15;
    SELECT @Date_Now:=UNIX_TIMESTAMP();
    
    set @SecondsPastMax = @DaysPastMax * 86400;
    set @Date2UseMax = @Date_Now - @SecondsPastMax;
    
    DELETE all_rows.* FROM thread AS all_rows
    INNER JOIN (
    SELECT title, dateline, min(threadid) as min_threadid FROM thread
    GROUP BY title
    HAVING count(*) >1
    AND dateline BETWEEN @Date2UseMax AND @Date_Now
    ) AS dupe_rows ON dupe_rows.title = all_rows.title
    # In other words, exclude the lowest threadid of a dupe group
    AND dupe_rows.min_threadid < all_rows.threadid;
    Here is the PHP code (minus the connect string stuff which I use all the time elsewhere):

    PHP Code:
    <?php

    // Finds and deletes duplicate threads (based on highest thread number)

    $UnixTime time();
    $DaysBack 15;
    $SecondsBack $DaysBack 86400;
    $DateBack $UnixTime $SecondsBack;

    /*
    print "---<br />";
    print $UnixTime . "<br />";
    print $DaysBack . "<br />";
    print $SecondsBack . "<br />";
    print $DateBack . "<br />";
    print "---";
    */

    $query1="
    DELETE all_rows.* FROM thread AS all_rows
    INNER JOIN (
    SELECT title, dateline, min(threadid) as min_threadid FROM thread
    GROUP BY title
    HAVING count(*) >1
    AND dateline BETWEEN '
    $DateBack' AND '$UnixTime'
    ) AS dupe_rows ON dupe_rows.title = all_rows.title
    AND dupe_rows.min_threadid < all_rows.threadid"
    ;

    mysql_query($query1);

    mysql_close();
    ?>
    Via the print statements above (when uncommented), I can see that the variables are being correctly created. But no threads are being deleted which the pure mysql code does fine.

    It must be something very simple. But with only limited PHP experience, I'm not seeing the problem.

    Does anyone know what I'm doing wrong?

    Thanks. -- Rik

    ps: If you are curious, this is all related to importing Usenet newsgroups hourly.
  • Darkimmortal
    Senior Member
    • Apr 2009
    • 686
    • 3.8.11

    #2
    Try removing the single quotes around $DateBack and $UnixTime in the query

    Comment

    • Rik Brown
      Senior Member
      • May 2005
      • 345
      • 4.2.X

      #3
      Originally posted by Darkimmortal
      Try removing the single quotes around $DateBack and $UnixTime in the query
      Thanks for the suggestion. Tried. It has no effect. -- Rik

      Comment

      • rossco_2005
        Senior Member
        • Apr 2005
        • 465
        • 4.1.x

        #4
        Try echo $query1; before mysql_query($query1); to see if your SQL is correct before sent to mysql.
        This will tell you where your problem is.

        http://filesharingtalk.com

        Comment

        • Rik Brown
          Senior Member
          • May 2005
          • 345
          • 4.2.X

          #5
          Originally posted by rossco_2005
          Try echo $query1; before mysql_query($query1); to see if your SQL is correct before sent to mysql.
          This will tell you where your problem is.
          Thank you for the suggestion. At least I can see the variables are being inserted properly in the mysql line. The output was as follows:

          DELETE all_rows.* FROM thread AS all_rows INNER JOIN (SELECT title, dateline, min(threadid) as min_threadid FROM thread GROUP BY title HAVING count(*) >1 AND dateline BETWEEN 1268272490 AND 1269568490) AS dupe_rows ON dupe_rows.title = all_rows.title AND dupe_rows.min_threadid < all_rows.threadid

          Everything looks fine. However, the database is not being changed (I can run the mysql query directly in mysql right afterward and see changes being made correctly).

          So I'm still stumped here.

          Any other ideas? Thanks. -- Rik

          Comment

          • ENF
            Senior Member
            • Apr 2002
            • 2677
            • 3.8.11

            #6
            Silly question. Where is PHP getting the MySQL connection information?

            Is this section part of a larger file that contains the pointers for the database?
            To be updated...

            Comment

            • Rik Brown
              Senior Member
              • May 2005
              • 345
              • 4.2.X

              #7
              Originally posted by ENF
              Silly question. Where is PHP getting the MySQL connection information?

              Is this section part of a larger file that contains the pointers for the database?
              Thanks for looking at this problem. Yes, I cut out the database connect stuff at the top of the script before putting the rest of the script online here. It shouldn't be any problem as I use it all the time for other scripts. But here it is below with the sensitive password and all other information turned to "x":

              Code:
              $user="x";
              $password="x";
              $database="x";
              $server="x";
              mysql_connect($server,$user,$password);
              @mysql_select_db($database) or die( "Unable to select database");
              But to add to my prior message, I can take the "echoed" output suggested in the message above by rossco_2005 and run that back directly through mysql and even that works fine outside of php. I've looked at permissions and have made them 777. The script takes about 7 seconds to execute either via the PHP script or when its run directly within mysql.

              I actually run this same script without the date range checking on a much smaller site with no problems. So I keep thinking there must be something staring me in the face that I'm missing.

              Again, any suggestions are welcomed. Thanks. -- Rik

              Comment

              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
              Working...