Run a query based on another query using mysql php

Question

The code below searches my mysql database and comes back with postcodes like IG6,RM11,RM8,RM4,RM2,RM6,RM7,RM1,RM5 and a distance using a stored procedure. (All ok)

PROBLEM: With these results, I want to search another table in same database that may have job information with those Postcodes (probably using LIKE).

What's the best way to get this working? I have tried many examples (implode, arrays, etc) Is one connection to database correct? How do I query the variable as it does come back with 2 columns, postcode and Distance. Should I split in an array (how?)

END PRODUCT: HGV Driver RM5, Cleaner RM5, Teacher RM5

(SELECT title FROM jobinfo WHERE location IN results from other query);

 <?php
    include ("conn.php");
    $first="RM5";

    $result = mysql_query("select outcode, GetDistance(Lat, Lon, (SELECT Lat from postcodes where outcode = '$first' limit 1),(SELECT Lon from postcodes where outcode = '$first' limit 1)) as Distance from postcodes having Distance < 3 order by Distance DESC;");
    while($row = mysql_fetch_array($result))
    {
        echo  ($row['outcode']) ;
    } 
    // This returns postcodes

    $resultb = mysql_query("SELECT title FROM jobinfo WHERE location IN ($results[outcode]) ");
    while($row = mysql_fetch_array($resultb))
    {
        echo  ($row['title']) ;
    }
    mysql_close($con);
?>

Please help.....any reference to join table needs full explanation as all so far don't help!

score 1 · Accepted Answer · edited May 23 '17 at 11:43

First Prepare the output into the clause:

in the first while loop:

while($row = mysql_fetch_array($result))
 {
    $array[] = $row['outcode'] ;
 }

Then prepare the array for the IN clause:

foreach ($array as $a) {$clause.= "'$a',";}
$clause=substr($clause,0,-1)

Finally use the clause for the IN statement:

$resultb = mysql_query("SELECT title FROM jobinfo WHERE location IN ($clause) "

===== EDIT === LIKE statement

For like.. you need multiple like statement OR together.. Using SQL LIKE and IN together

Change the prepare clause code to this:

foreach ($array as $a) {$clause.= " location LIKE '%$a%' OR";}
$clause=substr($clause,0,-3)

AND the sql becomes:

$resultb = mysql_query("SELECT title FROM jobinfo WHERE $clause ");

Of course you will want to addin some more error checking.. think of the possible injection.

This works Yay, but it only finds exact matches, e.g. 'RM5', not as it has in the field 'Driver RM5'. how do I extend your great code to add this....I'm sure the placement of % are needed somewhere? — Mikeys4u, Sep 26 '12 at 22:24

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

I think you're trying to do something like this answer MySQL LIKE IN()?

Also, please use parametrized queries How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:27

Community

1
1

answered Sep 26 '12 at 22:34

solidau

4,021
3
24
45

Can you shed some light how to do it in prepared statement? I always have problem with prepared statements when the statement needs to be dynamic like this example.. – Moe Tsao Sep 26 '12 at 22:44
First, you need to switch your database objects so that you are using PDO instead of the mysql_* functions (the mysql_* functions are depricated). Here's the PDO doc http://php.net/manual/en/ref.pdo-mysql.php Then just follow the example from the first answer to the second link I posted. Use the named parameters and then set your variables as the parameters. – solidau Sep 26 '12 at 23:00
1

oh, i think it just hit me what you mean. just build the SQL statement as a string first, using :var symbol notation instead of sticking in your variables, then bind your variables to the named symbols using the array syntax like in the example I posted – solidau Sep 26 '12 at 23:03
Ahh.. that explains. Thanks a lot! Will try it next time! – Moe Tsao Sep 26 '12 at 23:27

Run a query based on another query using mysql php

2 Answers2