1

So I have 3 tables: donor, blood_type, user_account. I am trying to populate the donor table which contains user_id and blood_id, but there is no join between the blood_group and the user_account table so I tried this, but it didn't work. Can someone please tell what I am doing wrong? I am very new to php and databases. 

<?php
if(isset($_POST['submit'])) { 

    $conn = mysqli_connect("localhost", "root" , "");

    if(!$conn) { 
        die("Cannot connect: "); 
    }

    mysqli_select_db($conn,"blood_bank_project"); 

    $sql = "INSERT INTO user_account(username, password) VALUES ('$_POST[user]', '$_POST[psw]');";
    $sql .="INSERT INTO donor(first_name,last_name,email_add,gender, birthday, telephone, city, last_donation,user_id, blood_id)VALUES('$_POST[fname]', '$_POST[lname]', '$_POST[email]', '$_POST[gender]', '$_POST[Birthday]', '$_POST[Telephone]', '$_POST[city]', '$_POST[lastdonation]')";  
    $sql .="UPDATE donor SET blood_id = (SELECT blood_id from blood_type where blood_group= '$_POST[bloodgroup]');";  
    $sql .="UPDATE donor SET user_id = (SELECT user_id from user_account where username= '$_POST[user]')"; 

    if(mysqli_multi_query($conn, $sql)){ 
        echo'executed'; 
    } 
} 
?>
eestrada
  • 1,575
  • 14
  • 24
loreen99
  • 43
  • 7
  • 1
    Post code and your output or else you will get down-voted a lot. – Xogle Apr 13 '16 at 21:06
  • I posted it it is not showing for some reason – loreen99 Apr 13 '16 at 21:08
  • Use the `{ }` code markup tool in the SO editor. – Barmar Apr 13 '16 at 21:10
  • Is the name of the table `blood_group` or `blood_type`? – Barmar Apr 13 '16 at 21:13
  • Can you post your HTML form as well? – Kurt Leadley Apr 13 '16 at 21:14
  • The `UPDATE` queries are updating **all** rows of `donor`, since there's no `WHERE` clause to limit the updates. – Barmar Apr 13 '16 at 21:14
  • blood_type which has 2 columns blood_id and blood_group – loreen99 Apr 13 '16 at 21:15
  • Your question starts with **I have 3 tables donor, blood_group, user_account** Is that a typo? – Barmar Apr 13 '16 at 21:15
  • There is a where clause in both updates – loreen99 Apr 13 '16 at 21:16
  • You have 10 column names listed in the `INSERT` statement, but only 8 values in the `VALUES` list. – Barmar Apr 13 '16 at 21:16
  • YES! I will edit the question now – loreen99 Apr 13 '16 at 21:17
  • The `WHERE` clause is just in the `SELECT` subquery, not the `UPDATE` main query. – Barmar Apr 13 '16 at 21:17
  • You have a "w" before `$sql` in the second `UPDATE` statement. You also have an extra semicolon in the 1st and 3rd `$sql` statement. – Kurt Leadley Apr 13 '16 at 21:19
  • I added WHERE to the update main query. it doesn't work. I think mysqli_multi_query($conn, $sql) is returning false since it doesn't echo "executed" – loreen99 Apr 13 '16 at 21:24
  • I read that the multiple sql queries are connected with a semi colon – loreen99 Apr 13 '16 at 21:26
  • [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Apr 13 '16 at 21:30
  • Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). – Jay Blanchard Apr 13 '16 at 21:42
  • I will. I just need to test the insert first – loreen99 Apr 13 '16 at 21:44

2 Answers2

2

You can use a SELECT clause to produce the values for an INSERT. In this case, you can use that to select the appropriate values from the other tables.

INSERT INTO donor (user_id, blood_id, first_name,last_name,email_add,gender, birthday, telephone, city, last_donation)
SELECT u.user_id, b.blood_id,
       '$_POST[fname]', '$_POST[lname]', '$_POST[email]', '$_POST[gender]', '$_POST[Birthday]', '$_POST[Telephone]', '$_POST[city]', '$_POST[lastdonation]'
