Simultaneous mysql queries + PHP

Question

I need retrieve data from 2 tables at the same time, the tables are not linked by foreigns keys or such.

$query1 = "select idemployee from employee where address like 'Park Avenue, 23421'";
$query2   "select idcompany from company where bossName  like 'Peter'";

How can I do this with a kinda thread in PHP?. I've heard that threads are no safe in PHP.

UPDATED:

I got an input field that needs to looks data in both tables, is like search on both tables and show the posible results based on the employee address or boss's name, so you can type an address or just the boss's name. It's just a representation on what I need

Answer 1

Either use a single query, or look into something like Gearman to have workers performing jobs asynchronously (I assume the current code is only an example: if the queries you have there are performing so badly you want to perform them async. then you most likely have a database problem). Having some deamon processes ready to go to perform tasks is relatively simple.

.

Answer 2

Um...

$query1 = "select idemployee from employee where address like ?";
$query2 =  "select idcompany from company where bossName  like ?";

$stmt1 = $pdo->prepare($query1);
$stmt1->execute(array('Park Avenue, 23421'));
$employee = $stmt1->fetch();

$stmt2 = $pdo->prepare($query2);
$stmt2->execute(array('Peter'));
$company = $stmt2->fetch();

What am I missing?

Answer 3

You could use MYSQLI_ASYNC and http://docs.php.net/mysqli.poll (both only available with php 5.3+ and mysqlnd).
But then you'll need a separate connection to the MySQL server for each query.

Answer 4

Depends on what do you want to do with those queries. If, for example, you are using an AJAX form and can make two requests, you should create separate scripts, where each returns the results for each query. That is effectively running them in separate processes, so they execute simultaneously.

There is no such thing as threading per se in PHP, you can see a hack around it here (using full fledged processes.)

Answer 5

Counter answer to my previous:

Create a new table

CREATE TABLE EmployeeBossXref (
    id INT auto_increment,
    employee_id INT,
    boss_id INT,
    company_id INT,
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (boss_id) REFERENCES Employee(id),
    FOREIGN KEY (company_id) REFERENCES Company(id)
) ENGINE=InnoDB;

Then change SQL to:

select Employee.name, Boss.name, Company.name FROM Employee 
JOIN EmployeeBossXref ebx ON ebx.employee_id=Employee.id 
JOIN Employee Boss ON Boss.id=ebx.boss_id
JOIN Company ON Company.id=ebx.company_id
WHERE Employee.address LIKE 'Park Avenue, 23421'
  AND Boss.name LIKE 'Peter';

With this system, all bosses are employees (which they logically are!), employees can have more than one, or no boss.

Answer 6

You dont. Do you have an engineering reason you need to do this?

Making two queries simultaneously is still going to hit the same database, and the database is going to do the same amount of work. Its not going to make anything faster, and, you'll have the overhead of the additional threads/processes being created.

If you really need better concurrency, consider a 2nd (or 3rd or 4th) real-time replicated database for SELECT queries, to offload some of the work from the main database.