[SOLVED] Help updating two rows

bcraig · December 18, 2007

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

<?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());
}
?>

<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> </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> </td>
            <td>

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

fenway · December 18, 2007

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 · December 18, 2007

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

So what does that mean i have to do?

fenway · December 18, 2007

Why do you think 1 row is not enough?

bcraig · December 18, 2007

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 · December 18, 2007

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 · December 19, 2007

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 · December 19, 2007

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

bcraig · December 21, 2007

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:

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());
}

Sign In

[SOLVED] Help updating two rows

Recommended Posts

bcraig

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

bcraig

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

bcraig

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

bcraig

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

bcraig

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information