PHPee.com PHP development forum
Multipage problem - SQL expert needed !

 
Post new topic   Reply to topic    phpee.com Forum Index -> PPhlogger Support
View previous topic :: View next topic  
Author Message
fxcounters.com



Joined: 24 Jan 2004
Posts: 18

PostPosted: Fri Jul 16, 2004 12:39 am    Post subject: Multipage problem - SQL expert needed ! Reply with quote
Hi,

I run a statistics site with many users and lately I have been experiencing major problems with the same pages showing up multiple times, having multiple colors (I've counted 18 different MP entries for exactly the same URL for one of the accounts !!) . I'm trying to find a solution for this so any assistance is very much appreciated.

In the mean time, I found I can delete the pphl_XXXXX_mpdl table which will clean out the MP entries and rediscover them. I would like to prevent doing this all manually so I'm looking for a statement I can apply in MySQLAdmin that will do this automatically. The manual statement I'm using looks like this:

DELETE FROM pphl_42382_mpdl WHERE id <> '1';

(I'm leaving the first entry intact)

The 42382 number needs to be dynamic i.e. I need to find an SQL statement that can dynamically determine which pphl_xxxxx_mpdl tables exist and then delete all rows in them, ideally all in one statement.

Who can help ??

Many Thanks,
Mike.
Back to top
View user's profile Send private message
sdjl
Moderator


Joined: 17 Nov 2003
Posts: 751
Location: London, UK

PostPosted: Fri Jul 16, 2004 7:58 am    Post subject: Reply with quote
Well you've got the most important code sorted Smile

All you need to do is add some checking with PHP to it and you should be able to delete the entries you require.

As regards to your problem, you need to make sure that each occurance of the same page (with different colour) is actually not just another unique page name/number.
The script shouldn't add additional colours unless the page is unique. This would most likely include casing of the filename as well as file path information.

David
_________________
-----
Free Logger Accounts | PPHlogger Manual | PM Me
Back to top
View user's profile Send private message Visit poster's website
fxcounters.com



Joined: 24 Jan 2004
Posts: 18

PostPosted: Fri Jul 16, 2004 10:09 am    Post subject: Reply with quote
Hi,

Thanks for your reply. I checked the entries in the Database tables and the page names (it happens primarely with the root document i.e. '\') and associated titles are absolutely identical ! I found an account that has 53 rows which are identical expect for the (auto-increment) id !!

Still would like that SQL statement please !

Many Thanks,
Mike
Back to top
View user's profile Send private message
sdjl
Moderator


Joined: 17 Nov 2003
Posts: 751
Location: London, UK

PostPosted: Fri Jul 16, 2004 11:53 am    Post subject: Reply with quote
You wrote the statement in your original post...

David
_________________
-----
Free Logger Accounts | PPHlogger Manual | PM Me
Back to top
View user's profile Send private message Visit poster's website
fxcounters.com



Joined: 24 Jan 2004
Posts: 18

PostPosted: Fri Jul 16, 2004 1:49 pm    Post subject: Reply with quote
The statement in my post is manual.. I have over 400 _mpdl tables and I don't want to have to run this all manually. The SQL I'm looking for has to do the following:

- Run a query that determines all the XXXXX values from the available pphl_XXXXX_mpdl tables in the database
- Use the gathered list of XXXXX values to Delete all rows from every mpdl table exept the row where id = 1

I don't really want to start coding up some PHP to do this so I'm trying to find out if this can be done with a single SQL statement.

Many Thanks
Mike.
Back to top
View user's profile Send private message
sdjl
Moderator


Joined: 17 Nov 2003
Posts: 751
Location: London, UK

PostPosted: Fri Jul 16, 2004 3:56 pm    Post subject: Reply with quote
You'd need to use some PHP in with that to get it to work.
If you want to pay me for my time, i'd be more than willing to write something.

David
_________________
-----
Free Logger Accounts | PPHlogger Manual | PM Me
Back to top
View user's profile Send private message Visit poster's website
fxcounters.com



Joined: 24 Jan 2004
Posts: 18

PostPosted: Fri Jul 16, 2004 5:06 pm    Post subject: Reply with quote
Thanks for the offer.

I have actually found a very easy way to do it in the MySQLAdmin interface.. just select all _mpdl tables and select 'empty' from the dropdown.. It won't preserve the first record but it still seems to work fine.

I would still like to know how it happens though !

If you like to see how it looks, I have my backup site running the old DB:

http://www.meinwebserver.com/~fxcounte/counter/dspLogs.php

Log in with 'fxdomains' - guest

if you scroll down to the MP section (in logs) you can see many, many entries for the same page.

Best Regards,
Mike.
Back to top
View user's profile Send private message
sdjl
Moderator


Joined: 17 Nov 2003
Posts: 751
Location: London, UK

PostPosted: Fri Jul 16, 2004 8:04 pm    Post subject: Reply with quote
Strange.
Have you run the optimize admin and opimize user database commands from the admin side of pphlogger?

David
_________________
-----
Free Logger Accounts | PPHlogger Manual | PM Me
Back to top
View user's profile Send private message Visit poster's website
fxcounters.com



Joined: 24 Jan 2004
Posts: 18

PostPosted: Tue Jul 20, 2004 5:55 pm    Post subject: Reply with quote
Yes, I run that quite regularly. It does slightly decrease the DB size so it seems to work. Should that fix this issue ?
Back to top
View user's profile Send private message
sdjl
Moderator


Joined: 17 Nov 2003
Posts: 751
Location: London, UK

PostPosted: Tue Jul 20, 2004 10:11 pm    Post subject: Reply with quote
I have no idea to be honest. I just thought that maybe if you didn't the tables could be getting confused or corrupt in some way.

David
_________________
-----
Free Logger Accounts | PPHlogger Manual | PM Me
Back to top
View user's profile Send private message Visit poster's website
Post new topic   Reply to topic    phpee.com Forum Index -> PPhlogger Support All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB 2.0.8
Theme design based on Team 101 Designs, modified by Philip Iezzi