Populating drop down menu

[Wozzer]

Hi all,

I'm trying to create a page that allows the user to select a case from a drop down menu and in return, the page will print the relevant details.

So far - I've managed to create a drop down menu populated with "M References". I've also managed to get the page to print out all the rows in the mySQL table. However, I can't figure out how to link them together.

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
    $options .="<option>" . $caseReference['CaseReference'] . "</option>";
}

$menu="<form id='filter' name='filter' method='post' action=''>
  <label>M:</label>
    <select name='filter' id='filter'>
      " . $options . "
    </select>
</form>";

echo $menu;

?>
<tr>
    <td>
      <table border="1">
      <tr>
        <td>Case ID</td>
        <td>Exhibit ID</td>
		<td>Date / Time</td>
		<td>File Size</td>
		<td>Camera Make</td>
		<td>Camera Model</td>
		<td>Height</td>
		<td>Width</td>
		<td>Longitude</td>
		<td>Latitude</td>
		<td>Map</td>
      </tr>
<?php
 while($caseReference = mysql_fetch_array($caseResult)) {
  echo '<tr><td>';
  echo $caseReference['CaseReference'];
  echo '</td><td>';
  echo $caseReference['ExhibitReference'];
  echo '</td><td>';
  echo $caseReference['DateTimeCreated'];
  echo '</td><td>';
  echo $caseReference['FileSize'];
  echo '</td><td>';
  echo $caseReference['CameraMake'];
  echo '</td><td>';
  echo $caseReference['CameraModel'];
  echo '</td><td>';
  echo $caseReference['Height'];
  echo '</td><td>';
  echo $caseReference['Width'];
  echo '</td><td>';
  echo $caseReference['Longitude'];
  echo '</td><td>';
  echo $caseReference['Latitude'];
  echo '</td><td>';
  echo '<a href="https://maps.google.co.uk/maps?q='.$caseReference['Longitude'].',-2.102723">View on map</a>';
  echo '</td></tr>';
} 

 
?>

I know im missing a piece of code, I'm just not sure what to add. Could someone point me in the right direction?

Thanks

 

[Ross211]

OK, I think I know what you're trying to do. Whenever a value is selected in the drop down menu, you want the table below to update based upon the selection? You would use JavaScript/jQuery and PHP's GET Method to perform this.

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
    $options .="<option>" . $caseReference['CaseReference'] . "</option>";
}

$menu="
    <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
      " . $options . "
    </select>";

echo $menu;

?>

Now what you need to do is modify your options array to something like this, putting the primary key to your case reference data so it can be taken in using the GET Method when the select listbox is changed and the page is refreshed to update the table below. -

<option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?>"><?php echo $caseReference['CaseReference']; ?></option>

Now you need to use PHP's GET Method to pull in the primary key for the case reference data so that you can output the data to your table based upon what the user selected in the select listbox.

It would look something like this -

if (!empty($_GET['caseReference']) && is_numeric($_GET['caseReference'])){
$caseReference = (int) $_GET['caseReference'];
} else {
header('Location: /caseReference.php?caseReference=empty');
}

Let me note that this is not how I would write this particular functionality, but I wanted to stay as close to how you are writing this so you could get the idea.

Why are you putting static HTML into PHP variables? This seems like a head ache to me and it wastes resources IMO.

I suggest avoiding echo'ing HTML via PHP when it is possible. It is a PITA to read, and this way your business logic is seperated from your presentation layer.

Example -

	<div style="text-align:left;">
	<?php if ($inboxcount == 1 ){ ?>
	<b>Inbox</b> contains <?php echo $inboxcount; ?> message.<br />
	You have <?php echo $pmTotal; ?> messages stored, of a total 100 allowed.<br />
	<?php } else { ?>
	<b>Inbox</b> contains <?php echo $inboxcount; ?> messages.<br />
	You have <?php echo $pmTotal; ?> messages stored, of a total 100 allowed.<br />
	<?php } ?>
	<div id="progressbar"></div><br />
	<b>Jump to a Folder:</b>
	<select style="font-size:1.05em;" onchange="if (this.value) window.location.href=this.value">
		<option value="<?php echo $_SERVER['PHP_SELF']; ?>?p=inbox" selected="selected">Inbox (<?php echo $inboxcount; ?>)</option>
		<option value="<?php echo $_SERVER['PHP_SELF']; ?>?p=sent">Sent (<?php echo $sentcount; ?>)</option>
	</select>
	</div>

Best of luck

 

[Wozzer]

Thanks for the reply Ross!

Seems a lot more complicated than I first imagined.

Looks like Im going to have to read up a bit more on PHP!

As for mixing PHP with HTML - I wasn't aware this was a bad way of programming. I've only just started using PHP. Thanks for the heads up.

 

[Wozzer]

The only part i'm not 100% following is:

if (!empty($_GET['caseReference']) && is_numeric($_GET['caseReference'])){
$caseReferencePK = (int) $_GET['caseReferencePK'];
} else {
header('Location: /caseReference.php?caseReference=empty');
}

