CODEWITHSUNDEEP
phpmysql
Bruce
Bruce

Reputation: 1071

Multiple mysql table joins with php?

Honestly I have no idea how to do what I am trying to do. I am not overly experienced in php nor in Mysql but I am trying and could use some help, preferably with working example code.

Problem: I have 3 tables

  1. members
  2. customfields
  3. customvals

members contains:

membername | Id

customfields contains:

rank | name

customvals contains

fieldid | userid | fieldvalue

Table columns match at

customvals.userid=members.id
customvals.fieldid=members.rank

What I need to do is match the data so that when page.php?user=membername is called it displays on the page

Table1.membername:<br>
Table2.name[0] - Table3.fieldvalue[0]<br>
Table2.name[1] - Table3.fieldvalue[1]<br>
etc...

(obviously displaying only the information for the said membername)

The more working the code, the more helpful it is for me. Please don't just post the inner join statements. Also it is most helpful to me if you could explain how and why your solution works

So far here is what I have for code:

$profileinfocall = "SELECT Table1.`membername`, Table2.`name`, Table3.`fieldvalue`
FROM members AS Table1
LEFT JOIN customvals AS Table3 ON Table1.`id` = Table3.`userid`
LEFT JOIN customfields AS Table2 ON Table3.`fieldid` = Table2.`rank`
WHERE Table1.`membername` = $username;";

$membercall = "SELECT * FROM members WHERE membername=$username";

$profileinfo = mysql_query($profileinfocall, $membercall);

while($row = mysql_fetch_array($profileinfo)) {
echo $row['membername'];
}

Obviously this doesn't work as I get the following errors:

Warning: mysql_query() expects parameter 2 to be resource, string given on line 534.

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in on line 535

Upvotes: 1

Views: 67

Answers (2)

Jan
Jan

Reputation: 43169

While this is a very broad question and you have not provided any PHP code, you might want to break it down into various sections:

Establishing a connection to the database (with mysqli) and sending a query:

$c = mysqli_connect("localhost","user","password","db");
if (mysqli_connect_errno())
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
else {
    $result = mysqli_query($c,"SELECT * FROM members");
    while($row = mysqli_fetch_assoc($result)) {
        echo "{$row['membername']}";
    }
}
mysqli_close($c);

Tieing your tables together:

It is better to start off with a clear structure (including line breaks) when getting into the MySQL syntax. One way would be to have some sort of query skeleton:

SELECT tablealias.column, table2alias.field3
FROM table AS tablealias
LEFT|RIGHT|INNER JOIN table2 AS table2alias ON table.id=table2.id
WHERE (this and that = true or false, LIKE and so on...)

Breaking it down to your specific problem this would be:

SELECT Table1.`membername`, Table2.`name`, Table3.`fieldvalue`
FROM members AS Table1
LEFT JOIN customvals AS Table3 ON Table1.`id` = Table3.`userid`
LEFT JOIN customfields AS Table2 ON Table3.`fieldid` = Table2.`rank`
WHERE Table1.`Id` = 'UserID to be searched for'

Improvements & Security measures:

But there is even more to it than meets the eye. If you have just begun, you might as well dive directly into prepared mysqli- statements. Given the query to get your members, the only changing part is the ID. This can be used for a prepared statement which is much more secure than our first query (though not as fast). Consider the following code:

$sql =  "SELECT Table1.`membername`, Table2.`name`, Table3.`fieldvalue`
    FROM members AS Table1
    LEFT JOIN customvals AS Table3 ON Table1.`id` = Table3.`userid`
    LEFT JOIN customfields AS Table2 ON Table3.`fieldid` = Table2.`rank`
    WHERE (Table1.`Id` = ?)";
$c = mysqli_connect("localhost","user","password","db");
$stmt = $c->stmt_init();
if ($stmt->prepare($sql)) {
    $stmt->bind_params("i", $userid);
    $stmt->execute();
    while ($stmt->fetch()) {
        //do stuff with the data
    }
    $stmt->close();
}
$mysqli->close();

Upvotes: 1

GrafiCode
GrafiCode

Reputation: 3374

This SQL query should do it:

SELECT a.membername, a.Id, b.fieldid, b.userid, b.fieldvalue, c.rank, c.name
FROM members AS a
LEFT JOIN customvals AS b ON a.id = b.userid
LEFT JOIN customfields AS c ON b.rank = c.fieldid
WHERE a.Id = #MEMBERIDHERE#;

Upvotes: 1

Related Questions