Mysql select multiple query

Question

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

your query is failing. try adding mysql_error() to your query - $sql=mysql_query("SELECT * FROM ...) or die(mysql_error()); — Sean
– Sean, Commented Jul 9, 2013 at 7:59
do you mean job_code from 2nd table must be the same as employee code form 1st one? — vladkras
– vladkras, Commented Jul 9, 2013 at 8:03
Something else to note, please consider that mysql functions are now deprecated. Please look at using PDO or mysqli instead. — webnoob
– webnoob, Commented Jul 9, 2013 at 9:11

Sean · Accepted Answer · 2013-07-09 08:24:34Z

2

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

answered Jul 9, 2013 at 8:24

Sean

12.4k3 gold badges31 silver badges49 bronze badges

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

Comments

exussum · Accepted Answer · 2013-07-09 07:59:15Z

0

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

answered Jul 9, 2013 at 7:59

exussum

18.6k8 gold badges35 silver badges67 bronze badges

Comments

Anigel · Accepted Answer · 2013-07-09 07:59:42Z

0

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% "

answered Jul 9, 2013 at 7:59

Anigel

3,4351 gold badge19 silver badges23 bronze badges

4 Comments

Robert Coroianu Over a year ago

I know but i post on question only sql query.

Anigel Over a year ago

ok so what was the error you got but didn't put in your question? Kind of an important bit of information to include wouldn't you think?

webnoob Over a year ago

@Anigel - Regardless of the error, the query he is trying to use is clearly malformed and invalid. That's the issue.

Anigel Over a year ago

@webnoob, I get this, but just saying use this query instead does not help the OP learn how to debug his own issues and just increases dependance on other people to answer their problems.

Sundar · Accepted Answer · 2013-07-09 08:00:39Z

0

Your SQL query is wrong Try like this

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

answered Jul 9, 2013 at 8:00

Sundar

4,6506 gold badges40 silver badges63 bronze badges

Comments

vladkras · Accepted Answer · 2013-07-09 08:09:27Z

0

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

answered Jul 9, 2013 at 8:09

vladkras

17.2k4 gold badges50 silver badges55 bronze badges

Collectives™ on Stack Overflow

Mysql select multiple query

5 Answers 5

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related