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 — outis
– outis, Commented Mar 2, 2011 at 14:42

Community · Accepted Answer · 2017-05-23 10:28:02Z

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, 2017 at 10:28

CommunityBot

11 silver badge

answered Mar 2, 2011 at 14:38

outis

77.7k23 gold badges155 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Best practice to execute multiple SQL queries using PHP PDO

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related