Answer to my problem here: select from multipe tables join return null if columns not match
Answer to the tittle: Down there :)
I'm trying to select an extra table without any condition but I'm getting unknown table.
if ($stmt = $mysqli->prepare("SELECT members.*, account_type.*, friends.* FROM members INNER JOIN account_type ON account_type.type = members.acc_type
WHERE members.acc_type = ?")) {
$stmt->bind_param('s', $_SESSION['acc_type']);
I need to get data from friends, but I don't have any special requirement from that table, just get data.
Actually, the error is: unknown column friends.
I joined the table but it was interfering with the other tables and it was not working as expected. By example: if both accounts (friendID and userID) have the same acc_type, everything works fine, but if the logued account (userID) have a different acc_type nothing is printed. I don't know why... so I just need to get data.
How can I do this?
Thanks in advance!
Added some more code:
if(isset($_GET['id'], $_SESSION['user_id'])) {
/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT COUNT(*) rowCount FROM friends WHERE friendID = ? AND userID = ?")) {
$stmt->bind_param('ii', $_GET['id'], $_SESSION['user_id']);
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($rowCount);
/* fetch values */
if($stmt->fetch()) {
if ($rowCount > 0) {
$stmt->close();
if ($stmt = $mysqli->prepare("SELECT members.*, account_type.*, friends.*
FROM friends, members INNER JOIN account_type ON account_type.type = members.acc_type
WHERE members.acc_type = ?")) {
$stmt->bind_param('s', $_SESSION['acc_type']);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_array();
echo '<h2 class="post-title"><a href="#">Informe de Beneficios </a></h2>';
if ($row['status'] == 1) {
$status = 'Activa';
} elseif ($row['status'] == 0) {
$status = 'No Activa';
}
var_dump($row['status']);
echo '<br>';
var_dump($result);
echo '
<table align="center" width="500"><b>
<tr><b>
<td><img src="/images/profile/' . $row['logotipo'] . '" /></td><b>
<td align="right"><h2 align="center">' . $row['name'] . '</h2><b>' . $status . '</b></td><b>
</tr><b>
</table><b>
<div class="list top">
<p>Mensualidades abonadas y bonificaciones recibidas para ' . date('Y') . ':</p>
<ul>
<li><a class="month">Mes</a></li>
<li><a class="status">Estado</a></li>
<li><a class="bonus">Bonificaciones</a></li>
<li><a class="cashed">Recibida</a></li>
</ul>
</div>
';
$monthNames = array("Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre");
$paid = array(); //Placeholder for paid months.
for ($i = 1; $i < 13; $i++) {
$month = 'month' . $i;
$cashed_month = 'cashed_month' . $i;
if($row[$month] == 1) {
$paid[] = 'Pagado';
$bonus[] = $row['bonus'] . '€';
} else {
$paid[] = 'No Pagado';
$bonus[] = 'n/a';
}
if ($row[$cashed_month] == 1) {
$cashed[] = 'Si';
} elseif ($row[$month] == 1) {
$cashed[] = 'No';
} else {
$cashed[] = 'n/a';
}
}
//Now make the HTML list
foreach($monthNames as $key => $month) {
echo '
<div class="list">
<ul>
<li><a class="month">' . $month . '</a></li>
<li><a class="status">' . $paid[$key] .'</a></li>
<li><a class="bonus">' . $bonus[$key] . '</a></li>
<li><a class="cashed">' . $cashed[$key] . '</a></li>
</ul>
</div>';
}
echo '<div class="clear"></div>';
echo '<p>Total recibido para ' . date('Y') . ': ' . $row['total'] . '€</p>';
echo '<a href="/friend">Volver</a>';
} else echo $mysqli->error;
} else{
echo $_SESSION['username'], ', no estás autorizado para ver la información de este socio ya que no es tu amigo...<br><br>No intentes nada raro y no uses la barra de dirección para navegar por el sitio.<br><br>Si sigues insistiendo, serás baneado.';
}
}
/* close statement */
//$stmt->close();
} else echo $mysqli->error;
/* close connection */
//$mysqli->close();
}
I need to make a list that displays the months and if they are paid of not, also the monthly bonus the user gets for inviting a friend, and the last column if the user already get the bonus or is pending.
Depending on the account_type, users may get more or less bonus, so I need to print the bonus column from account_type and print it in the table, the problem is that if I change the acc_type for the connected user will not work nothing, just displaying the months, not paid (everyone) and "n/a" in the rest of the columns.
These are the tables:
- account_type: two columns: type (varchar, used in the query) and bonus (int, should be printed).
- friends: here i just need 13 columns: total and cashed_month1-12 (int)
- members: id and acc_type (both used in the query), the rest of columns should be printed.
account_type: bonus should be printed just for the connected user
friends no matter what id, there's only 1 row with friendID and userID so the data is the same for both ids. members: here i need to print status for friendID ($_GET['id']), acctype for connected user ($_SESSION['acc_type']) but just a check to print the correct bonus value.
So the final list should be: month_name | paid_or_not | bonus | cashed_or_not.
The problem, actually, is that if acc_type doesn't match acc_type from friendID will not display anything :(
I tried a query joining friends table and I thought that was the problem so I wanted to try if not joining it the problem will dissapear...
How can I fix this?
