[RESOLVED] Result of one query prints with all the others

[jeggerd]

So my query is being odd,

When i run the submit and code of one query the results of this query also prints too, I'm not sure why.

so for example, if I run:

$average = $_POST['average'];

$query5 = "SELECT c.con_fname, r.Reviewer_Contact_con_id, question_id, AVG( DISTINCT question_score)
FROM Contact c, Individual_Review r
WHERE r.Reviewer_Contact_con_id = c.con_id
AND con_fname = ";
$query5 = $query5 . "'" . $average . "' GROUP BY r.Proposal_proposal_id;";

it works, but it also prints the results of this query:

$check = $_POST['check'];

$query7 = "Select c.con_fname, c.con_lname, s.Contact_con_id,
IF(s.Contact_con_id IS NULL, 'NO', 'YES')
From Contact c Left Join (Select Contact_con_id FROM Speakers
WHERE speaker_year = '". $check . "') As s
ON c.con_id = s.Contact_con_id";
$query7 = $query7 . " ORDER BY c.con_fname;";

If anyone could help point me the right way, that'd be great!
Thanks

[bradgrafelman]

Can you show us a complete code snippet that exhibits the behavior you're seeing? So far you've just shown us a couple of variable assignments, so it's impossible to say what's going on.

Also note the issue about SQL injection attacks and/or errors I mentioned in your other thread.

Finally, note that you can save yourself from typing in a variety of ways such as by using the '.=' concatenation-assignment operator or even [man]sprintf/man. Examples:

$average = $_POST['average']; 

$query5 = "SELECT c.con_fname, r.Reviewer_Contact_con_id, question_id, AVG( DISTINCT question_score) 
FROM Contact c, Individual_Review r 
WHERE r.Reviewer_Contact_con_id = c.con_id 
AND con_fname = "; 
$query5 .= "'" . $average . "' GROUP BY r.Proposal_proposal_id;";

$average = $_POST['average']; 

$query5 = sprintf(
		"SELECT c.con_fname, r.Reviewer_Contact_con_id, question_id, AVG( DISTINCT question_score) 
		FROM Contact c, Individual_Review r 
		WHERE r.Reviewer_Contact_con_id = c.con_id 
		AND con_fname = '%s'
		GROUP BY r.Proposal_proposal_id;",
		$average
);

[jeggerd]

Haha i'm not quite sure what a snippet is but here is my complete code.

I was working on putting in some of the things you told me in the other forum.
Oh thanks for the shorthand tip, I'll implement that soon!

$conn = mysql_connect("$server", "$user", "$pass") or die("Connection Error or Bad Port");

mysql_select_db($dbname) or die("Missing Database");
?>


   <?php

$lastName = isset($_POST['lastName']) ? $_POST['lastName'] : NULL;
$lastName= mysqli_real_escape_string($conn, $lastName);

if ($lastName == NULL) {
    $query = "SELECT c.*, s.speaker_year FROM Contact c, Speakers s WHERE s.Contact_con_id = c.con_id ";
} Else {
    $query = "SELECT c.*, s.speaker_year FROM Contact c, Speakers s WHERE s.Contact_con_id = c.con_id 
AND con_lname =  ";
}
$query = $query . "'" . $lastName . "' ORDER BY con_lname;";


?>


<?php

$state = $_POST['state'];
$state = mysqli_real_escape_string($conn, $state);

$query1 = "Select proposal_id, proposal_status, proposal_title, proposal_description, con_fname, con_affiliation, con_city, role
from Contact c, Proposal p, Presents px
WHERE px.Speakers_Contact_con_id = c.con_id
AND px.Proposal_proposal_id = p.proposal_id
AND c.con_state = ";
$query1 = $query1 . "'" . $state . "' ORDER BY p.proposal_id;";
?>

<?php

$review = $_POST['review'];
$review = mysqli_real_escape_string($conn, $review);

$query2 = "Select con_fname, con_lname con_id, proposal_id, proposal_title
from Contact c JOIN Individual_Review ir JOIN Proposal p
WHERE ir.Reviewer_Contact_con_id = c.con_id
AND ir.Proposal_proposal_id = p.proposal_id
AND con_fname = ";
$query2 = $query2 . "'" . $review . "' ORDER BY c.con_fname;";
?>

<?php

$mail = $_POST['mail'];
$mail = mysqli_real_escape_string($conn, $mail);

