Db Issue: only one record is read

[toocoded]

Hi All,

I have a problem and I dont understand what's happening, because my method usually works. I am querying a bunch of records from a database given a foreign key id. See code as follows

$castQuery = "SELECT * FROM cast WHERE production_id = '$newID' ";
	$cresult = mysql_query($castQuery, $conn);	
	$cast = "";
	while ($crow = mysql_fetch_row($cresult)) {
		$real_name = $crow[1];
		$stage_name = $crow[2];
		$cast = "<p><b>$real_name</b> ~ $stage_name</p>";
	}

I dont understand why im only getting 1 record.

Any help would be appreciated.

[Xtremefaith]

Not sure I completely understand some of your variables that are being called, and I can't see if this snippet here is within a loop of some sort, but from what I see it seems like you told the query to only pull the 1 production_id that matches the $newID unless you have production_id's with the same value, but as an id field I assume that each is unique and auto-incremented. Still new at this myself but that's where I see a problem.

[toocoded]

The production_id is not a Primary Key in this context but actually a Foreign Key. I however, am trying to get all the records in the table that matched the variable $newID. See the table below for a sample af the data in the database.

cast_id 	real_name 	stage_name 	production_id
	1 	Debra    	Debra 	                22
	2 	Ryan    	Calpher the Brisc 	1
	3 	Ryan            happy uther 	        22
	4 	Ryan     	Calpher the Brisc 	24
	5 	Gary    	Dready 	                1
	6 	Kim Senior 	Ashley Dove 	        28

So if $newID hold the value '22' i should be getting 2 records instead of one.

[toocoded]

OH wow. I just figured it out. I totally forgot to use the "." before the "=" sign.
Where I have $cast = "<p><b>$real_name</b> ~ $stage_name</p>"; I should have had $cast .= "<p><b>$real_name</b> ~ $stage_name</p>"; so that the iterations forst iterations are read.

Slight yet tricky thing to overlook i guess. Thanks for your help though Xtremefaith.

[vaibhav1983]

Try using mysql_fetch_array($cresult)
This gives you every row with an associated array.

You can access the field names by their name.
e.g.
while($row=mysql_fetch_array($cresult))
{
extract($row);
// Now fields will be extracted into their variable names.
// $realname will contain the field realname from db for that row.
// $stage_name will contain the filedname stage_name for that row.
}

For learning this, I recommend making a lot of test scripts.
This helps your learning.

[toocoded]

Wow. This makes life so much easier. Thanks so much for this info - Im gonna try it right away :)