primary key and foreign key in php

Question

Hi guys i really need some help down here with these primary keys and foreign keys.I've tried searching for leads but i'm not able to figure it out. Please help me out here and i'm sorry if this question has been asked before

i've got 2 tables and their columns

client id(pk) | organisation | description | token

In the transaction table, all the values in it were manually key in by me through php myadmin even selecting the c_id which is the foreign key.so my question is for example i create a new organisation called ccc, i want it to get automatically assigned to the c_id of 3 in the transaction table.Also everytime i update the tokens, it should display in the trasaction table as trx_id(i already set it as auto increament) but the c_id should match the organisation i've selected and increment the row accordingly.........Hope you guys can help me out quick and i'm sorry if i'm confusing you......I can post the code if you guys want :)

This script below add the client

<?php 

include 'connection.php';

// Get values from form
$organisation=$_POST['organisation'];
$description=$_POST['description'];
$token=$_POST['token'];


// Insert data into mysql
$sql="INSERT INTO client(organisation, description, token)VALUES('$organisation', '$description', '$token')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful".
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='login_success.php'>Back to main page</a>";
}

else {
echo "ERROR";
}

// close connection
mysql_close();
?>

transaction table script

    <?php 

include 'connection.php';

?>  
<html>
<head>
<title>ThaiCreate.Com PHP & MySQL Tutorial</title>
</head>
<body>
<form action="add.php?id=<?=$_GET["id"];?>" name="frmEdit" method="POST">
<?
$strSQL = "SELECT * FROM client WHERE id = '".$_GET["id"]."' ";
$objQuery = mysql_query($strSQL);
$objResult = mysql_fetch_array($objQuery);
if(!$objResult)
{
echo "Not found id=".$_GET["id"];
}
else
{
?>
<table width="600" border="1">
<tr>
<th width="91"> <div align="center">id </div></th>
<th width="160"> <div align="center">organisation </div></th>
<th width="198"> <div align="center">current token </div></th>
</tr>
<tr>
<td><?=$objResult["id"];?></div></td>
<td><?=$objResult["organisation"];?></td>
<td><?=$objResult["token"];?></td>
</tr>
</table>

How many tokens do you want to add to this organisation: <input type="text" id="newToken" name="newToken" /><br/>
Please fill up the reason for adding the tokens : <input type="text" id="reason" name="reason" /><br/>
<input type="submit" name="submit" value="submit">

<? 

$newToken = isset($_POST['newToken']) ? $_POST["newToken"] : "";
$reason = isset($_POST['reason']) ? $_POST["reason"] : "";


$sql="UPDATE client SET token = token + '$newToken' WHERE id = '".$_GET["id"]."' ";

$result=mysql_query($sql) or die ("Error Query [".$sql."]");
?>

<?

if ($newToken == true)
{

mysql_query("START TRANSACTION");
$date = date("Y-m-d H:i:s");

$query_string_count = "SELECT count(*) AS num FROM transaction WHERE c_id =" . $_GET["id"]; 
  $query = mysql_query($query_string_count);
  $result = mysql_fetch_array($objQuery);

  $num = $objResult["num"];

  INSERT INTO `transaction` (`trx_id`, `c_id`, `trx_description`, `trx_date`, `action`, `no_of_token`) VALUES ($num + 1, $_GET["id"], '$reason', '".$date."', 'Add token', '$newToken')";

  mysql_query("COMMIT TRANSACTION");





?>

<?
header("Location:login_success.php"); 
}
else {



}
?>

<?

}
mysql_close();

?>
</form>
</body>
</html>

how do i post my script here should i do it under the comments part — user1270779, Mar 15 '12 at 08:41

ab_dev86 · Answer 1 · 2012-03-15T09:36:45.820

1

The problem is that you set up the trx_id as auotoincrement. If you need keep separated the number of transcation of each client then the trx_id shouldn't be autoincrement.

For example each time you do insert on the transaction table:

1: start new transcation 2: number = count the number transactions that has as c_id the id of the client of interest 3: insert into transaction and set trx_id = number + 1 4: commit transaction

Note: in this case trx_id could not unique as you have more clients. If you need it you can insert a new column in the transaction table that is primary_key and in this case autoincrement

Here a snapshot of code to do what i described:

    //new db transaction
  mysql_query("START TRANSACTION");

  // count number of transaction for clients $_GET["id"]
  $query_string_count = "SELECT count(*) AS num FROM transactions WHERE c_id =" . $_GET["id"]; 
  $query = mysql_query($query_string_count);
  $result = mysql_fetch_array($objQuery);

  $num = $objResult["num"];

  //insert new transaction for client $_GET["id"] with id $num + 1
  INSERT INTO `transaction` (`trx_id`, `c_id`, `trx_description`, `trx_date`, `action`, `no_of_token`) VALUES ($num + 1, $_GET["id"], '$reason', '".$date."', 'Add token', '$newToken')";

   //COMMIT -> persist on db
   mysql_query("COMMIT TRANSACTION");

edited Mar 15 '12 at 09:36

answered Mar 15 '12 at 08:41

ab_dev86

1,952
16
21

1

OP might also be interested to know why and how to use transactions and that he needs `InnoDB` as table engine to use them. http://dev.mysql.com/doc/refman/5.0/en/commit.html and same examples http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples – Basti Mar 15 '12 at 08:50
so right now what i have is that for inserting into the clients table its $sql="INSERT INTO client(organisation, description, token)VALUES('$organisation', '$description', '$token')"; and inserting into transaction table its $date = date("Y-m-d H:i:s"); $sql = "INSERT INTO `transaction` (`trx_id`, `c_id`, `trx_description`, `trx_date`, `action`, `no_of_token`) VALUES ('$reason', '".$date."', 'Add token', '$newToken')"; $result=mysql_query($sql); $result=mysql_query($sql) – user1270779 Mar 15 '12 at 08:54
i just posted the new code.....i'm sorry if i'm just using your code but i'm pretty desperate and lost for now.... – user1270779 Mar 15 '12 at 09:51
:) that's why forums exist. However for production code always sanitize the input you receive: mysql_real_escape_string($_GET["id"]); $organisation= mysql_real_escape_string($_POST['organisation']); and so on for each $_GET or $_POST value you use – ab_dev86 Mar 15 '12 at 10:09
Is there any way for me to get in touch wid you to explain this more......i really need this help asap......its my job.....i'm so sorry – user1270779 Mar 15 '12 at 10:18
ho sir i'm not able to view your email.Could you kindly pls go over to my profile and send me a test mail pls. – user1270779 Mar 15 '12 at 10:59
i'm really sorry for the trouble – user1270779 Mar 15 '12 at 11:17
same for me, can't see your mail post it here i'll write you – ab_dev86 Mar 15 '12 at 11:53

primary key and foreign key in php

1 Answers1