$query3 = "Select c.con_fname, c.con_lname, c.con_street_addy, c.con_city, c.con_state, c.con_zip,
con_affiliation, p.proposal_title, p.proposal_status
from Contact c, Proposal p, Presents px
WHERE px.Speakers_Contact_con_id = c.con_id
AND px.Proposal_proposal_id = p.proposal_id
and px.role = ";
$query3 = $query3 . "'" . $mail . "' ORDER BY c.con_fname;";
?>

<?php

$rtype = $_POST['rtype'];
$rtype = mysqli_real_escape_string($conn, $rtype);

$query4 = "Select r.reviewer_type, c.*
from Contact c, Reviewer r
WHERE r.Contact_con_id = c.con_id
AND reviewer_type = ";
$query4 = $query4 . "'" . $rtype . "' ORDER BY r.reviewer_type;";
?>

<?php

$average = $_POST['average'];
$average = mysqli_real_escape_string($conn, $average);

$query5 = "SELECT c.con_fname, r.Reviewer_Contact_con_id, question_id, AVG( DISTINCT question_score)
FROM Contact c, Individual_Review r
WHERE r.Reviewer_Contact_con_id = c.con_id
AND con_fname = ";
$query5 = $query5 . "'" . $average . "' GROUP BY r.Proposal_proposal_id;";
?>

<?php

$rgroups = $_POST['rgroups'];
$rgroups = mysqli_real_escape_string($conn, $groups);


$query6 = "SELECT r.rev_groups_id, c.con_fname, c.con_lname, con_phone, rev_groups_pass, count(p.proposal_id) 
FROM Review_Groups r JOIN Proposal p on r.rev_groups_id = p.Review_Groups_rev_groups_id
JOIN Presents px on px.Proposal_proposal_id = p.proposal_id
JOIN Contact c on px.Speakers_Contact_con_id = c.con_id
JOIN Reviewer rw on rw.Review_Groups_rev_groups_id = r.rev_groups_id
WHERE rw.reviewer_type = 'local' ";

if ($_POST['rgroups']= "") {
    $query6 .= " AND r.rev_groups_id = '" . $rgroups . "' Group BY r.rev_groups_id;";
}else{
    $query6 = "SELECT r.rev_groups_id, c.con_fname, c.con_lname, con_phone, rev_groups_pass, count(p.proposal_id) 
                FROM Review_Groups r JOIN Proposal p on r.rev_groups_id = p.Review_Groups_rev_groups_id
                JOIN Presents px on px.Proposal_proposal_id = p.proposal_id
                JOIN Contact c on px.Speakers_Contact_con_id = c.con_id
                JOIN Reviewer rw on rw.Review_Groups_rev_groups_id = r.rev_groups_id
                WHERE rw.reviewer_type = 'local'
                AND r.rev_groups_id = '" . $rgroups . "' ";
}

?>

<?php

$check = $_POST['check'];
$check = mysqli_real_escape_string($conn, $check);

IF($check != ""){
$query7 = "Select c.con_fname, c.con_lname, s.Contact_con_id,
IF(s.Contact_con_id IS NULL, 'NO', 'YES')
From Contact c Left Join (Select Contact_con_id FROM Speakers
WHERE speaker_year = '". $check . "') As s
ON c.con_id = s.Contact_con_id";
$query7 .= " ORDER BY c.con_fname;";}
?>


<p>
The query:
<p>
<?php
print $query;
?>
</br></br>
<?php
print $query1;
?>
</br></br>
<?php
print $query2;
?>
</br></br>
<?php
print $query3;
?>
</br></br>
<?php
print $query4;
?>
</br></br>
<?php
print $query5;
?>
</br></br>
<?php
print $query6;
?>
</br></br>
<?php
print $query7;
?>
<hr>


<p>
Result of query:
<p>

<?php
$result = mysql_query($query)or die(mysql_error());
$result1 = mysql_query($query1)or die(mysql_error());
$result2 = mysql_query($query2)or die(mysql_error());
$result3 = mysql_query($query3)or die(mysql_error());
$result4 = mysql_query($query4)or die(mysql_error());
$result5 = mysql_query($query5)or die(mysql_error());
$result6 = mysql_query($query6)or die(mysql_error());
$result7 = mysql_query($query7)or die(mysql_error());



print "<pre>";
while($row = mysql_fetch_array($result))
  {
    print "\n";
    print "{$row[0]} {$row[1]} {$row[2]}, {$row[3]}, {$row[4]}, {$row[5]}, {$row[6]} 
	{$row[7]} {$row[8]} {$row[9]} {$row[10]} {$row[11]}";
  }

