Help with Update query

[raouleduke]

I am a little frustrated, I have this

1992,1993,1994,1995,1996,1997,1998,1999,2000,2001,2002,2003,2004,2005

and am trying to put it into a select statement, I have used =, LIKE and IN and gives no errors but does not update.

Thanks in advance.

[TheDefender]

Share the SQL statement, and the table structure so we know how to help you...

[raouleduke]

$selectedstatestr = 1992-1993-1994-1995-1996-  etc.....


.............


// Turn the - into ',' 
	$selectedstatestr = str_replace("-", ",", $selectedstatestr); 
	// Remove the extra ', from the start, and ,' from the end 
	$selectedstatestr = "'".$selectedstatestr.""; 

$selectedzipstr = " $selectedzip"; 

// Turn the - into ',' 
$selectedzipstr = str_replace("-", ",", $selectedzipstr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedzipstr = "'".$selectedzipstr."'"; 

  $query = "UPDATE DATABASE SET ACTIVE='1' WHERE CLOSEDATE > '$date'";   

  if($selectmake =='1'){
  $query.=" AND MAKE = \"$selectedmakestr\" "; 
  }
  if($selectmodel =='1'){
  $query.=" AND MODEL = \"$selectedmodelstr\" "; 
  }
  if($selectyear =='1'){ 
  $query.=" AND YEAR = \"$selectedyearstr\" "; 
  }
  if($selectcity =='1'){ 
  $query.=" AND CITY = \"$selectedcitystr\" "; 
  }
  if($selectstate =='1'){
  $query.=" AND STATE = \"$selectedstatestr\" "; 
  }
  if($selectzip =='1'){
  $query.=" AND ZIP = \"$selectedzipstr\" ";
  }
print $query;
$result = mysql_query($query) or die(mysql_error());

[raouleduke]

This works, coming from a form to post.

$select_make = $_POST['select_make'];
$select_model = $_POST['select_model'];
$select_year = $_POST['select_year'];
$select_city = $_POST['select_city'];
$select_state = $_POST['select_state'];
$select_zip = $_POST['select_zip'];
$MAKE = implode("','",$_POST['MAKE']);
$MODEL = implode("','",$_POST['MODEL']);
$YEAR = implode("','",$_POST['YEAR']);
$CITY = implode("','",$_POST['CITY']);
$STATE = implode("','",$_POST['STATE']);
$ZIP = implode("','",$_POST['ZIP']);

  $query = "UPDATE $catagory$subcatagory SET ACTIVE='1' WHERE CLOSEDATE > '$date'";   

  if($select_make =='1'){
  $query.=" AND MAKE IN ('".$MAKE."')"; 
  }
  if($select_model =='1'){
  $query.=" AND MODEL IN ('".$MODEL."')"; 
  }
  if($select_year =='1'){ 
  $query.=" AND YEAR IN ('".$YEAR."')"; 
  }
  if($select_city =='1'){ 
  $query.=" AND CITY IN ('".$CITY."')"; 
  }
  if($select_state =='1'){
  $query.=" AND STATE IN ('".$STATE."')"; 
  }
  if($select_zip =='1'){
  $query.=" AND ZIP IN ('".$ZIP."')";
  }
$result = mysql_query($query) or die(mysql_error());
print $query;

And this is what I am trying to get working, I am pulling the data from a database.

$selectmake = $row["select_make"]; 
	$selectmodel = $row["select_model"];  

	$selectyear = $row["select_year"];  

	$selectcity = $row["select_city"];  

	$selectstate = $row["select_state"];  

	$selectzip = $row["select_zip"];   

    $selectedmake = $row["selected_make"]; 
	$selectedmodel = $row["selected_model"];  

	$selectedyear = $row["selected_year"];  

	$selectedcity = $row["selected_city"];  

	$selectedstate = $row["selected_state"];  

	$selectedzip = $row["selected_zip"];   

}	
$selectedmakestr = "$selectedmake"; 

