How to pull data from one table into another

Question

I have a form (called suggestions) on my site that users fill in and submit. The data from the form is pulling through properly however I want to extract the email of the user too when they submit - this data is currently in the 'users' table (user will have given this info on sign up) in the database and I'm not sure how to access it?

Here's what I have so far:

$table = 'suggestions';
$id = (isset($_SESSION['u_id']) ? $_SESSION['u_id'] : null);
$email =
$optionOne = '';
$optionTwo = '';

$suggestions = selectAll($table);

if (isset($_POST['new-suggestion'])) {
  global $conn;

  $id;
  $email;
  $optionOne = $_POST['optionOne'];
  $optionTwo = $_POST['optionTwo'];
  $sql = "INSERT INTO $table (user_id, email, option_1, option_2) VALUES (?, ?, ?, ?)";

  if (!empty($optionOne) && !empty($optionTwo)) {
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ssss', $id, $email, $optionOne, $optionTwo);
    $stmt->execute();

  } else {
    echo "All options must be entered";
  }
}

User ID is extracted via session so I'm thinking I could use this to get their email? Not sure what the '$email' variable should equal to get me this.

If you can extract their user ID from the session, why not use that user ID to `SELECT` their user record from the DB? It looks like you've already figured out how to execute a SQL query. You just need to do that, followed by a call to `fetch()` to get the results. See my answer for an idea of how it's done. — kmoser, Nov 30 '20 at 21:29

score 0 · Answer 1 · answered Nov 30 '20 at 21:33

0

$id = (isset($_SESSION['u_id']) ? $_SESSION['u_id'] : null);

if ( NULL !== $id ) {
  // Assumes user table is named "users" and email column is named "email":
  $sql = "SELECT email FROM users WHERE id = ?";
  $stmt = $conn->prepare($sql);
  $stmt->bind_param('s', $id);
  $stmt->execute();
  $row = $stmt->fetch();
  $email = $row[ 'email' ];
} // else no id specified in session

This is missing a whole bunch of error checking but the general idea should work.

answered Nov 30 '20 at 21:33

kmoser

8,780
3
24
40

Thanks for this, makes sense. Getting uncaught error on '$stmt->bind_param('s', $id);' though? – Alex0720 Nov 30 '20 at 21:41
1

Exactly what does the error message say? Also, is `$id` a number or a string? – kmoser Nov 30 '20 at 22:34
Fatal error: Uncaught Error: Call to a member function execute() on bool is the error message, $id is a number – Alex0720 Dec 01 '20 at 18:11
@Alex0720 Enable error reporting [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Dec 01 '20 at 18:15
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); where should I add this in my code? – Alex0720 Dec 01 '20 at 19:11
Before new mysqli or mysqli_connect – Dharman Dec 01 '20 at 19:12
@Alex0720 Since `$i` is a number, change `$stmt->bind_param('s', $id);` to `$stmt->bind_param('i', $id);` – kmoser Dec 01 '20 at 19:18
How would that help? Casting to an integer will not make any difference and could even be worse depending on circumstances – Dharman Dec 01 '20 at 19:19
Because `$i` is a number (presumably an int), the bind param type should be `'i'`, as shown in the [bind_param() documentation](https://www.php.net/manual/en/mysqli-stmt.bind-param.php). `'s'` might work, too, but that doesn't mean it's technically correct. – kmoser Dec 01 '20 at 19:28
Changing to an integer hasn't changed anything unfortunately @kmoser. I changed to 'WHERE u_id' as this is the row in the users table and getting this error now -> Notice: Trying to access array offset on value of type bool – Alex0720 Dec 01 '20 at 20:35
@Alex0720 As I mentioned in my answer, you're missing error checks on calls to things like `bind_param()`, `execute()` and `fetch()`. Those functions may return FALSE, for example, to indicate an error. Also, as @Dharman said, you should check the SQL error. – kmoser Dec 01 '20 at 22:44
@kmoser Please don't tell people to check the return value of these functions. As you can see OP already has error reporting switched on so manual checking whether each function was successful is completely unnecessary and can even be dangerous. – Dharman Dec 01 '20 at 22:47

How to pull data from one table into another

1 Answers1