PHP Mysql Statement - multiple statements

Question

I have a PHP page that displays the results of a mysql query. each returned record is assigned a check box with the a value equal to the Row ID column.

the mysql statement in place inserts the selected rows into a new table.

I would like to add a second mysql query that updates the status of another table where the selected ID's match. the query is below:

UPDATE despgoods_alldetails,loaddetails SET despgoods_alldetails.locstatus ='LoadCreated' WHERE despgoods_alldetails.despgoodsid = $val

The page PHP Code that works currently is( I am aware of some incorrect code):

> <?php
>     mysql_connect("localhost", "hulamin_hulamin", "Hulamin2011")or die("cannot connect");    
>     mysql_select_db("hulamin_loc")or die("cannot select DB");
>     $sql="SELECT `despgoodsid`,`crtd dept`,`customer`,`loc cust rel`,`case no`,`gross mass`,`case width`,`case length` from
> despgoods_alldetails where transporttypename= 'localpmb' and
> locstatus='unplanned' and customer <> 'customer'";
>     $result=mysql_query($sql);
>     $count=mysql_num_rows($result);   putenv("TZ=Africa/Johannesburg");   ?> <table border=0>
>     <tr>
>         <td>
>             <form name="form1" method="post">
>                 <table border=0
>                     <tr>
>                         <th>&nbsp;</th>
>                         <th width=150>Dispatch Area</th>                      
>                         <th width=150>Customer</th>  
>                       <th width=150><center>Release Number</th>
>                       <th width=130><center>Case Number</th>
>                       <th width=80><center>Weight</th> 
>                       <th width=80><center>Width</th> 
>                       <th width=80><center>Length</th> 
>                   </tr> <?php
>     while($rows=mysql_fetch_array($result)){ ?>
>                     <tr>
>                         <td><input type="checkbox" name=check[]  value="<?php echo $rows['despgoodsid']; ?>"></td>
>                         <td><?php echo $rows['crtd dept']; ?></td>
>                         <td><?php echo $rows['customer']; ?></td>
>                       <td><center><?php echo $rows['loc cust rel']; ?></td>
>                         <td><center><?php echo $rows['case no']; ?></td>
>                         <td><center><?php echo $rows['gross mass']; ?></td>
>                       <td><center><?php echo $rows['case width']; ?></td>
>                       <td><center><?php echo $rows['case length']; ?></td>
>                       
>                     </tr>                                   
> 
> <?php
>     } ?>
>                     <tr>
>                         <td colspan=3><input name="Next" type="submit" id="Next" value="Next"></td>
>                     </tr>
>                     <?php
>                     
>                     
>                             
>                             $check=$_POST['check'];
>                             
>                         if($_REQUEST['Next']=='Next'){  {
>                             $sql="INSERT INTO loaddetails (despgoodsid,dispatcharea,Customer, casenumber, weight, loadstatus) 
>                           SELECT `despgoodsid`,`crtd dept`,Customer,`case no`,`gross mass`,'loadplanned'
>                           FROM despgoods_alldetails WHERE `despgoodsid` = '$val'";
>                             
>                             foreach($check as $key=>$value)
>                             {
>                             $sql="INSERT INTO loaddetails (despgoodsid,dispatcharea,Customer, casenumber, weight, loadstatus)
>                           SELECT `despgoodsid`,`crtd dept`,Customer,`case no`,`gross mass`,'loadplanned'
>                           FROM despgoods_alldetails WHERE `despgoodsid` = '$value'";
>                             $final=mysql_query($sql);
>                             if($final)
>                             {
>                             echo "<meta http-equiv=\"refresh\" content=\"0;URL=http://www.hulaminloc.co.za/planningplatform/planlocalpmbstep2.php\">";
>                             }                                            } 
>                                 }
>                                 }
>                    mysql_close(); ?> </table> </form> </td> </tr> </table>

How can I add my update statement to run in addition to the select statement for each selected row?

Any help is appreciated.

Thanks and Regards, Ryan Smith

mishu · Accepted Answer · 2011-10-13 10:58:04Z

1

you could create a trigger that would fire when data is saved in the secondary table; check mysql manual for triggers if you think this might be what you need

answered Oct 13, 2011 at 10:58

mishu

5,3971 gold badge24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Smudger Over a year ago

Hi, The PHP Page quoted above inserts data into a loaddetails table. I then need to alter the status of the products table for the inserted rows. I am not sure a trigger would acommodate this for only my selected rows. Thanks @mishu

mishu Over a year ago

considering that the table that needs to be updated is part of the query that must cause the update triggers are not the case here; so I think that you can't avoid using two separate sql queries; just run the update query you already have after the insert

mishu Over a year ago

just add the update query (that you already have) under the insert.. like this: $query = ' UPDATE despgoods_alldetails SET locstatus ='LoadCreated' WHERE despgoodsid = '.$value.' '; $update = mysql_query($query);

mishu Over a year ago

