USe Mysql Query results to form a new query.

aaronrb · January 18, 2013

Hi everyone..

i have 2 tables...

Accounts & Students..

Both contain the field agreement_id which is used to link the 2.

I am using this query to display a table of results

$sql = mysql_query("SELECT * FROM accounts GROUP BY agreement_id ORDER BY payer_name");
if(mysql_num_rows($sql) === 0){ die("No Accounts Available");}
echo '<b>Total Accounts: </b>'.mysql_num_rows($sql);
?>



<table >
<tbody>
<tr class="header">
<td><b>Payers Name</b></td>
<td><b>No. of Students</b></td>
<td><b>Account I.D.</b></td>
<td><b>Contract Length</b></td>
<td><b>Date Started</b></td>
<td><b>Fee</b></td>

</tr>
<?php
while($row = mysql_fetch_array($sql)){
?>

ALL results display fine from this query...

and then there is a CSS popbox on mouse over the payers name.. and the box displays the students name which is retrieved from the students DB.

But for EVERY result in the first query, the students name in the popbox is the same...

Here is my complete code. Its this area that seems to be the problem

<!-- HIDDEN / POP-UP DIV -->
     <div id="pop-up">
       <h3>Students Linked to this Account:</h3>
       <p><?php 
       $id = $row['agreement_id'];

       $sql2 = mysql_query("SELECT * from students WHERE agreement_id = '$id' ");


       while($row2 =  mysql_fetch_array($sql2)){
           echo $row2['name']; 
       }
       ?>

<?php
session_start(); // NEVER forget this!
if(!isset($_SESSION['loggedin']))
{
// Get the full URL of the current page
$return = $_SERVER['REQUEST_URI'];
// header('Location:http://www.fsma.co.uk/?refer='.rawurlencode($return));

die("To access this page, you need to <a href='login.php?return=".$return."'>LOGIN</a>"); // Make sure they are logged in!
} // What the !isset() code does, is check to see if the variable $_SESSION['loggedin'] is there, and if it isn't it kills the script telling the user to log in!
?><?php
ini_set('display_errors',1);
error_reporting(E_ALL);
?>
<!DOCTYPE html>
<html>
<head><script type="text/javascript">
window.onload = function(){
var text_input = document.getElementById ('id');
text_input.focus ();
text_input.select ();
}
</script><link rel="stylesheet" type="text/css" href="style.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title></title>
<style type="text/css">


h1, h3 {
margin: 0;
padding: 0;
font-weight: normal;
}



div#container {

}

/* HOVER STYLES */
div#pop-up {
display: none;
position: absolute;
width: 280px;
padding: 10px;
background-color: #ffffff;
border: 1px solid #000000;
font-size: 90%;
}
</style>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script>
<script type="text/javascript">
$(function() {
var moveLeft = 20;
var moveDown = 10;

$('a#trigger').hover(function(e) {
$('div#pop-up').show();
//.css('top', e.pageY + moveDown)
//.css('left', e.pageX + moveLeft)
//.appendTo('body');
}, function() {
$('div#pop-up').hide();
});

$('a#trigger').mousemove(function(e) {
$("div#pop-up").css('top', e.pageY + moveDown).css('left', e.pageX + moveLeft);
});

});
</script> </head>
<body><div class="return_to_main"><a href="index.php"><img src="images/main_page.jpg" /></a></div>
<center><h1> View Accounts </h1><br /><?php
//connect to DB
$conn = mysql_connect('localhost', 'web77-fsma', 'raymond');
mysql_select_db() ***** date removed ******

$sql = mysql_query("SELECT * FROM accounts GROUP BY agreement_id ORDER BY payer_name");
if(mysql_num_rows($sql) === 0){ die("No Accounts Available");}
echo '<b>Total Accounts: </b>'.mysql_num_rows($sql);
?>



<table >
<tbody>
<tr class="header">
<td><b>Payers Name</b></td>
<td><b>No. of Students</b></td>
<td><b>Account I.D.</b></td>
<td><b>Contract Length</b></td>
<td><b>Date Started</b></td>
<td><b>Fee</b></td>

</tr>
<?php
while($row = mysql_fetch_array($sql)){
?>
<tr >
<td><center><b>
<div id="container"> <a href="#" id="trigger"><?php echo $row['payer_name']; ?> </a>
<!-- HIDDEN / POP-UP DIV -->
<div id="pop-up">
<h3>Students Linked to this Account:</h3>
<p><?php
$id = $row['agreement_id'];

$sql2 = mysql_query("SELECT * from students WHERE agreement_id = '$id' ");


while($row2 = mysql_fetch_array($sql2)){
echo $row2['name'];
}
?>
</p>
</div> </div>

</b></center></td>
<td><center><b><?php echo mysql_num_rows($sql2); ?></b></center></td>
<td><b><?php echo $row['agreement_id']; ?></b></td>
<td><center><b><?php echo $row['contract_length']; ?> </b></center></td>
<td><center><b><?php echo $row['contract_start']; ?></b></center></td>
<td><center><b>£<?php echo $row['payment_amount']; ?></b></center></td>

</tr>
<?php
}
?>
</table></center>

