current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I need to query a MySQL DB, use part of the results to write a query for a Postgres DB, then display results from both data sets in an HTML table.

I have a the result of the MySQL query like so

$orderid = array(); 
while ($row = mysql_fetch_array($qry_result)) {
    $orderid[] = $row[order_id];
}

$orderlist =  implode(', ', $orderid );

PS: Yes, I know that I should be using mysqli/PDO. Old habits die hard.

$query2 = "SELECT 
                            data1,
                            data2,
                            total
                        FROM order_summary os 
                        WHERE os.order_id IN ($orderlist)
                        LIMIT 25";

So that works, not as fast as I'd like, but it works. (Suggestions on a better way to handle that are appreciated too!)

Now, when I try to get this to display I'm doing something like this:

$result2 = pg_query($query2) or die('Query failed: ' . pg_last_error());

            while ($rowrs = pg_fetch_array($result2, null, PGSQL_ASSOC)) {
                $display_string .= "<tr class='text-center'>";
                $display_string .= "<td>$rowrs[data1]</td>";
                $display_string .= "<td>$rowrs[data2]</td>";
                $display_string .= "<td>\$$rowrs[total]</td>";

$qry_result = mysql_query($query) or die(mysql_error());                    

            while($row = mysql_fetch_array($qry_result)){
                $display_string .= "<td>$row[email]</td>";}
}

//magical other things unrelated to the table

$display_string .= "</tr>";
$display_string .= "</table>";
echo $display_string

The issue is that either the results from MySQL display in a single row in the HTML table or repeat over and over. The intended result would be the email paired with the order_id—once per row.

I feel like I'm doing something stupid here and this is an easy fix. Any help is greatly appreciated.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Without seeing the actual queries, this is a longshot, but it should get you started.

First, fetch order_id and email from your mysql query, then store the results as an associative array. 

while($row=mysql_fetch_array($result)) {
    $orderid[$row['order_id']] = $row['email'];
}

Since it is an associative array, you should get the order list with

$orderlist =  implode(', ', array_keys($orderid) );

If you're getting the order id back from postgres, you don't need to query mysql again. Instead, retrieve the email just by doing

 $email = $orderid[$rowsrs['order_id']];
 $display_string .= "<td>$email</td>";

Mandatory reminder, use Pdo!!! Specially because when working with two engines you will literally work half than you're working now.

Also, array keys must be quoted. Don't make your poor php interpreter assume stuff.

share|improve this answer
    
Thanks! I had a moment of clarity after submitting this and got things working in a different way. As to repeating things... the queries aren't actually doing that, I just quickly cleaned up semi-sensative data and didn't do a good job of it. Thank you again for your direction! –  Kehalo Aug 19 at 2:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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