Is there any better way to fetch two tables in one statement or query?

Question

The 'Do' method is for prepared statements and returns (by fetchall()). I am also using two looping statements. Is there any better way to fetch two tables in one statement or query? If not, what can I do to improve this code?

// 'id' is index in 'userinfo' table and 'id' in 'election' table is a child index(foreign key)
// 'ElectionId' is also the index, but it is not connected with othertable (only 'id' is linked) 
 <?php
 $call = new world();
 $get = $call->Do("SELECT Votes, Views, id FROM election WHERE ElectionId = ?", array("electionIdUknwn"));
 foreach($get as $show)
 {
 <div>"Votes is :".$show[0]</div>
 $add = $call->Do("SELECT username FROM userinfo WHERE id = ?", array($show[2]) );
 foreach($add as $display)
 {
 <div>"Username is :".$display[0]</div>
 }
 <div>"Views are :".$show[1]</div>
 } 
 ?>

Answer 1

SQL:

This is just a simple join.

SELECT e.Votes, e.Views, e.id, u.username 
FROM election as e, userinfo as u 
WHERE e.id= u.id WHERE e.ElectionId=? 
ORDER BY e.id ASC

Now you can iterate over all lines and every time e.id is changing you know that you have to display a new group.

Are there really multiple users per Votes and Views pair? I guess Votes and Views are counting columns so your model is kind of strange. Maybe you can find better names, so it is easier to get an idea what you want to do.

PHP:

• usually classes are starting with a capital letter.
• usually methods are starting with a lower case letter.
• there is something missing in your output, some echos?
• it is best practice to skip the ?> at the end, do avoid sending whitespace in case you will change the page header later.
• $call and world are really strange names