I'd like to ask if it's possible to create a column to have content based on the number of other columns, in MySQL server.
By example, column name: count, it's content should be the number of other columns that have the same id:
table members: column id and count
table two: column id and others
Is it possible to store in member.count the number of columns in table two that have the same id than members.id?
If not, I need to create a php function that counts the tables and store the value in members.count, like:
// this should be a for() for every user id
// select all id from members
if ($stmt = $mysqli->prepare("SELECT COUNT(*) rowCount FROM t2 WHERE id = ?")) {
$stmt->bind_param('ii', $user_id);
$stmt->execute();
$stmt->bind_result($rowCount);
if ($stmt->fetch()) {
$number_of_rows = $rowCount; // I'm not sure if this variable will store once the stament is closed.
$stmt->close();
$stmt = $mysqli->prepare("UPDATE members SET count = ? WHERE id = ?");
$stmt->bind_param('ii', $number_of_rows, $user_id);
if (! $insert_stmt->execute()) {
header('Location: /error?err=Registration failure: Contacte al administrador.');
}
}
I'm not really sure, how can I do this, if the first way it's not possible, I'd appreciate some help finishing this function.
Thanks in advance!