update multiple tables 1 query

[patchido]

Hi, i can't manage to think on how to update 2 tables in 1 same query. here is my context.

 

 

i have a table called "guias" with a primary incremental key "id" and another field called "guia". the other table called "tblpaqueteria" has "id" a field called "guia_id" (this one matches a record from the other table) and the last field called "Estatus". i get 2 arrays via post method to my php function, one filled with the "guia_id" i want to modify, and another with the "guia" number i want to update the info to.

 

here is my code:

				$ids = $_POST['ids'];
				$os = $_POST['os'];
				
				$sql = "UPDATE 
							tblpaqueteria
						SET
							Estatus = 2 ";	
							
				$sql .= "WHERE
							guia_id IN (";	

				foreach($ids as $id){
					$sql .= $id . ",";
				} 			
				$sql = rtrim($sql, ',');
				$sql .= ")";

this works great, i change the field "Estatus" to the lines i need to, but know i also need to add the $os info to the lines in the other table matching the "id" field.

 

 

HELP PLZ!

 

THANKS

[jazzman1]

Try,

<?php

$ids = implode("', '", array_map('intval', $_POST['ids']));

$sql = sprintf("UPDATE tblpaqueteria t
        INNER JOIN guias g ON (g.id = t.guia_id)
        SET t.Estatus = 2, g.guia = '%s'
        WHERE t.guia_id IN($ids)",
 mysql_real_escape_string($_POST['os']));

Is the value of Estatus always is set to 2 or it's a dynamic one? 

Edited May 8, 2013 by jazzman1

[patchido]

it is always 2, $_POST['os'] is an array, i get an error becuase of that.

 

 

 

<br /> <b>Warning</b>: mysql_real_escape_string() expects parameter 1 to be string, array given in <b>E:\xampplite\htdocs\Mensajeria\includes\registro.class.php</b> on line <b>184</b><br />

Edited May 8, 2013 by patchido

[jazzman1]

Well, submit the form and give us the results back.

echo '<pre>'.print_r($_POST['os'], true).'</pre>'; exit; 

$ids = implode(", ", array_map('intval', $_POST['ids']));

$sql = sprintf("UPDATE tblpaqueteria t
    
        INNER JOIN guias g ON (g.id = t.guia_id)
        
        SET t.Estatus = 2, g.guia = '%s'
        
        WHERE t.guia_id IN($ids)",
        
 mysql_real_escape_string($_POST['os']));

[patchido]

<pre>Array ( [0] => 3323 ) </pre>

[jazzman1]

Ah....you're using an array in your html with integer value. 

 

So, try this...

$ids = implode(", ", array_map('intval', $_POST['ids']));

$sql = sprintf("UPDATE tblpaqueteria t
    
        INNER JOIN guias g ON (g.id = t.guia_id)
        
        SET t.Estatus = 2, g.guia = %u
        
        WHERE t.guia_id IN($ids)",
        
        intval($_POST['os'][0]));

[patchido]

that worked! but sometimes ill get more than 1 value, how can i make that dynamic?

[jazzman1]

More then one value where?

[patchido]

in the array $os

[jazzman1]

I don't sure if we can use IN operator in the SET statement - check in the web.

 

Another possible solution is to loop the result through php.

[patchido]

hmm, im not sure how to do that.

[jazzman1]

I'll try to give you an example later, OK?!

 

But, the question is, why do you want to update the same filed two or more times in the same sql statement. What is the point of that?

 

The last update would be lost from the new one.

Edited May 8, 2013 by jazzman1

[patchido]

the array "id" would also have more than 1 value, both arrays have the same length, and the indexes match for ex the id with index of 0 would have the "guia" in the "os" array with the index of 0 and ht indexed 1 would be indexed with the one of 1 too, i am not sure if i am being clear.

 

Thanks.

[patchido]

a more ilustrative example, ill have a id array with this info [34,35,36] and an array $os with [134,145,256]

 

i want to change tblpaqueteria.Estatus = 2 in all tblpaqueteria.guia_id matching 34 35 and 36, after that i want that in the guias.guia updates to 134 where guias.id = 34 and guias.guia updates to 145 where guias.id = 35 and guia.guias updates to 256 where guias.id = 36.

 

 

THANSK

[Barand]

 You would use
 
 

... WHERE tblpaqueteria.guia_id IN (34, 35, 36)

 
 and for the guias value
 
 

... SET guia.guias =
    CASE tblpaqueteria.guia_id
        WHEN 34 THEN 134
        WHEN 35 THEN 145
        WHEN 36 THEN 256
    END

[jazzman1]

Yes master, I've buided the sql with CASE too , it looks like that for now:

$sql = "UPDATE tblpaqueteria t INNER JOIN guias g ON (g.id = t.guia_id) SET t.Estatus = 2, 
g.guia = CASE WHEN t.guia_id = 1 THEN g.guia = 1 END WHERE t.guia_id IN(1,2)";

I need a little time to create the php code to make it dynamic

 

[jazzman1]

Ok, that's the test:

<?php

$ids = array(12, 13, 14);

$os = array(23, 24, 154);

$i = 0;

$sql = "UPDATE tblpaqueteria t INNER JOIN guias g ON (g.id = t.guia_id) SET t.Estatus = 2, g.guia = CASE ";

foreach ($os as $val) {
    
  $sql .= sprintf("WHEN t.guia_id = %u THEN g.guia = %u ", intval($ids[$i]), intval($val));
  
  $i++;
  
}

$sql .= "END WHERE t.guia_id IN (".implode(", ", array_map('intval', $ids)).")";

echo '<pre>'.print_r($sql, true).'</pre>';

Results:

 

UPDATE tblpaqueteria t INNER JOIN guias g ON (g.id = t.guia_id) SET t.Estatus = 2, g.guia = CASE WHEN t.guia_id = 12 THEN g.guia = 23 WHEN t.guia_id = 13 THEN g.guia = 24 WHEN t.guia_id = 14 THEN g.guia = 154 END WHERE t.guia_id IN (12, 13, 14)

 

 

So, replace $ids = $_POST['ids'] and $os = $_POST['os'][0] and try yourself.

 

You could also customize my code make it much more readable creating a custom php functions or something else.

 

Anyways that code has to work for you,  if you got any errors just let's know.

 

jazz.

[jazzman1]

EDIT: Re-reading my post I saw an error.

 

$os = $_POST['os'][0] should be $os = $_POST['os'], sorry about that.

[patchido]

so, this is what i ended up with your example.

$ids = $_POST['ids'];

				$os = $_POST['os'];
				$i = 0;

				$sql = "UPDATE tblpaqueteria t INNER JOIN guias g ON (g.id = t.guia_id) SET t.Estatus = 2, g.guia = CASE ";

				foreach ($os as $val) {
					
				  $sql .= sprintf("WHEN t.guia_id = %u THEN g.guia = %u ", intval($ids[$i]), intval($val));
				  
				  $i++;
				  
				}

				$sql .= "END WHERE t.guia_id IN (".implode(", ", array_map('intval', $ids)).")";
								
				mysql_query($sql);
				return $sql;

i got this query 

 

UPDATE tblpaqueteria t INNER JOIN guias g ON (g.id = t.guia_id) SET t.Estatus = 2, g.guia = CASE WHEN t.guia_id = 39 THEN g.guia = 50 WHEN t.guia_id = 40 THEN g.guia = 100 END WHERE t.guia_id IN (39, 40)

 

 

and the table tblpaqueteria did update while the table guia didn't.

[jazzman1]

Hey patchido,

 

it sounds my CASE is wrong....I don't know....I should try to make a test with a real data..

 

I think, Barand could be provide us a better solution of this issue.

 

jazz.

[patchido]

solved it, this made the trick

				$ids = $_POST['ids'];

				$os = $_POST['os'];
				$i = 0;

				$sql = "UPDATE tblpaqueteria INNER JOIN guias ON (guias.id = tblpaqueteria.guia_id) SET tblpaqueteria.Estatus = 2, guias.guia = CASE tblpaqueteria.guia_id";

				foreach ($os as $val) {
					
				  $sql .= sprintf(" WHEN  %u THEN  %u ", intval($ids[$i]), intval($val));
				  
				  $i++;
				  
				}

				$sql .= "END WHERE tblpaqueteria.guia_id IN (" . implode(", ", array_map('intval', $ids)) . ")";

i used your example with the example barand made.

 

 

Thanks a lot guys!

[jazzman1]

He-he, I just forgot to add a table name to my CASE.

 

Good for you

[Barand]

Yes master, I've buided the sql with CASE too , it looks like that for now:

$sql = "UPDATE tblpaqueteria t INNER JOIN guias g ON (g.id = t.guia_id) SET t.Estatus = 2, 
g.guia = CASE WHEN t.guia_id = 1 THEN g.guia = 1 END WHERE t.guia_id IN(1,2)";

I need a little time to create the php code to make it dynamic

 

I gave you the CASE syntax ie WHEN condition THEN value

 

g.guia = CASE WHEN t.guia_id = 1 THEN g.guia = 1 END

[jazzman1]

Hey Barand,

 

I find your personal replies very usefully for me since I've been a member to this forum

 

So I just wanted to use this CASE structure ( with optional expression) :

CASE [ expression ]

WHEN condition_1 THEN result 1
WHEN condition_2 THEN result 2

END

So, I just forgot to add a column name to expression, it should have been something like that:

$sql = "UPDATE tblpaqueteria t 

INNER JOIN guias g ON (g.id = t.guia_id) 

SET t.Estatus = 2,  g.guia = CASE t.guia_id 

WHEN t.guia_id = 1 THEN g.guia = 10
 
WHEN t.guia_id = 2 THEN g.guia = 12

END 

WHERE t.guia_id IN(1,2)";

[Barand]

SET g.guia = 
    CASE t.guia_id 
        WHEN 1 THEN 10
        WHEN 2 THEN 12
    END 

or

SET g.guia = 
    CASE  
        WHEN t.guia_id=1 THEN 10
        WHEN t.guia_id=2 THEN 12
    END