Category 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 »

DELETE rows using SELECT results – part 2

Theres already a post here about deleting rows from some table using results from a different query. This is basically the same but more raw/basic. So i have: – a table ‘events’ with id_event,title, etc. – a table ‘users’ with id_user, name, etc. – a table ‘rel_users_events’, which is a relationship between ‘users’ and ‘events’,… 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 – Create new user

The use case scenario was an installation of Tiny Tiny RSS that doesnt allow an empty MySQL password. My local mysql doesnt have a password set so i was stuck on that screen. To fix it, i just had to create a new user on my mysql, give it privileges and a non empty password.… 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.

Group and concatenate rows

If we have a table like this: id name year 1 museum 2 2012 2 museum 1 2012 3 museum 2 2010 4 museum 1 2011 5 museum 3 2012 And we want to list all the museums and on which year they..well, they did something. Something like this result: name year museum 1 2012,2011… Read More »

insert into table using a select from another table

Following the update with data from another table, i am leaving this for quick reference. To insert data into table2 from table2: insert into table2 (field1,field2,field3) select table1.field1,table1.field2,table1.field3 from table1 (all the above is on the same line) We can make this as complex as needed.