homepage Welcome to WebmasterWorld Guest from 23.21.9.44
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql php selection
display a selection based on the user input in a table
brancook




msg:3482194
 6:52 pm on Oct 19, 2007 (gmt 0)

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




msg:3482253
 8:09 pm on Oct 19, 2007 (gmt 0)

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

brancook




msg:3482284
 8:38 pm on Oct 19, 2007 (gmt 0)

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




msg:3482290
 8:48 pm on Oct 19, 2007 (gmt 0)

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




msg:3482415
 12:13 am on Oct 20, 2007 (gmt 0)

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

brancook




msg:3482690
 1:35 pm on Oct 20, 2007 (gmt 0)

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




msg:3484194
 5:43 pm on Oct 22, 2007 (gmt 0)

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




msg:3484206
 5:53 pm on Oct 22, 2007 (gmt 0)

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

eelixduppy




msg:3484211
 5:57 pm on Oct 22, 2007 (gmt 0)

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




msg:3484215
 6:06 pm on Oct 22, 2007 (gmt 0)

Here is what I get:

Parse error: syntax error, unexpected T_STRING

on the line of the $sql statement

eelixduppy




msg:3484253
 6:45 pm on Oct 22, 2007 (gmt 0)

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




msg:3484260
 6:57 pm on Oct 22, 2007 (gmt 0)

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




msg:3484270
 7:08 pm on Oct 22, 2007 (gmt 0)

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




msg:3484276
 7:17 pm on Oct 22, 2007 (gmt 0)

I still get the same result....

man this is driving me crazy

brancook




msg:3485638
 12:06 am on Oct 24, 2007 (gmt 0)

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




msg:3486115
 11:59 am on Oct 24, 2007 (gmt 0)

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>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved