update large MySQL database by rows

I have mysql database with 24K rows. How can I update first 100 rows then next 100, until full database is updated

The way I do it now, it by using simple update query directly in phpmyadmin below. It takes 5 mins+ and some times it crashes

Update tracks
Inner join ol_vidviews ON (tracks.name = ol.vidviews.name)
SET tracks.views = ol_vidviews.post_id

Can someone post php code to do this?

24k rows is nothing for database. phpmyadmin is an issue here.

Send SQL query directly to the database.

MrAttribution said:

24k rows is nothing for database. phpmyadmin is an issue here.

Send SQL query directly to the database.

Click to expand...

How do I send query to database?

For now 24K rows, but will go up as I more data plus I will be updating 24K rows weekly basis

shaymiller said:

How do I send query to database?

Click to expand...

You need a client software. Default ones are command line clients:
MySQL: https://dev.mysql.com/doc/refman/5.7/en/mysql.html
Postgres: https://www.postgresql.org/docs/current/app-psql.html

But it's a learning curve.

You can use something easier with graphical interface, like https://www.heidisql.com/ or http://www.sequelpro.com/

phpMyAdmin is actually database client with graphical interface, but it's very slow. Try Heidi or Sequel, it should work for your amount of data.

MrAttribution said:

You need a client software. Default ones are command line clients:
MySQL: https://dev.mysql.com/doc/refman/5.7/en/mysql.html
Postgres: https://www.postgresql.org/docs/current/app-psql.html

But it's a learning curve.

You can use something easier with graphical interface, like https://www.heidisql.com/ or http://www.sequelpro.com/

phpMyAdmin is actually database client with graphical interface, but it's very slow. Try Heidi or Sequel, it should work for your amount of data.

Click to expand...

Sorry for the late follow-up

I was hoping for not to use a client software and the admin CP I am building is all online. Isn't there a php code or javascript code to update mysql step by step?

24k rows should get updated in seconds (max) not minutes. Use MySQL cli instead.. it won't crash on you.

shaymiller said:

Sorry for the late follow-up

I was hoping for not to use a client software and the admin CP I am building is all online. Isn't there a php code or javascript code to update mysql step by step?

Click to expand...

Use something like MySQL Workbench: https://www.mysql.com/products/workbench/

I'm guessing you have the host details (IP/Username/Password) so you can set up a connection using mySQL Workbench and run the query (should take a few minutes to install and set up a connection).

Way better than phpmyadmin and if you want to do some PHP/mySQL work, could be worth moving away from phpmyadmin and using a separate mySQL client

davvv said:

Use something like MySQL Workbench: https://www.mysql.com/products/workbench/

I'm guessing you have the host details (IP/Username/Password) so you can set up a connection using mySQL Workbench and run the query (should take a few minutes to install and set up a connection).

Way better than phpmyadmin and if you want to do some PHP/mySQL work, could be worth moving away from phpmyadmin and using a separate mySQL client

Click to expand...

Its a wordpress blog self-hosted. I don't know much about workbench.

I came across this website. https://www.plus2net.com/php_tutorial/ajax-progress2.php

maybe that might work.

I am surprised there is no code online to use.

I'm not too sure on your DB structure but an update query on 24k rows shouldn't take over 5 mins so could be worth double checking your query to see if it can be optimised (do you need an Inner Join and can you link the tables based on ID's rather than names).

When you say self hosted, does that mean that you're using a service that sets up the Wordpress installation for you so you don't need to install anything, so don't have access to the DB directly (hostname & DB credentials)?

If it takes a long time to run but doesn't timeout, could be worth just chucking your mySQL query into a PHP file, upload it to the server (if you have access) and then run it through the night to make the necessary updates.

Once done, remove your PHP file from the server

If you need help setting up a script to connect to your DB and run a query, the following link can help: https://www.a2hosting.co.uk/kb/developer-corner/mysql/connect-to-mysql-using-php


Also, could be worth adding your question to https://stackoverflow.com/, you'll get answers from developers who can possibly answer your query quicker (as I'm not too sure how many PHP/mySQL developers there are on this forum)