[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Advice required on best way to present PHP MySQL records for user to select

Hi all
I have a PHP website created in Dream-weaver.  The site uses a MySQL database to record data entered by the site administrator.  The site shows cars in stock by date acquired.  The user can choose to select a list of car manufacturers taken from the stock table.  I am using a Select Distinct query to do this and all is fine, however.  Now that the list of stock manufacturers is getting quite long and is populating a table that is displayed to the user, it doesn't look quite right.  The displayed manufacturers are a link to another form that displays all stock cars of that manufacturer.  It all works fine but just doesn't look right.  I was thinking of ways to make the Manufacturers list a bit more presentable and have the following ideas, but I have no idea how to get them to work:-

Idea One: Is there a way to display the manufacturer’s record in a table that returns the manufacturers list in columns and rows?  I was thinking is there a way to may say 10 records populate column 1 then the next 10 populate column 2 and so on (the maximum number of records that can be returned by the Select Distinct query is 49, but we do not envisage this ever being the case)?  I have tried to get Dream-weaver to use different queries an return certain records but is all falls apart.

Idea Two:  Use a form with a dropdown list.  The dropdown could be dynamically populated from the Select distinct manufacturers but then I need it to get the records from the database for all cars matching that manufacturer.  I have messed around with this and seem to get stuck with only being able to retrieve the first manufacturer on the list regardless of which one was selected in the drop down!

Any suggestions on how to do this or advice on a different way of presenting the manufacturers list will be greatly appreciated.
0
rosecomputers
Asked:
rosecomputers
  • 4
  • 4
1 Solution
 
Jason C. LevineNo oneCommented:
Hi rosecomputers,

Idea One isn't going to work all that well due to the way tables are constructed.  Because you have to define data horizontally (<tr><td></td><td></td></tr>) before you can define it vertically, placing all the records for one manufacturer in each column would be pretty tough.  You may want to repost this part of the question in the PHP and Databases TA and see if the PHP gurus have a different take on it.

Idea Two should work just fine and you probably don't even need to hand code any PHP.  The initial form contains a dynamic dropdown populated from your Select Distinct query.  On submission, the value from the form selects records in a new recordset with the dropdown value as the filter.

Post the code for Idea Two and let's see why that didn't work for you...
0
 
rosecomputersAuthor Commented:
Hi Thanks for the prompt reply.  The code that I have tried is basically:-

<?php
//Connection statement
require_once('Connections/jonestestconnect.php');

// begin Recordset
$query_Recordset1 = "SELECT Manufacturer FROM tbcars";
$Recordset1 = $jonestestconnect->SelectLimit($query_Recordset1) or die($jonestestconnect->ErrorMsg());
$totalRows_Recordset1 = $Recordset1->RecordCount();
// end Recordset

?>

<form action="man_select_list.php?Manufacturer=<?php echo $Recordset1->Fields('Manufacturer'); ?>" method="post" name="JonesesParts" id="JonesesParts" >
                                       
                                <td><div align="right">Select Manufacturer from the list </div></td>
                                <td align="left" valign="middle"><select name="select">
                                  <?php
  while(!$Recordset1->EOF){
?>
                                  <option value="<?php echo $Recordset1->Fields('Manufacturer')?>"><?php echo $Recordset1->Fields('Manufacturer')?></option>
                                  <?php
    $Recordset1->MoveNext();
  }
  $Recordset1->MoveFirst();
?>
                                </select                                
   <input type="submit" name="Submit" value="Submit" />

When I 'post' the the form it seems to pass the information correctly as i see the browser go to the web address man_select_list.php?Manufacturer= but only the first manufacturer appears here regardless of what is selected and even then the record set in the target page contains no records. if I type man_select_list.php?Manufacturer=AUDI into the browser the records return correctly! Please tell me where it is going wrong.

Regards

Mike
0
 
Jason C. LevineNo oneCommented:
This won't work:

<form action="man_select_list.php?Manufacturer=<?php echo $Recordset1->Fields('Manufacturer'); ?>" method="post" name="JonesesParts" id="JonesesParts" >

All that will appear is:

man_select_list.php?Manufacturer=

Because PHP processes before the page is served to the user, that echo command in the action resolves to blank.  What you need to do is base the recordset in man_select_list.php to use Form Value = Manufacturer as the filter when defining the recordset on the page.

Submitted form values appear in the $_POST array, not the query string.
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
rosecomputersAuthor Commented:
Thanks for the comments,
I will now display my complete ignorance for how forms work and ask What do I do to put it right?
what is the correct syntax?

Thanks in advance
0
 
Jason C. LevineNo oneCommented:
Just do:

<form action="man_select_list.php" method="post" name="JonesesParts" id="JonesesParts">

On man_select_list.php, you have a recordset already defined.  Double-click the recordset behavior in the Behaviors panel to get to the recordset definition window.

In that window, you have the Filter set to URL Parameter = Manufacturer.  Change it from URL Parameter to Form Value.  This page will now pull up records that match the Manufacturer selected on the first page.
0
 
rosecomputersAuthor Commented:
Thanks for the help.  Sorry for my ignorance :-(
0
 
rosecomputersAuthor Commented:
Jason or anyone
While I am on this topic, would it be possible to select the value from the first dropdown and then pass the result to another dropdown in the same form or another form on the same page?  I was just thinking if the user could select the manufacturer from the first drop down and a second drop down is then populated with the car types of that manufacturer.  When the user selects the car type (with an index number) that then opens another page with all the cars details selected by the unique ID?
0
 
Jason C. LevineNo oneCommented:
>> Thanks for the help.  Sorry for my ignorance :-(

Don't worry about it!  We all had to start somewhere...

>> While I am on this topic, would it be possible to select the value from the first dropdown and then pass the result to another dropdown in the same form or another form on the same page? <<

The short answer is yes, this is possible.  However, it can get a little complicated to do.  You can use javascript to set the value of the second drop down, which will require some javascript know-how and some hand-coding of the PHP.  Alternately, you could have the first form submit to the same page (i.e.,  the page with the first form is firstformpage.php and the form action posts to firstformpage.php) and have that second dropdown recordset be dependent on the value submitted from the first form.  I would also use the Show Region if Recordset Is Not Empty to hide the second drop down box until the first form is submitted.

Now that I've typed that out, that would be the way to go.  It's easy and you only need the built in behaviors...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now