...

View Full Version : PHP command to display all records



kenwvs
02-16-2008, 05:24 AM
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.

shyam
02-16-2008, 08:27 PM
looks more like a mysql question...u have to give more information about ur table structures to get an answer

kenwvs
02-16-2008, 09:29 PM
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

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']))
{
?>

StupidRalph
02-17-2008, 12:24 AM
Hello again..:)



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



$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 (http://ww.google.com/search?q=SQL+injection) and MySQL Real Escape String (http://www.php.net/mysql-real-escape-string)

kenwvs
02-17-2008, 12:51 AM
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.

StupidRalph
02-17-2008, 01:01 AM
Look at the sample code I put up...

Opposed to hardcoding your SQL statement:

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:

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

SELECT * from Meetings WHERE City='Atlanta' ORDER BY Time

The query is flexible this way.

kenwvs
02-17-2008, 03:41 AM
If I am understanding correctly, by using the code you suggested

$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


$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.

Inigoesdr
02-17-2008, 06:11 AM
If I am understanding correctly, by using the code you suggested

$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:

$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:

$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:

$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() (http://php.net/mysql_real_escape_string), and intval() (http://php.net/intval)/htmlentities() (http://php.net/htmlentities) if necessary.

kenwvs
02-17-2008, 06:39 AM
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?



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>

Inigoesdr
02-17-2008, 06:47 AM
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.

kenwvs
02-17-2008, 09:49 PM
Once I removed the ternary expression it is working like I was hoping.

Thanks for the help in getting this to work.

kenwvs
02-17-2008, 09:57 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum