I'm back with another Laravel issue that I can't seem to crack with my procedural PHP, direct mysql_query background. It basically involves the Eloquent ORM over multiple tables.
I have 3 tables (Users, User_Profiles, and User_Answers). The Users table only keeps the very basics of the user (auto_incremented id, email, password), the User_Profiles table contains a few more details (image url, country, city, gender, etc) and belongs_to the User model. The User_Answers table is a list of answers given by the user and also belongs_to the User model.
What I would like to do is select all rows from the User_Profiles table where the city is the same as the city of the logged-in user, get their user_id's in an array and then compare the answers (from the User_Answers table) to the answers of the currently logged in User. The following is what I'm looking to do but in plain PHP and (for explanation purposes only) mysql_query. I apologize in advance for the awful code. I just need to be pointed in the right direction.
<?php
$link = mysql_connect("localhost", "xxx", "xxx");
$db = mysql_select_db('testersize', $link);
$id = 2;
$sql = "SELECT city FROM user_profiles WHERE user_id = $id";
$query = mysql_query($sql, $link);
$row = mysql_fetch_row($query);
$city = $row[0];
echo $city . "</br>";
$sql = "SELECT user_id FROM user_profiles WHERE city = '$city'";
$matches = mysql_query($sql, $link);
//
while($row = mysql_fetch_row($matches)){
$u_id = $row[0];
$sql = "SELECT books, movies, music FROM user_answers WHERE user_id = $u_id";
$query2 = mysql_query($sql, $link);
$row2 = mysql_fetch_row($query2);
echo "<h2>User Number: " . $u_id . "</h2>";
echo "your favorite books:" . $row2[0] . "</br>";
echo "your favorite movies:" . $row2[1] . "</br>";
echo "your favorite music:" . $row2[2] . "</br>";
}
?>