DB driven menu. How to just show "A" beginning airfields

VinceGledhill · July 31, 2012

Hi People.

I am building a database driven menu for my site and with help from some kinds souls on here now have it working of a sorts.

http://www.airfieldcards.com/vg_temp/index.php

However, my list is shown in no particular order, and would like to order it like so.

Alpha

(all DB items starting with "a" listed alphabetically

Bravo

(all DB items starting with "b" listed alphabetically

Here is my code so far....

 <?php include("db_connect.php");
$users_sql = "SELECT * FROM users";
$users_query = mysql_query($users_sql) or die (mysql_error());
$rsUsers = mysql_fetch_assoc ($users_query);

?>

<h2>News</h2>
    <div class="scroll">
           
      <h3>31st July 2012</h3>
      <p class="news">New HTML site created which has made the whole job of adding new airfields that much easier.  As soon as you submit an airfield it becomes visible.
      
      </div>
<li><a href="add.php">Add New Airfield</a></li>
<h2>Airfields</h2>
<ul>
<li><a href="alpha.php">Alpha</a></li>

<ul>
<?php do {
?>
<li><a href=""><?php echo $rsUsers ['username']; ?>	</a></li>
<?php	
} while ($rsUsers = mysql_fetch_assoc ($users_query));
?>
</ul>

<li><a href="bravo.php">Bravo</a></li>
<li><a href="charle.php">Charlie</a></li>
<li><a href="delta.php">Delta</a></li>
<li><a href="echo.php">Echo</a></li>
<li><a href="foxtrot.php">Foxtrot</a></li>
<li><a href="golf.php">Golf</a></li>
<li><a href="hotel.php">Hotel</a></li>
<li><a href="india.php">India</a></li>
<li><a href="juliet.php">Juliet</a></li>
<li><a href="kilo.php">Kilo</a></li>
<li><a href="lima.php">Lima</a></li>
<li><a href="mike.php">Mike</a></li>
<li><a href="november.php">November</a></li>
<li><a href="oscar.php">Oscar</a></li>
<li><a href="papa.php">Papa</a></li>
<li><a href="romeo.php">Romeo</a></li>
<li><a href="sierra.php">Sierra</a></li>
<li><a href="tango.php">Tango</a></li>
<li><a href="uniform.php">Uniform</a></li>
<li><a href="victor_zulu.php">Victor - Zulu</a></li>

</ul>

In the code the } while ($rsUsers = mysql_fetch_assoc ($users_query)); lists all the airfields but I would like to list only those starting with "A" in this section.

Please can someone educate me?

Barand · July 31, 2012

SELECT ..... WHERE whateverfieldnameyouarelookingfor LIKE 'A%'

will find all "whateverfieldnameyouarelookingfor" that begin with "A"

VinceGledhill · August 1, 2012

Thanks for that.

I have tried it here...

 <?php include("db_connect.php");
$users_sql = "SELECT * FROM users";
$users_query = mysql_query($users_sql) or die (mysql_error());
$rsUsers = mysql_fetch_assoc ($users_query);

?>

<h2>News</h2>
    <div class="scroll">
           
      <h3>31st July 2012</h3>
      <p class="news">New HTML site created which has made the whole job of adding new airfields that much easier.  As soon as you submit an airfield it becomes visible.
      
      </div>
<li><a href="add.php">Add New Airfield</a></li>
<h2>Airfields</h2>

<li><a href="alpha.php">Alpha</a></li>


<?php do {
?>
<li><a href=""><?php echo $rsUsers ['username'] LIKE 'A%'; ?>	</a></li>
<?php	
} while ($rsUsers = mysql_fetch_assoc ($users_query));
?>


<li><a href="bravo.php">Bravo</a></li>

But get nothing.

I have also tried it here

<?php include("db_connect.php");
$users_sql = "SELECT * FROM users";
$users_query = mysql_query($users_sql) or die (mysql_error());
$rsUsers = mysql_fetch_assoc ($users_query);

?>

<h2>News</h2>
    <div class="scroll">
           
      <h3>31st July 2012</h3>
      <p class="news">New HTML site created which has made the whole job of adding new airfields that much easier.  As soon as you submit an airfield it becomes visible.
      
      </div>
<li><a href="add.php">Add New Airfield</a></li>
<h2>Airfields</h2>

<li><a href="alpha.php">Alpha</a></li>


<?php do {
?>
<li><a href=""><?php echo $rsUsers ['username']; ?>	</a></li>
<?php	
} while ($rsUsers = mysql_fetch_assoc ($users_query)LIKE 'A%');
?>


<li><a href="bravo.php">Bravo</a></li>

And still get nothing.

I cannot put it in the "SELECT * FROM users" at the top because I want the other menu items to show "B" "C" etc.

carugala · August 1, 2012

$users_sql = "SELECT * FROM users"

ORDER BY name... SQL

Use the DB to manipulate the data, not the code. Very useful.

VinceGledhill · August 1, 2012

Thanks carugala, that's a good start, now working in alphabetical order at least. ;-)

Christian F. · August 1, 2012

You really should be using JOINs for this, VinceGledhill. Not only will it make the code easier to maintain, but it'll also save you a whole lot of SQL requests. Something like this should work:

SELECT u.`id`, u.`username`, a.`id`, a.`name` FROM `users` AS u
LEFT JOIN `airfields` AS a ON a.`userid` = u.`id`
WHERE a.`name` LIKE 'A%'
ORDER BY u.`username`, a.`name`

Replace your first query with this, and delete the one you're using inside the loop. This will fetch all the necessary data, in one pull only.

Printing it out is quite easy too. Just test on the username to see if you need to create a new menu, there are plenty of examples on this forum on how to do it.

VinceGledhill · August 1, 2012

Thanks for that.

What I would really like to do is to replicate how it works via the joomla front end which is here

http://www.airfieldcards.com

Any ideas how I can click Alpha, then show the records that start with an "A"

then Bravo... etc. Just like it is in the joomla front end.

PFMaBiSmAd · August 1, 2012

The suggestion that Barand posted was how to create a SELECT query statement that does what you asked about.

However, your list of separate hard-code pages - alpha.php, bravo.php, charlie.php, ... is not how you should be doing this. You should have one page that accepts a $_GET parameter on the end of the URL that specifies what to display. Something like - <li><a href="index.php?nav=a">Alpha</a></li> <li><a href="index.php?nav=b">Bravo</a></li>... You would put the $_GET['nav'] value (after validating it) into the LIKE '$search%' term in the query.

You should also have an array of navigation values/labels $nav = array('a'->'Alpha','b'=>'Bravo',...), so that you can dynamically produce your Alpha, Bravo, ... navigation links and so that you can validate the submitted $_GET parameter before putting it into the query statement (you would make sure that the submitted value is exactly only one of the array keys or that the array element with that key isset.) You could also query your database table to get just a list of the actual starting first letters and only output navigation links for the letters that exist in the data.

PFMaBiSmAd · August 1, 2012

You could also query your database table to get just a list of the actual starting first letters and only output navigation links for the letters that exist in the data.

^^^ If you did this, you could also get a count of the number of airfields for each letter and display that next to the link -

Alpha (25)

Bravo (73)

...

VinceGledhill · August 1, 2012

Thanks PFMaBiSmAd.

Would it be too much trouble to ask you to show me how? I am getting on a bit and I'm struggling.

PFMaBiSmAd · August 1, 2012

<?php

// list of letter/labels
$nav=array('A'=>'Alpha',
'B'=>'Bravo',
'C'=>'Charlie',
'D'=>'Delta',
'E'=>'Echo',
'F'=>'Foxtrot',
'G'=>'Golf',
'H'=>'Hotel',
'I'=>'India',
'J'=>'Juliet',
'K'=>'Kilo',
'L'=>'Lima',
'M'=>'Mike',
'N'=>'November',
'O'=>'Oscar',
'P'=>'Papa',
'Q'=>'Quebec',
'R'=>'Romeo',
'S'=>'Sierra',
'T'=>'Tango',
'U'=>'Uniform',
'V'=>'Victor-Zulu');
/*
'W'=>'Whiskey',
'X'=>'Xray',
'Y'=>'Yankee',
'Z'=>'Zulu');
*/

// build navigation
$nav_menu = "<ul>\n";
foreach($nav as $key=>$value){
$nav_menu .= "<li><a href='?nav=$key'>$value</a></li>\n";
}
$nav_menu .= "</ul>\n";

// output navigation where you want it on your page
echo $nav_menu;


// process the page request
$search = isset($_GET['nav']) ? strtoupper(trim($_GET['nav'])) : '';
$search = isset($nav[$search]) ? $search : '';

if($search != ''){
// one of the possible letters was submitted

// if V, requires special handling to match V-Z (left as a programming exercise...)

$query = "SELECT * FROM your_table WHERE some_column LIKE '$search%' ORDER BY some_column";

echo $query; // show resulting query for demo purposes

// execute your query and loop over the results
}

VinceGledhill · August 1, 2012

Brilliant work thanks mate.

VinceGledhill · August 1, 2012

PFMaBiSmAd and everyone else, thanks a million for that.

I now know that I am far too stupid to learn all this. Your coding I could never have done, it's amazing to me.

I was just sick of using the joomla to add a menu item every time, but I'm now getting in so deep, I want to make it happen without taking up too much of you good peoples time.

I have the pages set up as follows.

CSS running the style (not very good at the moment I know)

navmenu.php is the navigation menu that is under the header image

sidelinks.php is where I have put your code

footer.php is obviously where the footer is.

So, the main page (left part) is called "content" in the CSS and the navigation part of the page is called "right-col" in the CSS, and has a php include statement to grab that.

How do I get the individual airfield menu to come up under the "alpha" and then when you click on the "airfield name" show results.php with the correct "card" in the "content" section?

PFMaBiSmAd · August 2, 2012

How do I get the individual airfield menu to come up under the "alpha"

By building a sub-menu from the records that the query matches for the submitted first letter and then outputting that sub-menu in the proper place when you are building the main A-Z navigation links -

<?php

// list of letter/labels
$nav=array('A'=>'Alpha',
'B'=>'Bravo',
'C'=>'Charlie',
'D'=>'Delta',
'E'=>'Echo',
'F'=>'Foxtrot',
'G'=>'Golf',
'H'=>'Hotel',
'I'=>'India',
'J'=>'Juliet',
'K'=>'Kilo',
'L'=>'Lima',
'M'=>'Mike',
'N'=>'November',
'O'=>'Oscar',
'P'=>'Papa',
'Q'=>'Quebec',
'R'=>'Romeo',
'S'=>'Sierra',
'T'=>'Tango',
'U'=>'Uniform',
'V'=>'Victor-Zulu');
/*
'W'=>'Whiskey',
'X'=>'Xray',
'Y'=>'Yankee',
'Z'=>'Zulu');
*/

// process the page request
$search = isset($_GET['nav']) ? strtoupper(trim($_GET['nav'])) : '';
$search = isset($nav[$search]) ? $search : '';

if($search != ''){
// one of the possible letters was submitted

// if V, requires special handling to match V-Z
if($search == 'V'){
	$where_clause = "WHERE some_column REGEXP '^[v-z]'"; // regexp could be used in all cases, but it is likely much slower than a LIKE comparison
} else {
	$where_clause = "WHERE some_column LIKE '$search%'";
}

$query = "SELECT * FROM your_table $where_clause ORDER BY some_column";

echo $query; // show resulting query for demo purposes

// execute your query and loop over the results
$result = mysql_query($query);
$sub_menu = ''; // define as empty string in case there are zero matching rows
if(mysql_num_rows($result) > 0){
	$sub_menu = "<ol>\n";
	while($row = mysql_fetch_assoc($result)){
		$_GET['id'] = $row['id']; // set the id=x key/value for producing the link
		// build the link, keeping any existing get parameters as is
		$sub_menu .= "<li><a href='?" . http_build_query($_GET, '', '&') . "'>{$row['name']}</a></li>\n";
	}
	$sub_menu .= "</ol>\n";
}
}

// build navigation
$nav_menu = "<ul>\n";
foreach($nav as $key=>$value){
$nav_menu .= "<li><a href='?nav=$key'>$value</a>";
if($key == $search){
	$nav_menu .= $sub_menu; // insert sub-menu under the correct letter's main menu
}
$nav_menu .= "</li>\n";
}
$nav_menu .= "</ul>\n";

// output navigation where you want it on your page
echo $nav_menu;

then when you click on the "airfield name" show results.php with the correct "card" in the "content" section?

The links you build in the sub-menu would contain the id of the field as a get parameter on the end of the url (or you could use the field name instead.) You would use that id in a query statement on the target page to retrieve the information for the requested field.

Sign In

DB driven menu. How to just show "A" beginning airfields

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information