Matching information in mysql_fetch_array results

Okay, I am not 100% sure what I am looking to do is called, but I could really use some help. First of all I am pulling from two different systems which is why I don't just do a join or a sub query for what I am doing. One is Sybase Advantage the other is MySQL so I am trying to accomplish this in PHP.

I am querying the first database and returning the results using odbc_fetch_array. My first query looks something like this.

$sql1="SELECT ticketname, locationid FROM tickets WHERE custid = '$custid'"

$rs1=odbc_exec($conn,$sql1);

I then loop through the information using the following:

while($row_rs1 = odbc_fetch_array($rs1)) {}

I need to get the name from the MySQL database for each locationid from the first query.

$locations = mysql_query("SELECT locationname FROM locations WHERE locationid = (locationid from first query)

I know that I can do a query in the loop for each location to find the location name that is stored in the mysql database, but I want to keep the load down on the server since this page gets a lot of traffic. Instead of running a query to the MySQL database each time it loops through I was wondering if there was a way to do the MySQL query once before the loop then based on the locationid from the first query looping through, get the location name from the second query that was saved into an array.

I am not sure what I am trying do is called or if it's possible, but I would really appreciate some help / direction.

Thanks so much for any help.

locations = mysql_query("SELECT locationname FROM locations WHERE locationid = ".$row_rsl['locationid']);

I know this doesn't address your "don't do too much in MySQL", but I doubt there'd be much difference in speed between getting every "locations" in one hit and comparing in PHP vs proper SQL lookups for every locationid.

Yeah, I am running it now and there really isn't a speed issue, but I know they want the same page with every ticket ever put in the system at some point which is hundreds of tickets on a single page. I have already brought up my concerns with server performance, but you know how it goes when they want something specific and are determined to have it. :thumbsup:

I'll let you into a little secret, I made a mistake a few years ago. I have a database with around 200,000 rows in one table, 50,000 rows in another table, and 10 rows in a third table. The code does complex joins between them.

The code took around 5 minutes per lookup, which I figured was alright because it's such a massive amount of work.

Fast forward a few years: I take another look at the database and think "Hey, why didn't I put an index here, here and here...". I apply the indexes and re-run the code. 2 seconds.

Yes, a couple of indexes reduced the running time from 5 minutes to 2 seconds.

Moral of this story: Never underestimate the pure awesome power of a well optimised database and the speed of ridiculously complex queries.

Unfortunately though this is across two different systems. I am pulling the first query from Sybase Advantage and the second from MySQL. :rolleyes:

Yeah, that is a pity, though I was referring more to your MySQL lookups.