Sorting a datbase query with a huge number of variables

[SaturdayNightSpecial]

Hi All,  Sorry for all the questions but I guess that's what happens when you have a Sales/Marketing guy building a site.  I've been trying to learn so much at once that my head is spinning and would like some help in at least getting guided in the right direction.  I've been trying some various coding but can't get this to work yet.  Here's what I'm doing.  This section of the site is a news section that will have news from all across the nation.  Many people will want to filter this news to, by state, by region, by organization etc so I've made a drop down menu from AllwebMenus (very nice menu builder by the way).  There's probably somewhere close to 1,000 variables (items/buttons) in this menu. There is a link area in each item in the menu builder software so I'm hoping I can use that link in the menu builder software to pass the filter through the url.

 

What would be the best way to filter the results, SELECT, IF, WHERE and is there any way to use ARRAYS or something to limit the amount of code that's needed?  I've got the news displaying in a repeat region on the main page and it's linked and displaying correctly in the detail page so that part is done, now I just need to get the menu buttons to filter it.

 

While I'm asking, I've got the data all in one table and using one recordset.  I'm only displaying the data from 4 columns but I've also selected all the columns in the recordset that will be needed to filter the data.  Is that the correct way to do it for the fastest page load or should I be using one recordset just for the display of the data and then create another recordset to filter the data?  I do have one common column (nfid) that is unique to tie the two recordsets together if you think that's the best way to do it.

 

Thanks

[ignace]

search.php?query=This%20Is%20What%20I%Look%For&by=state&order=DESC&size=100

 

query=This%20Is%20What%20I%Look%For

by=state

order=DESC

size=100

 

$validBy = array(..);
$validSizes = array(..);
$query = $_GET['query'];//This Is What I Look For
$by = in_array($_GET['by'], $validBy) ? $_GET['by'] : 'state'/*default*/;
$order = 'DESC' === $_GET['order'] ? 'DESC' : 'ASC'/*default*/;
$size = in_array(intval($_GET['size'])), $validSizes) ? intval($_GET['size']) : 10/*default*/;

$sql = "SELECT field1, field2 FROM table WHERE field3 LIKE '%$query%' ORDER BY $by $order LIMIT $size";

[SaturdayNightSpecial]

Thanks for the quick response ignace.  My head just exploded but I think I'm OK, the keyboard is just a little messy.

 

I'll start looking up these functions and learn them, that was most of my problem in that I just didn't even know what I should be looking for.  I was reading a bunch of stuff that really didn't apply before.

 

[ignace]

Thanks for the quick response ignace.  My head just exploded but I think I'm OK, the keyboard is just a little messy.

 

The mess created is in correlation with the size of your head and it's contents whereby in some cases a vacuum cleaner will no longer fussice

 

I'll start looking up these functions and learn them, that was most of my problem in that I just didn't even know what I should be looking for.  I was reading a bunch of stuff that really didn't apply before.

 

I think I know most of the PHP functions and what they do so if you need something you may always ask and I'll tell you if such a function exists (and it's name ofcourse)

[SaturdayNightSpecial]

Thanks, I really appreciate the help.  I'm going through your code now to try to figure it out.  I'm sure I will be back with more questions though. I have some now but I'll see if I can get it figured out first.

[SaturdayNightSpecial]

Hi ignace I'm back.  Five more hours of trying and no cigar.  I'm trying to use the code you gave but I think you may have been thinking more in ordering the data by state rather than filtering it by state.  Currently, I have all the news items from all states displaying in a repeat region with 10 records per page.  Many of my visitors will only want to see the news from their state so I've built a menu with drop down lists that I want to use to filter the records so they'll only see the news for their state.  The variable will be passed through the url link on the state buttons.  I did some testing on it in a tutorial and using a separate recordset I was able to do it but it produced a ton of code, I can't imagine trying to do that for a 1,000 links.  I tried to reproduce that method in my site today and couldn't even make it work again...geesh.

 

Here's some of the code for the repeating region maybe that will help some.  I've deleted all the code I've been trying today since I couldn't get it to work so I can start fresh again.

			<?php do { ?>
          <div class="news_content_style" id="div_repeat_news"><span class="news_headline"><?php echo $row_rs_news_content['nf_article_heading']; ?></span><br />
          <?php echo $row_rs_news_content['nfauthorname']; ?><br />
            <?php echo $row_rs_news_content['nfcreatedon']; ?> <a href="racingnewsdetail.php?racing_news_item=<?php echo $row_rs_news_content['nfid']; ?>"><?php echo excerpt($row_rs_news_content['nfarticle'],300); ?></a><br />
            <br />
          </div>
            <?php } while ($row_rs_news_content = mysql_fetch_assoc($rs_news_content)); ?>
           </td>

 