FROM user_accounts AS u
CROSS JOIN blood_type AS b
WHERE u.username = '$_POST[user]' AND b.blood_group= '$_POST[bloodgroup]'

I also strongly recommend you use prepared queries instead of substituting $_POST variables, as the latter subjects you to SQL-injection. I also recommend against using mysqli_multi_query -- it's rarely needed and only makes checking for success harder. If you insert into user_accounts using a separate query, you can then use mysqli_insert_id($conn) to get the user_id assigned when you inserted into user_accounts, instead of using the above JOIN. You can also use the MySQL built-in function LAST_INSERT_ID() to get it.

$stmt = mysqli_prepare($conn, "INSERT INTO user_account(username, password) VALUES (?, ?);") or die("Can't prepare user_account query: " . mysqli_error($conn));
mysqli_stmt_bind_param($stmt, "ss", $_POST['user'], $_POST['psw']);
mysqli_execute($stmt);
$stmt2 = mysqli_prepare($conn, "
    INSERT INTO donor (user_id, blood_id, first_name,last_name,email_add,gender, birthday, telephone, city, last_donation)
    SELECT LAST_INSERT_ID(), b.blood_id, ?, ?, ?, ?, ?, ?, ?, ?
    FROM blood_type AS b
    WHERE b.blood_group= ?") or die ("Can't prepare donor query: " . mysqli_error($conn));
mysqli_stmt_bind_param($stmt2, "sssssssss", $_POST['fname'], $_POST['lname'], $_POST['email'], $_POST['gender'], $_POST['Birthday'], $_POST['Telephone'], $_POST['city'], $_POST['lastdonation'], $_POST['bloodgroup']);
mysqli_execute($stmt2);
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • $sql = "INSERT INTO user_account(username, password) VALUES ('$_POST[user]', '$_POST[psw]');"; $sql .= "INSERT INTO donor (user_id, blood_id, first_name,last_name,email_add,gender, birthday, telephone, city, last_donation)SELECT u.user_id, b.blood_id, '$_POST[fname]', '$_POST[lname]', '$_POST[email]', '$_POST[gender]', '$_POST[Birthday]', '$_POST[Telephone]', '$_POST[city]', '$_POST[lastdonation]'FROM user_accounts AS u CROSS JOIN blood_type AS b WHERE u.username = '$_POST[user]' AND b.blood_group= '$_POST[bloodgroup]'"; (mysqli_multi_query($conn,$sql) – loreen99 Apr 13 '16 at 21:41
  • It doesn't give me an error, it is just not inserting anything to my table – loreen99 Apr 13 '16 at 21:43
  • You have to call `mysqli_error()` to get the error message. – Barmar Apr 13 '16 at 21:49
  • I've updated the answer to show how to do it with prepared statements. – Barmar Apr 13 '16 at 21:49
  • Thank you very much I really appreciate your help but it gives me this error – loreen99 Apr 13 '16 at 21:54
  • Fatal error: Call to undefined function mysqli_bind_param() – loreen99 Apr 13 '16 at 21:55
  • Sorry, it's an alias for `mysqli_stmt_bind_param` that was removed in PHP 5.4. – Barmar Apr 13 '16 at 21:56
  • it is now giving me a bunch of warnings Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given – loreen99 Apr 13 '16 at 22:03
  • That will happen if `mysqli_prepare()` gets an error, and you didn't check for it. Do you have the `or die` code like in my answer? – Barmar Apr 13 '16 at 22:06
  • No I added it now! there seems to be a wrong semi colon because it keeps telling that there is unexpected ';' here $stmt = mysqli_prepare($conn, "INSERT INTO user_account(username, password) VALUES (?, ?);" or die("Can't prepare user_account query: " . mysqli_error($conn)); – loreen99 Apr 13 '16 at 22:16
  • I was missing a parenthesis at the end of the first `mysqli_prepare` call. – Barmar Apr 13 '16 at 22:18
  • Still doesn;t work! Can't prepare donor query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') SELECT LAST_INSERT_ID(), b.blood_id, ?, ?, ?, ?, ?, ?, ?, ? FROM blo' at line 1 – loreen99 Apr 13 '16 at 22:26
  • Had an extra comma in the column list. – Barmar Apr 13 '16 at 22:27
  • It keeps giving the same error. I cant find the extra comma – loreen99 Apr 13 '16 at 22:37
  • It was between `last_donation` and `)`, right before the place where the error message says that there was a syntax error. – Barmar Apr 13 '16 at 22:39
  • No errors but no data gets to my table! i have been struggling with this for over a week now! – loreen99 Apr 13 '16 at 22:42
  • Not sure what happened, but I accidentally deleted `mysqli_execute($stmt);`. Are you reading and understanding the code? Didn't you wonder how the first statement would execute without calling `mysqli_execute`? Or are you just blindly copying it without learning how it works? – Barmar Apr 13 '16 at 22:46
  • I have mysqli_execute() this is my first time working with php prepared statements or php for that matter – loreen99 Apr 13 '16 at 22:48
  • Now I can't figure out what's wrong, unless the username is a duplicate so the insert doesn't work. – Barmar Apr 13 '16 at 22:49
  • mysql_stmt_bind_param() should be my mysqli_stmt_bind_param() right? – loreen99 Apr 13 '16 at 22:52
  • Yes, that was a typo. – Barmar Apr 13 '16 at 22:54
  • I fixed that long time ago.. no data gets through! but I really appreciate your help. I will look more into prepared statements – loreen99 Apr 13 '16 at 23:05
  • using prepared statements the user_account table gets populated but not the donor table. Do you have any idea what could be the problem ? Thank u very much – loreen99 Apr 15 '16 at 16:32
  • Is it reporting an error? Are you sure `$_POST['bloodgroup']` has a match in `blood_type.blood_group`? – Barmar Apr 15 '16 at 16:39
  • This is what I am getting in mysql log 2016-04-15T16:19:47.177655Z 42 [Warning] Warning: Enabling keys got errno 0 on blood_bank_project.donor, retrying – loreen99 Apr 15 '16 at 16:48
  • Yes, I am sure it has a match – loreen99 Apr 15 '16 at 16:48
  • Does it work if you try the query by hand, after filling in all the `?` with the actual values from the form? – Barmar Apr 15 '16 at 16:57
  • Does it report an error? If it doesn't get an error, and there's a matching row in `blood_type`, I can't think of any reason why it wouldn't perform the insert. – Barmar Apr 15 '16 at 18:32
  • One row was inserted. but when I tried to insert another row it doesn't work! – loreen99 Apr 15 '16 at 18:53
  • You have to do the inserts in pairs: first insert into `user_account`, then insert into `donor`. Otherwise, `LAST_INSERT_ID()` won't get the ID of the new user account. – Barmar Apr 15 '16 at 18:59
  • Can you please suggest how can I do this ? – loreen99 Apr 15 '16 at 19:26
  • It's how my code is written. You could add a check that the first one successfully inserted into `user_account` by testing `mysqli_affected_rows($conn)` before you try to insert into `donor`. – Barmar Apr 15 '16 at 19:30
  • At some point you've got to do your own debugging and programming, you can't really rely on some random from the Internet to do this all for you. – Barmar Apr 15 '16 at 19:31
0

theres a few things wrong with that code snippet:

  1. Line 15: You've got a rogue 'w' at the start of the line before your $sql variable
  2. All of your $_POST'ed parameters need to be in the format $_POST['parameter'] (Missing quotes, remember to escape your already quoted ones in places)
  3. The where clause sub-select query in line 14 is selecting from a table that does not exist (blood_type)

I guess what your trying to achieve is a mapping between 'user_account' and 'donor' of which you may be better either storing a foreign key in the user account table of the 'donor_id', or a matrix/mapping table that links the two together.

The matrix/mapping table would hold the primary key date from both user_account and donor to create your matrix.

You can then get to either table information from the other knowing just one side of the information.

I'd also make sure your escaping your inbound variables in your queries to prevent any SQL Injection attacks (see here)

Dan Streeter
  • 2,259
  • 2
  • 16
  • 16