SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple queries help please...

    Morning All,

    Iím confused about running multiple queries on one page. Basically on this page runs a query to delete a record and select data from a database

    PHP Code:
    <?php

    // login stuff

    $msg "";

    if(isset(
    $_POST['Submit']))
    {
        
    $total $_POST['total'];
        
    $td 0;
        
    $i 0;
        
        for(
    $i 1$i <= $total$i++)
        {
            if(isset(
    $_POST["d$i"]))
            {
                
    mysql_query("DELETE FROM reports WHERE report_id=".$_POST["d$i"]);
                
    $td++;
            }
        }

        
    $msg "$td reports(s) deleted!";
    }

    $result mysql_query("SELECT teams.team_name as home_teams
    FROM reports, teams
    WHERE reports.home_team_id = teams.team_id"
    );

    $num mysql_num_rows($result);
    $n 0;
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Test</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <?php echo $msg?>
    <br / >
    <form name="form1" method="post" action="">
      <?php while($row mysql_fetch_array($result)){
        
    $n++;
      
    ?>
       <input type="checkbox" name="d<?php echo $n;?>" value="<?php echo $row['report_id'];?>">
      <?php echo $row['home_teams'];?> -
      <?php echo $row['away_teams'];?> <br />
      <?php
      
    }
      
    ?>
       <input type="submit" name="Submit" value="Delete" class="button"> 
       <input name="total" type="hidden" id="total" value="<?php echo $n?>"> 
    </form>
    </body>
    </html>
    http://www.slougheaz.org/football/ad...edit_list2.php

    But I also want to add another query:

    Code:
     
    $result2 = mysql_query("SELECT teams.team_name as away_teams
    FROM reports, teams
    WHERE reports.away_team_id = teams.team_id");
    How can I include this so I can also output these query results on my page?

    Thanks

    Chris

  2. #2
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your code must be running properly. what is your problem now. Is it giving any error message? Instead of using counter numbers in the checkbox name, you can use the checkbox array and get it from PHP.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  3. #3
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try out this:
    PHP Code:
    <?php
    // login stuff
    $msg "";
    if(isset(
    $_POST['Submit'])){
        
    $total $_POST['total'];
        
    $repids $_POST['rep_id'];
        foreach(
    $repids as $id){
            
    mysql_query("DELETE FROM reports WHERE report_id='$id'");
        }
        
    $msg count($repids) . " reports(s) deleted!";
    }
    $result mysql_query("SELECT teams.team_name as home_teams FROM reports, teams WHERE reports.home_team_id = teams.team_id");
    $num mysql_num_rows($result);
    $n 0;
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Test</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <?php echo $msg?>
    <br / >
    <form name="form1" method="post" action="">
      <?php while($row mysql_fetch_array($result)){
        
    $n++;
      
    ?>
       <input type="checkbox" name="rep_id[]" id="rep_id[]" value="<?php echo $row['report_id'];?>">
      <?php echo $row['home_teams'];?> -
      <?php echo $row['away_teams'];?> <br />
      <?php
      
    }
      
    ?>
       <input type="submit" name="Submit" value="Delete" class="button"> 
       <input name="total" type="hidden" id="total" value="<?php echo $n?>"> 
    </form>
    </body>
    </html>
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  4. #4
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rajug,

    Appreciate that, but maybe I did not explain myself clearly enough. My code worked before. All I wanted to do was add the below query to it:

    Code:
    $result2 = mysql_query("SELECT teams.team_name as away_teams
    FROM reports, teams
    WHERE reports.away_team_id = teams.team_id");
    So it outputs this information on my page as well as my delete query and first select query.

    Any suggestions?

    Thanks

  5. #5
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh ok try this then. You can just do like this within the same form or create separate form and show second result in another form.
    PHP Code:
    <?php
    // login stuff
    $msg "";
    if(isset(
    $_POST['Submit'])){
        
    $total $_POST['total'];
        
    $repids $_POST['rep_id'];
        foreach(
    $repids as $id){
            
    mysql_query("DELETE FROM reports WHERE report_id='$id'");
        }
        
    $msg count($repids) . " reports(s) deleted!";
    }
    $result mysql_query("SELECT teams.team_name as home_teams FROM reports, teams WHERE reports.home_team_id = teams.team_id");
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Test</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <?php echo $msg?>
    <br / >
    <form name="form1" method="post" action="">
      <?php while($row mysql_fetch_array($result)){
        
    $n++;
      
    ?>
       <input type="checkbox" name="rep_id[]" id="rep_id[]" value="<?php echo $row['report_id'];?>">
      <?php echo $row['home_teams'];?> -
      <?php echo $row['away_teams'];?> <br />
      <?php
      
    }

    $result2 mysql_query("SELECT teams.team_name as away_teams FROM reports, teams WHERE reports.away_team_id = teams.team_id");
     while(
    $row mysql_fetch_array($result2)){
        
    $n++;
      
    ?>
       <input type="checkbox" name="rep_id[]" id="rep_id[]" value="<?php echo $row['report_id'];?>">
      <?php echo $row['home_teams'];?> -
      <?php echo $row['away_teams'];?> <br />
      <?php
      
    }
      
    ?>
       <input type="submit" name="Submit" value="Delete" class="button"> 
       <input name="total" type="hidden" id="total" value="<?php echo $n?>"> 
    </form>
    </body>
    </html>
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  6. #6
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Eeek, really sorry but think i've been misunderstood again.

    On my form:

    PHP Code:
    <form name="form1" method="post" action="">
      <?php while($row mysql_fetch_array($result)){
        
    $n++;
      
    ?>
       <input type="checkbox" name="rep_id[]" id="rep_id[]" value="<?php echo $row['report_id'];?>">
      <?php echo $row['home_teams'];?> -
      <?php echo $row['away_teams'];?> <br />
      <?php
      
    }
      
    ?>
       <input type="submit" name="Submit" value="Delete" class="button"> 
       <input name="total" type="hidden" id="total" value="<?php echo $n?>"> 
    </form>
    I get the details from my $result query.

    So in this same piece of code I want to get the data from $result2 in this same piece of code as well.

    Sorry if I did not explain too well but thank you for helping me...

    Thanks

    Chris

  7. #7
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh i think there is no way of merging two result sets and get result on the way what you want. Since there are the same form elements within the same form tag, what is the problem and you need to do in the same piece of code?? You can store the values into an array from both result queries and you can show the form with foreach() loop too.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  8. #8
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again,

    Here’s my problem. What’s happening here is I’m working with a normalized database with 2 tables:

    results
    result_id
    home_team_id
    home_score
    away_score
    away_team_id

    teams
    team_id
    team_name

    so what I want to do is run two queries. So the first on the results table to get all the results from the database:

    So I can say:

    home_team_id 2-1 away_team_id

    Then for example if home_team_id=1 and away_team_id=2 I could run these two queries on the same form to find out the team_name:

    Code:
    //  Query to get the home team name
    $result2 = mysql_query("SELECT teams.team_name as away_teams
    FROM reports, teams
    WHERE reports.away_team_id = teams.team_id");

    Code:
    //  Query to get the away team name
    $result2 = mysql_query("SELECT teams.team_name as home_teams
    FROM reports, teams
    WHERE reports.home_team_id = teams.team_id");
    So this can’t be done using my code I posted?

    Thanks

    Chris

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but found out the answer - it was by combining a Join twice on the teams table.


Bookmarks

Posting Permissions

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