CODEWITHSUNDEEP
phpmysqlselect
Robert Coroianu
Robert Coroianu

Reputation: 426

Mysql select multiple query

I have this table:

------------------------------
|ID | name   | employee_code |
------------------------------
|24 | Robert |    20234      |
------------------------------

AND

-------------------------------------
|ID |   job_code   |     team       |
-------------------------------------
|24 | 241124 | Robert, Eduard, Etc. |
-------------------------------------

I want to search in second table by employee code and i try something like this:

$sql=mysql_query("SELECT * FROM works WHERE (SELECT name FROM employee WHERE employee_code LIKE '%".$_GET['employee_code']."%' AS searchname) team Like %searchname% ");

Result:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Upvotes: 0

Views: 724

Answers (5)

Sean
Sean

Reputation: 12433

Try this query -

$employee_code = mysql_real_escape_string($_GET['employee_code']);

$sql=mysql_query("SELECT w.* 
                  FROM employee e
                  JOIN works w 
                  ON w.team LIKE CONCAT('%', e.name ,'%')
                  WHERE employee_code LIKE '%$employee_code%'");

see this SQLFiddle example - http://sqlfiddle.com/#!2/8f8b7/1

Upvotes: 2

vladkras
vladkras

Reputation: 17227

SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.employee_code = t2.job_code

or

SELECT t1.id, t1.name, t2.team FROM table1 t1 INNER JOIN table2 t2 ON t1.employee_code = t2.job_code

for cleaner result

Upvotes: 0

Sundar
Sundar

Reputation: 4650

Your SQL query is wrong Try like this

 SELECT * FROM works WHERE works.ID=employee.ID AND 
   employee.employee_code=".$_GET['employee_code']."

Upvotes: 0

Anigel
Anigel

Reputation: 3437

This would probably tell you exactly what was wrong.

$sql=mysql_query("SELECT * FROM works WHERE (SELECT name FROM employee WHERE employee_code LIKE '%".$_GET['employee_code']."%' AS searchname) team Like %searchname% ");
if (!$sql)
    echo mysql_error();

You should never just assume that your query has worked and then carry on to use the resource in another command without checking that it did in fact work.

Another thing you should not do is just put user input directly into SQL queries without any form of escaping as it will enable anyone to take complete control of your database.

SELECT * FROM works WHERE (SELECT name FROM employee WHERE employee_code LIKE '%".mysql_real_escape_string($_GET['employee_code'])."%' AS searchname) team Like %searchname% "

Upvotes: 0

exussum
exussum

Reputation: 18578

You should be looking at a join .

select * from table1 inner join table2 using (`ID`) where job_code = ....

Then you have 1 row with both tables joined together

also your using mysql_* functions, These are no longer maintained please update to mysqli_* or PDO.

Also you need to escape your queries, There an SQL injection attack waiting to happen in that code

Upvotes: 0

Related Questions