[squibby]

Hi i have a small annoying problem. The following code works perfectly except one thing. The variable $stuid come from a previous INSERT query and is stored no problem. I echo it out to confirm this. However when i try to put it into the following query i get no result or errors.

		
$stuid = mysql_insert_id();
echo $stuid;
			
$query2 = ("SELECT 
`STU_ID`,`STU_Chinese_Name`,`STU_English_Name`,`STU_Gender`,`STU_Phone_Number`,`STU_Email`,`STU_Notes`,`STU_Birthday`,`STU_Picture`,`STU_joindate`,`STA_English_Name`,`SRC_Source_Name`
   FROM source
	INNER JOIN students ON source.SRC_ID = students.STU_Source
	INNER JOIN staff ON students.STU_Course_Consultant = staff.STA_ID
	INNER JOIN transaction_master ON students.STU_ID = transaction_master.TM_Customer_ID
	INNER JOIN transaction_detail ON transaction_master.TM_TRX = transaction_detail.TD_ID
	INNER JOIN services ON transaction_detail.TD_Service_ID = services.SER_ID
	WHERE students.STU_ID = $stuid");

$display = mysql_query($query2) or die ('Error Selecting data to display');
		
					
			echo "<table class = 'gridtable' >
			<thead>
			<tr>
			<th>ID</th>
			<th>Chinese Name</th>
			<th>English Name</th>
			<th>Gender</th>
			<th>Phone</th>
			<th>Email</th>
			<th>DOB</th>
			<th>Join Date</th>
			<th>Source</th>
			</tr>
			</thead>
			</tbody>";

			while($row = mysql_fetch_array($display))
			  {
			  
			  echo "<tr>";
			  echo "<td>" . $row['STU_ID'] . "</td>";
			  echo "<td>" . $row['STU_English_Name'] . "</td>";
			  echo "<td>" . $row['STU_Chinese_Name'] . "</td>";
			  echo "<td>" . $row['STU_Gender'] . "</td>";
			  echo "<td>" . $row['STU_Phone_Number'] . "</td>";
			  echo "<td>" . $row['STU_Email'] . "</td>"; 
			  echo "<td>" . $row['STU_Birthday'] . "</td>"; 
			  echo "<td>" . $row['STU_joindate'] . "</td>"; 
			  echo "<td>" . $row['SRC_Source_Name'] . "</td>"; 
			  }
			echo "</tbody>";
			echo "</table>";

I know the query works fine because if i change the line

 WHERE students.STU_ID = $stuid    

to this

 WHERE students.STU_ID = 167 

everything displays. So why will the query not take the variable? I have tried putting these "" `` '' around it but it makes no difference. Any ideas? I had this working in a previous task but it wont work here.

[Dormilich]

what does var_dump($stuid) give you?

[squibby]

it gives me this

int(477)

[Dormilich]

is 477 a valid student id in your joined table? (you said 167 were one)

just to make sure you could also echo the SQL query ...

This post has been edited by Dormilich: 08 February 2012 - 07:16 AM

[squibby]

477 MUST be valid as it is generated from the ID of the query before it that has gone into the database (see below)

After changing the code and refreshing the browser the table shows no results. i have no errors. The var dump shows int(477) the echoed variable shows 477. When i go to phpmyadmin there is indeed a record with id 477. So its in the database.

the full code

<?php
include('header.php');
include('auth.php');
 ?>

<div id="content">
	<div id="logbox">
		<b>You are logged in as:&nbsp;<font color=#00CC00><?php  echo  $_SESSION['myusername'];?></font></b>
	</div>
	<div id="contentleft">

	
<div id = "newheader">
	<p>Added Successfully!</p>
</div><br>
		
		
		
		<?php
			$chinesename = $_POST['chinesename'];
			$englishname = $_POST['englishname'];
			$gender = $_POST['gender'];
			$courseconsultant = $_POST['courseconsultant'];
			$phonenumber = $_POST['phonenumber'];
			$email = $_POST['email'];
			$source = $_POST['source'];
			$birthday = $_POST['birthday'];
			$joindate = $_POST['joindate'];
			$notes = $_POST['notes'];
			$image = $_FILES['file']['name'];
			$tmp_name = $_FILES['file']['tmp_name'];

			include ('connect.php');
			mysql_query("set names utf8");

			$query = "INSERT INTO students(STU_Chinese_Name,STU_English_Name,STU_Gender,STU_Course_Consultant,STU_Phone_Number,STU_Email,STU_Source,STU_Notes,STU_Picture,STU_Birthday,STU_joindate) VALUES ('$chinesename','$englishname','$gender','$courseconsultant','$phonenumber','$email','$source','$notes','$image','$birthday','$joindate')";
			
			mysql_query($query) or die ('Error updating database');
			
			
			$stuid = mysql_insert_id();
			echo $stuid;
			var_dump($stuid);
			
			$query2 = ("SELECT `STU_ID`,`STU_Chinese_Name`,`STU_English_Name`,`STU_Gender`,`STU_Phone_Number`,`STU_Email`,`STU_Notes`,`STU_Birthday`,`STU_Picture`,`STU_joindate`,`STA_English_Name`,`SRC_Source_Name`
			FROM source
			INNER JOIN students ON source.SRC_ID = students.STU_Source
			INNER JOIN staff ON students.STU_Course_Consultant = staff.STA_ID
			INNER JOIN transaction_master ON students.STU_ID = transaction_master.TM_Customer_ID
			INNER JOIN transaction_detail ON transaction_master.TM_TRX = transaction_detail.TD_ID
			INNER JOIN services ON transaction_detail.TD_Service_ID = services.SER_ID
			WHERE students.STU_ID = $stuid");

			$display = mysql_query($query2) or die ('Error Selecting data to display');
		
					
			echo "<table class = 'gridtable' >
			<thead>
			<tr>
			<th>ID</th>
			<th>Chinese Name</th>
			<th>English Name</th>
			<th>Gender</th>
			<th>Phone</th>
			<th>Email</th>
			<th>DOB</th>
			<th>Join Date</th>
			<th>Source</th>
			</tr>
			</thead>
			</tbody>";

			while($row = mysql_fetch_array($display))
			  {
			  
			  echo "<tr>";
			  echo "<td>" . $row['STU_ID'] . "</td>";
			  echo "<td>" . $row['STU_English_Name'] . "</td>";
			  echo "<td>" . $row['STU_Chinese_Name'] . "</td>";
			  echo "<td>" . $row['STU_Gender'] . "</td>";
			  echo "<td>" . $row['STU_Phone_Number'] . "</td>";
			  echo "<td>" . $row['STU_Email'] . "</td>"; 
			  echo "<td>" . $row['STU_Birthday'] . "</td>"; 
			  echo "<td>" . $row['STU_joindate'] . "</td>"; 
			  echo "<td>" . $row['SRC_Source_Name'] . "</td>"; 
			  }
			echo "</tbody>";
			echo "</table>";
					?>



			<br><fieldset>
			<h2>What do you want to do next?</h2>
			<br>
			<a href="menu_addinfo.php">Add another student</a><br>
			<a href="form_addstudent.php">Delete this record</a><br>
			<a href="form_addstudent.php">Add a transaction</a><br>
			</fieldset>

	</div>
	<?php include('sidebar.php'); ?>
</div>
<?php include('footer.php'); ?>

[Dormilich]

if there is a record with the ID 477 in the students table does not mean that the joined query has results for that ID, too. you could check what the result set looks like in phpMyAdmin if you take your query and leave off the WHERE condition.

[squibby]

ok hang on this is weird. i tried putting in recent values:

e.g STU_ID = 477 and it shows up nothing. Even though those records with those IDs are clearly in the database as i can see them in phpmyadmin.

ID 167 is an old ID from before i was doing this. im really confused now.

[Dormilich]

what about transaction_master.TM_Customer_ID?

[squibby]

ok i am getting closer to an answer here. but still not sure. When i pull up the query in phpmyadmin it gives me a result set of only 29 records. We both know there are over 400 records in my student table from the ids that are being generated. ID 167 is included in that result of 29 so thats why it was showing up. arggg

Now, if i test the code and do this

$stuid = 167 and then drop that in the query it brings back the record 167. So the identified problem is the query. Why on earth is it only bringing back 29 records when there are clearly loads more.

Im not sure what you mean about transaction_master.TM_Customer_ID

[Dormilich]

in your query you have the (JOIN) condition students.STU_ID = transaction_master.TM_Customer_ID every row that does not match this condition is not included in the final result set.

[squibby]

Yes thats it! The new students are added before i add the transaction data for them. So of course the query isnt bringing back any records if there have been no transactions added!

Because i have many tables in my database it gets tedious figuring out what is joined where so i use this generic construct knowing that it will bring me the data i need when i need it.

However in this case that was obviously pretty stupid. I should only use the tables that are absolutley neccessary in getting the results i needed.

The reason why i did it in the first place was so that i could echo out the actual names of various fields instead of just having a load of ID's on the page that the user couldnt understand.

Maybe there is a better way as the POST ed data is only IDS but on the forms i have real values displayed which are pulled from the database.


Anyway my updated code below does the trick. Thanks Dormilich you are extremly good! heres the banana

SELECT `STU_ID` , `STU_Chinese_Name` , `STU_English_Name` , `STU_Gender` , `STU_Phone_Number` , `STU_Email` , `STU_Notes` , `STU_Birthday` , `STU_Picture` , `STU_joindate` , `STA_English_Name` , `SRC_Source_Name`
FROM source
INNER JOIN students ON source.SRC_ID = students.STU_Source
INNER JOIN staff ON students.STU_Course_Consultant = staff.STA_ID
WHERE students.STU_ID = $stuid

[Dormilich]

squibby, on 08 February 2012 - 04:16 PM, said:

Thanks Dormilich you are extremly good!

just a matter of experience. I have used JOINs over half a dozen tables as well.