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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts

    While loop ++ sql statement

    Hey Guys,

    Hoping somebody can help me

    I am trying to run multiple sql strings within a while loop.

    So instead of doing:

    PHP Code:
    while ($row mysql_fetch_array($resultMYSQL_ASSOC))

        {

    echo 
    $row["Name"]


    I would like to do this:

    while ($row2 = mysql_fetch_array($result, MYSQL_ASSOC))

    PHP Code:
        {
            
            
    $sqlstring"UPDATE People
    SET Name="
    .$row["Name"].",Interests=".$row["Interests"]." WHERE Group='".$row["Group"]."'";

        } 
    I am running other queries prior to this to get the information that I need to determine what rows to update.

    The script overall works and the correct items are being updated, however only 1 row is being update which I would assume is the last one being pulled and that it is because $sqlstring is being overwritten each time, is this correct?

    If so how can I ensure that every sql query gets a unique variable e.g $sqlstring4 , is it possible to do a increase number on something like this?

    Many thanks in advance.

  • #2
    New Coder
    Join Date
    Sep 2011
    Posts
    74
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You need to run the query inside the loop, if you are doing it outside of the loop only the query from the last iteration will run. Or you could add each query to a array then process that after the loop has finished.

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Combining the SQL to get rid of the loop would be more efficient. You haven't posted the SELECT query so I can't show how to combine them.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    Combining the SQL to get rid of the loop would be more efficient. You haven't posted the SELECT query so I can't show how to combine them.
    Hi ,

    Sorry for the late reply, this is the code I currently have.

    Many thanks for your help!!

    PHP Code:
       $sqlstring "SELECT * FROM People
    WHERE Location !=''"
    ;

    $result mysql_query($sqlstring) or die('query failed: ' mysql_error());

    $peopleareas "";

    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC))
        {
            
            
    $peopleareas $peopleareas."OR area='".$row["Location"]."' ";
        }
        
        
        
        
        
    $sqlstring2 "SELECT * FROM arealist
    WHERE area ='none' $peopleareas"
    ;

    $result mysql_query($sqlstring2) or die('query failed: ' mysql_error());
    while (
    $row2 mysql_fetch_array($resultMYSQL_ASSOC))

        {
            
            
    $sqlstring1"UPDATE People
    SET Manager='"
    .$row2["manager"]. WHERE Location='".$row2["area"]."'";

        }

    print $sqlstring1; 
    Last edited by IamHe; 01-12-2014 at 03:33 AM.

  • #5
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    The following should be fairly close to being able to do all of what you want in a single query. I haven't tested it so it might not be quite%2
    Last edited by felgall; 01-12-2014 at 03:45 AM.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #6
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    The following should be fairly close to being able to do all of what you want in a single query. I haven't tested it so it might not be quite%2

    Hey,

    Sorry for the late reply, I havent had a minute to get back on and carry on with this. .

    I don't see anything above?

    Thanks again for your help

  • #7
    Regular Coder
    Join Date
    Mar 2009
    Posts
    107
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi,

    I have managed to achieve what I wanted using JOIN ...

    Creating a new table all together and taking from the two old tables the specific data that I require.

    I am not sure if this is the best solution here, but it has sure stripped away ALLOT of code and seems to work well for what I needed.

    Many thanks for everybody's help with this.


  •  

    Posting Permissions

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