??

[Kevin_Isley]

The problem is, the ID numbers are in the MySQL database, and I have to compare the OLD Data which now is in a excel sheet to the NEW database which is in the MySQL database tables, and compare them, get the right id input in, and then insert the row into the database.

[leatherback]

OK,
this an idea?

Load the old data from a csv (comma delimited file) into the new table (I'll call it now data). Add to this table the colum where you want the client ID (Say d_c_nr). In this table we also have a colum with names, say d_c_name.

You also have a table with clients, with an id colum: c_id and a name: c_name.

I htink this should be more easy with a join, but.. I am not sure how they work .. Not used them (Never seen the need. Maybe somebody can explain to me what hte big ++ of joins is..)

anyway:

$result = mysql_query("select * from clients");  

  while ($row=mysql_fetch_array($result))
  {
   $update = mysql_query("update data set d_c_nr=clients.c_id where d_c_name = c_name");
  }

Should then do the trick. Then you delete th ecolum with names from data and you're done.

J.

PS: Do this sort of conversions always AFTER making a backup of your data..

J.

[ultraslacker]

This conversion can be done strictly through sql, as long as there is a table which provides newid and oldid. I do not see much information from the original post, so I cannot post the sql.

I htink this should be more easy with a join, but.. I am not sure how they work .. Not used them (Never seen the need. Maybe somebody can explain to me what hte big ++ of joins is..)

A DBA has to know how to use JOINs as they are far more efficient, given a relational database. This problem is an example - after loading the necessary data, the conversion could be done with one sql statement involving a JOIN, INSERT INTO ...SELECT.

One good way to learn about joins is just run through a basic tutorial about normalization and sql, or experiment with a small set of data and a few tables.

Maybe I will write one, as I see a lot of users struggling with this concept.

[leatherback]

Hi Ultraslacker,

Jeah... I know. But then again.. I am more a weekender then a full-fledged DBA! And since they have taken the programming out of the GIS environments I do not get to use dabatase programming much anymore (Used to do a lot of spatial GIS programming during my studies though. There you ONLY work with relational databases. There I did do a lot of joins, but given up after switching to smaller ( << 10.000 records) databases. these joins were defined in join-files though, with join identifiers & restore-tables. Does that exists in MySQL too? Maybe I should grab some of these old scripts.?).

But.. You say they are more efficient? Hm.. Perhaps it becomes now a thing to look at. (I am now working on building a web-community for my daytime-knowledge-field: Spatial ecology), which may grow quite big..)

IF you do get around writing a tutorial.. Please let us (me!) know where to find it!

J.