Unable to display results from MySQL query

[Twinbird]

Hello,

 

I have a database named 'grad' - within it are three tables: 'grads', 'profs', and 'meetings'.

The grads table has 6 columns: grad_id, grad_name, research_interests, prof1, prof2, prof3

The profs table has 3 columns: prof_id, prof_name, userid

The meetings table has 4 columns: grad_name, research_interests, prof_id, time

Here is my PHP code:

try
{
$pdo = new PDO('mysql:host=localhost;dbname=grad', 'guser', '*****');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
echo 'Unable to connect to database server. Here\'s an error code (Hupefully): ' . $e;
exit();
}

// This value is only fixed at 1 for this example!
$logged_prof_id = 1;

// This SQL query is correct and retrieves the data I want when I run it on my MySQL database from phpMyAdmin
$sql = "SELECT grad_name, research_interests FROM grads WHERE prof1 = :profid or prof2 = :profid or prof3 = :profid AND grad_name NOT IN ( SELECT grad_name FROM meetings);";
$s = $pdo->prepare($sql);
$s->bindValue(':profid', $logged_prof_id);
$s->execute();

$result = $s->fetchAll($sql);

 

The columns prof1, prof2 and prof3 in the grads table will contain values for prof_id. So, prof1/2/3 will be used to link that entry to a prof using the prof_id column.

 

I want to display a list of students who's prof1/2/3 fields contain 1.

 

I am confused as to why I need to use the execute method of the PDOStatement object before using a method to return the restuls of the SQL query. How can I produce a list of results (using a foreach loop would be good)?

 

Thank you!

 

Twin

[Barand]

Remove prof1/2/3 column from the table and create a separate table

 

Table gradprof : grad_id, prof_id

 

Check out "normalization"

[Twinbird]

Thanks for the tip Barand.

I have created the gradprof table and am now using the following SQL statement:

$sql = "SELECT grads.grad_name, grads.research_interests FROM grads INNER JOIN gradprof ON grads.grad_id = gradprof.grad_id WHERE gradprof.prof_id = :profid AND grads.grad_name NOT IN ( SELECT grad_name FROM meetings);";

 

However, my original problem remains. I am not sure how to properly display the results of this query using PHP.

I have almost the exact same code (see below) to display the users name.

 

$sql = "SELECT prof_id, prof_name FROM profs WHERE userid= :userid;";
$s = $pdo->prepare($sql);
$s->bindValue(':userid', $_POST['userid']);
$s->execute();
$result = $s->fetch();

echo 'You are logged in as "' . $result['prof_name'] . '"';

[Barand]

I'm not a PDO expert but I think you want something like

 

$s->execute();
while ($result = $s->fetch()) {
  echo "{$result['grad_name']} <br>{$result['research_interests']}<br><br>";
}

[Twinbird]

Thanks a lot Barand, everything's coming along nicely now