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 12 of 12
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP command to display all records

    I have a script working for a meeting list where you can choose the group, city and the day and it lists all meetings for that group in that city on that particular day. How would I set it up so you can select the group and city and it would display all meetings for a group in a city on any day (all days of the week). Would I include where I list the days "All Days" and somehow tell it to include all days, or would I somehow have it list all days if a day is not selected? I would also like to be able to select all meetings on a particular day in all cities.

    I am not sure what the command would be for this. It is getting the meeting data from a mysql database.

  • #2
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    looks more like a mysql question...u have to give more information about ur table structures to get an answer
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have one table and it contains a list of the different cities that the meetings are held in.

    I then have a second table that includes all the rest of the data (code (incrementing field), city,day, time, address, type, building, group name, notes, other, map)

    Here is the code that I am using right now that allows you to select a city, and then you can select the day of the week. The problem is if you don't know what day of the week the meeting is, you can't find the meeting, so I would like to modify it so they can either put in a day of the week, or leave the field empty and it will then return a list of all meetings for that particular city. If possible I would also like to be able to choose a day of the week and have it show a list of all meetings in all cities on that day, but this function isn't nearly as important.

    Hope this explains it a bit better.

    PHP Code:
    <?php

    if (!isset($_POST['Submit']))
    {
        
    ?>

    <form action="" method="post">
    <center>

                               City / Town: <select name='City'>

                            <?php

        $result 
    mysql_query("SELECT * FROM City ORDER BY City") or die(mysql_error());
    while (
    $row mysql_fetch_assoc($result)) {
    echo 
    "<option>$row[City] </option>";
    }

        
    ?></select>
                                            Day of Week:<select name="Day">
                                    <option value="Sunday">Sunday</option>
                                    <option value="Monday">Monday</option>
                                    <option value="Tuesday">Tuesday</option>
                                    <option value="Wednesday">Wednesday</option>
                                    <option value="Thursday">Thursday</option>
                                    <option value="Friday">Friday</option>
                                    <option value="Saturday">Saturday</option>
                            </select>
                            </center><BR>
    <div><center><input type="submit" name="Submit" value="Display Meetings"></center></div>
    </Form>
    <a href="/maps/Lethbridgehall.jpg">Map</a>
    <?php
    }else{

    $City=$_POST['City'];
    $Day=$_POST['Day'];
    }

    $result mysql_query("SELECT * from Meetings WHERE City='".$_POST['City']."' and Day='".$_POST['Day']."' ORDER BY Time")
    or die(
    mysql_error());

    $num=mysql_numrows($result);

    mysql_close();
    echo 
    "<table border='1'>";
    ?>
    <center>
    <table border="1" cellspacing="1" cellpadding="1"></center>
    <?php
    if(isset($_POST['Submit']))
    {
    ?>
    <tr>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">City</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Day</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Time</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Address</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Building</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Type</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">GRName</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Notes</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Other</font></th>
    <th><font face="Arial, Helvetica, sans-serif" font size="2">Map</font></th>
    </tr>
    <?php
    }
    ?>
    <?php
    $i
    =0;
    while (
    $i $num) {

    $City=mysql_result($result,$i,"City");
    $Day=mysql_result($result,$i,"Day");
    $Time=mysql_result($result,$i,"Time");
    $Address=mysql_result($result,$i,"Address");
    $Building=mysql_result($result,$i,"Building");
    $Type=mysql_result($result,$i,"Type");
    $GRName=mysql_result($result,$i,"GRName");
    $Notes=mysql_result($result,$i,"Notes");
    $Other=mysql_result($result,$i,"Other");
    $Map=mysql_result($result,$i,"Map");
    ?>

    <tr>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $City?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Day?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Time?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Address."."?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Building."."?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Type."."?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $GRName."."?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Notes."."?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Other."."?></font></td>
    <td><font face="Arial, Helvetica, sans-serif" font size="2"><? echo $Map."."?></font></td>
    </tr>

    <?php
    $i
    ++;
    }


    echo 
    "</table>";
    ?>
    <?php
    if(isset($_POST['Submit']))
    {
    ?>

  • #4
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Hello again..:)

    PHP Code:
    $result mysql_query("SELECT * from Meetings WHERE City='".$_POST['City']."' and Day='".$_POST['Day']."' ORDER BY Time")
    or die(
    mysql_error()); 
    PHP Code:
    $sql "SELECT * FROM `Meetings` WHERE `City` = $_POST['City']";

    $day = (empty($_POST['day']) ? "AND `Day`='".$_POST['Day']'" : ' );  //ternary operator checks to see if they selected a day

    $sql .= $day;
    $sql .= "ORDER BY `Time`"
    If you were to break up this line here and build your SQL statement dynamically it would work. This isn't tested so but it should put you on the right track. Also, you shouldn't be so casual in inserting $_POST values into your queries.


    See. SQL-injection and MySQL Real Escape String
    Last edited by StupidRalph; 02-17-2008 at 02:05 AM. Reason: Corrected Google link
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will read through the articles you have suggested to understand the security issues I am faced with. Can you please explain what you mean by "build your SQL statement dynamically". I am not understanding what you are trying to say.

  • #6
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Look at the sample code I put up...

    Opposed to hardcoding your SQL statement:
    Code:
    SELECT * from Meetings WHERE City='".$_POST['City']."' and Day='".$_POST['Day']."' ORDER BY Time
    You can build it according to user input. If the user doesn't fill out the day field, then we can leave it off the WHERE clause so that it selects all days.

    Depending how the user filled out our form the SQL statement might look like this:
    Code:
    SELECT * from Meetings WHERE City='Atlanta' AND Day='2008-02-16' ORDER BY Time
    Or like this if they did not fill out the day field
    Code:
    SELECT * from Meetings WHERE City='Atlanta' ORDER BY Time
    The query is flexible this way.

    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #7
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I am understanding correctly, by using the code you suggested
    PHP Code:
    $sql "SELECT * FROM `Meetings` WHERE `City` = $_POST['City']"

    $day = (empty($_POST['day']) ? "AND `Day`='".$_POST['Day']'" : ' );  //ternary operator checks to see if they selected a day 

    $sql .= $day
    $sql .= "ORDER BY `Time`"
    instead of the code I had originally used

    PHP Code:
    $result mysql_query("SELECT * from Meetings WHERE City='".$_POST['City']."' and Day='".$_POST['Day']."' ORDER BY Time"
    or die(
    mysql_error()); 
    it should allow the user to get all days of the week when they don't select any one given day.

    The problem I am having is by using the code you provided it creates a syntax error. It was while ago that I created this to start with, and am having troubles understanding why you are using $sql = instead of the $result=mysql_query command that I had used.

  • #8
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by kenwvs View Post
    If I am understanding correctly, by using the code you suggested
    PHP Code:
    $sql "SELECT * FROM `Meetings` WHERE `City` = $_POST['City']"

    $day = (empty($_POST['day']) ? "AND `Day`= '".$_POST['Day']'" : ' );  //ternary operator checks to see if they selected a day 

    $sql .= $day
    $sql .= "ORDER BY `Time`"
    The problem I am having is by using the code you provided it creates a syntax error. It was while ago that I created this to start with, and am having troubles understanding why you are using $sql = instead of the $result=mysql_query command that I had used.
    The $day line should be:
    PHP Code:
    $day = (empty($_POST['day']) ? "AND `Day` = '" $_POST['Day'] . "'" '' );  //ternary operator checks to see if they selected a day 
    Once you have the $sql variable set you would use:
    PHP Code:
    $result mysql_query($sql) or die(mysql_error()); 
    Also note that while it's easier to read the query like this, you can place the ternary expression inline with the concatenation operator:
    PHP Code:
    $result mysql_query("SELECT * from Meetings WHERE City='".$_POST['City']."'" . (empty($_POST['day']) ? "AND `Day` = '" $_POST['Day'] . "'" '' ) . " ORDER BY Time") or die(mysql_error()); 
    Also, make sure you sanitize user input with mysql_real_escape_string(), and intval()/htmlentities() if necessary.
    Last edited by Inigoesdr; 02-17-2008 at 07:14 AM.

  • #9
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is starting to make a little more sense to me. I have the days of the week listed in my form, for them to select a day, and had it originally set to show Sunday by default, but in changing this to allow all days to be selected, I would need to have a blank line.... Would I simply use the "Select a Day" which has no option connected to it, to default to showing all days?

    PHP Code:
    Day of Week:<select name="Day">
                                    <
    option value="">Select a Day</option>
                                    <
    option value="Sunday">Sunday</option>
                                    <
    option value="Monday">Monday</option>
                                    <
    option value="Tuesday">Tuesday</option>
                                    <
    option value="Wednesday">Wednesday</option>
                                    <
    option value="Thursday">Thursday</option>
                                    <
    option value="Friday">Friday</option>
                                    <
    option value="Saturday">Saturday</option>
                            </
    select

  • #10
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Yeah, that should work, but then you don't need to have the ternary expression at all. Just insert the value for the day into the database regardless of whether it's empty or not. When you display it you shouldn't have a problem checking whether it's empty, and if it is, displaying all days.

  • #11
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Once I removed the ternary expression it is working like I was hoping.

    Thanks for the help in getting this to work.

  • #12
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just did some checking on the ORDER BY clause, and I don't see where it allows to sort by DAY (Monday, Tuesday, etc). It doesn't work to do it alphabetically, so would I need to assign each day a number (1=Sunday, 2=Monday, etc) and then sort by number to have them sort by day and then by time? I can ORDER BY day, time; and this will group the days together, but it starts with Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday. I am looking to get it in a Sunday - Saturday order.
    Last edited by kenwvs; 02-17-2008 at 11:31 PM. Reason: clarify


  •  

    Posting Permissions

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