Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Can't get this code working. the statement works fine directly on the table, just not when executed from the PHP file:

    <strong>Multi Update</strong><br>

    <?php
    $host="localhost"; // Host name
    $username="user_name"; // Mysql username
    $password="Password"; // Mysql password
    $db_name="dbname"; // Database name
    $tbl_name="tablename"; // Table name

    // Connect to server and select databse.
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");

    $sql="SELECT 
      mik1vm_product.product_id,
      mik1vm_product.product_sku,
      mik1vm_product.product_name,
      mik1vm_product.product_in_stock,
      mik1vm_product.product_in_stock_cardiff
    FROM $tbl_name";
    $result=mysql_query($sql);

    // Count table rows
    $count=mysql_num_rows($result);
    ?>
    <table width="500" border="0" cellspacing="1" cellpadding="0">
    <form name="form1" method="post" action="">
    <tr>
    <td>
    <table width="500" border="0" cellspacing="1" cellpadding="0">


    <tr>
    <td align="center"><strong>Product Id</strong></td>
    <td align="center"><strong>SKU</strong></td>
    <td align="center"><strong>Name</strong></td>
    <td align="center"><strong>In Stock</strong></td>
    <td align="center"><strong>In Stock Cardiff</strong></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td align="center"><? $product_id[]=$rows['product_id']; ?><? echo $rows['product_id']; ?></td>
    <td align="center"><input name="product_sku[]" type="text" id="product_sku" value="<? echo $rows['product_sku']; ?>"></td>
    <td align="center"><input name="product_name[]" type="text" id="product_name" value="<? echo $rows['product_name']; ?>"></td>
    <td align="center"><input name="product_in_stock[]" type="text" id="product_in_stock" value="<? echo $rows['product_in_stock']; ?>"></td>
    <td align="center"><input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="<? echo $rows['product_in_stock_cardiff']; ?>"></td>
    </tr>
    <?php
    }
    ?>
    <tr>
    <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
    </tr>
    </table>
    </td>
    </tr>
    </form>
    </table>
    <?php
    // Check if button name "Submit" is active, do this
    if($Submit){
    for($i=0;$i<$count;$i++){
    $sql1="UPDATE $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'";
    $result1=mysql_query($sql1);
    }
    }

    if($result1){
    header("location:test.php");
    }
    mysql_close();
    ?>

The page loads fine, all data is loaded correctly. but just doesn't update the DB.

Thanks in advance for any help.

UPDATE

OK So I guess I was a bit off the mark here, Obviously I'm new to PHP so please be patient.

I modified the code to:

    <strong>Multi Update</strong><br>

    <?php
    $host=""; // Host name
    $username=""; // Mysql username
    $password=""; // Mysql password
    $db_name=""; // Database name
    $tbl_name=""; // Table name


    // Connect to server and select databse.
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");

    $sql="SELECT 
      mik1vm_product.product_id,
      mik1vm_product.product_sku,
      mik1vm_product.product_name,
      mik1vm_product.product_in_stock,
      mik1vm_product.product_in_stock_cardiff
    FROM $tbl_name";
    $result=mysql_query($sql);

    // Count table rows
    $count=mysql_num_rows($result);
    ?>
    <table width="500" border="0" cellspacing="1" cellpadding="0">
    <form name="form1" method="post" action="">
    <tr>
    <td>
    <table width="500" border="0" cellspacing="1" cellpadding="0">


    <tr>
    <td align="center"><strong>Product Id</strong></td>
    <td align="center"><strong>SKU</strong></td>
    <td align="center"><strong>Name</strong></td>
    <td align="center"><strong>In Stock</strong></td>
    <td align="center"><strong>In Stock Cardiff</strong></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td align="center"><? $product_id[]=$rows['product_id']; ?><? echo $rows['product_id']; ?></td>
    <td align="center"><input name="product_sku[]" type="text" id="product_sku" value="<? echo $rows['product_sku']; ?>"></td>
    <td align="center"><input name="product_name[]" type="text" id="product_name" value="<? echo $rows['product_name']; ?>"></td>
    <td align="center"><input name="product_in_stock[]" type="text" id="product_in_stock" value="<? echo $rows['product_in_stock']; ?>"></td>
    <td align="center"><input name="product_in_stock_cardiff[]" type="text" id="product_in_stock_cardiff" value="<? echo $rows['product_in_stock_cardiff']; ?>"></td>
    </tr>
    <?php
    }
    ?>
    <tr>
    <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
    </tr>
    </table>
    </td>
    </tr>
    </form>
    </table>
    <?php
    // Check if button name "Submit" is active, do this

    if(isset($_POST['Submit']) && $_POST['Submit'] == 'Submit'){
        for($i=0;$i<$count;$i++){
            $sql1="UPDATE $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'";
            $result1=mysql_query($sql1);
        }
    }


    if($result1){
    header("location:test.php");
    }
    mysql_close();
    ?>

Which is now giving errors:

On initial page load: e: Undefined variable: sql1 in test.php on line 74

But when the page is submitted there are loads:

Notice: Undefined variable: product_sku in test.php on line 70

Notice: Undefined variable: product_name in test.php on line 70

Notice: Undefined variable: product_in_stock in test.php on line 70

Notice: Undefined variable: product_in_stock_cardiff in test.php on line 70

The list goes on......

Final line:

Notice: Undefined variable: product_in_stock_cardiff in /test/test.php on line 70 UPDATE mik1vm_product SET product_sku='', product_name='', product_in_stock='' , product_in_stock_cardiff='' WHERE product_id='74'

share|improve this question
 
you can leave out the ?> at EOF. is error reporting and display errors set properly? any error or warning visible? –  Hajo Apr 22 '12 at 15:29
 
Get any errors? Where is $product_sku or even $Submit set? –  Aaron W. Apr 22 '12 at 15:31
 
No there are no errors at all, on submit the page re-loads, but the data reverts to the values from the DB even if changed before the update. So I can only assume there is an error, but I can't find out what. –  Paul Apr 22 '12 at 15:36
 
Make sure that $Submit it actually true. Also, I don't see where any of the variables used in the UPDATE query are being initialized. Try echo $sql1; exit; right after the query (before result1...). That will show whether the query is being run at all and what exactly its running. –  Ayman Safadi Apr 22 '12 at 15:40
add comment

1 Answer

up vote 0 down vote

I don't see that you're $Submit variable is set anywhere. Try

if(isset($_POST['Submit']) && $_POST['Submit'] == 'Submit'){
    for($i=0;$i<$count;$i++){
        $sql1="UPDATE $tbl_name SET product_sku='$product_sku[$i]', product_name='$product_name[$i]', product_in_stock='$product_in_stock[$i]' , product_in_stock_cardiff='$product_in_stock_cardiff[$i]' WHERE product_id='$product_id[$i]'";
        $result1=mysql_query($sql1);
    }
}

In to future you'll be able avoid mistakes like this, with setting:

ini_set('error_reporting', E_ALL);

Or the equivalent setting in your php.ini file.

share|improve this answer
2  
You're assuming the OP has a form with an element named "Submit", with a value of "Submit". That's a pretty big assumption. –  Ayman Safadi Apr 22 '12 at 15:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.