</body>
</html>

Any help is much appreciated, have been racking my brains for 2 days !!!! ARGH!

Thanks,

Jessica · January 18, 2013

Never run queries in loops, first of all. You can accomplish this with a join.

Once you fix that I have a feeling your other problem will be fixed, since I'm sure you've mixed up $sql and $sql2 or $row and $row2.

aaronrb · January 18, 2013

I have rewritten things a couple of times to make sure the variables were in the right place..

and i did look at join, but got rather confused.. Il have another look now.. Thanks for you reply !

Jessica · January 18, 2013

I actually just wrote a tutorial about this the other day on my blog, it might help you with the logic part. It's much more abstract than yours but give it a read.

http://thewebmason.com/tutorial-parent-child-lists/

aaronrb · January 18, 2013

Ok, i have this query


SELECT *
FROM accounts
INNER JOIN students
ON accounts.agreement_id=students.agreement_id

it displays a student name in the box, but still its the same name for every row.

aaronrb · January 18, 2013

I actually just wrote a tutorial about this the other day on my blog, it might help you with the logic part. It's much more abstract than yours but give it a read.

http://thewebmason.c...nt-child-lists/

Sorry i didnt see you had replied before i did.. im going to look at the tutorial now...

Jessica · January 18, 2013

You'll need to rewrite your code to reflect having only one query. Post your new code.

aaronrb · January 18, 2013


<center><h1> View Accounts </h1><br /><?php 
//connect to DB
$conn = mysql_connect('localhost', 'web77-fsma', 'raymond');
mysql_select_db("web77-fsma", $conn) or die(mysql_error());

//$sql = mysql_query("SELECT * FROM accounts GROUP BY agreement_id ORDER BY payer_name");