I'm not sure where to implement this? My primary key in my Database is called PID, so i'm assuming that would replace caseReference PK.

 

[Ross211]

You're welcome, no problem. You know the concept of what you're trying to do you just needed a point in the right direction at how to accomplish it.

I forgot to mention, this could also be done using AJAX/jQuery without the PHP GET Method. If you're familiar with using AJAX you could use jQuery's change function on a select listbox to get the primary key and then run a SQL query and return the result set live on the same page without a refresh.

 

[Wozzer]

You're welcome, no problem. You know the concept of what you're trying to do you just needed a point in the right direction at how to accomplish it.

I forgot to mention, this could also be done using AJAX/jQuery without the PHP GET Method. If you're familiar with using AJAX you could use jQuery's change function on a select listbox to get the primary key and then run a SQL query and return the result set live on the same page without a refresh.

My web dev skills go as far as coding a basic template with css. That's about it

 

[Disparia]

Thanks for the reply Ross!

Seems a lot more complicated than I first imagined.

Looks like Im going to have to read up a bit more on PHP!

Also to read: MySQLi or PDO (my choice) for database access.

Zend has been trying to ween people off the MySQL functions for awhile and as of 5.3.0, has started depreciating them. It's not really a problem if you just dabble here or there, but if you want to write code that will survive version upgrades (after depreciation is removal), might want to learn one of the above.

 

[Ross211]

The only part i'm not 100% following is:

if (!empty($_GET['caseReference']) && is_numeric($_GET['caseReference'])){
$caseReferencePK = (int) $_GET['caseReferencePK'];
} else {
header('Location: /caseReference.php?caseReference=empty');
}

I'm not sure where to implement this? My primary key in my Database is called PID, so i'm assuming that would replace caseReference PK

Ah, yes... this would be in the header of your PHP script that also shows the select listbox and table. Basically, run this in the header when you get your PID using the GET Method when the page refreshes after the listbox selection. Then build your table by running a SQL statement after getting the PK based upon the selection from the listbox.

Like this below -

if (!empty($_GET['caseReference']) && is_numeric($_GET['caseReference'])){
$caseReferencePK = (int) $_GET['caseReference'];
} else {
header('Location: /caseReference.php?caseReference=empty');
}

$caseReference = mysql_query("SELECT * FROM `mcases` WHERE `PID` = '$caseReferencePK' ");

 

[Wozzer]

I'll give it ago and update with results. Cheers all.

 

[Ross211]

I also forgot to mention... When the page is refreshed after a selection is made from the select listbox, you will want to keep the last selection selected.

Here is one way to accomplish that -

<option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?; ?>" <?php if (!empty($_GET['caseReference']) && $_GET['caseReference'] == $caseReference['CaseReference']) { echo 'selected="selected"'; } ?>> <?php echo $caseReference['CaseReference']; ?></option>

Again, best of luck

 

[Wozzer]

I must be completely missing the point. I've re-read your post four times and tried to make sense of it. I'm sure this is what your asking, although the link is going straight to "/caseReference.php?caseReference=empty"

PHP Header:

		<?php
		require('mysqlConnect.php');
		$order = "SELECT * FROM `mcases`";
		$caseResult = mysql_query($order);
		
		if (!empty($_GET['caseReference']) && is_numeric($_GET['caseReference'])){
		$caseReferencePK = (int) $_GET['caseReference'];
		} else {
		header('Location: /caseReference.php?caseReference=empty');
		}
		
		$caseReference = mysql_query("SELECT * FROM `mcases` WHERE `PID` = '$caseReferencePK' ");
		?>

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
    $options .="<option>" . $caseReference['CaseReference'] . "</option>";
}

$menu="
    <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
      " . $options . "
    </select>";
	echo $menu;
?>

<option value="
<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$PID ?>">
<?php echo $caseReference['CaseReference']; ?>
</option>

Then I have the table set out below. Sorry to be a pain!

 

[Ross211]

/caseReference.php is what I mean to say your current PHP script. If there is no GET variable set you want it to go to the name of your current PHP script file that has the select option listbox and table output. I put caseReference.php just for an example. Put the name of your PHP script file in place of /caseReference.php

    <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
<?php
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) { ?>
    <option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?>"><?php echo $caseReference['CaseReference']; ?></option>
<?php } ?>
    </select>

I changed some of your code for the better. Try this above. Your example above mine is only outputting one caseReference primary key in the select listbox. You want each primary key for each caseReference in all of the entries of the select listbox. The above example can accomplish that.

/edit

I also spot another issue. You need something like this instead of what you have above -

<?php
require('mysqlConnect.php');
$order = "SELECT * FROM `mcases`";
$caseResult = mysql_query($order);

if (!empty($_GET['caseReference']) && is_numeric($_GET['caseReference'])){
$caseReferencePK = (int) $_GET['caseReference'];
$caseReference = mysql_query("SELECT * FROM `mcases` WHERE `PID` = '$caseReferencePK' ");
} else {
header('Location: /caseReference.php?caseReference=empty');
}

?>

I'm sorry but there I realize that this is very hard for me to give better advice without knowing the structure of your database.

 

