Jump to content

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


VinceGledhill
 Share

Recommended Posts

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. ;)

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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)

...

Link to comment
Share on other sites

<?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
}

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.