$sql = mysql_query("SELECT *
FROM accounts
INNER JOIN students
ON accounts.agreement_id=students.agreement_id
GROUP BY accounts.payer_name");
//if(mysql_num_rows($sql) === 0){ die("No Accounts Available");}
//echo '<b>Total Accounts: </b>'.mysql_num_rows($sql);
?>



   <table >
<tbody>
<tr class="header">
<td><b>Payers Name</b></td>
                       <td><b>No. of Students</b></td> 
                       <td><b>Account I.D.</b></td>
                       <td><b>Contract Length</b></td>
                       <td><b>Date Started</b></td>
                       <td><b>Fee</b></td>

</tr>
          <?php 
          while($row = mysql_fetch_array($sql)){
          ?>
               <tr >
                   <td><center><b>
                            <div id="container">  <a href="#" id="trigger"><?php echo $row['payer_name']; ?> </a>
        <!-- HIDDEN / POP-UP DIV -->
     <div id="pop-up">
       <h3>Students Linked to this Account:</h3>
       <p>
       <?php 
      echo $row['name'];
       ?>
           </p>
     </div>   </div>

           </b></center></td>
           <td><center><b><?php echo mysql_num_rows($sql); ?></b></center></td>
    <td><b><?php echo $row['agreement_id']; ?></b></td>
<td><center><b><?php echo $row['contract_length']; ?> </b></center></td>
                       <td><center><b><?php echo $row['contract_start']; ?></b></center></td>
                       <td><center><b>£<?php echo $row['payment_amount']; ?></b></center></td>

</tr>
               <?php 
          }
               ?>
   </table></center>

Jessica · January 18, 2013

Which name is the one that is wrong?

Have you verified it using a tool like phpmyadmin?

aaronrb · January 18, 2013

its the $row['name'] that doesnt show correctly. here:



<div id="pop-up">
       <h3>Students Linked to this Account:</h3>
       <p>
       <?php 
      echo $row['name'];
       ?>
           </p>
     </div>

i have just checked using PHPMYADMIN, using the same query, it shows students and payers names correctly... however same payers have 2 or 3 students linked to them, and it only shows one..

Edited January 18, 2013 by aaronrb

aaronrb · January 18, 2013

oops sorry thats because i had a group on the payer_name.. so it does show correctly in phpmyadmin

aaronrb · January 18, 2013

Ok, i have found out for some reason the JQUERY SCRIPT i added to show a little pop up of student names, on mouseover.. was doing something to prevent it from working..

as i followed jessica's tutorial which worked fine.. then i added the JQUERY to there and it no longer worked...

so to get this page working ASAP i have removed the jquery and just added a row to the table... (and changed to the mysqli code..)

<table >
<tbody>
<tr class="header">
<td><b>Payers Name</b></td>
<td><b>No. of Students</b></td>
<td><b>Account I.D.</b></td>
<td><b>Contract Length</b></td>
<td><b>Date Started</b></td>
<td><b>Fee</b></td>

</tr>
<?php

$res = $mysqli->query($sql);
//We are starting with no states listed yet.
$last_account = NULL;
while ($row = $res->fetch_assoc()){
//Check if the last state listed is not the same as the current one.
//If it is not the same, echo the new state name.
if($last_account != $row['agreement_id']){


// while($row = mysql_fetch_array($sql)){
?>
<tr >
<td><center><b>

<?php echo $row['payer_name']; ?>
</b></center></td>
<td><center><b><?php echo mysqli_num_rows($res); ?></b></center></td>
<td><b><?php echo $row['agreement_id']; ?></b></td>
<td><center><b><?php echo $row['contract_length']; ?> </b></center></td>
<td><center><b><?php echo $row['contract_start']; ?></b></center></td>
<td><center><b>£<?php echo $row['payment_amount']; ?></b></center></td>

</tr><tr><td><?php echo $row['name']; ?></td></tr>
<?php

//Assign the new state as the last state.
$last_account = $row['agreement_id'];
}
//echo "{$row['city']}<br />";
}
?>
</table>

but the only issue now is that for the $row['name']

some accounts have more than one student, how can i get it to list all the students for that account on that row... ??

I have attached an image of hte output, the red circles show the student names, which for some rows should have multiple names

Edited January 18, 2013 by aaronrb

Jessica · January 19, 2013

Move the echo out of the if part

aaronrb · January 19, 2013

Ah i see what you mean, thanks...

sorry i must sound really stupid, now i have another issue.

Shown in the image below, i have highlighted the same account, which has 3 students linked to it, but shows 2 of them together and the 3 on its own..

In accounts table, there is only one account with this number "20134652"

and then in the students table, there are 3 students with the same account number "20134652"

aaronrb · January 19, 2013

OK, i have been looking through the code i have...

<?php
session_start(); // NEVER forget this!
if(!isset($_SESSION['loggedin']))
{
// Get the full URL of the current page
$return = $_SERVER['REQUEST_URI'];
// header('Location:http://www.fsma.co.uk/?refer='.rawurlencode($return));
   die("To access this page, you need to <a href='login.php?return=".$return."'>LOGIN</a>"); // Make sure they are logged in!
} // What the !isset() code does, is check to see if the variable $_SESSION['loggedin'] is there, and if it isn't it kills the script telling the user to log in!
?><?php
ini_set('display_errors',1);
error_reporting(E_ALL);
?>
<!DOCTYPE html>
<html>
   <head><link rel="stylesheet" type="text/css" href="style.css">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
   </head>
   <body><div class="return_to_main"><a href="index.php"><img src="images/main_page.jpg" /></a></div>
   <center><h1> View Accounts </h1><br /><?php
//connect to DB
//$conn = mysql_connect('localhost', 'web77-fsma', 'raymond');
//mysql_select_db("web77-fsma", $conn) or die(mysql_error());
$mysqli = new mysqli('localhost', 'web77-fsma', 'raymond', "web77-fsma");
//$sql = mysql_query("SELECT * FROM accounts GROUP BY agreement_id ORDER BY payer_name");
if ($mysqli->connect_errno) {
   echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$sql = "SELECT *
FROM accounts
INNER JOIN students ON accounts.agreement_id = students.agreement_id";
//if(  $sql->num_rows === 0){ die("No Accounts Available");}
//$row_cnt = $result->num_rows;
if ($stmt = $mysqli->prepare($sql)) {
   /* execute query */
   $stmt->execute();
   /* store result */
   $stmt->store_result();
   if($stmt->num_rows === 0){ die('No Accounts Available');}
   printf("<b>Totl Accounts: </b> %d", $stmt->num_rows);
   /* close statement */
   $stmt->close();
}
;
?>



   <table >
<tbody>
 <tr class="header">
  <td><b>Payers Name</b></td>
				    <td><b>No. of Students</b></td>
				    <td><b>Account I.D.</b></td>
				    <td><b>Contract Length</b></td>
				    <td><b>Date Started</b></td>
				    <td><b>Fee</b></td>

 </tr>
	   <?php

	   $res = $mysqli->query($sql);
//We are starting with no states listed yet.
$last_account = NULL;
while ($row = $res->fetch_assoc()){
//Check if the last state listed is not the same as the current one.
//If it is not the same, echo the new state name.
if($last_account != $row['agreement_id']){


	  // while($row = mysql_fetch_array($sql)){
	   ?>
		    <tr >
			    <td><center><b>

				    <?php echo $row['payer_name']; ?>
	    </b></center></td>
	    <td><center><b><?php echo mysqli_num_rows($res); ?></b></center></td>
 <td><b><?php echo $row['agreement_id']; ?></b></td>
   <td><center><b><?php echo $row['contract_length']; ?> </b></center></td>
				    <td><center><b><?php echo $row['contract_start']; ?></b></center></td>
				    <td><center><b>£<?php echo $row['payment_amount']; ?></b></center></td>
				 </tr>   
				 <tr>
		    <?php

 //Assign the new state as the last state.
 $last_account = $row['agreement_id'];
}
 ?>	 
 <td><?php echo $row['name']; ?></td>
 <?php }
		    ?>
  </tr> </table></center>

   </body>
</html>

And it appears i have 1 account number which is display ALL 3 students underneath... but then the rest only show 1 or 2 of the linked students!

All details are in DB are correct, im confused how it can work for one result and not the rest !

aaronrb · January 19, 2013

Thanks for the help, somehow by including an ORDER BY it seems to work nicely now !!

Sign In

USe Mysql Query results to form a new query.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information