Help with JOIN UPDATE loop

[dflow]

i have the following problem:

im trying to update the region_id in citylist2233 according to the region_id in regionlist2233.

 

i can compare regionlist2233.region_name = worldregionlist22.regionname and get the new and old id

 

im messing the loop somewhere:

 

<?php
//Insert Unique region id and update 



$query1 = 'SELECT country_id from worldcountrylist where enable =1 LIMIT 1 ';
$results1 = mysql_query($query1);

if (mysql_num_rows($results1) > 0) {
while($row1 = mysql_fetch_array($results1)) {
$query2 = 'SELECT * from citylist2233 where country_id ="'.$row1['country_id'].'" ';
$results2 = mysql_query($query2);
if (mysql_num_rows($results2) > 0) {
while($row2 = mysql_fetch_array($results2)) {
//echo $row2['city_id'].''.$row2['city_name'].'-'.$row2['region_id'].'-';
echo $row2['city_name'].'-'.$row2['city_id'].'-'.$row2['region_id'].'<BR>';

$query3='select regionlist2233.region_name ,regionlist2233.region_id from worldregionlist22 
LEFT JOIN regionlist2233 on worldregionlist22.regionname = regionlist2233.region_name AND worldregionlist22.region_id <> regionlist2233.region_id AND  worldregionlist22.country_id = regionlist2233.country_id WHERE  regionlist2233.region_id="'.$row2['region_id'].'" AND  regionlist2233.country_id="'.$row1['country_id'].'" ';
$results3 = mysql_query($query3);
if (mysql_num_rows($results3) > 0) {
while($row3 = mysql_fetch_array($results3)) {


echo $updatequery='UPDATE citylist2233 SET region_id='.$row3['region_id'].' WHERE region_id='.$row2['region_id'].' AND country_id='.$row1['country_id'].'';}}
$update = mysql_query($updatequery)
}
}
}
}

?>

[xyph]

You're approaching this incorrectly. You shouldn't need to have your queries in loops, let alone nested loops.

 

Why are you only using JOINs on the 3rd select query?

[dflow]

You're approaching this incorrectly. You shouldn't need to have your queries in loops, let alone nested loops.

 

Why are you only using JOINs on the 3rd select query?

please elaborate

[xyph]

You obviously know how to use JOINs, judging by the contents of $query3. Why haven't you used similar methods in your previous queries to prevent unnecessary calls to the database?

 

I'm assuming you only need a small nudge in the right direction, considering you've left us completely in the dark as far as database information goes.

 

To be honest, I really have no idea what's going on. Without seeing the complete picture, putting the pieces of your puzzle together is going to be difficult. You will receive only as much effort back as you put in.

[dflow]

You obviously know how to use JOINs, judging by the contents of $query3. Why haven't you used similar methods in your previous queries to prevent unnecessary calls to the database?

 

I'm assuming you only need a small nudge in the right direction, considering you've left us completely in the dark as far as database information goes.

 

To be honest, I really have no idea what's going on. Without seeing the complete picture, putting the pieces of your puzzle together is going to be difficult. You will receive only as much effort back as you put in.

 

i'll try and make it clearer:

 

i merged an old and new regionlist tables into one regionlist2233 skipping existing regions and inserting only new

now i need to update the citylist2233 with the old region_ids 

 

 

db

CREATE TABLE `citylist2233` (

  `city_id` int(11) NOT NULL,

  `country_id` int(11) NOT NULL,

  `city_name` varchar(200) NOT NULL,

  `region_id` int(11) DEFAULT NULL,

  KEY `city_id` (`city_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

CREATE TABLE `regionlist2233` (

  `region_id` int(4) NOT NULL,

  `country_id` int(4) NOT NULL,

  `city_id` int(11) NOT NULL,

  `region_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `state_code` varchar(50) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE `worldregionlist22` (

  `country_id` int(11) NOT NULL,

  `countrycode` varchar(2) NOT NULL,

  `region_id` varchar(11) NOT NULL,

  `regionname` varchar(255) NOT NULL,

  `Enable` int(1) NOT NULL,

  `rid` int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

[dflow]

???bump

[Muddy_Funster]

Your actualy bumping a topic you only started yesterday on a forum where bumping in any form is aginst the rules?....That's kinda rude.

 

i'll try and make it clearer:

 

i merged an old and new regionlist tables into one regionlist2233 skipping existing regions and inserting only new

now i need to update the citylist2233 with the old region_ids 

 

you'll need to try harder, that doesn't clear things up: you merged old and new inserting only new, now you need to update a different table with only old.... 

[dflow]

Your actualy bumping a topic you only started yesterday on a forum where bumping in any form is aginst the rules?....That's kinda rude.

 

i'll try and make it clearer:

 

i merged an old and new regionlist tables into one regionlist2233 skipping existing regions and inserting only new

now i need to update the citylist2233 with the old region_ids 

 

you'll need to try harder, that doesn't clear things up: you merged old and new inserting only new, now you need to update a different table with only old.... 

 

sorry kind of frustrated with this topic.

and it's kind of holding me back it's like my plugin number and i'm live with a new service

 

[dflow]

sometimes the ADHD just gets you in a corner

for anyone interested i solved this

<?php
//Insert Unique region id and update 


$query1 = 'SELECT country_id from worldcountrylist where enable =1 LIMIT 1 ';
$results1 = mysql_query($query1);

if (mysql_num_rows($results1) > 0) {
while($row1 = mysql_fetch_array($results1)) {
$query2 = 'select regionlist2233.region_name,regionlist2233.region_id,worldregionlist22.regionname ,worldregionlist22.regionid,regionlist2233.country_id  from worldregionlist22 
LEFT JOIN regionlist2233 on worldregionlist22.regionname = regionlist2233.region_name AND worldregionlist22.regionid <> regionlist2233.region_id AND  worldregionlist22.country_id = regionlist2233.country_id where worldregionlist22.regionid REGEXP "^[0-9]+$" and  regionlist2233.country_id <>100
';}
$results2 = mysql_query($query2);
if (mysql_num_rows($results2) > 0) {
while($row2 = mysql_fetch_array($results2)) {
//echo $row2['city_id'].''.$row2['city_name'].'-'.$row2['region_id'].'-';
$row2['region_name'].' change with '. $row2['regionname'].'<br>';
$row2['region_id'].' change with '. $row2['regionid'].'<br>';

//echo $row2['worldregionlist22.regionid'].'<br>';
$query = 'UPDATE citylist2233 SET region_id ='.$row2['region_id'].' where  region_id ='.$row2['regionid'].' and country_id='.$row2['country_id'].'   ';
echo $query.';<br><br>';
}
}}

?>