Wwwebber's Blog

How To Delete from Multiple Tables in Mysql

Posted in MySql by wwwebber on August 11, 2009

$query1 = ‘DELETE FROM table1 where id=1’;

$query2 = ‘DELETE FROM table2 where id=1’;

Many MySql newbies don’t know that you can join delete just like select and updates. Here’s how you’d issue a single query in a join

$betterQuery = ‘DELETE t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id= t2.id  WHERE  t1.id=1’;

Same effect but you’re only query the datbase once rather than twice.

Comments Off on How To Delete from Multiple Tables in Mysql

How to import CSV into MySQL table: Step by Step Instructions with Pictures

Posted in MySql, phpMyAdmin, tutoring services by wwwebber on January 1, 2009

Just show me the steps dude

I recently had to create a zip code locator program for someone’s website that I was designing. This site was for parents/guardians/students to be able to find a tutor. Basically, hubalub.com is a tutoring service, and one that is, I believe, unique from most of the other tutoring services out there. That aside, never having written a script like that before, I soon found out that you get the latitude and longitude of a given zip code and find all zip codes with a given radius. In another post, I’ll describe some optimizations that you definitely want to pursue to implement this–let’s put it this way: the database of zip codes has about 42,000 rows! And you don’t want to query 42,000 rows ! Anyway, to get the zip codes and their associated data like county name etc.. you ‘re going to need to download a zip code database. There are several free and commercial ones available. Just do a google search for them. If like me, you get a humongous CSV text file with all of this data, you’ll want to know how to get all the data from this CSV into a MySQL table. And PhpMyAdmin comes to the rescue:

Steps to Import CSV into MySQL table

  • Step 1) Create a table that has the necessary number of columns. If the CSV has 10 types of data, you’ll need 10 columns.
  • Step 2) After having created you MySQL table, log into phpMyAdmin and click on this table.I had a table called `morris_zipcodes`.
  • Step 3) Click on the ‘import ‘ (For this step you can refer to the picture below
  • Step 4) You should now be looking at something like the next picture. I circled in red the important parts of the form.

  • Step 5) Click on the ‘csv’ radio button. You will then see a fields that have to do with your CSV is formatted. Since my CSV’s fields were not separated by tabs I entered ‘\t’ as you can see above (no quotes). My fields were not enclosed by quotes so I removed the default quotation marks for ‘Fields enclosed by’. I left the fields escaped by in its default form (didn’t need to worry about escaping). And lastly, since my lines were terminated by a new line I entered the ‘\n’ for the ‘lines terminated by’ field.
  • Step 6) I browsed to the massive text file, uploaded it and was soon happily greated by this message : “Import has been successfully finished, 79948 queries executed.” WOw that’s a lot of queries. I think it took about a minute or two, for MySQL to create all of those rows, not bad!

Comments Off on How to import CSV into MySQL table: Step by Step Instructions with Pictures

MySql: error: ‘Access denied for user using password: YES)’

Posted in MySql by wwwebber on December 20, 2008

Just wanted to post this in order to, possibly, prevent other people from a bit of frustration when attempting to connect to a MySql database. I am int the  process of creating a private tutoring services site that makes use of PhpFox’s social network script. Anyway, after creating the database in cpanel  , and adding a user I tried to connect to the database. It turns out the MySql error message that I got “Access denied for user using password: YES)” . Now, I knew that I wasn’t using ‘YES’ as a password! What the hell!   However, it turns out that , MySql generates this error to mean that I was indeed trying to use a password(hence the “YES”).

Comments Off on MySql: error: ‘Access denied for user using password: YES)’