Resolved Plesk SQL query to find domains and PHP versions

[Laurence@]

In 2018 it's my mission to upgrade 10,000 websites to PHP 7. Can someone give me the SQL query to:

1) Find the overall number of websites on a particular PHP version.
2) List all said domains on a particular PHP version.

Cheers.

 

[IgorG]

Try to use as basis something like:

mysql> select dom.name, h.php_handler_id from domains dom LEFT JOIN hosting h ON (dom.id=h.dom_id) order by h.php_handler_id;

you can easily modify it according your needs.

 

[Laurence@]

Thanks Igor.

 

[FrancoiseL]

Hi, Igor, Thank you for this.
How would I also get the Version of WordPress used by those domains?

 

[FrancoiseL]

Hi, Igor, Thank you for this.
How would I also get the Version of WordPress used by those domains?

I found this to get the WP version:
select dom.name, dp.val, h.php_handler_id from domains dom LEFT JOIN dom_param dp ON (dom.id=dp.dom_id) where dp.param="wapp" or dp.param="wapp-version"
How can I combine the two queries?

 

[nelteren]

* You need to add both JOINs to the query to select from all three tables.
* When JOINing multiple tables, add conditions that only apply to one table to the JOIN to get a faster result.
* I replaced the 'hosting' join to an INNER JOIN so that sites that used to have a wordpress installation but changed to 'nohosting' won't be shown. This could be the case if they were migrated to another provider but kept the DNS on the current plesk server. Replace with LEFT JOIN if this is not desired.
* The 'LEFT JOIN' will list sites where wordpress is not present (dp.val will be NULL) there. Replace the 'LEFT JOIN' on dom_param by INNER JOIN if you only want to see the wordpress sites.
* The ORDER BY will list the oldest php versions first.
* When comparing a SQL column to a fixed value, use single quotes.

SELECT dom.name, dp.val, h.php_handler_id
    FROM psa.domains dom   
    INNER JOIN psa.hosting h ON (dom.id=h.dom_id)
    LEFT JOIN psa.dom_param dp ON (dom.id=dp.dom_id) AND (dp.param='wapp' or dp.param='wapp-version')
    ORDER BY h.php_handler_id ASC, dp.val ASC;