I was just asking why you don't move the redirect outside of the foreach statement so that it would run the needed number of times.. but maybe there's something that I am missing since you don't have a problem on this

mishu Over a year ago

if that what you are wondering: you can also split the insert and select in two queries if you want

|

Smudger · Accepted Answer · 2011-10-13 19:52:51Z

I though I would post the complete working code here. Big up to @mishu for all the help and patience.

<?php
    mysql_connect("localhost", "user", "password")or die("cannot connect");    
    mysql_select_db("database")or die("cannot select DB");
    $sql="SELECT `despgoodsid`,`crtd dept`,`customer`,`loc cust rel`,`case no`,`gross mass`,`case width`,`case length` from despgoods_alldetails where transporttypename= 'localpmb' and locstatus='unplanned' and customer <> 'customer'";
    $result=mysql_query($sql);
    $count=mysql_num_rows($result);
    putenv("TZ=Africa/Johannesburg");

?>
<table border=0>
    <tr>
        <td>
            <form name="form1" method="post">
                <table border=0
                    <tr>
                        <th>&nbsp;</th>
                        <th width=150>Dispatch Area</th>                        
                        <th width=150>Customer</th>  
                        <th width=150><center>Release Number</th>
                        <th width=130><center>Case Number</th>
                        <th width=80><center>Weight</th> 
                        <th width=80><center>Width</th> 
                        <th width=80><center>Length</th> 
                    </tr>
<?php
    while($rows=mysql_fetch_array($result)){
?>
                    <tr>
                        <td><input type="checkbox" name=check[]  value="<?php echo $rows['despgoodsid']; ?>"></td>
                        <td><?php echo $rows['crtd dept']; ?></td>
                        <td><?php echo $rows['customer']; ?></td>
                        <td><center><?php echo $rows['loc cust rel']; ?></td>
                        <td><center><?php echo $rows['case no']; ?></td>
                        <td><center><?php echo $rows['gross mass']; ?></td>
                        <td><center><?php echo $rows['case width']; ?></td>
                        <td><center><?php echo $rows['case length']; ?></td>

                    </tr>                                   

<?php
    }
?>
                    <tr>
                        <td colspan=3><input name="Next" type="submit" id="Next" value="Next"></td>
                    </tr>
                    <?php



                            $check=$_POST['check'];

                        if($_REQUEST['Next']=='Next'){
 {



$maxloadid = ' 
select 
max(loadid) +1 
from 
loaddetails 
'; 
$resultmaxloadid = mysql_query($maxloadid); // run the query 

// there was an error - output the message 
if (!$resultmaxloadid) 
{ 
echo 'there was an error in your query'; 
echo mysql_error(); 
die; 
} 

$loadid = mysql_result($resultmaxloadid, 0);

foreach($check as $key=>$value) // 
{ 
// get the details for one of the selected values 
$query = ' 
SELECT 
`despgoodsid`, 
`crtd dept`, 
`Customer`, 
`case no`, 
`gross mass` 
FROM 
despgoods_alldetails 
WHERE 
`despgoodsid` = "'. mysql_real_escape_string($value) . '" 
';

$result = mysql_query($query); // run the query 

// there was an error - output the message 
if (!$result) 
{ 
echo 'there was an error in your query'; 
echo mysql_error(); 
die; 
} 

// we got here, everything is fine 

if (!mysql_num_rows($result)) // the above query did not fetch any records, so there's a problem 
{ 
continue; // skip this record.. "continue" will ignore the rest of the foreach loop and go to the next step 
} 

$details = mysql_fetch_assoc($result); 

// inset the values 
$query = ' 
INSERT INTO 
loaddetails 
SET 
despgoodsid = "'.$details['despgoodsid'].'", 
dispatcharea= "'.$details['crtd dept'].'", 
Customer = "'.$details['Customer'].'", 
casenumber = "'.$details['case no'].'", 
weight = "'.$details['gross mass'].'", 
loadstatus= "loadplanned",
loadid = "'.$loadid.'" 
'; 

$result = mysql_query($query); // run the query 

// there was an error - output the message 
if (!$result) 
{ 
echo 'there was an error in your query'; 
echo mysql_error(); 
die; 
} 

// update the status 
$query = ' 
UPDATE 
despgoods_alldetails 
SET 
locstatus ="LoadCreated" 
WHERE 
despgoodsid = "'.mysql_real_escape_string($value) . '"
'; 
$result = mysql_query($query); // run the query 

// there was an error - output the message 
if (!$result) 
{ 
echo 'there was an error in your query'; 
echo mysql_error(); 
die; 
} 


} // end of foreach statement 
if($final) 
{ 
echo "<meta http-equiv=\"refresh\" content=\"0;URL=http://www.hulaminloc.co.za/planningplatform/index.phpx`\">"; 
}



                                }
                                }

mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>

Thanks again to @Mishu and all the other contributors on the site.

Collectives™ on Stack Overflow

PHP Mysql Statement - multiple statements

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related