Choosing field based on ID (MySQL)?

[kilbey1]

I've got a query running that pulls back the desired data. However, I want to pull back a field (Section_Value) where Section_ID = 1, or 2, or 3 (depending). Here's the query:

$sql = "SELECT * FROM resume INNER JOIN candidate WHERE candidate.Candidate_ID = '".$id."'
		AND
		resume.Candidate_ID = candidate.Candidate_ID";

Now I want to do something like the following:

<?php echo $row['Section_Value']; ?>

Except Section_Value is different where Section_ID is 1, or 2, or 3, etc.

How does it get set up? Do I have to do an IF statement of some sort, or do I need to do something like the following:

<?php echo $row['Section_Value'][1]; ?>

Thanks,
Eve

[Weedpacket]

If I understand you right, you want to look at a different field in the returned result set (e.g., 'Section_Value') depending on the value of Section_ID?

Not sure about your schema's design, but if you return all four values (the different values and the ID), and you had an array that associated section ID with field names

$value_by_id = array(1=>'Section_Value', 2=>'Another Field', etc.);

You could then look at the value of $row[$value_by_id[$row['Section_ID']]];
.

[kilbey1]

Hmmm. Not sure this would work this way. The table structure is as so:

Candidate_ID
Section_ID
Section_Value

A scenario:

170 / 1 / this field contains info1
170 / 2 / this field contains entirely different data
etc.

Where Section_Value from Section_ID 1 would be presented in 1 place, and Section_Value from Section_ID 2 would be presented elsewhere, for that candidate.

[tsinka]

Hi,

I think the solution of Weedpacket should do the trick (if understood you right).

Something like this:

$arrValues = array();
$sql = "SELECT * FROM resume INNER JOIN candidate WHERE candidate.Candidate_ID = '".$id."' 
        AND 
        resume.Candidate_ID = candidate.Candidate_ID";

$res = mysql_query($sql);

while ($row = mysql_fetch_array($res)) {
  $arrValues[$row['Section_ID']] = $row['Section_Value'];
}

.....

// section one
if (isset($arrValues[1])) {
  echo "The section one value: ".$arrValues[1]."<br>\n";
}

....

// section two
if (isset($arrValues[2])) {
  echo "The section two value: ".$arrValues[2]."<br>\n";
}

Something like that....

Thomas

[Weedpacket]

Oh, so you want to separate out the different Section_Value records according to their Section_ID. Yes, I think I'm on the same page now.

I'm going to use one array indexed by Section_ID; each element of this array will be an array of all of the relevant Section_Values.

On second thought; forget it. I see tsinka's got 99.4% of the way ahead of me. The only change I'd make would be

$arrValues[$row['Section_ID']][] = $row['Section_Value']; 

And then $arrValues[1] would be an array of all the Section_Values that go with Section_ID 1, and so forth.

(If I was programming in E_ALL (and I usually do), I'd make sure that I'd set $arrValues[1]=array() before trying to put elements in it.)

[kilbey1]

The following code appears to work, but the first value doesn't appear. Any clues as to why this may be?

		$arrValues = array();
$sql = "SELECT * FROM resume INNER JOIN candidate WHERE candidate.Candidate_ID = '".$id."'
        AND
        resume.Candidate_ID = candidate.Candidate_ID";

	$res = mysql_query($sql);
	$row = mysql_fetch_array($res);

	while ($row2 = mysql_fetch_array($res)) {
	  //$arrValues[$row['Section_ID']] = $row['Section_Value'];
	  $arrValues[$row2['Section_ID']] = $row2['Section_Value'];
			  //print_r($row2['Section_Value']);
			  //echo " - ";
			  //print_r($row2['Section_ID']);
			  //echo "<P>";
	} 

[tsinka]

remove the line
$row = mysql_fetch_array($res);

That should fix it.

Thomas

[kilbey1]

It wasn't an error in your code. I have to have it there to allow all my $row['fields'] to be returned that were already in my HTML output. Otherwise, these get broken and don't display. But yes, it does solve my 'other' issue; just presents a new problem. 🙂

[kilbey1]

This issue is still not resolved. The information pulled through arrValues functions; however, no other data appears. If I remove the line $row = mysql_fetch_array($res), then all other data except the one from arrValues appears.

[tsinka]

Post the code you have so far including all the code that displays the data.

Thomas

[kilbey1]

I think you'd be looking for the attached. 🙂 Note, I've stripped away the rest of the code from consultants.php, in order to make it more readable and isolate the problem. 'indie.php' gets included.

[tsinka]

Hi,

assuming that the data like First_Name, Last_Name and so on is always the same in each result record inside the while loop and that Section_ID and Section_Name differ, the fastest solution to your problem might be to change the while loop a little bit:

$row = array();
while ($row2 = mysql_fetch_array($res)) {
  if (empty($row))
    $row = $row2;

  $arrValues[$row2['Section_ID']] = $row2['Section_Value'];
} 

With this modification you don't need to edit you files too much.

Thomas

[kilbey1]

For some reason, this is breaking my code (as in, blank page shows up unless i comment this portion out). Here's what I've got...

edit: it starts breaking at while($row2 = mysql_fetch_array($res)) {...

		$sql = "SELECT * FROM resume INNER JOIN candidate WHERE candidate.Candidate_ID = '".$id."'
        AND
        resume.Candidate_ID = candidate.Candidate_ID";

	$res = mysql_query($sql);

	$row = array(); 
	while ($row2 = mysql_fetch_array($res)) {
		if (empty($row)) { $row = $row2; }
		$arrValues[$row2['Section_ID']] = $row2['Section_Value'];
	}

But you're right about assuming that the data (like First_Name, Last_Name) is always the same in each result record inside the while loop and that Section_ID and Section_Name differ.

[tsinka]

Ok,

add
error_reporting(E_ALL);
on top of the script to check what might be wrong.

The code:

$sql = "SELECT * FROM resume r INNER JOIN candidate c "
      ."ON r.Candidate_ID=c.Candidate_ID "
      ."WHERE c.Candidate_ID = '$id'"; 

$res = mysql_query($sql) or die("SQL error: ".mysql_error()); 
$row = array();  

$arrValues = array();
while ($row2 = mysql_fetch_array($res)) { 
  if (empty($row)) { 
    $row = $row2; 
  } 

  $arrValues[$row2['Section_ID']] = $row2['Section_Value']; 
}

I couldn't find a syntax error but maybe I'm blind 🙂

Thomas

[kilbey1]

Works beautifully! I'll test it out a bit but on first run it worked perfectly. Thanks, this is just what I needed!