Solved

PHP & MSSQL - SQL Loop within another SQL loop?

Posted on 2011-02-28
8
268 Views
Last Modified: 2013-12-13
Hi all,

I'm trying to run a sql query (as a loop) within another sql loop thats already running, unfortunately, im not getting any results from the inner query - a basic example of what im trying to do is:

while($query1 = mysql_fetch_array($querycode1))
  {
      $mainname=$query1['name']
    echo $mainname;
               while($query2 = mysql_fetch_array($querycode2))
                {
                   $othername=$query2['name']
                   echo $othername;
                 }
  }


In that example it doesnt sound logical having a loop within a loop, but the second query uses variables from the first to make it work if that makes sense.

Does anyone know where im going wrong - is it possible to do what im trying to do?!

Thanks
0
Comment
Question by:Bergstr
8 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34997352
you presumable don't "run" the query2 inside the loop, which you need to do...
0
 
LVL 6

Expert Comment

by:nagyistvan
ID: 34997377
0
 

Author Comment

by:Bergstr
ID: 34997664
Angellll - I'm not sure what you mean by that - by "running" it im just doing the following:

while($query2 = mysql_fetch_array($querycode2))

 - $querycode2 looks like:

$querycode2 = mssql_query("select * from table");

I'm assuming the mssql_query is what you mean by running it..?

nagyistvan - sorry, dont think thats what im looking for... based on the results from the first query, i then need to query another table using data from the initial query, and will get multiple results from the second query, which again i may need to run a loop to go through. this may end up being 4 or 5 levels deep.

Thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34998157
yes, and you need to put that line inside the first loop.

that said, you might to explain a bit more about what you are trying to do, possibly you can solve this with pure sql?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:MatthewP
ID: 34998359
Did you mean mssql_query and not mysql_query?

It looks like you're doing a mysql fetch on an mssql query when you should be using mssql_fetch_array.

0
 

Author Comment

by:Bergstr
ID: 34998692
Sorry - the actual code is mssql in all parts of the code - but still doesnt work

Angellll - I'll try my best to explain what im currently doing..! :

The first table is a list of orders - im querying that and just using the order details to check that we have sufficient material to supply the order.

Query 1 = select * from orders

then i want to check all the level-1 materials for stock - if they are short of stock, display them, if they have stock, just ignore them and check the next part.

If a level-1 part IS short, check all level-2 stock levels and so on, until im at the lowest point of assembly.

I dont think there is an easy way i can check it without doing multiple queries as  i need if statements at each level to decide if i need to go further into the parts?
0
 
LVL 8

Expert Comment

by:ropenner
ID: 35032907
structure should have the two commented out lines in it, if your second query depends on the contents returned by the first one.

while($query1 = mysql_fetch_array($querycode1))  {
    $mainname=$query1['name']
    echo $mainname;
// create $query2 $query2 = "SELECT.... FROM ... WHERE ...= $mainname"";
// execute $query2 mysql_query($query2);
   while($query2 = mysql_fetch_array($querycode2))  {
           $othername=$query2['name']
           echo $othername;
     }
  }
0
 

Author Closing Comment

by:Bergstr
ID: 35339741
I've just come back and read this, and looked at my example code, and i can now see exactly what your saying!

I'm not sure if that was the problem, however i got it working - going into multiple levels within a loop - not sure if this was the problem, but looking at the code i posted it looks like it possibly was :)

Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now