[Wozzer]

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
    $options .="<option>" . $caseReference['CaseReference'] . "</option>";
}

$menu="
 <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
<?php
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) { ?>
    <option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?>"><?php echo $caseReference['CaseReference']; ?></option>
<?php } ?>
    </select>";
	echo $menu;
?>

<option value="
<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$PID ?>">
<?php echo $caseReference['CaseReference']; ?>
</option>

I seem to have a slight error in my code. I think its to do with closing tags - Just been looking for the last 20 minutes but can't find it.

 

[Ross211]

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
$options .="<option>" . $caseReference['CaseReference'] . "</option>";
}

$menu="
<label>M:</label>
<select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
<?php

^^^ You're starting another PHP block before ending

 

[GSquadron]

How weird, i just did today one with numbers
Anyway, you can fetch the results from a database and use a foreach inside the select

 

[Wozzer]

I did try and close it, but it wouldn't seem to allow it. (By allow it I mean, the colour coded coding in notepad++ stayed grey).

I think it might be something to do with the variable $menu. Is that in the correct place?

 

[Ross211]

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
    $options .="<option>" . $caseReference['CaseReference'] . "</option>";
}

$menu="
 <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
<?php
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) { ?>
    <option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?>"><?php echo $caseReference['CaseReference']; ?></option>
<?php } ?>
    </select>";
	echo $menu;
?>

You have another problem too, your $menu variable can't start in one PHP block and then continue into another...

Please get out of the habit of echo'ing HTML via PHP now. It will help you a lot, trust me.

    <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
<?php
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) { ?>
    <option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?>"><?php echo $caseReference['CaseReference']; ?></option>
<?php } ?>
    </select>

 

[Wozzer]

Looks as If I have a lot of reading to with PHP!!

Thanks for your help Ross. I think it's probably better if I go away and research PHP a bit more instead of pestering you with questions. As you've said - it's hard to give advice when you don't know the database structure

 

[GSquadron]

<?php
$options = '';
$filter=mysql_query("SELECT * FROM `mcases` WHERE `CaseReference` > 0");
while($caseReference = mysql_fetch_array($caseResult)) {
    $options .="<option>" . $caseReference['CaseReference'] . "</option>";
}
?>
$menu="
 <label>M:</label>
    <select id='filter' name='filter' onchange='if (this.value) window.location.href=this.value'>
<?php
$filter=mysql_query("SELECT * FROM mcases WHERE CaseReference > 0");
while($caseReference = mysql_fetch_array($caseResult)) { ?>
    <option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$caseReferencePKGoesHere ?>"><?php echo $caseReference['CaseReference']; ?></option>
<?php } ?>
    </select><?php
	echo $menu;
?>

<option value="
<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$PID ?>">
<?php echo $caseReference['CaseReference']; ?>
</option>

Try this

 

[Wozzer]

Thanks Aleksander Dishnica, Although the variable $menu hasn't been picked up?

DB: http://www.techpowerup.org/uploaded.php?file=120814/db.jpg

 

[GSquadron]

Wow man your code seems very weird, never seen such weird php programming.
You have copied the code somewhere i guess because making code like this seems non-logic

Tell me what you want to do.

EDIT:Copy the code again above, i edited it.

 

[Wozzer]

Tell me what you want to do.

Whenever a value is selected in the drop down menu, the table below updates based upon the selection.

Wow man your code seems very weird, never seen such weird php programming.

I only started learning PHP two days ago. Probably explains a lot.

 

[GSquadron]

Ok here is the code, but i did a mistake on purpose so you can learn a lot more from it.
It will work though:

<select name="name">
<?php
$connect = mysql_connect('localhost', 'root', '123456');
$db = mysql_select_db("iphone");
$query = mysql_query("SELECT * FROM cell");
$names = mysql_fetch_array($query);
foreach ($names as $name):
?>
<option value="<?php echo $name; ?>">
<?php echo $name; ?>
</option>
<?php endforeach; ?>
</select>

<?php
	switch($name){
		case 'samsung':
			echo 'Hello world!';
			break;
	}
?>

Also, change the passwords name and other things to your choices
It sounds very weird to start with such things, as it would not mean any learning at all.
The difficulty doesn't sound for beginners, especially logic.
I did the mysql on purpose, not mysqli because that would complicate things a bit more i guess.
It surprises me your know how to create the database since day 2
Nice work!

 

[Wozzer]

Ok here is the code, but i did a mistake on purpose so you can learn a lot more from it.
It will work though:

Thanks Aleksander, thats probably the best way of learning imo.

Also, change the passwords name and other things to your choices
It sounds very weird to start with such things, as it would not mean any learning at all.
The difficulty doesn't sound for beginners, especially logic.
I did the mysql on purpose, not mysqli because that would complicate things a bit more i guess.
It surprises me your know how to create the database since day 2
Nice work!

I've dabbled with PHP on and off for years, but it was only till recently that I sat down and started to learn it.

Thanks for all your help. I'll dabble with this code and post results.

 

[GSquadron]

U'r welcome