while($row1 = mysql_fetch_array($result1))
  {
    print "\n";
    print "{$row1[0]} {$row1[1]} {$row1[2]}, {$row1[3]}, {$row1[4]}, {$row1[5]}, {$row1[6]} 
	{$row1[7]}";
  }
while($row2 = mysql_fetch_array($result2))
  {
    print "\n";
    print "{$row2[0]} {$row2[1]} {$row2[2]}, {$row2[3]}";
  }
while($row3 = mysql_fetch_array($result3))
  {
    print "\n";
    print "{$row3[0]} {$row3[1]} {$row3[2]} {$row3[3]}, {$row3[4]}, {$row3[5]}, {$row3[6]} 
	{$row3[7]} {$row3[8]}";
  }
while($row4 = mysql_fetch_array($result4))
  {
    print "\n";
    print "{$row4[0]} {$row4[1]} {$row4[2]} {$row4[3]}, {$row4[4]}, {$row4[5]}, {$row4[6]} 
	{$row4[7]} {$row4[8]} {$row4[9]} {$row4[10]} {$row4[11]} {$row4[12]}";
  }
while($row5 = mysql_fetch_array($result5))
  {
    print "\n";
    print "{$row5[0]} {$row5[1]} {$row5[2]}, {$row5[3]}";
  }
while($row6 = mysql_fetch_array($result6))
  {
    print "\n";
    print "{$row6[0]} {$row6[1]} {$row6[2]}, {$row6[3]}, {$row6[4]}, {$row6[5]}";
  }
while($row7 = mysql_fetch_array($result7))
  {
    print "\n";
    print "{$row7[0]} {$row7[1]} {$row7[2]} {$row7[3]}";
  }
print "</pre>"

?>

[jeggerd]

So I'm thinking, it is showing the results because on the html page, there are many submit forms that access these queries. So if someone clicks submit in a different area, it is still accessing the queries that run the if statement when it is blank.

I'm not sure how I could tell the php to only run if that specific submit button is clicked.

[Derokorian]

Give each submit button a unique value and check for that value to determine which submit button was pressed.

[jeggerd]

Thanks for your help!

by that do you mean take my html forms (for example):

<form action="finalProject1.php" method="POST">

<input type="text" name="rgroups"> <br>
<input type="submit" value="submit">
<input type="reset" value="erase">
</form>

<hr>


Query 8:

<p>
Please enter a year to see if the anyone in the contact list is a speaker or reviewer for your chosen year. (Returns yes or no)
<br/> *There will be a blank entry as the database bugged after the FK was added.*
<p>

<form action="finalProject1.php" method="POST">

<input type="text" name="check"> <br>
<input type="submit" value="submit">
<input type="reset" value="erase">
</form>

and change the part that says <input type="submit" value="submit">
to value="submit1" and the other to submit2?

I'm not quite sure how I'd tell php to look for that input value. Pardon my ignorance, i've been looking at php code all night yesterday and all day today. my brain is kinda blown lol

[jeggerd]

ok I figured out what you meant, and this is my new code. It checks for the specific submit, but for every query now, it just says the query is empty on the results page.....

if (isset($_POST['lastName'])) {
$lastName = isset($_POST['lastName']) ? $_POST['lastName'] : NULL;
$lastName= mysql_real_escape_string($lastName);

$query = "SELECT c.*, s.speaker_year FROM Contact c, Speakers s WHERE s.Contact_con_id = c.con_id 
AND con_lname =  ";
$query .= "'" . $lastName . "' ORDER BY con_lname;";
if ($lastName == ""){
	$query = "SELECT c.*, s.speaker_year FROM Contact c, Speakers s WHERE s.Contact_con_id = c.con_id ";
}
}
?>


<?php
if (isset($_POST['state'])) {
$state = $_POST['state'];
$state = mysql_real_escape_string($state);

$query1 = "Select proposal_id, proposal_status, proposal_title, proposal_description, con_fname, con_affiliation, con_city, role
from Contact c, Proposal p, Presents px
WHERE px.Speakers_Contact_con_id = c.con_id
AND px.Proposal_proposal_id = p.proposal_id
AND c.con_state = ";
$query1 .= "'" . $state . "' ORDER BY p.proposal_id;";}
?>

<?php
if (isset($_POST['review'])) {
$review = $_POST['review'];
$review = mysql_real_escape_string($review);

$query2 = "Select con_fname, con_lname con_id, proposal_id, proposal_title
from Contact c JOIN Individual_Review ir JOIN Proposal p
WHERE ir.Reviewer_Contact_con_id = c.con_id
AND ir.Proposal_proposal_id = p.proposal_id
AND con_fname = ";
$query2 .= "'" . $review . "' ORDER BY c.con_fname;";}
?>

<?php
if (isset($_POST['mail'])) {
$mail = $_POST['mail'];
$mail = mysql_real_escape_string($mail);

$query3 = "Select c.con_fname, c.con_lname, c.con_street_addy, c.con_city, c.con_state, c.con_zip,
con_affiliation, p.proposal_title, p.proposal_status
from Contact c, Proposal p, Presents px
WHERE px.Speakers_Contact_con_id = c.con_id
AND px.Proposal_proposal_id = p.proposal_id
and px.role = ";
$query3 .="'" . $mail . "' ORDER BY c.con_fname;";}
?>

<?php
if (isset($_POST['rtype'])) {
$rtype = $_POST['rtype'];
$rtype = mysql_real_escape_string($rtype);

$query4 = "Select r.reviewer_type, c.*
from Contact c, Reviewer r
WHERE r.Contact_con_id = c.con_id
AND reviewer_type = ";
$query4 .= "'" . $rtype . "' ORDER BY r.reviewer_type;";}
?>

<?php
if (isset($_POST['average'])) {
$average = $_POST['average'];
$average = mysql_real_escape_string($average);

$query5 = "SELECT c.con_fname, r.Reviewer_Contact_con_id, question_id, AVG( DISTINCT question_score)
FROM Contact c, Individual_Review r
WHERE r.Reviewer_Contact_con_id = c.con_id
AND con_fname = ";
$query5 .= "'" . $average . "' GROUP BY r.Proposal_proposal_id;";}
?>

<?php
if (isset($_POST['rgroups'])) {
$rgroups = isset($_POST['rgroups']) ? $_POST['rgroups'] : NULL;
$rgroups = mysql_real_escape_string($rgroups);


$query6 = "SELECT r.rev_groups_id, c.con_fname, c.con_lname, con_phone, rev_groups_pass, count(p.proposal_id) 
FROM Review_Groups r JOIN Proposal p on r.rev_groups_id = p.Review_Groups_rev_groups_id
JOIN Presents px on px.Proposal_proposal_id = p.proposal_id
JOIN Contact c on px.Speakers_Contact_con_id = c.con_id
JOIN Reviewer rw on rw.Review_Groups_rev_groups_id = r.rev_groups_id
WHERE rw.reviewer_type = 'local' 
AND r.rev_groups_id = '" . $rgroups . "' ";

if ($rgroups== NULL) {
    $query6 .= " AND r.rev_groups_id = '" . $rgroups . "' Group BY r.rev_groups_id;";        

}
}
?>

<?php
if (isset($_POST['check'])) {
$check = $_POST['check'];
$check = mysql_real_escape_string($check);

$query7 = "Select c.con_fname, c.con_lname, s.Contact_con_id,
IF(s.Contact_con_id IS NULL, 'NO', 'YES')
From Contact c Left Join (Select Contact_con_id FROM Speakers
WHERE speaker_year = '". $check . "') As s
ON c.con_id = s.Contact_con_id";
$query7 .= " ORDER BY c.con_fname;";}
?>


<p>
The query:
<p>
<?php
print $query;
?>
</br></br>
<?php
print $query1;
?>
</br></br>
<?php
print $query2;
?>
</br></br>
<?php
print $query3;
?>
</br></br>
<?php
print $query4;
?>
</br></br>
<?php
print $query5;
?>
</br></br>
<?php
print $query6;
?>
</br></br>
<?php
print $query7;
?>
<hr>


<p>
Result of query:
<p>

<?php
$result = mysql_query($query)or die(mysql_error());
$result1 = mysql_query($query1)or die(mysql_error());
$result2 = mysql_query($query2)or die(mysql_error());
$result3 = mysql_query($query3)or die(mysql_error());
$result4 = mysql_query($query4)or die(mysql_error());
$result5 = mysql_query($query5)or die(mysql_error());
$result6 = mysql_query($query6)or die(mysql_error());
$result7 = mysql_query($query7)or die(mysql_error());



print "<pre>";
while($row = mysql_fetch_array($result))
  {
    print "\n";
    print "{$row[0]} {$row[1]} {$row[2]}, {$row[3]}, {$row[4]}, {$row[5]}, {$row[6]} 
	{$row[7]} {$row[8]} {$row[9]} {$row[10]} {$row[11]}";
  }

while($row1 = mysql_fetch_array($result1))
  {
    print "\n";
    print "{$row1[0]} {$row1[1]} {$row1[2]}, {$row1[3]}, {$row1[4]}, {$row1[5]}, {$row1[6]} 
	{$row1[7]}";
  }
while($row2 = mysql_fetch_array($result2))
  {
    print "\n";
    print "{$row2[0]} {$row2[1]} {$row2[2]}, {$row2[3]}";
  }
while($row3 = mysql_fetch_array($result3))
  {
    print "\n";
    print "{$row3[0]} {$row3[1]} {$row3[2]} {$row3[3]}, {$row3[4]}, {$row3[5]}, {$row3[6]} 
	{$row3[7]} {$row3[8]}";
  }
while($row4 = mysql_fetch_array($result4))
  {
    print "\n";
    print "{$row4[0]} {$row4[1]} {$row4[2]} {$row4[3]}, {$row4[4]}, {$row4[5]}, {$row4[6]} 
	{$row4[7]} {$row4[8]} {$row4[9]} {$row4[10]} {$row4[11]} {$row4[12]}";
  }
while($row5 = mysql_fetch_array($result5))
  {
    print "\n";
    print "{$row5[0]} {$row5[1]} {$row5[2]}, {$row5[3]}";
  }
while($row6 = mysql_fetch_array($result6))
  {
    print "\n";
    print "{$row6[0]} {$row6[1]} {$row6[2]}, {$row6[3]}, {$row6[4]}, {$row6[5]}";
  }
while($row7 = mysql_fetch_array($result7))
  {
    print "\n";
    print "{$row7[0]} {$row7[1]} {$row7[2]} {$row7[3]}";
  }
print "</pre>"

[bradgrafelman]

You still need to re-think the logic of the entire script. Depending upon which data was POST'ed, you define some variables containing SQL queries... yet later on you simply act is if they were all defined and proceed to execute and then process a bunch of nonexistent variables.

Furthermore, there's no need for the multiple variables at all if you're only going to execute at most one of them.

[jeggerd]

hm this will take some pondering i guess haha. I wasnt sure what ya meant by needing multiple variables, but i did remove all of the isset() that i added, so its' back to showing the if null results of the $query and $query7
maybe I just need to check for those two specific queries with the isset()

[jeggerd]

I'll keep posting updates as I get it solved, in case others have a similar issue.

So a friend of mine emailed and said to try using a name field to each submit button like name="submit_query1". This is like what Drak said higher up, I just didn't quite get it. My friend also said depending on the submit button being pressed you will see $_POST['submit'] equal to the name field of the button pressed.

I'm just trying to figure out where to but the $_POST part

[johanafm]

First off, I'd recommend using some meaningful names rather than query1, query2 etc, such as lastNameSubmit, reviewSubmit etc. Then you could

if (isset($_POST['lastNameSubmit']))
{
	$query = '...';
	$result = mysql_query($query);
}
if (isset($_POST['lastNameSubmit']))
{
	$query = 'this matches your "query1"';
	$result = mysql_query($query);
	if ($result)
	{
		if (!mysql_num_rows($result))
		{
			$searchoutput = 'Nothing found';
		}
		else
		{
			$searchoutput = 'HTML code to display meaningful headers for the result';
			while ($row = mysql_fetch_assoc($result))
			{
				$searchoutput .= 'html code combined with data from each row';
			}
		}
	}
	else
	{
		error_log(sprintf('%s (%d): %s', mysql_error(), mysql_errno(), $query));
		$searchoutput = 'An error occured. Please try again';
	}
}
# The exact same thing again, except you have another query,
# and obviously some other titles for each column so the user understands what
# data they are looking at
elseif (isset($_POST['reviewSubmit']))
{
	$query = 'this matches your "query2"';
	$result = mysql_query($query);
	if ($result)
	{
		if (!mysql_num_rows($result))
		{
			$searchoutput = 'Nothing found';
		}
		else
		{
			$searchoutput = 'HTML code to display meaningful headers for the result';
			while ($row = mysql_fetch_assoc($result))
			{
				$searchoutput .= 'html code combined with data from each row';
			}
		}
	}	
	else
	{
		error_log(sprintf('%s (%d): %s', mysql_error(), mysql_errno(), $query));
		$searchoutput = 'An error occured. Please try again';
	}
}
# etc for all other forms you have
elseif (isset())
{

}

# then, finally
echo $searchoutput;

[jeggerd]

whooo thats a ton of if statements lol.
well i got it all sorted out and am using switch now. I had it explained to me in person, and that is way easier than I was doing.

Thanks all of you for your help and great ideas! I really appreciate it!

Now if i can just figure out how to add a \n to my implode(' ',$row) function lol