Page 1 of 1

conditional display

Posted: Mon Jun 29, 2009 9:10 am
by DAVID87
pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


Hi all,

again great help this site has been so far.

I need some help in a display page on my site.

Currently I have some code that calls back all of the data from a table in my sql database and is displayed in alternating row colours.

What I am looking to do is to have a selection box above this code that say you can select the year from it will then change the sql statement to have a "Where" statement in it. Is it possible to do this with the user selecting the option in the dropdown box and then it changes automatically and not have to press a submit button?

the current code I have to display ALL data is as follows:

Code: Select all

<?php
 
include 'dbc.php';
 
$sql="SELECT * FROM fixtures ORDER BY year,month,date ASC";
$result=mysql_query($sql);
 
?>
    <table width="100%">
        <tr>
        <td class="rowQ" width="10%">Year</td>
        <td class="rowQ" width="10%">Month</td>
        <td class="rowQ" width="10%">Date</td>
        <td class="rowQ" width="10%">Day</td>
        <td class="rowQ" width="10%">Fixture</td>
        <td class="rowQ" width="30%">Venue</td>
        <td class="rowQ" width="10%">Results</td>
        <td class="rowQ" width="10%">Type</td>
        </tr>
       <?php
        while($rows=mysql_fetch_array($result)){
        if($color==1){
       echo "<tr class='rowA'>
         <td>$rows[year]</td>
         <td>$rows[month]</td>
         <td>$rows[date]</td>
         <td>$rows[day]</td>
         <td>$rows[fixture]</td>
         <td>$rows[venue]</td>
         <td>$rows[results]</td>
         <td>$rows[type]</td>
         </tr>";
        $color="2";
        } else {
       echo "<tr class='rowB'>
         <td>$rows[year]</td>
         <td>$rows[month]</td>
         <td>$rows[date]</td>
         <td>$rows[day]</td>
         <td>$rows[fixture]</td>
         <td>$rows[venue]</td>
         <td>$rows[results]</td>
         <td>$rows[type]</td>
         </tr>";
        $color="1";
        }   
        }
        mysql_close();
        ?>
</table>
pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: conditional display

Posted: Mon Jun 29, 2009 9:17 am
by jayshields
You can do it without the user pressing a submit button by using Javascript and using the onchange event for the select box, the onchange event would then trigger a form submission. This ofcourse still requires that the page refreshes. To do it without a page refresh you'll have to use AJAX.

Re: conditional display

Posted: Mon Jun 29, 2009 10:14 am
by DAVID87
javascript would be fine.

could anyone give an example bit of code that I could use for this. I do not mind so much that the page will refresh its just that I don't want the user to have to select a "submit" style button when they need to show all the data shown from say 2006 to 2009.

Thanks for the reply and any help with an example code would be greatly appreciated.

Re: conditional display

Posted: Mon Jun 29, 2009 12:03 pm
by jayshields

Code: Select all

<form id="myform" method="get" action="#">
  <select name="whatever" onchange="document.getElementById('myform').submit();">
    <option value="foo">Foo</option>
    <option value="bar">Bar</option>
  </select>
</form>

Re: conditional display

Posted: Mon Jun 29, 2009 3:57 pm
by DAVID87
thats great and thanks for the help.

this works and refreshes the page but how do I get this to effect the sql statement so that it shows the data selected in the dropdown list?

my dropdown list id year and when selected 2009 I want it to only show 2009 results.

Any help?

Im guessing it needs to change the WHERE statement but how do I do that?

Re: conditional display

Posted: Mon Jun 29, 2009 5:55 pm
by jayshields

Code: Select all

if(isset($_GET['whatever']))
{
  $sql = "SELECT * FROM fixtures WHERE `year` = " . mysql_real_escape_string($_GET['whatever']) . " ORDER BY year,month,date ASC";
}
else
{
  $sql="SELECT * FROM fixtures ORDER BY year,month,date ASC";
}

Re: conditional display

Posted: Tue Jun 30, 2009 4:53 am
by DAVID87
Thanks for this.

I have applied both bits of code to my page but now I get a SQL error on my page saying:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in "URL" on lone "no."

the line number refers to the line

"while($rows=mysql_fetch_array($result)){"

Any advice?

full code:

Code: Select all

 
    <h1>Fixtures</h1>
<form id="selectyearform" method="get" action="#">
    <select name="selectyear" onchange="document.getElementById('selectyearform').submit();">
    <option value="09">2009</option>
    <option value="08">2008</option>
    <option value="07">2007</option>
    <option value="06">2006</option></select></form>
    <?php
 
include 'dbc.php';
 
$result=mysql_query($sql);
 
if(isset($_GET['selectyear']))
{
  $sql = "SELECT * FROM fixtures WHERE year = " . mysql_real_escape_string($_GET['selectyear']) . " ORDER BY year,month,date ASC";
}
else
{
  $sql="SELECT * FROM fixtures ORDER BY year,month,date ASC";
}
 
