putting customerid that hasnt been created while registering

Question

The title is probably complicated, but i'll explain here. SO basically when the customer registers, they get a unique customer code, which is auto incremented in the database. However, while registering, the customer adds credit card info and the customer code is a foreign key in the card table. Now what I want to do is put the customer code into card table. Is it possible or is it better to just ask user to add credit card when registered?

My queries look like this:

<?php
$connection = 
mysql_connect("com-db-02.student-cit.local", "team16", "DbSLzU")
or die (mysql_error());


$db = mysql_select_db("team16") or die(mysql_error());

$DepotId = $_POST['depotId'];
$FName = $_POST['custFName'];
$LName = $_POST['custLName'];
$Email = $_POST['custEmail'];
$Address = $_POST['address'];
$Town = $_POST['town'];
$County = $_POST['county'];
$Eircode = $_POST['eircode'];
$Phone = $_POST['phone'];
$Password = $_POST['custPassword'];
$cardtype = $_POST['cardType'];
$cardHolder = $_POST['cardHolder'];
$cardNumber = $_POST['cardnumber'];
$expiryDate = $_POST['expirydate'];
$securitycode = $_POST['securitycode'];
// TO DO OTHER TEXTBOXES

$query1 = "SELECT DEPOTID FROM DEPOT WHERE LOCATION ='$DepotId'";

$result1 = mysql_query($query1);
$depotID = -1;

while($row = mysql_fetch_array($result1))
{
        $depotID = $row['DEPOTID'];
}
$query3 = "INSERT INTO card(customerCode) SELECT customerCode * FROM customer WHERE customerCode= customerCode";
$query = "INSERT INTO CUSTOMER(DEPOTID, CUSTFNAME, CUSTLNAME, CUSTEMAIL, ADDRESS, TOWN, COUNTY, EIRCODE, 
        PHONE, CUSTPASSWORD) VALUES('$depotID','$FName', '$LName', '$Email', '$Address', 
            '$Town', '$County', '$Eircode', '$Phone', '$Password')";

$query2 = "INSERT INTO CARD(CARDTYPE, CARDHOLDER, CARDNUMBER, EXPIRYDATE, SECURITYCODE, CUSTOMERCODE) VALUES('$cardNumber','$cardtype', '$cardHolder', '$expiryDate', '$securitycode')";

/*$result = mysql_query($query);*/
$result = mysql_query($query2);
$result = mysql_query($query3);
echo $query2();
echo $query3();
?>

I need to put customer code into card table but the code doen't exist yet

It is better that you ask the user to enter the Card information during the registration,but when you use INSERT QUERY, it should be two QUERIES. One for inserting USER in Table and if successful insertion get the CUSTOMER_ID of the LAST_INSERTED_ROW in USERS table, then built your 3rd QUERY to insert CARDS information in a different table like USER_CARD_INFO. @Matuesz — Asif Mehmood, Mar 08 '16 at 19:56
First you'll have to enter the Data in Customers Table, as your business logic says: **"CUSTOMERS OWN/HAVE CARDS (not the cards own/have users :-) GOT-IT)"** — Asif Mehmood, Mar 08 '16 at 20:02
@MalikAsif I get that you need to have 3 queries, the thing i don't get is how to extract the customercode from customer table and then put it into card table. — Mateusz, Mar 08 '16 at 20:35
You will have to get the LAST_INSERT_ID() [That is why it was CAPITILIZED] . Happy that you have accepted the answer showing that your problem is solved :-) @Mateusz — Asif Mehmood, Mar 09 '16 at 07:11

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

You can obtain the last inserted auto-incremented value with LAST_INSERT_ID().

You should perform first the INSERT INTO CUSTOMER statement ($query). After that you can immediately insert the card record together with the customer reference like this:

INSERT INTO CARD (CUSTOMERCODE, CARDTYPE, CARDHOLDER, CARDNUMBER, EXPIRYDATE, 
                  SECURITYCODE)
          VALUES (LAST_INSERT_ID(), ?, ?, ?, ?, ?);

The question marks are place holders for the values to insert with a prepared statement. You should use prepared statements to avoid SQL injection.

Also, don't use mysql_ functions, they are deprecated. Use mysqli_ or PDO instead.

Note that in $query3 you had an * that did not belong there, but anyway, you don't need that statement any more. In the solution you need only two queries, not three. The second reads the last inserted customer code and at the same time performs the card insertion.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 08 '16 at 20:42

trincot

317,000
35
244
286

so i put "INSERT INTO CARD..." straight after the query or $result? also you have customercode twice in card, is that right? – Mateusz Mar 08 '16 at 20:50
Yes, you execute (1) `INSERT INTO CUSTOMER` and then (2) `INSERT INTO CARD` with the special function `LAST_INSERT_ID()`. I removed the duplicate field. Thanks for pointing that out, I had not noticed you had it already there and I blindly copied it. – trincot Mar 08 '16 at 21:22
I get fatal error for some reason, it says: Fatal error: Call to undefined function INSERT INTO CARD(CARDNUMBER, CUSTOMERCODE, CARDTYPE, CARDHOLDER, EXPIRYDATE, SECURITYCODE,) VALUES('02158', LAST_INSERT_ID, 'Amex', 'jack jackson', '11/01/2019', '033')() – Mateusz Mar 08 '16 at 21:31
You have a trailing comma before the closing parentheses: `SECURITYCODE,)`, and you have some trailing parenthesis `()` at the end of the statement.... AND you do not have the necessary parentheses after `LAST_INSERT_ID`. It should be `LAST_INSERT_ID()`. – trincot Mar 08 '16 at 21:33
I got rid of the comma, I don't know why the parenthesis appears at the end. I added the parentheses at the end of last_insert_id. Maybe I should define last_insert_id before? This is how the query looks now: $query2 = "INSERT INTO CARD(CARDNUMBER, CUSTOMERCODE, CARDTYPE, CARDHOLDER, EXPIRYDATE, SECURITYCODE) VALUES('$cardNumber', LAST_INSERT_ID(), '$cardtype', '$cardHolder', '$expiryDate', '$securitycode')"; – Mateusz Mar 08 '16 at 22:20
OK, it turns out my error was so stupid, I added or else die(mysql error) and it told me I used unsupported date format. – Mateusz Mar 09 '16 at 00:11
it would work fine @trincot . But can you tell me (just for information) that if many simultaneous requests are sent to the server for registering. In what way would it behave **LAST_INSERT_ID** (would it not create ambiguity) (I mean to say would it not in rare cases link wrong customers with the some one elses CARD_INFORMATION – Asif Mehmood Mar 09 '16 at 07:14
1

That question was answered [here](http://stackoverflow.com/questions/21185666/mysql-last-insert-id-and-concurrency). Please, for your own sake, make sure to move away from mysql_ functions, as they are not maintained any more. Also stop inserting strings in SQL statements. It is ***not*** safe. You will not regret making the move to prepared statements with PDO or mysqli. Do it the right way. – trincot Mar 09 '16 at 08:12

putting customerid that hasnt been created while registering

1 Answers1