Checking all the Values of the column from selected row

Question

I would like to find out if all the column of the row selected has the equal value. And if all have the same values I would like to perform some action.

id (tableA)   id2 (reference tableB) status
-------------------------------------------
     1               2               On 
     2               2               Off
     3               3               On
     4               3               On

if id2=3 have the same value of status { // perform action here }. How can I check the values of id=3 then perform some action here? I'm using mysql and php.

I don't know if I'm doing it right:

$change = mysql_query("SELECT status from  tableA where status =   'On' and id=3 ");
if ($change == 'On')// if all the columns

{ mysql_query("  UPDATE tableB 
         SET status2 = 'On Going' where id2 =3")or die(mysql_error());
}

Result should be updated in tableB:

id    status2
---------------
3     On Going

Please edit your question and include the results you would like to get back for your sample data. — Gordon Linoff, Mar 27 '15 at 06:04
what action you want to perform? id2=3 have the same value of status menas?? — Kavin Smk, Mar 27 '15 at 06:05

score 0 · Answer 1 · answered Mar 27 '15 at 06:13

0

$change = mysql_query("SELECT `stat` from  `order_details` 
          where `stat` =  'Cancelled'");
$res=mysql_fetch_array($change);

remember it will fetch only last value from database if you want all the records you must use while loop

    if ($res == "Cancelled")
{


         mysql_query("UPDATE `order1` 
                 SET `status` = 'Cancelled' where `order_id` ='".$order_id."'")or die(mysql_error());
    }

try this...

answered Mar 27 '15 at 06:13

Vivek Singh

2,453
1
14
27

How will I know if $res == "Cancelled" ? – chang Mar 27 '15 at 06:16
you are matching the condition in query and then you miss to fetch the matching results that is what i have corrected.. – Vivek Singh Mar 27 '15 at 06:17

score 0 · Accepted Answer · edited May 23 '17 at 12:33

You might want to use mysql_num_rows() function of PHP to achieve your goal.
Are you sure that status column name is different from the stat column name? And they are not the same column?
Use a tick (') to parametize the variable in your query.
At least use mysql_real_escape_string() function.
You should practice mysqli_* prepared statement rather than the deprecated mysql_* to prevent SQL injections.

Your revised code:

$change = mysql_query("SELECT stat FROM order_details WHERE stat = 'Cancelled'");
if (mysql_num_rows($change) == 1){ /* IF THE QUERY FOUND 1 RESULT */
  mysql_query("UPDATE order1 SET status = 'Cancelled' WHERE order_id = '$order_id'") or die(mysql_error());
}

If you'll be doing it in prepared statement:

if($stmt = $YourConnectionDB->prepare("SELECT stat FROM order_details WHERE stat = 'Cancelled'")){

  $stmt->execute();
  $stmt->store_result();
  $numberofrows = $stmt->store_result();

  if($numberofrows == 1){
    if($stmt2 = $YourConnectionDB->prepare("UPDATE order1 SET status = 'Cancelled' WHERE order_id = ?")){
      $stmt2->bind_param("i",$order_id);
      $stmt2->execute();
      $stmt2->close();
    } /* END OF SECOND PREPARED STATEMENT */
  } /* END OF CHECKING NUMBER OF ROWS */

  $stmt->close();
} /* END OF PREPARED STATEMENT */

thank you for revising my code. $change = mysql_query("SELECT stat from order_details where stat = 'Cancelled ' and order_id= $order_id "); if (mysql_num_rows($change) == 1){ /* IF THE QUERY FOUND 1 RESULT */ mysql_query("UPDATE order1 SET status = 'Cancelled' WHERE order_id = '$order_id'")or die(mysql_error()); } — chang, Mar 27 '15 at 06:23
@chang - did it solved your problem? If it did, you can accept my answer or simply upvote it. — Logan Wayne, Mar 27 '15 at 06:24
order_id= $order_id - this is the only line that's missing in the code that you gave but it is working now. thank you so much for your help! — chang, Mar 27 '15 at 06:30

Checking all the Values of the column from selected row

2 Answers2