mysql php selection

display a selection based on the user input in a table

[brancook]

Hi all, I'm stuck and need some advice. What I'm trying to do is display some data in a table based on what a user chooses from a drop down menu. The choose the item from the drop down menu, click submit and the data form that item is displayed in the table. I need to some how place the $targetrma variable inside the SELECT statement in the WHERE area but when I remove "dropbox.ref_status_id=status.id" and insert "rma_number = '$targetrma'" I get the record repeated for each record that is in the status table. So I think I need to somehow combine the two statement in the WHERE area. Here is my code:

<div id='column2'>
<h1>View the Status of an RMA</h1>
<br />
<form action="<?php echo $_SERVER['PHP_SELF'];?>"
method="get" enctype="multipart/form-data">
<?php
print '<select name="rma">'."\n";
print '<option value="none">Select RMA number</option>'."\n";
$rma_number = mysql_query('SELECT * FROM rma');while ($rma = mysql_fetch_array($rma_number, MYSQL_ASSOC)){
print'<option value="'.$rma['id'].'"';
if($record_data['ref_rma_id']==$rma['id']) print " selected=\"selected\" ";
print '>'.$rma['rma_number']."</option>\n";

}
print '</select>'."<br />\n";
?>
<br />
<input type="submit" value="Submit" />
</form>

<?php
$targetrma = "RMA-test-one";
$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id";
$filelist = @mysql_query($sql);
if (!$filelist) {
exit('Database error: ' . mysql_error());
}
?>
<br />
<br />

<p>The following files are stored in the database:</p>

<div align="center">
<table id="rmaview">
<tr>
<th>RMA Number</th>
<th>RMA Status</th>
<th>Filename</th>
<th>Date</th>
<th>Note</th>
<th>Unit Type</th>
</tr>
<?php
if (mysql_num_rows($filelist) > 0) {
while ($f = mysql_fetch_array($filelist)) {
?>
<tr>
<td>
<?php echo $f['rma_number'];?>
</td>
<td>
<?php echo $f['status_name'];?>
</td>
<td>
<a href="<?php echo $_SERVER['PHP_SELF'];?>?action=view&id=<?php echo $f['id'];?>"><?php echo $f['filename'];?></a>
</td>
<td>
<?php echo $f['date'];?>
</td>
<td>
<?php echo $f['description'];?>
</td>
<td>
<?php echo $f['category_name'];?>
</td>
</tr>

<?php
}
} else {
?>
<tr><td>No Files!</td></tr>
<?php
}
?>
</table>
</div>

[brancook]

I currently have the record plugged into $targetrma, just to make sure the sql query worked.

[brancook]

Ok this is where I'm at now, here is my sql select statement:

$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id AND rma.rma_number = '$targetrma'";

Now when I insert the record that I have currently inserted in the database which looks like this:

$targetrma = "RMA-test-one";

The table displays the record properly.

But what I need is $targetrma to display what the user selects from the drop down menu from the form. Which is listed in the original post.

[mooger35]

You are using the get method in your form so if I read it right you should have:

$targetrma = $_GET['rma'];

instead of: $targetrma = "RMA-test-one";

[brancook]

I plugged in the $_GET['rma']; and it still doesn't seem to work. Anything that I may be missing?

[brancook]

My sql SELECT statement needed to be tweaked so I fixed that to:

$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id AND dropbox.ref_rma_id=rma.id AND rma.rma_number = '$targetrma'";

But I still can't get that $targetrma to use what the user selects.

[PHP_Chimp]

You have

$filelist = @mysql_query($sql);

Have you tried un-muting the errors for that statement? As you have error handling for no files built in to your script.
Un-muting that error may give you another clue as to why it is not working.

[brancook]

Thanks for responding, what do you mean by un-muting?

[eelixduppy]

he means to removed the error suppression operator, @. Try the following and tell us what you get:

$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id AND dropbox.ref_rma_id=rma.id AND rma.rma_number = '"mysql_real_escape_string($targetrma)."'";
$filelist = mysql_query($sql) or die(mysql_error());

[brancook]

Here is what I get:

Parse error: syntax error, unexpected T_STRING

on the line of the $sql statement

[eelixduppy]

error on my part, sorry about that:

$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id AND dropbox.ref_rma_id=rma.id AND rma.rma_number = '".mysql_real_escape_string($targetrma)."'"; 

[brancook]

no problem, I appreciate your help...

I uploaded that chunk of code but I don't get any errors... The form just pretty much ignors me when I click submit, although the id is getting passed to the url

http://www.example.com/page.php?rma=79

[edited by: eelixduppy at 7:08 pm (utc) on Oct. 22, 2007]

[eelixduppy]

So you really want something like this, then:

$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id AND dropbox.ref_rma_id=rma.id AND rma.rma_number = '".mysql_real_escape_string([b]$_GET['rma'][/b])."'"; 

[brancook]

I still get the same result....

man this is driving me crazy

[brancook]

I think the problem may lie here:

if($record_data['ref_rma_id']==$rma['id']) print " selected=\"selected\" ";

I don't have $record_data defined anywhere.

This is a merger of two different blocks of code and somehow slipped in. How can I modify the if statement within that option value? I don't think I need it.

[brancook]

Ok, so I've updated my drop down menu. I'm still getting the same result. I can't seem to get $targetrma to grab the selection from the drop down menu to display the information for that selection. Here is my code to this point:

<div id='column2'>
<h1>View the Status of an RMA</h1>
<br />
<form action="<?php echo $_SERVER['PHP_SELF'];?>"
method="GET" enctype="multipart/form-data">
<?php
echo'<select name="rma">';

$res=mysql_query("select * from rma");
if(mysql_num_rows($res)==0) echo "there is no data in table..";
else
for($i=0;$i<mysql_num_rows($res);$i++) {
$row=mysql_fetch_assoc($res);
echo"<option>$row[rma_number]</option>";
}
echo'</select>';
?>
<br />
<input type="submit" value="Submit" />
</form>

<?php
$targetrma = $_POST['rma'];
$sql = "SELECT rma.rma_number, status.status_name, filename, date, description FROM dropbox, rma, status WHERE dropbox.ref_status_id=status.id AND dropbox.ref_rma_id=rma.id AND rma.rma_number = '$targetrma'";
$filelist = @mysql_query($sql);
if (!$filelist) {
exit('Database error: ' . mysql_error());
}
?>
<br />
<br />

<p>The following files are stored in the database:</p>

<div align="center">
<table id="rmaview">
<tr>
<th>RMA Number</th>
<th>RMA Status</th>
<th>Filename</th>
<th>Date</th>
<th>Note</th>
<th>Unit Type</th>
</tr>
<?php
if (mysql_num_rows($filelist) > 0) {
while ($f = mysql_fetch_array($filelist)) {
?>
<tr>
<td>
<?php echo $f['rma_number'];?>
</td>
<td>
<?php echo $f['status_name'];?>
</td>
<td>
<a href="<?php echo $_SERVER['PHP_SELF'];?>?action=view&id=<?php echo $f['id'];?>"><?php echo $f['filename'];?></a>
</td>
<td>
<?php echo $f['date'];?>
</td>
<td>
<?php echo $f['description'];?>
</td>
<td>
<?php echo $f['category_name'];?>
</td>
</tr>

<?php
}
} else {
?>
<tr><td>No Files!</td></tr>
<?php
}
?>
</table>
</div>

</div><!-- close column2 -->
</div> <!-- close contentarea2 -->
</div>
<!-- close contentarea1 -->
</body>
</html>