Topic: [SOLVED] Help updating two rows

[bcraig]

Can anyone help me by looking at this code and telling me why it will only update one row. The fields im trying to update are sub_counter and counter but on seperate rows.


I have a table 'categories' which has sub categoriges connected to main categories thru 'parent_id'

When i insert a new record into 'auctions' i want the counter fields  in 'categories' to update by adding 1 to their existing number where cat_id equals $_POST['sub_cat'] and parent_id equals $_POST['main_cat']

but its adding "1" to 'counter' and 'sub_counter' on the same row and only where cat_id equals $_POST['sub_cat']


this is example of table 'categories'

cat_id      |     parent_id      |     cat_name      |     counter     |     sub_counter
    1                   0                   MAINONE                 0                     0
    2                   0                   MAINTWO                0                     0
    3                   0                   MAINTHREE              0                     0
    4                   0                   MAINFOUR                0                     0
    5                   1                   subOneA                  0                     0
    6                   1                   subOneB                  0                     0
    7                   2                   subTwoA                  0                     0
    8                   2                   subTwoB                  0                     0
    9                   3                   subThreeA                0                     0
    10                  3                   subThreeB               0                     0
    11                  4                   subFourA                 0                     0
    12                  4                   subFourB                 0                     0

Code: [Select]

<?php require_once('Connections/jobsnz_conn.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "createAuction")) {
  $insertSQL = sprintf("INSERT INTO auctions (`user`, title, description, pict_url, category, parent_category, duration, start_price, location, payment, required_qual, required_ref, supply_material, deadline, preffered_day) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                      GetSQLValueString($_POST['user'], "text"),
                      GetSQLValueString($_POST['title'], "text"),
                      GetSQLValueString($_POST['description'], "text"),
                      GetSQLValueString($_POST['image'], "text"),
                      GetSQLValueString($_POST['sub_cat'], "int"),
                      GetSQLValueString($_POST['main_cat'], "int"),
                      GetSQLValueString($_POST['duration'], "int"),
                      GetSQLValueString($_POST['startprice'], "double"),
                      GetSQLValueString($_POST['check_location'], "text"),
                      GetSQLValueString($_POST['payment'], "text"),
                      GetSQLValueString($_POST['quals'], "text"),
                      GetSQLValueString($_POST['refs'], "text"),
                      GetSQLValueString($_POST['materials'], "text"),
                      GetSQLValueString($_POST['deadline'], "date"),
                      GetSQLValueString($_POST['day'], "text"));

$updateSQL = sprintf("UPDATE categories SET sub_counter=%s WHERE cat_id=%s",
                       GetSQLValueString($_POST['sub_counter'], "int"),
                       GetSQLValueString($_POST['sub_cat'], "int"));
$updateSQL2 = sprintf("UPDATE categories SET counter=%s WHERE parent_id=%s",
                       GetSQLValueString($_POST['counter'], "int"),
                       GetSQLValueString($_POST['main_cat'], "int"));


  mysql_select_db($database_jobsnz_conn, $jobsnz_conn);
  $Result1 = mysql_query($insertSQL, $jobsnz_conn) or die(mysql_error());
  $Result2 = mysql_query($updateSQL, $jobsnz_conn) or die(mysql_error());
  $Result2 = mysql_query($updateSQL2, $jobsnz_conn) or die(mysql_error());
}
?>

Code: [Select]

<form action="<?php echo $editFormAction; ?>" method="POST" name="createAuction">
        <table width="100%"  border="0" cellspacing="0" cellpadding="0" bgcolor="#0099FF">
  <tr>
            <td><input name="counter" type="text" id="counter" value="1">
            <input name="sub_counter" type="text" id="sub_counter" value="1"></td>
            <td><?php $main_cat = $_POST['category']; echo "<input name=\"main_cat\" type=\"text\" id=\"main_cat\" value=\"".$main_cat."\">" ?></td>
          </tr>  
  <tr>
            <td>&nbsp;</td>
            <td><?php $user = $_SESSION['web_user']; echo "<input name=\"user\" type=\"text\" id=\"user\" value=\"".$user."\">" ?></td>
          </tr>
          <tr>
            <td>Sub Category </td>
            <td>
<select name="sub_cat" id="sub_cat">
<?php 
$colname_rs_user = $_SESSION[ "web_user" ];
$sql_subcat = sprintf("SELECT cat_name, cat_id FROM categories WHERE parent_id = %s", $colname_rs_catid);
$res_subcat = mysqli_query($mysqli, $sql_subcat);
$sql_location = sprintf("SELECT city FROM a_users WHERE username = '$colname_rs_user'");
$res_location = mysqli_query($mysqli, $sql_location);

if ($res_subcat) {
while ($newArray = mysqli_fetch_array($res_subcat, MYSQLI_ASSOC)) {
$name = $newArray['cat_name'];
$id = $newArray['cat_id'];

echo "<option value=\"".$id."\">".$name."</option>";
    }
mysqli_free_result($res_subcat);
} else {
printf("Could not retrieve records: %s\n", mysqli_error($mysqli));
} ?>
            </select>
</td>
          </tr>
          <tr>
            <td>Title</td>
            <td><input name="title" type="text" id="title"></td>
          </tr>
          <tr>
            <td>Description</td>
            <td><textarea name="description" cols="50" id="description"></textarea></td>
          </tr>
          <tr>
            <td>Image</td>
            <td><input name="image" type="text" id="image">
            <input type="submit" name="browse" value="Browse"></td>
          </tr>
          <tr>
            <td>Duration</td>
            <td>
