...

View Full Version : Can someone please explain how I select all records from a table?



dareichmann
09-20-2011, 06:36 PM
Hello,

I am using a calendar script and I have created a separate php file to display all appointments from a given user in a simple table. I was able to display individual users in the table, but I can't seem to select all the records and have them displayed. I want my Master List option to list ALL appointments for the 3 agents. Please help me figure this out because I have a feeling it is something really simple, but I can't seem to pinpoint my problem.

Thanks!

My form:

<form method="post" action="spreadsheet.php?go" id="searchform">
<td><select class="frmInput" name="user_id" style="width:150px;">
<option value="*">Master List</option>
<option value="5">Gary H</option>
<option value="6">Stan J</option>
<option value="7">Scott M</option>
</td>
<input type="submit" name="submit" value="Search">
</form>


My PHP:

<?php
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/[0-9*]+/", $_POST['user_id'])){
$user_id=$_POST['user_id'];
//connect to the database
$db=mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
//-select the database to use
$mydb=mysql_select_db("sms_cal");
//-query the database table
$sql="SELECT event_id, title, description, category_id, venue, user_id, s_date, e_date, s_time, e_time FROM events WHERE user_id LIKE '$user_id'";
//-run the query against the mysql query function
$result=mysql_query($sql) or die (mysql_error ());
//-create while loop and loop through result set
echo "<table border='1'>
<tr>
<th>Customer Name</th>
<th>Description</th>
<th>Category</th>
<th>Venue</th>
<th>Date</th>
<th>Time</th>
</tr>";

while($row=mysql_fetch_array($result)){
$event_id=$row['event_id'];
$title=$row['title'];
$description=$row['description'];
$category_id=$row['category_id'];
$s_date=$row['s_date'];
$venue=$row['venue'];
$s_time=$row['s_time'];
$e_time=$row['e_time'];


//-display the result of the array



echo "<tr>";
echo "<td>" . $title ." </td>";
echo "<td>" . $description ." </td>";
echo "<td>" . $category_id ." </td>";
echo "<td>" . $venue ." </td>";
echo "<td>" . $s_date ." </td>";
echo "<td>" . $s_time ." &nbsp ". $e_time ." </td>";
echo "</tr>";
}
echo "</table>";

}
}
}
?>

djm0219
09-20-2011, 06:38 PM
Remove the WHERE clause on your SQL statement to get all of the entries in the table.

dareichmann
09-20-2011, 06:42 PM
Won't that mess up my ability to only view the individual agents appointments?

djm0219
09-20-2011, 07:06 PM
Well I assumed, probably incorrectly, that you would know/understand how to include the logic to detect whether or not the master list option had been selected and modify the query. Sorry about that.



$user_id=$_POST['user_id'];
//connect to the database
$db=mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
//-select the database to use
$mydb=mysql_select_db("sms_cal");
//-start building the query the database table
$sql="SELECT event_id, title, description, category_id, venue, user_id, s_date, e_date, s_time, e_time FROM events";
// a specific user was selected so limit what we look for in the query
if ($user_id != '*') {
$sql .= " WHERE user_id LIKE '$user_id'";
}
//-run the query against the mysql query function
$result=mysql_query($sql) or die (mysql_error ());



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum