Run another query if variable changes

PHP
1

I’m trying to get a query to run if a variable changes
I have one table (review) and if the worker record changes, another table (worker) which has a review_count field and id like to increment it by 1, like…

<?php
$id = $_POST['id'];

$r_date = date('Y-m-d', strtotime(str_replace('-', '/', $_POST['r_date'])));
$p_id = $_POST['p_id'];
$rating = $_POST['rating'];
$review = $_POST['review'];

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    

try {

$sql = "UPDATE review SET
r_date = :r_date,
p_id = :p_id,
rating = :rating,
review = :review,
WHERE id = :id";

//run query on worker table to add 1 to review  
//count field where p_id is the primary key of the worker table

$stmt = $dbh->prepare($sql);

$stmt->execute(array(
 id => $id,
 :r_date => $r_date,
 :p_id => $p_id,
 :rating => $rating,
 :review => $review,
  ));
header("Location: reviews.php");

exit;
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
?>

thanks…

2

I would think you could do this.

$sql = "UPDATE review SET
  r_date = :r_date
, p_id = :p_id
, rating = :rating
, review = review + 1
WHERE id = :id";
3

First impulse: Dont do this unless the count is going to be used frequently. Instead count the review table when you need to display the count.

But, to answer your question directly:

$sql = "UPDATE review SET
r_date = :r_date,
p_id = :p_id,
rating = :rating,
review = :review,
WHERE id = :id";

$stmt = $dbh->prepare($sql);
$stmt->execute(someparametersherepresmuably);
$sql = "UPDATE worker SET review_count = review_count+1 WHERE id =  ?";
$stmt = $dbh->prepare($sql);
$stmt->execute(anarraywiththeidparameter);
4

ok, I use the review_count and I just didn’t know how to display the count from another table, sop heres my PHP to show t5he variable

<?=$row['r_count']?>

But,I guess its better to do away with that variable and just get a count from the other table like…

$result = mysql_query("SELECT COUNT(*) FROM worker WHERE p_id == '.$id");
if (!$result) {
die('Error:' . mysql_error());
}
echo mysql_result($result, 0);

Is that ok?

5

Errrm, AFAIK you can not mix DEPRECATED mysql_ functions with PDO and you will need to change those out.

6

like this then

$sql = "SELECT count(*) FROM worker WHERE p_id == '.$id.'"; 
$result = $con->prepare($sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn();

echo $num_of_rows;
7 
$sql = "
    SELECT count(*)
    FROM worker
    WHERE p_id = :id -- there is no == in MySql
";
$result = $con->prepare($sql); 
$result->execute(array(':id' => $id));
$number_of_rows = $result->fetchColumn();
echo $num_of_rows;
8

Thanks, that worked great. The only (minor) correction was to var name was different.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.