[SOLVED] loop within sql error statement?

[nomis]

Hi, this is a portion of a script which will associate individuals with specific sites, by use of a link table.

 

I have the script working well, but I'm trying to make the error screen more readable to end-users.  I'm wondering if I can get the results entered (from a checkbox) reported in the error message, but keep getting a parse error.  Is it possible to run a while loop inside the "die" condition?

 

 

	$personsite ="select s.site_name ". 
		"from site s, person_site ps " .
		"where s.site_id = ps.site_id " .
		"and ps.person_id = '$person_id'" ;
$result = mysqli_query($dbc, $personsite) ;	

foreach ($_POST['site_id'] as $site_id) {	


	$ptquery = "INSERT into person_site (person_id, site_id)".
			"VALUES ('$person_id', '$site_id')" ;

	mysqli_query($dbc, $ptquery)
		or die ('<div class="error">Error: ' . $site_id .  ' '.$person_id . ' One or more of those sites has already been selected for this person. </div> 
		         <p>Please click your back button and verify your entries.</p>
				 <p>' . while($rr = mysqli_fetch_array($result)) { 
				  echo  $rr['site_name'];
				 echo '<br />' .
					} 
					.'</p>');

	  //or die ('Error: '.mysqli_error($dbc));
  
}	

 

If I remove this piece, it works fine;  it's bringing back $site_id and $person_id fine. 

 

. while($rr = mysqli_fetch_array($result)) { 
				  echo  $rr['site_name'];
				 echo '<br />' .
					} 
					.'</p>'

 

Is there something wrong with my syntax, or with my logic, or is this simply not possible this way?  The error is right on the line where the while statement starts.

 

Thanks!

[nomis]

Okay, I realize I was going about this the wrong way...  I was under the assumption that it would always be erring for the same reason which, even though I may have been right, is a bad method for going about this... (even if it is possible).

 

I ended up handling duplicate entries before the insert (which makes more sense):

 

	foreach ($_POST['site_id'] as $site_id) {	

	$personsite ="select s.site_name ". 
		"from site s, person_site ps " .
		"where s.site_id = ps.site_id " .
		"and ps.person_id = '$person_id'" ;
	$result = mysqli_query($dbc, $personsite) ;	

	$exist = "SELECT * FROM person_site WHERE person_id= $person_id and site_id = $site_id ";
	$rexist = mysqli_query($dbc, $exist) ;

     
	if (mysqli_num_rows($rexist) != 0 )   {
    echo 'this person already exists in: <br /> <br /> '; 
	while ($rr = mysqli_fetch_array($result)) { 
				  echo  $rr['site_name'] . '<br />';
					} 
}
	else {


	$ptquery = "INSERT into person_site (person_id, site_id)".
			"VALUES ('$person_id', '$site_id')" ;

	mysqli_query($dbc, $ptquery)
			or die ('Error: '.mysqli_error($dbc));
	  }
  
}	

[nomis]

okay, though the above code works, I have a minor problem which, though not crucial to the running of the application, produces very ugly results, which I'd like to avoid.

 

What in this is that the data above is being grabbed from a series of checkboxes.  It works and looks perfect if a person only adds ONE site (site_id) at a time, or selects ONE site_id which already exists.

 

The problem is that because it is within a foreach loop, it

 

1. produces the exact same row for each duplicate entry for the error rows with

if (mysqli_num_rows($rexist) != 0 )

 

2. for the rows which are successfully inserted, it produces a confirmation row for each one, concatenating the list with each entry.

 

The form runs fine, it's just that the visible output is ugly.    I can't figure out a way around this, as I need the operations to occur within the foreach loop, otherwise the variables are not passed properly.

 

Does anyone have any suggestions for how to limit the output statements to be only once for each of the two above circumstances?

 

[nomis]

this is still bugging me, and I haven't been able to come up with a solution

 

I apologize if this is asking too much, but can anyone take a look at this and point me in the right direction for a logical way of handling this?    Should I go back to trying to include a loop inside the "die" statement?

 

thanks

 

 

[nomis]

Okay, I ended up taking an entirely different tack to my problem.  I had managed to keep the messages from looping by taking portions out of the foreach, but was still failing at causing an error if more than one duplicate entry occurred (something very possible).

 

I decided to rethink this, and realized that though validation may be useful, it may not be the most user-friendly option.  Since I was already grabbing the choices from the database, and I already had the $person_id variable, it seemed to make more sense to eliminate the chance of error, and only show sites to which this person was not already associated in the first place, which was handled by way of a simple sql query.

 

anyway, problem solved.   If anyone's curious, this is what I used:

 

	<?php 
$siteinfo = "select site_id, site_name
			from site
			where site_id not in
			(select s.site_id
			from site s, person_site ps
			where ps.person_id = $person_id
			and s.site_id = ps.site_id)
			order by site_name";
$r = mysqli_query($dbc, $siteinfo ); 

while ($row = mysqli_fetch_array($r)) {
//	if (mysqli_fetch_array($rexist) != mysqli_fetch_array($r)) {
echo '<input type="checkbox" id="site_id" name="site_id[]" ' ;
echo 'value="' . $row[0] .'"';
echo '> ' . $row[1] . '<br />';
}
?>