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.

Advertisements

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)’