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!

Community · Accepted Answer · 2017-05-23 11:43:12Z

1

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.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Sep 26, 2012 at 22:07

Moe Tsao

1,0546 silver badges9 bronze badges

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

1 Comment

Mikeys4u Over a year ago

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?

Community · Accepted Answer · 2017-05-23 12:27:53Z

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, 2017 at 12:27

CommunityBot

11 silver badge

answered Sep 26, 2012 at 22:34

solidau

4,0914 gold badges27 silver badges47 bronze badges

4 Comments

Moe Tsao Over a year ago

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..

solidau Over a year ago

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 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 Over a year ago

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

Moe Tsao Over a year ago

Ahh.. that explains. Thanks a lot! Will try it next time!

Collectives™ on Stack Overflow

Run a query based on another query using mysql php

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related