[SOLVED] Getting all the rows from a table

bdmovies · October 9, 2007

Ok, I've played with this for hours now, and I know it's something stupid! I'm trying to get all the rows in a table that match a query. It's always displaying one less row than it has, you'll notice I display the number of rows and then actually echo the rows, but for ex, it will say 3 results, and then display 2 results

Here's the source code

<script language="JavaScript" type="text/javascript">
function validate() {
if (document.searchcase.search_value.value == "") {
alert("Please enter text to search!")
}
}
</script>
<link href="/epso/style.css" rel="stylesheet" type="text/css" />
<form action="" method="post" name="searchcase">

<?php

$search_id = $_POST['search_id'];
$search_value = $_POST['search_value'];
$submit = $_POST['search'];

//On SUBMIT
if (isset($submit)) {

//Check if there's any text, if not, show the table
if ($search_value == NULL) {
show_search_table();
}

else{ //If there is text

$query = "SELECT * FROM case_information WHERE $search_id LIKE '$search_value%'";
$result = mysql_query($query) or die(mysql_error());
$nums = mysql_num_rows($result) or die(mysql_error());
$row = mysql_fetch_assoc($result) or die(mysql_error());

if ($nums == 1) { //If there is only 1 result
	 echo "
	<table border=0>
	<tr><td class=case_information colspan=2>ISSUED IN THE COUNTY OF <B>{$row['county_issued']}</b><br>
	IN THE STATE OF <b>{$row['state_issued']}</b><br>
	COURT CASE NUMBER <b>{$row['case_number']}</b>
	</tr>
	<tr><td class=style_box>{$row['plaintiff']}<br><small>Plaintiff</small>
	<br><b>vs.</b><br>
	{$row['defendant']}<br><small>Defendant</small></td></tr>
	</table>
	";
	}

elseif ($nums == 0) {
	show_search_table();
	echo "<span id=\"error_message\">There are no cases matching your search</span>";
	}

elseif ($nums > 1) {
	echo "<span id=\"numbers\">Your search returned <b>$nums</b> results</span><br />";
	echo "
	<table id=\"results_table\">
		<tr id=\"results_table_header\">
		<th>CASE NUMBER</th>
		<th>CLIENT FILE NUMBER</th>
		<th>PLAINTIFF</th>
		<th>DEFENDANT</th>
		<th>ADDRESS</th>
		<th>STATUS</th>
		<th>SERVER</th>
		<th> </th>
		</tr>
	";
	while ($row = mysql_fetch_assoc($result)) {
				echo "
				<tr>
				<td>{$row['case_number']}</td>
				<td>{$row['client_reference']}</td>
				<td>{$row['plaintiff']}</td>
				<td>{$row['defendant']}</td>
				<td>{$row['addressone']}</td>
				<td>{$row['status']}<nobr></td>
				<td>{$row['serverID']}</td>
				<td>Change</td>
				</tr>
				";	
				}	
	echo "</table>";
	$i = $i + 1;
	}
}

}

elseif(!isset($sumbit)) //No search yet, so show the table
{
show_search_table();
}

function show_search_table() 
{
echo '
<table border="0" style="background-color:#CCCCCC">
<tr><td>
Search by:</td>
<td>
<select name="search_id">
';
$search_options = array ("case_number" => "Case Number", "uniqueID" => "Job ID", "defendant" => "Defendant", "plaintiff" => "Plaintiff", "addressone" => "Address");

foreach( $search_options as $search_value=>$search_variables)
{
echo "<option value=$search_value>$search_variables</option>";
}

echo '
</select>
</td>
<td><input type="text" id="search_field" name="search_value" /></td>
<td><input type="submit" name="search" onClick="validate()" /></td>
</tr></table>
';
}
?>

haaglin · October 9, 2007

It might be because you already fetch the first row below the mysql_num_rows query.. Try to move that inside the if statement that prints if $num == 1:

 <script language="JavaScript" type="text/javascript">
function validate() {
if (document.searchcase.search_value.value == "") {
alert("Please enter text to search!")
}
}
</script>
<link href="/epso/style.css" rel="stylesheet" type="text/css" />
<form action="" method="post" name="searchcase">

<?php

$search_id = $_POST['search_id'];
$search_value = $_POST['search_value'];
$submit = $_POST['search'];

