Best practice to execute multiple SQL queries using PHP PDO

Question

What is the best practice to execute multiple SQL queries using PHP PDO?

I have 4 tables and each of them is running on MyISAM. As such they do not have Foreign Key support. It's a one-to-many design whereby there is 1 main table and the other table contains reference to the main table in the form of IDs.

For now, what I do is that I run the first query to get the ID from the main table. Once that is executed, I then perform another query to query other tables using the ID from the first query. Results from both the queries are then merged together (array_merge) and then displayed to the user.

Here's my code so far. I think you will get the gist and you can most probably tell that I'm a super beginner in PHP. :)

$sql1 = "SELECT * FROM student_records WHERE name=? LIMIT 1";
$stmt1 = $db->prepare($sql1);
$stmt1->execute(array($name));
$e11 = $stmt1->fetch();

$id = $e1['id'];

$sql2 = "SELECT file_name FROM images WHERE id=? LIMIT 1";
$stmt2 = $db->prepare($sql2);
$stmt2->execute(array($id));
$e2 = $stmt2->fetch();

$e = array_merge($e1, $e2);

I think that the code above is somewhat repetitive and redundant. Is there any suggestion and tips on how I can improve this?

possible duplicate of [multiple mysql queries into one php two-dimensional array](http://stackoverflow.com/questions/1172272/multiple-mysql-queries-into-one-php-two-dimensional-array) — outis, Mar 02 '11 at 14:42

score 2 · Answer 1 · edited May 23 '17 at 10:28

2

Use joins, and don't use SELECT * (select only the columns you need):

SELECT file_name 
  FROM student_records AS sr
    JOIN images AS i ON sr.id = i.id
  WHERE sr.name=?

edited May 23 '17 at 10:28

Community

1
1

answered Mar 02 '11 at 14:38

outis

75,655
22
151
221

Best practice to execute multiple SQL queries using PHP PDO

1 Answers1