Page 1 of 1

Easier way to update records / won't update

Posted: Wed May 27, 2009 7:12 pm
by KeeganWolf
Hello,

I'm trying to create a series of functions that will retrieve integer values from a table, reduce them by a value of 1 or 2, and then update the table with the new information. This is sort of a template. My issue is 1. Is there an easier way to write this while still working as a function with values passed to it, and 2. This will not update the records in my sql table.

Code: Select all

 
<?php
//session_start(); 
 
// Connect to database
include_once "../scripts/connect_to_mysql.php";
$store = 4071;
    $f1 = "SELECT * FROM Inventory$store WHERE invnum = 11522";
    $f2 = "SELECT * FROM Inventory$store WHERE invnum = 11011";
    $f3 = "SELECT * FROM Inventory$store WHERE invnum = 11121";
    $f4 = "SELECT * FROM Inventory$store WHERE invnum = 11459";
    $f5 = "SELECT * FROM Inventory$store WHERE invnum = 11466";
    $f6 = "SELECT * FROM Inventory$store WHERE invnum = 11228";
    $f7 = "SELECT * FROM Inventory$store WHERE invnum = 11463";
    $f8 = "SELECT * FROM Inventory$store WHERE invnum = 11139";
    $g1 = mysql_query($f1);
    $g2 = mysql_query($f2);
    $g3 = mysql_query($f3);
    $g4 = mysql_query($f4);
    $g5 = mysql_query($f5);
    $g6 = mysql_query($f6);
    $g7 = mysql_query($f7);
    $g8 = mysql_query($f8);
    $row1 = mysql_fetch_array($g1);
    $row2 = mysql_fetch_array($g2);
    $row3 = mysql_fetch_array($g3);
    $row4 = mysql_fetch_array($g4);
    $row5 = mysql_fetch_array($g5);
    $row6 = mysql_fetch_array($g6);
    $row7 = mysql_fetch_array($g7);
    $row8 = mysql_fetch_array($g8);
    echo $row1['case'];
    echo $row2['case'];
    echo $row3['case'];
    echo $row4['case'];
    echo $row5['case'];
    echo $row6['case'];
    echo $row7['case'];
    echo $row8['case'];
    $res1 = ($row1['case'] - 1);
    $res2 = ($row2['case'] - 1);
    $res3 = ($row3['case'] - 1);
    $res4 = ($row4['case'] - 2);
    $res5 = ($row5['case'] - 2);
    $res6 = ($row6['case'] - 1);
    $res7 = ($row7['case'] - 1);
    $res8 = ($row8['case'] - 1);
    $h1 = "UPDATE Inventory$store SET case='$res1' WHERE invnum = 11522";
    $h2 = "UPDATE Inventory$store SET case='$res2' WHERE invnum = 11011";
    $h3 = "UPDATE Inventory$store SET case='$res3' WHERE invnum = 11121";
    $h4 = "UPDATE Inventory$store SET case='$res4' WHERE invnum = 11459";
    $h5 = "UPDATE Inventory$store SET case='$res5' WHERE invnum = 11466";
    $h6 = "UPDATE Inventory$store SET case='$res6' WHERE invnum = 11228";
    $h7 = "UPDATE Inventory$store SET case='$res7' WHERE invnum = 11463";
    $h8 = "UPDATE Inventory$store SET case='$res8' WHERE invnum = 11522";
    mysql_query($h1);
    mysql_query($h2);
    mysql_query($h3);
    mysql_query($h4);
    mysql_query($h5);
    mysql_query($h6);
    mysql_query($h7);
    mysql_query($h8);
    
 
if (!$g1) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}
?>
 
Any help is greatly appreciated, it's late and I'm so tired

Re: Easier way to update records / won't update

Posted: Thu May 28, 2009 1:15 am
by mischievous
Haven't tested this code because its 1 in the morning... but something like this is way more efficient and robust than the way your orginially had it. You simply just pass the function what inventory number you want and it does the rest.

Simply Put:

Code: Select all

doQuery('11522');

Code: Select all

<?php
//session_start();
 
// Connect to database
include_once "../scripts/connect_to_mysql.php";
 
function doQuery($invnum){
 
//Set Table Name? Not sure what Inventory is
    $table = "Inventory";
    $table .= "4071";
    
//setup mysql query
    $query = "SELECT * FROM $table WHERE invnum = $invnum";
    $resultQuery = mysql_query($query);
    
    //check to see if results found
    $resultCheck = mysql_num_rows($resultQuery);
    if($resultCheck > 0)
    {
        //loop through results
        foreach($resultQuery->result() as $row)
        {
            //echo initial value
            echo($row->case);
            
            //add 1 to case field result
            $newcase = $row->case + 1;
            
            //update field in mysql
            $updateQuery = "UPDATE $table SET case = $newcase WHERE invnum = $invnum";
            mysql_query($updateQuery);
        }
    } else {
        //print if no results where found for query
        echo"No Results Found for ".$invnum;
    }
}
 
?>
Hope that helps... :dubious: