Tag Archives: MySQL

DELETE all duplicates from TABLE

If we have a long table with thousands of records with some duplicate contents and we want to trim it down to the bare essentials (no duplicates), here’s one (of several) way of doing it: For this, assume we have: – a table called “emails”, with fields: id_email, email – the table has several records… Read More »

Preventing SQL injection in PHP/MySQL with PDO

This is a bit old but i wanted to add it here so it stays on record. SQL injection is the action to inject SQL code in web forms to perform a site attack and disrupt its services. For a better definition, go read wikipedia here (it even has some code for you to test… Read More »

MySQL Problem – “MySQL server has gone away” (but it hasn’t)

Had this problem a couple of times already and i better write what i did to solve it. Quite simple really, but it cost me a few hours of searching. This problem showed up when i was trying to restore large dumps of data into a Database. A few minutes into the restore, it crashed… Read More »

MySQL Command Line – Part 2

A small update/add to my previous post about MySQL command line, well, commands: So you know from my previous post how to open mysql command prompt, connect, etc. so now i am just going to leave here how you can restore a dump from inside the mysql prompt: First select the database you want to… Read More »

DELETE rows using SELECT results

  ok, so i made this nice query to..well, query two tables to check for missing ids on one of them (but it could be mostly anything else). Then i wanted to delete all the ids that dont have a match. This is kind of easily done by deleting the results from a query, but… Read More »

Remove line breaks and spaces from Table field

Had an issue with a huge table of email address not really compliant. Some had trailing/ending spaces, others even line breaks. To fix the line breaks, this worked:   update emails_table set email = replace(email, ‘\n’, ”)   To remove trailing/ending spaces, i used this:   update emails_table set email=trim(email)   Go and be happy.

Linux server – start/stop/restart mysql

Its merely a reminder so i dont go through the search to restart mysql on one of my stupid servers: Usually (i think) its like this: /etc/init.d/mysqld start /etc/init.d/mysqld stop /etc/init.d/mysqld restart On that server of mine, because of some config: /etc/rc.d/init.d/mysqld start /etc/rc.d/init.d/mysqld stop /etc/rc.d/init.d/mysqld restart

Find duplicate records in SQL table

There are a few ways to do this, but i am leaving this one here so its easy to find. SELECT nome, COUNT(nome) FROM Patrimonio GROUP BY nome HAVING ( COUNT(nome) > 1 ) This returns a list with two columns of records that exist in table “Patrimonio” more than once. A column for “nome”… Read More »

PHPMaker characters encoding problem

Its the second time i run across this issue with PHPMaker. Its not exactly the problem’s fault, more a config issue. I havent completely assessed all details on this issue, since i dont have complete access to the server i mention. I just want to leave this so next time something like this happens, i,… Read More »

update table with join condition from different table

If we want to make an update to a table, but have to condition that update with values from a second one, we do something like this: TABLE_1 id_t1 name fee 1 john 100 2 rita 100 3 peter 100 TABLE_2 id_t2 id_t1 type 1 1 single 2 2 single 3 3 married If we… Read More »