//On SUBMIT
if (isset($submit)) {

//Check if there's any text, if not, show the table
if ($search_value == NULL) {
show_search_table();
}

else{ //If there is text

$query = "SELECT * FROM case_information WHERE $search_id LIKE '$search_value%'";
$result = mysql_query($query) or die(mysql_error());
$nums = mysql_num_rows($result) or die(mysql_error());

if ($nums == 1) { //If there is only 1 result
	 $row = mysql_fetch_assoc($result) or die(mysql_error());
                 echo "
	<table border=0>
	<tr><td class=case_information colspan=2>ISSUED IN THE COUNTY OF <B>{$row['county_issued']}</b><br>
	IN THE STATE OF <b>{$row['state_issued']}</b><br>
	COURT CASE NUMBER <b>{$row['case_number']}</b>
	</tr>
	<tr><td class=style_box>{$row['plaintiff']}<br><small>Plaintiff</small>
	<br><b>vs.</b><br>
	{$row['defendant']}<br><small>Defendant</small></td></tr>
	</table>
	";
	}

elseif ($nums == 0) {
	show_search_table();
	echo "<span id=\"error_message\">There are no cases matching your search</span>";
	}

elseif ($nums > 1) {
	echo "<span id=\"numbers\">Your search returned <b>$nums</b> results</span><br />";
	echo "
	<table id=\"results_table\">
		<tr id=\"results_table_header\">
		<th>CASE NUMBER</th>
		<th>CLIENT FILE NUMBER</th>
		<th>PLAINTIFF</th>
		<th>DEFENDANT</th>
		<th>ADDRESS</th>
		<th>STATUS</th>
		<th>SERVER</th>
		<th> </th>
		</tr>
	";
	while ($row = mysql_fetch_assoc($result)) {
				echo "
				<tr>
				<td>{$row['case_number']}</td>
				<td>{$row['client_reference']}</td>
				<td>{$row['plaintiff']}</td>
				<td>{$row['defendant']}</td>
				<td>{$row['addressone']}</td>
				<td>{$row['status']}<nobr></td>
				<td>{$row['serverID']}</td>
				<td>Change</td>
				</tr>
				";	
				}	
	echo "</table>";
	$i = $i + 1;
	}
}

}

elseif(!isset($sumbit)) //No search yet, so show the table
{
show_search_table();
}

function show_search_table() 
{
echo '
<table border="0" style="background-color:#CCCCCC">
<tr><td>
Search by:</td>
<td>
<select name="search_id">
';
$search_options = array ("case_number" => "Case Number", "uniqueID" => "Job ID", "defendant" => "Defendant", "plaintiff" => "Plaintiff", "addressone" => "Address");

foreach( $search_options as $search_value=>$search_variables)
{
echo "<option value=$search_value>$search_variables</option>";
}

echo '
</select>
</td>
<td><input type="text" id="search_field" name="search_value" /></td>
<td><input type="submit" name="search" onClick="validate()" /></td>
</tr></table>
';
}
?>

bdmovies · October 10, 2007

No go, now it's not displaying the table for the if nums == 1 statement.....

bdmovies · October 10, 2007

Is this a "legal" validation test?

<?php
//Check if there's any text, if not, show the table
if ($search_value == NULL) {
show_search_table();
}

