Syntax for select statement

lukeurtnowski

Im trying to write a select statement which instead on having CatID = 3 (constant) in the where clause, im trying to have it compare CatID to a variable which would be choosen after making a selection in a combo box? Here's the select statement

select SName, CatID from Sub_Category where CatID=3 order by SName
Any help would be appreciated. thanks

pablogosse

Something like this should work:

$query = "select SName, CatID from Sub_Category ";
$query .= "from Sub_Category ";
$query .= "where CatID = ".$_POST['YOUR_VAR_HERE']." ";
$query .= "order by SName";

Cheers,
Pablo

lukeurtnowski

I tried altering the select statement, put now I got a parse error

<?php
			$query1 = mysql_query("select SName, CatID from 		
			Sub_Category where CatID=.$result['CatID'] order by SName") or die(mysql_error());

		while ($result1 = mysql_fetch_array($query1)) { 
		printf('<option value="'.$result1['CatID'] . '">'.$result1['SName'] . '</option>'); 
		} 

		echo "</SELECT>";
	?>

pablogosse

Hi there. The fix for the error is bolded in blue below:

<?php
$query1 = mysql_query("select SName, CatID from         

Sub_Category where CatID=[COLOR=blue][b]"[/b][/COLOR].$result['CatID'][COLOR=blue][b]."[/b][/COLOR] order by SName") or die(mysql_error());

while ($result1 = mysql_fetch_array($query1)) { 
  printf('<option value="'.$result1['CatID'] . '">'.$result1['SName'] . '</option>'); 
} 

echo "</SELECT>";
?>

In order to insert $result['CatId'] into the query, you have to concatenate it as you do in the printf within the while loop.

Cheers,
Pablo

lukeurtnowski

tthankyouthankyoutthankyou

lukeurtnowski

for some reason, it doesn't like those "

pablogosse

Huh? Not following...

Cheers,
Pablo

lukeurtnowski

Im trying to get the Primary key (CatID) out of a combo box, so the combo box that is then triggered to appear would use that variable for a table lookup.. thanks, want to see my code??

lukeurtnowski

the variable (PK or the other table) is in a while loop and is $result[CatID], can I call the variable outside the while loop

pablogosse

Sure. Send the code as an attachment and I'll take a look at it.

Cheers,
Pablo

lukeurtnowski

thanks for taking a look!

<html>
<head>
<script type="text/javascript">

function showIt() {
document.frm1.SID1.style.visibility="visible"; //set to visible
document.frm1.SID1.value=<?php echo "luke"; ?>;
}
function showItAgain() {
document.frm2.SID2.style.visibility="visible"; //set to visible
}
</script>
<?php
$hostname = "10.0.0.127";
$username = "luke";
$password = "blues";
$database = "luke";
$connection = mysql_connect($hostname , $username , $password)
or die("cannot make connection");
$db = mysql_selectdb($database , $connection)
or die("cannot find database");
?>
	<title>Untitled</title>
	<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<TABLE BORDER=0 cellpadding=5 WIDTH=100% align="center" >
<TR><TH COLSPAN=2><font size=6>Why pay a fee to find an expert witness?</FONT></TH>
</TR><TR><TH COLSPAN=2>
<font size=5 color=blue>Consolidated Consultants provides free expert witness referrals.</FONT></TH>
</TR><tr><td>
	<table border=1 cellpadding=5 BORDERCOLORDARK="BLack"  rules=none BACKGROUND="bgh3.gif" align=left>
	<tr BGCOLOR="#3399ff"><th><font color=white size=+2>Search for an expert witness</FONT></TH></tr>	<tr><th>
	<form method="get" action="search.php">
	<INPUT TYPE="Text" NAME="q" ALIGN="LEFT" SIZE="20">&nbsp;&nbsp;&nbsp;
	<INPUT TYPE="Submit" VALUE="Find">
	</th></tr>
	</FORM>
	<tr BGCOLOR="#3399ff"><th><font color=white size=+2>Browse by category</FONT></TH></tr>
	<tr><th >
	<font size=+2><tt>(Medical)</tt></font></th></tr><tr><td>
		<form name=frm1>
			<?php 
			$query = mysql_query("select CatName, CatID from Category where  					FieldID=2 order by CatName") or die(mysql_error()); 
			?> 
<SELECT Name="CatID" SIZE="1" onchange="showIt()">  

			<?php 
			while ($result = mysql_fetch_array($query)) { 
			printf('<option value="'.$result['CatID'] . '">'.$result['CatName'] . '</option>'); 
			$r1 = $result['CatID'];
			echo $r1;} 
			echo "</SELECT>";


		?>
<select name="SID1" style="visibility: hidden" onchange="document.location.href='Subcategory.php?SID=<?php echo 7; ?>'">


		<?php
		$query1 = mysql_query("select SName, CatID from         
		Sub_Category where CatID=4 order by SName") or die(mysql_error());

		while ($result1 = mysql_fetch_array($query1)) { 
		  printf('<option value="'.$result1['CatID'] . '">'.$result1['SName'] . '</option>'); 
		} 

		echo "</SELECT>";
		?>
</form>
	</d></tr>
	<tr><th>
	<font size=+2><tt>(Technical)</tt></font></th></tr><tr><td>
		<form name=frm2>
			<?php 
			$query = mysql_query("select CatName, CatID from Category where
			 FieldID=1 order by CatName") or die(mysql_error()); 
			?> 
<SELECT Name="CatID" SIZE="1" onchange="showItAgain()"> 
			<?php 
			while ($result = mysql_fetch_array($query)) { 
			printf('<option value="'.$result['CatID'] . '">'.$result['CatName'] . '</option>'); 
			} 
			echo "</SELECT>";
			$query1 = mysql_query("select SName, CatID from Sub_Category where  				CatID=3 order by SName") or die(mysql_error());
			?>
<select name=SID2 style="visibility: hidden" onchange="document.location.href='Subcategory.php?SID=3'"> 
			<?php
			while ($result1 = mysql_fetch_array($query1)) { 
			printf('<option value="'.$result1['CatID'] . '">'.$result1['SName'] . '</option>'); 
			} 

		echo "</SELECT>";
	?>
</form>
	</select>
	</Td></TR>
	<tr BGCOLOR="#3399ff"><th><font color=white size=+2>View CVs'</FONT></Th></tr>
	<tr><th><TR><Td><a href="fulllist.php">All Expert Witnesses</A></Td></TR>
	<tr><td><a href="medcvlist.php">Medical Expert Witnesses</A></td></tr>
	<tr><td><a href="teccvlist.php">Technical Expert Witnesses</A></td>	
	<TR BGCOLOR="white"><Td><a href="request.shtml">
	<font size=+2>Request an Expert</font></A></Td></TR>
	</tr><tr><th><hr color=blue></TH></TR>
		<tr><td><a href="become.shtml">Become an Expert Witness</a></Td></tr>
	<tr><td><a href="subscribe.shtml">Subscribe to our periodic newsletter</a></Td></tr>
	<tr><td><a href="contactus.shtml">Contact us</A></Td></tr>
	</table>
	<th ALIGN="LEFT"><IMG SRC="logo.gif" WIDTH=290 HEIGHT=299 ALT="" BORDER="0"></TH>
	<th>
	</TR>
</table>
</body>
</html>

pablogosse

Can you post any errors this is throwing, or give me a url where I can take a look at it in action?

Cheers,
Pablo

lukeurtnowski

you can see the page in action at
www.lukesplace.com/CCC/javascript.php

(make sure your browser is javascript enabled)

lukeurtnowski

On the medical and technical categories, I want the second combo box to change depending on the first. And once the selection is made on the 2nd box, you will be sent too Subcategory.php with SName passed along with it.
Thanks so much.