That's actually the code I was working where you replied to one of my questions last week when I was trying to limit the output of the string.  That part of the site is working great, I just now need to figure out how to filter that data by state, region etc.

 

Thanks again in advance.

[ignace]

I'm trying to use the code you gave but I think you may have been thinking more in ordering the data by state rather than filtering it by state.

 

No I wasn't it just selects state as a default (when none was provided) you can change it to organization or even something else (not present in the $validBy).

 

I was able to do it but it produced a ton of code

 

You are using Dreamweaver get used to it I've heard many people complain about the serious overhead Dreamweaver creates.

 

That part of the site is working great, I just now need to figure out how to filter that data by state, region etc.

 

I do think I used a different algorithm. I assumed something like:

 

<table>
<form action="search.php">
<tr>
  <td><input type="text" name="query"> <input type="hidden" name="order" value="DESC"></td>
  <td><select name="size">
           <option value="10">10</option>
           ..
           <option value="100">100</option>
         </select>
  </td>
</tr>
<tr>
  <th><button type="submit" name="by" value="state">State</button></th>
  <th><button type="submit" name="by" value="region">Region</button></th>
</tr>
repeat region
</form>
</table>

[SaturdayNightSpecial]

Hi ignace,

 

I probably didn't explain it well enough, that's a problem for me now as I don't know enough yet to use the proper terminology.  In the last code it looks like you were expecting this to be a form.  It's actually an index page where all the news is displayed and the links that will be used to filter the data are menu buttons.  It's a menu that I built using AllWebMenus.

 

I've taken out the code that I was trying so I could start fresh again so I don't have any code in there that I can show now but here's some of the things that happened while I've been trying it, maybe that will help diagnose the problem.  On one try it did make a change to the page but it didn't utilize the "excerpt" code that I showed on my last reply so it blew apart the page since it was showing the entire contents of the field.  On other attempts the address bar would change to index.php$State=ne (let's just use Nebraska) but there wasn't any change to the page content, it still displayed all the records.  When I did go back and try using a Dreamweaver server behavior as I had done in a tutorial it would test fine when I tried it in the Dreamweaver dialog box but it still wouldn't change the data displayed on the page.  That made me wonder, is it possible that the code that's used to generate the complete list of data in the repeat region is overriding the new code to filter the data.  Do I need a "return" line or something to release control from that first script?

 

Sorry again for being a pest but I just can't get this figured out.  I now some people in forums get upset when they think someone is just lazy and not doing the work but I assure that's not the case here, I'm working my butt off.  It's just that I've been trying to learn PHP, mysql, web design, Dreamweaver etc in the last two months so my knowledge is still really limited but I've got to get this site done.

 

Thanks for all your help.

[SaturdayNightSpecial]

I was just looking at something else.  Ignace, in the first code that you supplied would I even need the $validBY=array and $validSIZES = arrays since this data is just coming from the database and it's already formatted correctly in the database.  They're already in the "nfstate" column as al, ak, az etc.  The states are entered through a form list and are already validated in the form.  The only time they will be entered for this function is when I enter them in the url variable in the href line.

[ignace]

That made me wonder, is it possible that the code that's used to generate the complete list of data in the repeat region is overriding the new code to filter the data.  Do I need a "return" line or something to release control from that first script?

 

I honestly don't know but yes that is quite possible. Personally I like to write it myself as then I know what is in it and how it works (as I know that it uses my kind of thinking and thus also inherits my assumptions). As you are using Dreamweaver it may be possible that your thinking differs from that of those who wrote Dreamweaver and thus the code it generates creating problems along the way. I have nor experience with Dreamweaver nor with AllWebMenus so it possible I'm not able to help you.

[ignace]

I was just looking at something else.  Ignace, in the first code that you supplied would I even need the $validBY=array and $validSIZES = arrays since this data is just coming from the database and it's already formatted correctly in the database.  They're already in the "nfstate" column as al, ak, az etc.  The states are entered through a form list and are already validated in the form.  The only time they will be entered for this function is when I enter them in the url variable in the href line.

 

If your form is auto-validated then it's true you don't need them.

[SaturdayNightSpecial]

If your form is auto-validated then it's true you don't need them.

 

OK, thanks.  I'm going to try again when I get off work and this time I'll try it without those lines.