<select name="duration">
<option>7 Days</option>
<option>14 Days</option>
<option>28 Days</option>
            </select></td>
          </tr>
          <tr>
            <td>Start Price </td>
            <td><input name="startprice" type="text" id="startprice"></td>
          </tr>
          <tr>
            <td>Your Location</td>
            <td>

<?php
if ($res_location) {
while ($newArray = mysqli_fetch_array($res_location, MYSQLI_ASSOC)) {
$location = $newArray['city'];

echo "<input name=\"check_location\" id=\"check_location\" type=\"radio\" value=\"".$location."\"  checked>".$location."<br />";
    } 
}
?>
           
</td>
          </tr>
          <tr>
            <td>Specify Location</td>
            <td>
<input name="check_specify_location" id="check_specify_location" type="radio" value="specify_location"><input name="specify_location" type="text">
</td>
          </tr>         
  <tr>
            <td>Required Quals </td>
            <td><input name="quals" type="text" id="quals"></td>
          </tr>
          <tr>
            <td>Required Refs</td>
            <td><input name="refs" type="text" id="refs"></td>
          </tr>
          <tr>
            <td>Materials</td>
            <td><input name="materials" type="text" id="materials"></td>
          </tr>
          <tr>
            <td>Payment Method </td>
            <td><input name="payment" type="text" id="materials"></td>
          </tr>
          <tr>
            <td>Deadline</td>

            <td>
<input name="deadline" type="text" id="deadline">
            </td>
          </tr>
          <tr>
            <td>Preffered Day </td>
            <td><select name="day" id="day">
<option>Anytime</option>
<option>Weekend</option>
<option>Mon - Fri</option>
            </select></td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>

</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td><input type="submit" name="Submit" value="Submit"></td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
        </table>
<input type="hidden" name="MM_insert" value="createAuction">
</form>

[fenway]

I question your choice of incrementing a value... this isn't thread-safe.  Also, until the run the select query counterpart to your update statement, you won't know for sure how many rows are matching.

[bcraig]

I know... i dont know how to do that.

So what does that mean i have to do?

[fenway]

Why do you think 1 row is not enough?

[bcraig]

what are you talking about??

At the moment i just want to know how to update 2 seperate fields on seperate rows at once from post data ill work out the increment later

i want the inputs("counter" and "sub_counter" which equal 1) to post into the update query
so my table will end up looking like this example....



cat_id      parent_id      name      counter     subcounter
1                  0            one           1                0
2                  1            sub           0                1


after ill change it so it will be like... UPDATE categories SET counter=counter+1(however itsdone)  instead of using post



at the moment my code only doing this...

cat_id      parent_id      name      counter     subcounter
1                  0            one           0                0
2                  1            sub           1                1

[fenway]

what are you talking about??

Your OP asked "why it will only update one row".

I don't know what you mean by "instead of using  post".  You want to update 2 fields in a single row?  No... separated rows.  Related rows?

[bcraig]

I want to update two different fields on two different rows on submit

I have a table called 'categories' and inside it has main categories and sub categories...

categories
cat_id      parent_id      cat_name       counter         sub_counter
    1              0             MainOne           2                     0
    2              0             MainTwo           2                     0
    3              0             MainThree         1                     0
    4              0             MainFour           0                     0
    5              1             subOneA           0                     1
    6              1             subOneB           0                     1
    7              2             subTwoA           0                     2
    8              2             subTwoB           0                     0
    9              3             subThreeA          0                     1
    10             3             subThreeB          0                     0
    11             4             subFourA           0                     0
    12             4             subFourB           0                     0

And i have a table 'auctions'
auctions
id     title        category      parent_category
1     title1             5                   1
2     title2             6                   1
3     title3             7                   2
4     title4             7                   2
5     title5             9                   3

When i insert a new record into 'auctions' i also want to update the counters in 'categories' so sub_counter increases by 1 on the row that matches the sub category selected by the user. And counter increases by 1 on the row that matches the main category selceted by the user


example:
If the user wants to insert a record with the main category as MainOne and in the sub category as subOneA the categories table will update so that the row that has cat_id of "1" will add 1 to the allready existing number in the field 'counter'. Then upadate the row that has cat_if of "5" by adding 1 to the existing number in the field sub_counter.

So on the browse categories page i can echo the main category names and sub category names with the 'counter' number next to the main category names and 'sub_counter' number next to the sub category names to show how many records listed in each category.

haha hope that makes sense


Vesions in using:
MySQL 5.0.45
PHP 5.2.5
APACHE 2.2.6

[fenway]

Yes, you've posted that many times... post JUST the two update queries.

[bcraig]

Thanks for trying to help but i worked around it.

Insted of trying to put all the updates all together i seperated them and it worked.

Result:

Code: [Select]

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "createAuction")) {
  $updateSQLa = sprintf("UPDATE categories SET sub_counter=%s WHERE cat_id=%s",
                       GetSQLValueString($_POST['sub_counter'], "int"),
                       GetSQLValueString($_POST['sub_cat'], "int"));

  mysql_select_db($database_jobsnz_conn, $jobsnz_conn);
  $Resulta = mysql_query($updateSQL, $jobsnz_conn) or die(mysql_error());
}
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "createAuction")) {
  $updateSQLb = sprintf("UPDATE categories SET counter=%s WHERE cat_id=%s",
                       GetSQLValueString($_POST['counter'], "int"),
                       GetSQLValueString($_POST['main_cat'], "int"));

  mysql_select_db($database_jobsnz_conn, $jobsnz_conn);
  $Result1b = mysql_query($updateSQLb, $jobsnz_conn) or die(mysql_error());
}