else{ //If there is text
..on with the queires...

?>

bdmovies · October 10, 2007

Ok, I've re-written the page so in my opinion it's cleaner, but i'm still fairly new, anyway, now when I'm hit submit, nothing happens...

<?php
//Turn the form values into PHP variables
$search_value = $_POST['search_value'];
$search_id = $_POST['search_id'];
$submit = $_POST['submit'];

//Checks the number of results returned from $result
function check_number_of_results() {

//Query the search options
$query = "SELECT * FROM case_information WHERE $search_id LIKE '$search_value%'";
$result = mysql_query($query);

$nums = mysql_num_rows($result); //Get the number of rows queried

//If there are 0 results, echo the 0 result table
if ($nums == 0) {
	zero_results();
} //Ends IF

//If there is 1 result, echo the row
elseif ($nums == 1) {
	one_result();
} //Ends IF

//If there are many rows, echo only a "teaser" of the row
elseif ($nums > 1) {
	multiple_rows();
} //Ends IF

// Closes the check_number_of_results
} 

function zero_results() {
echo "<span id=\"error_message\"> There are no results for your search. </span>";
search_table();
}

function one_result() {
$one_row = mysql_fetch_row($result);
echo "Case Number:".$one_row['case_number']."<br />
	  Client Reference Number:".$one_row['client_reference']."<br />
	  ";
}

function multiple_rows() {
$multiple_row = mysql_fetch_assoc($result);
echo "<table id='results_table'><tr><th>Case Number</th><th>Client Reference Number</th></tr>";
while ($multiple_row = mysql_fetch_assoc($result)) {
	echo "<tr><td>".$multiple_row['case_number']."</td><td>".$multiple_row['client_reference']."</td></tr>";
	}
}

function search_table() {
echo "
<table border='0' style='background-color:#CCCCCC'>
<tr><td>
Search by:</td>
<td>
<select name='search_id'>
";

$search_options = array ("case_number" => "Case Number", "uniqueID" => "Job ID", "defendant" => "Defendant", "plaintiff" => "Plaintiff", "addressone" => "Address");

foreach( $search_options as $search_value=>$search_variables )
{
echo "<option value=$search_value>$search_variables</option>";
}

echo "
</select>
</td>
<td><input type='text' id='search_field' name='search_value' /></td>
<td><input type='submit' name='submit' /></td>
</tr></table>
";
}

if  (isset($submit)) {
check_number_of_results();
}
elseif (!isset($submit)) {
search_table();
}
?>

bdmovies · October 11, 2007

Bump....anybody?

darkfreaks · October 11, 2007

<?php

if  ($submit=="true") {
check_number_of_results();
}
elseif ($submit=="false") {
search_table();
} ?>

or even

<?php
if  ($submit) {
check_number_of_results();
}
elseif (!$submit) {
search_table();
} ?>

bdmovies · October 11, 2007

Ok, Now I'm back to everything working EXCEPT when there are multiple row, it is always 1 short.........aaaaaahhhhhhh!!!!!! It's driving me crazy

<?php
function zero_results($query, $result, $nums, $search_value) {
echo "<span id=error_message> There are $nums results for your search. </span>";
search_table();
}

function one_result($query, $result) {
$one_row = mysql_fetch_assoc($result);
echo "Case Number: {$one_row['case_number']}<br />";
echo "Client Reference Number:".$one_row['client_reference']."<br />
	  ";
return;
}

function multiple_rows($query, $result, $nums) {
$multiple_row = mysql_fetch_assoc($result);

echo "<table id='results_table'><tr><th>Case Number</th><th>Client Reference Number</th></tr>";

while ($multiple_row = mysql_fetch_assoc($result)) {
		echo "<tr><td>{$multiple_row['case_number']}</td><td>{$multiple_row['client_reference']}</td></tr>";
}
return;
}

function search_table() {
echo "
<form action='' method='post'>
<table border='0' style='background-color:#CCCCCC'>
<tr><td>
Search by:</td>
<td>
<select name='field_name'>
";

$search_field_options = array ("case_number" => "Case Number", "uniqueID" => "Job ID", "defendant" => "Defendant", "plaintiff" => "Plaintiff", "addressone" => "Address");

foreach( $search_field_options as $db_field_name=>$pretty_display_name )
{
echo "<option value=$db_field_name>$pretty_display_name</option>";
}

echo "
</select>
</td>
<td><input type='text' id='input_field' name='input_field' /></td>
<td><input type=submit name=submit value='Search' /></td>
</tr></table>
</form>
";
return;
}

//Turn the form values into PHP variables
$field_name = $_POST['field_name'];
$input_field = $_POST['input_field'];
$submit = $_POST['submit'];

//Checks the number of results returned from $result
function check_number_of_results($field_name, $input_field, $submit) {

//Query the search options
$query = "SELECT * FROM case_information WHERE '$field_name' LIKE '$input_field%'";
$result = mysql_query($query) or die(mysql_error());

echo $field_name;
echo $input_field;
echo $submit;

$nums = mysql_num_rows($result); //Get the number of rows queried

//If there are 0 results, echo the 0 result table
if ($nums == 0) {
	zero_results($query, $result);
}

//If there is 1 result, echo the row
elseif ($nums == 1) {
	one_result($query, $result);
}

//If there are many rows, echo only a "teaser" of the row
elseif ($nums > 1) {
	multiple_rows($query, $result, $nums);
}

return;
//Closes check_number_of_results	
}


if (isset($submit)) {
check_number_of_results();
}
elseif (!isset($submit)) {
search_table();
}
?>

darkfreaks · October 11, 2007

1 row short?

bdmovies · October 11, 2007

Yes,

if I do

<?php
$nums = mysql_num_rows($result);
echo $nums;

while ($multiple_row = mysql_fetch_assoc($result)) {
echo $multiple_row['case_number'];
}
?>

$nums will display 3 and the loop only shows 2...

darkfreaks · October 11, 2007

nvm

bdmovies · October 11, 2007

Ok, so I that worked......but gosh dangit! now no matter what I do I always go to multiple_rows();

<?php

function zero_results($query, $result, $nums, $search_value) {
echo "<span id=error_message> There are $nums results for your search. </span>";
search_table();
}

function one_result($query, $result) {
$one_row = mysql_fetch_assoc($result);
echo "Case Number: {$one_row['case_number']}<br />";
echo "Client Reference Number:".$one_row['client_reference']."<br />
	  ";
return;
}

function multiple_rows($query, $result, $nums) {
$multiple_row = mysql_num_rows($result);

echo "<table id='results_table'><tr><th>Case Number</th><th>C. Ref. Number</th><th>Plaintiff</th><th>Defendant</th><th>Address</th><th>Person to be Served</th></tr>";

while ($multiple_row = mysql_fetch_assoc($result)) {
		echo "<tr><td>{$multiple_row['case_number']}</td><td>{$multiple_row['client_reference']}</td><td>{$multiple_row['plaintiff']}</td><td>{$multiple_row['defendant']}</td><td>{$multiple_row['addressone']}<br />{$multiple_row['city']}, {$multiple_row['state']} {$multiple_row['zip']}</td><td>{$multiple_row['person_to_be_served']}</tr>";
}
return;
}

function search_table() {
echo "
<form action='' method='post'>
<table border='0' style='background-color:#CCCCCC'>
<tr><td>
Search by:</td>
<td>
<select name='field_name'>
";

$search_field_options = array ("case_number" => "Case Number", "uniqueID" => "Job ID", "defendant" => "Defendant", "plaintiff" => "Plaintiff", "addressone" => "Address");

foreach( $search_field_options as $db_field_name=>$pretty_display_name )
{
echo "<option value=$db_field_name>$pretty_display_name</option>";
}

echo "
</select>
</td>
<td><input type='text' id='input_field' name='input_field' /></td>
<td><input type=submit name=submit value='Search' /></td>
</tr></table>
</form>
";
return;
}

//Turn the form values into PHP variables
$field_name = $_POST['field_name'];
$input_field = $_POST['input_field'];
$submit = $_POST['submit'];

//Checks the number of results returned from $result
function check_number_of_results($field_name, $input_field, $submit) {

//Query the search options
$query = "SELECT * FROM case_information WHERE '$field_name' LIKE '$input_field%'";
$result = mysql_query($query) or die(mysql_error());

$nums = mysql_num_rows($result); //Get the number of rows queried

//If there are 0 results, echo the 0 result table
if ($nums == 0) {
	zero_results($query, $result);
}

//If there is 1 result, echo the row
elseif ($nums == 1) {
	one_result($query, $result);
}

//If there are many rows, echo only a "teaser" of the row
elseif ($nums > 1) {
	multiple_rows($query, $result, $nums);
}

return;
//Closes check_number_of_results	
}


if (isset($submit)) {
check_number_of_results();
}
elseif (!isset($submit)) {
search_table();
}
?>

darkfreaks · October 11, 2007

???

sasa · October 11, 2007

remove 1st row from function multiple_rows()

function multiple_rows($query, $result, $nums) {
//$multiple_row = mysql_fetch_assoc($result);

echo "<table id='results_table'><tr><th>Case Number</th><th>Client Reference Number</th></tr>";

while ($multiple_row = mysql_fetch_assoc($result)) {
		echo "<tr><td>{$multiple_row['case_number']}</td><td>{$multiple_row['client_reference']}</td></tr>";
}
return;
}

bdmovies · October 11, 2007

No Dice...

bdmovies · October 11, 2007

Bump....anyone got any ideas? ???

sasa · October 11, 2007

you don't pas any value to function check_number_of_results() but in definition of it it need 3

try

if (isset($submit)) {
//check_number_of_results();
check_number_of_results($field_name, $input_field, $submit);
}

bdmovies · October 12, 2007

Nope, it's still sending me to multiple_results()

bdmovies · October 12, 2007

Bump......please, this is frusterating.... ??? I hate not being able to figure out this kind of stuff, I know it's so simple

MmmVomit · October 12, 2007

Post your current code.

bdmovies · October 13, 2007

<?php

function zero_results($query, $result, $nums, $search_value) {
echo "<span id=error_message> There are $nums results for your search. </span>";
search_table();
return;
}

function one_result($query, $result) {
$one_row = mysql_fetch_assoc($result);
echo "Case Number: {$one_row['case_number']}<br />";
echo "Client Reference Number: {$one_row['client_reference']}<br />
	  ";
return;
}

function multiple_rows($query, $result, $nums) {

echo "<table id='results_table'><tr><th>Case Number</th><th>C. Ref. Number</th><th>Plaintiff</th><th>Defendant</th><th>Address</th><th>Person to be Served</th></tr>";

while ($multiple_row = mysql_fetch_assoc($result)) {
		echo "<tr><td>{$multiple_row['case_number']}</td><td>{$multiple_row['client_reference']}</td><td>{$multiple_row['plaintiff']}</td><td>{$multiple_row['defendant']}</td><td>{$multiple_row['addressone']}<br />{$multiple_row['city']}, {$multiple_row['state']} {$multiple_row['zip']}</td><td>{$multiple_row['person_to_be_served']}</tr>";
}
return;
}

function search_table() {
echo "
<form action='' method='post' name='search_form'>
<table border='0' style='background-color:#CCCCCC'>
<tr><td>
Search by:</td>
<td>
<select name='field_name'>
";

$search_field_options = array ("case_number" => "Case Number", "uniqueID" => "Job ID", "defendant" => "Defendant", "plaintiff" => "Plaintiff", "addressone" => "Address");

foreach( $search_field_options as $db_field_name=>$pretty_display_name )
{
echo "<option value=$db_field_name>$pretty_display_name</option>";
}

echo "
</select>
</td>
<td><input type='text' id='input_field' name='input_field' /></td>
<td><input type=submit name=submit value='Search' /></td>
</tr></table>
</form>
";
return;
}

//Turn the form values into PHP variables
$field_name = $_POST['field_name'];
$input_field = $_POST['input_field'];
$submit = $_POST['submit'];

//Checks the number of results returned from $result
function check_number_of_results($field_name, $input_field, $submit) {

//Query the search options
$query = "SELECT * FROM case_information WHERE '$field_name' LIKE '$input_field%'";
$result = mysql_query($query) or die(mysql_error());

$nums = mysql_num_rows($result); //Get the number of rows queried

//If there are 0 results, echo the 0 result table
if ($nums == 0) {
	zero_results($query, $result);
}

//If there is 1 result, echo the row
elseif ($nums == 1) {
	one_result($query, $result);
}

//If there are many rows, echo only a "teaser" of the row
elseif ($nums > 1) {
	multiple_rows($query, $result, $nums);
}

return;
//Closes check_number_of_results	
}


if (isset($submit)) {
check_number_of_results($query, $result, $nums);
}
elseif (!isset($submit)) {
search_table();
}
?>

[code]

[/code]

trq · October 13, 2007

The reason your code always displays one less row than it has is because you call....

$row = mysql_fetch_assoc($result) or die(mysql_error());

outside of any of your function calls. This places the first row into the $row array and then moves the pointer to the next row waiting for the next call to mysql_fetch_assoc().

The logic of having all the different types of results be called from seperate functions really isn't all that logical and actually makes your code harder to read and harder to impliment but its up to you I guess.

bdmovies · October 13, 2007

I'm not a very experienced PHP programmer, or any kind of programmer for that matter, how do you suggest I set it up?

bdmovies · October 13, 2007

Wow, well after many late nights working on this, I've finally got it working, displaying everything it should, when it should. Thank you all for the help, and I'm sure I'll be back with more questions, I'm writing my first PHP Application.

Sign In

[SOLVED] Getting all the rows from a table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information