Announcement

Collapse
No announcement yet.

Converting a simple mySQL query to operate via PHP

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Try removing the single quotes around $DateBack and $UnixTime in the query
    vB4 SuperCharged
    http://www.gamingmasters.co.uk

    Comment


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

      Comment


      • #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


        • #5
          Originally posted by rossco_2005 View Post
          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


          • #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


            • #7
              Originally posted by ENF View Post
              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...
              X