// Turn the - into ',' 
$selectedmakestr = str_replace("-", "','", $selectedmakestr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedmakestr = "'".$selectedmakestr."'"; 

echo "$selectedmakestr <br>";

$selectedmodelstr = "$selectedmodel"; 

// Turn the - into ',' 
$selectedmodelstr = str_replace("-", "','", $selectedmodelstr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedmodelstr = "'".$selectedmodelstr."'"; 

$selectedyearstr = "$selectedyear"; 

// Turn the - into ',' 
$selectedyearstr = str_replace("-", "','", $selectedyearstr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedyearstr = "'".$selectedyearstr."'"; 

$selectedcitystr = "$selectedcity"; 

// Turn the - into ',' 
$selectedcitystr = str_replace("-", "','", $selectedcitystr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedcitystr = "'".$selectedcitystr."'"; 

$selectedstatestr1 = "$selectedstate"; 

// Turn the - into ',' 
$selectedstatestr = str_replace("-", "','", $selectedstatestr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedstatestr = "'".$selectedstatestr."'"; 

$selectedzipstr = "$selectedzip"; 

// Turn the - into ',' 
$selectedzipstr = str_replace("-", "','", $selectedzipstr); 
// Remove the extra ', from the start, and ,' from the end 
$selectedzipstr = "'".$selectedzipstr."'"; 

  $query = "UPDATE LUTHERSELLANDRECOMMEND SET ACTIVE = '1' WHERE CLOSEDATE > '$date' ";   

  if($selectmake =='1'){
  $query.=" AND MAKE IN (".$selectedmakestr.")"; 
  }
  if($selectmodel =='1'){
  $query.=" AND MODEL IN (".$selectedmodelstr.")"; 
  }
  if($selectyear =='1'){ 
  $query.=" AND YEAR IN (".$selectedyearstr.")"; 
  }
  if($selectcity =='1'){ 
  $query.=" AND CITY IN (".$selectedcitystr.")"; 
  }
  if($selectstate =='1'){
  $query.=" AND STATE IN (".$selectedstatestr.")"; 
  }
  if($selectzip =='1'){
  $query.=" AND ZIP IN (".$selectedzipstr.")";
  }
print $query;

Thanks for any ideas.

[raouleduke]

Something I am not seeing, I have looked over this so many times, am I just not seeing something or am I doing this wrong.

[tsinka]

Hi,

I'm not sure ... is the part at the top of your code part of a while loop ? If yes you might want to change it a little bit:

$selectedmake = array();
$selectedmodel = array();
$selectedyear = array();
$selectedcity = array();
$selectedstate = array();
$selectedzip = array();

while ($row = mysql_fetch_array($res)) {
$selectmake = $row["select_make"];  

    $selectmodel = $row["select_model"];   

    $selectyear = $row["select_year"];   

    $selectcity = $row["select_city"];   

    $selectstate = $row["select_state"];   

    $selectzip = $row["select_zip"];    

    $selectedmake[] = $row["selected_make"];  

    $selectedmodel[] = $row["selected_model"];   

    $selectedyear[] = $row["selected_year"];   

    $selectedcity[] = $row["selected_city"];   

    $selectedstate[] = $row["selected_state"];   

    $selectedzip[] = $row["selected_zip"];    

}     

Then replace your str_replace lines by e.g.

$selectedmakestr = implode("','",$selectedmake);

and do it like shown in the php code that works with data coming from a post.

[/code]

If no ... 😕 ... post more code (the complete select query).
Thomas

[raouleduke]

The problem with using the implode is that it is a stored value, that is why I am using the Select query.

The code with the post comes from a form that they only use once to setup the stuff, after that I stored the values that they entered in the first time, I am selecting them in this query, the value is stored as 1992-1993-1994-1995-1996- etc.....
, and I want to use that to keep the database updated, Is there something else I need to be using in the after WHERE, or is IN the way to go if you want to select by multiple values for a certain column.

Thanks,

[tsinka]

Hi,

please post the output of print $query;

Thomas