?>
 
    <table width="100%">
        <tr>
        <td class="rowQ" width="10%">Year</td>
        <td class="rowQ" width="10%">Month</td>
        <td class="rowQ" width="10%">Date</td>
        <td class="rowQ" width="10%">Day</td>
        <td class="rowQ" width="10%">Fixture</td>
        <td class="rowQ" width="30%">Venue</td>
        <td class="rowQ" width="10%">Results</td>
        <td class="rowQ" width="10%">Type</td>
        </tr>
       <?php
        while($rows=mysql_fetch_array($result)){
        if($color==1){
       echo "<tr class='rowA'>
         <td>$rows[year]</td>
         <td>$rows[month]</td>
         <td>$rows[date]</td>
         <td>$rows[day]</td>
         <td>$rows[fixture]</td>
         <td>$rows[venue]</td>
         <td>$rows[results]</td>
         <td>$rows[type]</td>
         </tr>";
        $color="2";
        } else {
       echo "<tr class='rowB'>
         <td>$rows[year]</td>
         <td>$rows[month]</td>
         <td>$rows[date]</td>
         <td>$rows[day]</td>
         <td>$rows[fixture]</td>
         <td>$rows[venue]</td>
         <td>$rows[results]</td>
         <td>$rows[type]</td>
         </tr>";
        $color="1";
        }   
        }
        mysql_close();
        ?>
</table>
 

Re: conditional display

Posted: Tue Jun 30, 2009 5:03 am
by jayshields
You need to execute your SQL query and set the result to your $result variable before you try to iterate it. I would also advise you to put array indices inside single quotes if they are strings.

Re: conditional display

Posted: Tue Jun 30, 2009 5:14 am
by DAVID87
Sorry that makes no sense to me.
I am quite new to PHP and MySQL so I may seem a little dumb.

Any chance you could explain abit further?

Re: conditional display

Posted: Tue Jun 30, 2009 5:30 am
by BornForCode
Lemme check if i understand well: You need a select box and based on it's current selection you need to display the content accord with it, right?

Re: conditional display

Posted: Tue Jun 30, 2009 5:56 am
by DAVID87
I have now tried this code and nothing shows at all

???

Re: conditional display

Posted: Tue Jun 30, 2009 6:00 am
by BornForCode
The posted code is just for example, i cannot test it since i don't have your environment available. Use it as model/guidance to create your own working code.

For everything else is mastercard :D

Anyway something that looks and works better:

Code: Select all

<?php
session_start();
if(isset($_POST['year']))
$_SESSION['year'] = $_POST['year'];
?>
<form name="display_something" method="POST">
    Select year:
    <select name="year">        
        <?php
        $currentYear = date("Y");
        $selectedYear = isset($_SESSION['year']) ? $_SESSION['year'] : $currentYear;
        
        
        for($i=1970; $i<=$currentYear; $i++) {          
           echo '<option value="'.$i.'"'.(($i == $selectedYear) ? 'selected>' : '>').$i.'</option>';           
        }
        
        ?>
    </select>
    <input type="submit" value="submit"/>
</form>
 
<?php
 
$sql="SELECT * FROM fixtures where year = ".$_SESSION['year']." ORDER BY year,month,date ASC";
$result = mysql_query($sql);
?>
 
<table width="100%">
    <tr>
        <td class="rowQ" width="10%">Year</td>
        <td class="rowQ" width="10%">Month</td>
        <td class="rowQ" width="10%">Date</td>
        <td class="rowQ" width="10%">Day</td>
        <td class="rowQ" width="10%">Fixture</td>
        <td class="rowQ" width="30%">Venue</td>
        <td class="rowQ" width="10%">Results</td>
        <td class="rowQ" width="10%">Type</td>
    </tr>
    <?php
    if(mysql_num_rows($result))
    {
        $step=0;
        while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $style = (($step%2) == 0) ? 'rowB' : 'rowA';
            echo '<tr class="'.$style.'">';
            echo '<td>'.$rows['year'].'</td>';
            echo '<td>'.$rows['date'].'</td>';
            echo '<td>'.$rows['day'].'</td>';
            echo '<td>'.$rows['fixture'].'</td>';
            echo '<td>'.$rows['venue'].'</td>';
            echo '<td>'.$rows['results'].'</td>';
            echo '<td>'.$rows['year'].'</td>';
            echo '<td>'.$rows['type'].'</td>';
            echo '</tr>';           
            
            $step++;
        }
    }
    else
    {
        echo '<tr><td colspan="7">No results to display</td></tr>'; 
    }
    mysql_close();
    ?>
</table>
 
 

Re: conditional display

Posted: Tue Jun 30, 2009 12:58 pm
by DAVID87
Thanks everyone.

this has really helped. I have adapted my code a little bit and now everything works great.

Thank you all again for all the help.