0

how can i update and insert together

        require_once ('database.php'); 

$name = mysql_real_escape_string ($_REQUEST["name"]);
$course = mysql_real_escape_string ($_REQUEST["course"]);
$email = mysql_real_escape_string ($_REQUEST["email"]);
$contact = mysql_real_escape_string ($_REQUEST["contact"]);
$Date = mysql_real_escape_string ($_REQUEST["Date"]);

$sql = "SELECT * FROM registerlist WHERE name = '" . $name . "'";
$result = mysql_query ($sql, $dbconn); 

if (mysql_num_rows ($result) > 0) {
    $resultStr = header("Location:blog.php");

} else {

    $result = "SELECT * FROM courselist WHERE cname = '" . $course 
    . "'";
    $row=mysql_fetch_row($result);

    $sql = "INSERT INTO registerlist (name, Course, Email, Contact, 
  Date) VALUES ('" . $name . "', '" . $course . "', '" . $email . "', '" . 
   $contact . "','" . $date . "')";

    $result1= mysql_query($sql, $dbconn);
 $result =mysql_query("UPDATE courselist SET $Row['slot'] = 
           '$Row['slot'] - 1 '");
    if ($result1) {
    $resultStr = header("Location:blog.php");

    } 

  }

  echo json_encode($resultStr);

if the person register the course, the course slot will subract by 1 and student document will be insert into registerlist database.

qqq ss
  • 1
  • 1
  • 9
    mysql_* functions are deprecated as of PHP 5.5.0, and removed as of PHP 7.0.0. Switch your code to use [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) or [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. Be sure to use prepared statements and parameter binding, so **you'll never have to worry about quoting issues again.** – aynber Feb 18 '19 at 13:56
  • Check out [this](https://stackoverflow.com/questions/9927097/insert-and-select-in-single-query-mysql) – Swati Feb 18 '19 at 14:03
  • Possible duplicate of [How do you connect to multiple MySQL databases on a single webpage?](https://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage) – ᴄʀᴏᴢᴇᴛ Feb 18 '19 at 14:05

1 Answers1

0

I hope I did understand correctly: You want to update the courselist table at the same time a record was insert into the registerlist table? This can be done using triggers (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html , IF both databases run at the same SQL server) and/or table locks (https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html):

  1. Without trigger

    • Lock table courselist
    • Insert the record to registerlist
    • Update table courselist
    • Release table courselist

  2. With trigger You need a trigger that locks the table courselist before writing to the registerlist table, and a trigger that updates courselist and releases the lock after writing to registerlist. In this case you only insert the record into registerlist from your PHP code, and the table locking and courselist update is being done by the triggers within the SQL server.

In any case you can't write to both tables at the same time, there is no SQL statement to do that. But with locks you can simulate such a behavior.

When defining the target table of a SQL statement, you may prepend the tables database name like databaseName.tableName, if the Connection uses a different database per Default.

But aynber from the comments is absolutely right - you should move away from mysql_* asap!

Edit: This SQL example should show how table locking is working (all information about that can be found in the MySQL documentation from the link above):

LOCK TABLES courselist WRITE;
INSERT INTO registerlist …;
UPDATE courselist …;
UNLOCK TABLES;

You'll Need a WRITE lock, since you're going to write to the table. Other reading, writing or locking statements from other sessions are blocked until you release the lock.

A READ lock would prevent the table from being modified by any session. Writing (and write locking) attempts are blocked until you release the lock and all other READ locks from other sessions are released, too.

nd_
  • 93
  • 1
  • 9