Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Location
    Tampa
    Posts
    223
    Thanks
    23
    Thanked 0 Times in 0 Posts

    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.

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

    $rs1=odbc_exec($conn,$sql1); 
    I then loop through the information using the following:

    PHP Code:
    while($row_rs1 odbc_fetch_array($rs1)) {} 
    I need to get the name from the MySQL database for each locationid from the first query.

    $
    PHP Code:
    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.

  • #2
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    PHP Code:
    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.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Location
    Tampa
    Posts
    223
    Thanks
    23
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    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.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog

  • #5
    Regular Coder
    Join Date
    Jul 2004
    Location
    Tampa
    Posts
    223
    Thanks
    23
    Thanked 0 Times in 0 Posts
    Unfortunately though this is across two different systems. I am pulling the first query from Sybase Advantage and the second from MySQL.

  • #6
    Super Moderator
    Join Date
    Feb 2009
    Location
    England
    Posts
    539
    Thanks
    8
    Thanked 63 Times in 54 Posts
    Yeah, that is a pity, though I was referring more to your MySQL lookups.
    lamped.co.uk :: Design, Development & Hosting
    marcgray.co.uk :: Technical blog


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •