Mysql fetch array query?

Question

I am using php query to retrieve information from a mysql database with mysql_fetch_array and use the returned results to query another database? Im pretty novice. Is this possible?

$search =
"SELECT `a`,`b`

FROM 'DB'
WHERE `x` LIKE '$var'
LIMIT 0 , 30";

$result = mysql_query($search, $connection);
if($result){

while ($row = mysql_fetch_array($result)){
echo $row["a"]." ".$row["b"]."<br />";
                        }
} else {
echo "<p> Subject search failed. <p>";
echo "<p>" . mysql_error() . "</p>";
    }

<!-- SECOND QUERY -->   
$search2 = 
"SELECT `a`,`b`
FROM `DB`
WHERE `x` = **$row['a']**  *trying to grab the result from the first query and use as search query in second?*

LIMIT 0 , 100";

$result2 = mysql_query($search2, $connection);
if($result2){

while ($row2 = mysql_fetch_array($result2)){
echo $row2["a"]." ".$row2["b"]."<br />";
                        }
} else {
echo "<p> Subject search failed. <p>";
echo "<p>" . mysql_error() . "</p>";
}

Could someone point me in the right direction?

please place your code within a code tag, to promote readability, also, PLEASE use correct indentation, for the same reason

Dev · Accepted Answer · 2011-02-23 10:49:10Z

$search = "SELECT a,b FROM DB WHERE x LIKE '$var' LIMIT 0 , 30";
$result = mysql_query($search, $connection);
if($result){
    while ($row = mysql_fetch_array($result)){
        echo $row["a"]." ".$row["b"]." ";
        $search2 = "SELECT a,b FROM DB WHERE x = '".$row['a']."' LIMIT 0 , 100";
        $result2 = mysql_query($search2, $connection);
        if($result2){
            while ($row2 = mysql_fetch_array($result2)) {
                echo $row2["a"]." ".$row2["b"]." ";
            }
        } else {
            echo "Subject search failed.";
            echo mysql_error();
        }
    }
} else {
    echo "Subject search failed. ";
    echo mysql_error();
}

Thanks for the help and I'm very happy to know It can be done! Iv just tried with your solution and it returns the same error. Subject search failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0 , 100' at line 4. I believe the other comment by thasc may be try that I have used the $row variable in my while loop therefore cannot query it because it has run through the array and returned a false value to exit the loop. Is it possible to put the mysql query withing the original while loop?
I changed whole your code for you so replace your code with this and try to check it...

Jouke Waleson · Answer 2 · 2011-02-21 12:47:33Z

This is possible of course. Try this approach:

$firstresult = null;
while ($row = mysql .... etc()) //first query
    $firstresult = $row['a'];

if ($firstresult != null) {
     //do second query
     $result2 = mysql_query("SELECT * FROM table WHERE id='".$firstresult."';");
     //process query
     //etc..
}

If this solution is correct, don't forget to mark the solution as accepted. That's how StackOverflow works.

Dev · Answer 3 · 2011-02-21 12:57:22Z

$search = "SELECT a,b

FROM DB WHERE x LIKE '$var' LIMIT 0 , 30";

$result = mysql_query($search, $connection); if($result){

while ($row = mysql_fetch_array($result)){ echo $row["a"]." ".$row["b"]."
";

$search2 = "SELECT a,b FROM DB WHERE x = ".$row['a']."

LIMIT 0 , 100";

$result2 = mysql_query($search2, $connection); if($result2){

while ($row2 = mysql_fetch_array($result2)){ echo $row2["a"]." ".$row2["b"]."
"; } } else { echo "

Subject search failed.

"; echo "

" . mysql_error() . "

"; } } } else { echo "

Subject search failed.

"; echo "

" . mysql_error() . "

"; }

I changed your code... there are lots of problem with if and else brackets and also with while...

symcbean · Answer 4 · 2011-02-21 14:49:07Z

Why is the data on two seperate databases? Do you really mean that they are on seperate databases - or that they are on seperate instances of MySQL ?

A single mysql instance can provide multiple database, and you can write queries that pull in data from each one, e.g.

SELECT activity.*
FROM mysql.user user, audit.activity activity
WHERE activity.user=user.user

If you want to collate data from seperate MySQL instances, then as others have suggested, you will need to create 2 db connections, fire a query against one connection, then dynamically build and execute queries against the second (note you'll get significant performance benefits by using a prepared statement for the iterated query).

Another approach is to expose the second instance directly in the first instance of MySQL using the federated storage engine

asked	2 years ago
viewed	2145 times
active	2 years ago

Mysql fetch array query?

4 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql arrays or ask your own question.

Mysql fetch array query?

4 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql arrays or ask your own question.

Related