Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jan 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Syntax and SELECT Query

    Hello,

    I have two problems that I'm struggling with.

    I have a very basic HTML form which allows users to select 3 attributes for a search against one mySQL table (Table is named 'Results').

    Firstly they select from a drop down whether they want to sort asc/dsc then they select the column they wish to sort by and finally they have a radio button which says do they want to include or exclude a specific column from the table.

    I have done the HTML form as it was very basic. However where I am falling over is on the .php that this form is submitted to.

    I know how to run a SELECT query in isolation that would run both those possibilities (SELECT * FROM Results ORDER BY RunnerID ASC) but I don't know how to code one which receives its input from the form.

    I also don't know how to include or exclude a specific column based on the user selection.

    The user should be able to select how they want it sorted then have it displayed in HTML.

    The code I have for the HTML form is as follows:
    Code:
    <form action="xxxxx" method="post" name="displayTimeTable" onsubmit="return validate();">
    <table>
    
    <tr>
    
    <td>
    Sort table in =
    <select name="SortIn" id = "SortIn" >
    <option value="Please Choose">Please Choose</option>
    <option value="asc">Ascending order</option>
    <option value="desc">Descending order</option>
    </select>
    </td>
    
    <td>
    on =
    <select name="SortBy" id = "SortBy" >
    <option value="Please Choose">Please Choose</option>
    <option value="RunnerID">Runner ID</option>
    <option value="EventID">Event ID</option>
    <option value="Date">Date</option>
    <option value="FinishTime">Finish Time</option>
    <option value="Position">Position</option>
    <option value="CategoryID">Category ID</option>
    <option value="AgeGrade">Age Grade</option>
    <option value="PB">Personal Best</option>
    </select>
    </td>
    
    </tr>
    
    <tr>
    <td>
    Include AgeGrade
    <input type="radio" name="IncludeAgeGrade" value="1"/>Yes
    <input type="radio" name="IncludeAgeGrade" value="0" checked="checked"/>No
    </td>
    </tr>
    
    </table>
    <hr/>
    <input type="submit" name="submit" value="Submit"/>
    </form>
    Any advice on the PHP MySQL side of things would be much appreciated.

    Thanks

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,715
    Thanks
    25
    Thanked 660 Times in 659 Posts
    Something like this
    PHP Code:
    $directon $_POST["SortIn"];
    $sort $_POST["SortBy"];
    $query "SELECT * FROM Results ORDER BY ".$sort." "$directon.";"

    Use this to determine the out put
    if($_POST["IncludeAgeGrade"] == 1){
    //INCLUDE THE AGE GRADE IN OUTPUT
    }else{
    //DON'T INCLUDE
    }

    This must be in tune with the <option value="AgeGrade">Age Grade</option>
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • Users who have thanked sunfighter for this post:

    mcnikal (01-07-2014)

  • #3
    New to the CF scene
    Join Date
    Jan 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Well I've managed to get it all working minus the last part.

    The code I have so far is

    Code:
    $directon = $_POST["SortIn"];
    $sort = $_POST["SortBy"];
    $result = mysql_query ("SELECT * FROM Results ORDER BY ".$sort." ". $directon."");
    
    
    echo "<table border='1'>
    <tr>
    <th>RunnerID</th>
    <th>EventID</th>
    <th>Date</th>
    <th>FinishTime</th>
    <th>Position</th>
    <th>CategoryID</th>
    
    <th>AgeGrade</th>
    <th>PB</th>
    </tr>";
    
    while($row = mysql_fetch_array($result))
      {
      echo "<tr>";
      echo "<td>" . $row['RunnerID'] . "</td>";
      echo "<td>" . $row['EventID'] . "</td>";
      echo "<td>" . $row['Date'] . "</td>";
      echo "<td>" . $row['FinishTime'] . "</td>";
      echo "<td>" . $row['Position'] . "</td>";
      echo "<td>" . $row['CategoryID'] . "</td>";
       if($_POST["IncludeAgeGrade"] == 1)
    {
    echo "<td>" . $row['AgeGrade'] . "</td>";
    }
    
      echo "<td>" . $row['PB'] . "</td>";
    
      echo "</tr>";
      }
    echo "</table>";
    The way I have it structured if you select "Yes" on the HTML form for show AgeGrade there is no issue. However if you select "No" the column header for AgeGrade still echos because of the way I have my code. I'm unsure of how to get it so the column header and the column data only appear when yes is selected in the previous screen.

    Thanks again.

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,715
    Thanks
    25
    Thanked 660 Times in 659 Posts
    Use this:
    Code:
    	echo "<tr>";
    	echo "<td>" . $row['RunnerID'] . "</td>";
    	echo "<td>" . $row['EventID'] . "</td>";
    	echo "<td>" . $row['Date'] . "</td>";
    	echo "<td>" . $row['FinishTime'] . "</td>";
    	echo "<td>" . $row['Position'] . "</td>";
    	echo "<td>" . $row['CategoryID'] . "</td>";
    	if($_POST["IncludeAgeGrade"] == 1){
    		echo "<td>" . $row['AgeGrade'] . "</td>";
    	}else{
    		echo "<td>&nbsp;</td>";
    	}
    	echo "<td>" . $row['PB'] . "</td>";
    	echo "</tr>";
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • Users who have thanked sunfighter for this post:

    mcnikal (01-07-2014)

  • #5
    New Coder
    Join Date
    Oct 2013
    Location
    Bangalore, Karnataka
    Posts
    43
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Its basically used this select syntax
    "SELECT field1, ...fieldN table_name1, [WHERE Clause] [OFFSET M ][LIMIT N]"
    if try do to this code


  •  

    LinkBacks (?)

    1. 04-13-2014, 09:10 AM

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •