1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 9:46 am May 1, 2026

Forum Moderators: coopster

Message Too Old, No Replies

MySQL and php switch

         

var123

9:20 pm on Jan 22, 2009 (gmt 0)

10+ Year Member



Hey, I have a page that performs an SQL query and it works fine. I want the person viewing the page to be able to switch the view of the info being displayed.

For example:
The default will display bedrooms, baths, price, address, and city from the search.

Then if they hit a button, the page will display all the same info, but more info such as commission and contact info.

Would a switch statement be the best way to go about doing this? I tried to do it, but didn't get any of my sql results in the default page. So I removed the switch and put the code back to normal.

Here is my code:

<?
if ($numberOfRows==0) {
?>

Sorry. No records found !

<?
}
else if ($numberOfRows>0){

$i=0;
?>

<body>
<TABLE CELLSPACING="0" CELLPADDING="3" BORDER="1" WIDTH="100%">
<TR>
<TD><B>BR</B></TD>
<TD><B>BA</B></TD>
<TD><B>Listing Price</B></TD>
<TD><B>Address</B></TD>
<TD><B>City</B></TD>
</TR>

<?

while ($i<$numberOfRows)
{

if (($i%2)==0) { $bgColor = "#FFFFFF"; } else { $bgColor = "#C0C0C0"; }

$bed = MYSQL_RESULT($result,$i,"bed");
$bath = MYSQL_RESULT($result,$i,"bath");
$price = MYSQL_RESULT($result,$i,"price");
$address = MYSQL_RESULT($result,$i,"address");
$city = MYSQL_RESULT($result,$i,"city");
$baseComm = MYSQL_RESULT($result,$i,"price") * .03;
$agentFname = MYSQL_RESULT($result,$i,"listingAgentFname");
$agentLname = MYSQL_RESULT($result,$i,"listingAgentLname");
$agentPhone = MYSQL_RESULT($result,$i,"listingAgentPhone");
$agentName = $agentFname ." " . $agentLname;
?>

<TR BGCOLOR="<? echo $bgColor; ?>">
<TD nowrap><? echo $bed; ?></TD>
<TD nowrap><? echo $bath; ?></TD>
<TD nowrap><? echo $price; ?></TD>
<TD nowrap><? echo $address; ?></TD>
<TD nowrap><? echo $city; ?></TD>
</TR>

<?
$i++;

} // end while
?>
</TABLE>

<?
} // end of if numberOfRows > 0
?>
</body>

Any ideas or help would be greatly appreciated, Thanks.

Shores

3:53 pm on Jan 26, 2009 (gmt 0)

10+ Year Member



You should use a querystring parameter to turn on the extended info.

Something like that:

www.yoursite.com/page.php?extended=on

and in the code:
<TD nowrap><? echo $address; ?></TD>
<TD nowrap><? echo $city; ?></TD>
<?
if ($_GET["extended"]=="on"):

?>
<TD nowrap><? echo $extendedinfo1; ?></TD>
<TD nowrap><? echo $extendedinfo2; ?></TD>
<?endif;?>
</TR>

Bye!

var123

5:26 am on Jan 27, 2009 (gmt 0)

10+ Year Member



Thank you Shores for your reply. I think that just might work, but unfortunately when I tried it I got the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Any ideas on how to fix that?

Maybe I messed up something, but I made a button to turn "extended" on like this:

<a href="page.php?extended=on">Extended View</a>

So I'm not sure if that's the correct way to do it, please let me know.

Thanks

Shores

9:42 am on Jan 27, 2009 (gmt 0)

10+ Year Member



Your button code is ok, the error message means that the parameter you passed to the mysql_num_rows function is not the result of a call to mysql_query, or the query executed by the mysql_query function has SQL syntax errors in it.

Bye!

var123

5:41 pm on Jan 27, 2009 (gmt 0)

10+ Year Member



thanks again for the quick response Shores. Here is the query part of my code, would you mind taking a look?

$pt = $_POST['propType'];
$c = $_POST['city'];
$b = $_POST['bed'];
$pt2 = "'". $pt ."'";
$c2 = "'". $c ."'";

$orderByQuery = " ORDER BY price DESC";

$sql = "SELECT * FROM db_table WHERE propType= (" . $pt2 . ") AND city= (" . $c2 . ") AND bed= " . $b . " AND price BETWEEN " . $_POST['priceMin'] . " AND " . $_POST['priceMax'] . $orderByQuery;

$result = MYSQL_QUERY($sql);
$numberOfRows = MYSQL_NUM_ROWS($result);

Any ideas? It works fine until I hit the button for the extended view.

Thanks again for all your help

Shores

3:38 pm on Jan 29, 2009 (gmt 0)

10+ Year Member



It seems nothing changes in your query when extended=on... Are you sure that the query pull from mysql all the fields you need? It seems to me that the query is ok, the only way i could see an error appear is if you're trying to read a field that doesn't exist in the result set...

Also verify that nothing messes with you $result variable, and remember that the correct syntax for all php functions is all lowercase.

var123

1:13 am on Jan 30, 2009 (gmt 0)

10+ Year Member



Thank for getting back to me. I shouldn't have any errors with the fields. I removed the if statement and had all of the data go to the table no prob. Thanks for letting me know about the syntax, i fixed it. Was hoping that the syntax would be the error, but it wasn't. I am out of ideas lol.

Shores

12:35 pm on Jan 30, 2009 (gmt 0)

10+ Year Member



Could you please post the full page source?

var123

4:55 am on Feb 3, 2009 (gmt 0)

10+ Year Member



hey sorry for not getting back sooner, I am in the process of moving. Here is the full page source:

<?
$orderByQuery = " ORDER BY price DESC";

$pt = $_POST['propType'];
$c = $_POST['city'];
$b = $_POST['bed'];
$pt2 = "'". $pt ."'";
$c2 = "'". $c ."'";

$sql1 = "SELECT * FROM priorityListingsSub WHERE propType= (" . $pt2 . ") AND city= (" . $c2 . ") AND bed= " . $b . " AND price BETWEEN " . $_POST['priceMin'] . " AND " . $_POST['priceMax'] . " AND bonusType='SU' " . $orderByQuery;

$result1 = mysql_query($sql1);
$numberOfRows1 = mysql_num_rows($result1);

$sql2 = "SELECT * FROM priorityListingsSub WHERE propType= (" . $pt2 . ") AND city= (" . $c2 . ") AND bed= " . $b . " AND price BETWEEN " . $_POST['priceMin'] . " AND " . $_POST['priceMax'] . " AND bonusType='BO' " . $orderByQuery;

$result2 = mysql_query($sql2);
$numberOfRows2 = mysql_num_rows($result2);

if ($numberOfRows1 ==0) {
?>

Sorry. No records found !

<?
}
else if ($numberOfRows1 >0){

$i=0;
?>

<html>
<body>
<TABLE CELLSPACING="0" CELLPADDING="3" BORDER="1" WIDTH="100%">

<TR>
<TD><B>Listing #</B></TD>
<TD><B>BR</B></TD>
<TD><B>BA</B></TD>
<TD><B>Listing Price</B></TD>
<TD><B>Complex</B></TD>
<TD><B>Address</B></TD>
<TD><B>City</B></TD>
<?php if ("aView1"== "on"): ?>
<TD><B>Comm</B></TD>
<TD><B>Bonus</B></TD>
<TD><B>Total Comm</B></TD>
<?php endif;?>
<TD><B>Details</B></TD>
<TD><B>Add</B></TD>
</TR>


<?php

while ($i< $numberOfRows1)
{

if (($i%2)==0) { $bgColor = "#FFFFFF"; } else { $bgColor = "#C0C0C0"; }

$listingNum = mysql_result($result1,$i,"bonusType") . mysql_result($result1,$i,"propID");
$bed = mysql_result($result1,$i,"bed");
$bath = mysql_result($result1,$i,"bath");
$price = mysql_result($result1,$i,"price");
$complex = mysql_result($result1,$i,"propType");
$address = mysql_result($result1,$i,"address");
$city = mysql_result($result1,$i,"city");
$baseComm = mysql_result($result1,$i,"price") * .03;
$bonus = mysql_result($result1,$i,"price") * .02;
$total = $baseComm + $bonus;
$bonusType = mysql_result($result1,$i,"bonusType");
$add = '<input type ="checkbox" name="cb[]" value="">';
$agentFname = mysql_result($result1,$i,"listingAgentFname");
$agentLname = mysql_result($result1,$i,"listingAgentLname");
$agentPhone = mysql_result($result1,$i,"listingAgentPhone");
$agentName = $agentFname ." " . $agentLname;
$details = '<a href="details.pdf">Details</a>';

?>

<TR BGCOLOR="<?php echo $bgColor; ?>">
<TD nowrap><?php echo $listingNum; ?></TD>
<TD nowrap><?php echo $bed; ?></TD>
<TD nowrap><? echo $bath; ?></TD>
<TD nowrap>$<?php echo number_format($price, 0, '', ','); ?></TD>
<TD nowrap><?php echo $complex; ?></TD>
<TD nowrap><?php echo $address; ?></TD>
<TD nowrap><?php echo str_replace("_"," ",$city); ?></TD>
<?php if ("aView1"== "on"): ?>
<TD nowrap>$<?php echo number_format($baseComm, 2, '.', ','); ?></TD>
<TD nowrap>$<?php echo number_format($bonus, 2, '.', ','); ?></TD>
<TD nowrap>$<?php echo number_format($total, 2, '.', ','); ?></TD>
<?php endif; ?>

<TD nowrap><?php echo $details; ?></TD>
<TD nowrap><?php echo $add; ?></TD>
</TR>

<?php
$i++;

} // end while
?>
</TABLE>

<?php
} // end of if numberOfRows > 0
?>

<?php
// Start of the "Bonus" Listings

if ($numberOfRows2 ==0) {
?>

Sorry. No records found !

<?
}
else if ($numberOfRows2 >0){

$j=0;
?>

<TABLE CELLSPACING="0" CELLPADDING="3" BORDER="1" WIDTH="100%">
<TR>
<TD><B>Listing #</B></TD>
<TD><B>BR</B></TD>
<TD><B>BA</B></TD>
<TD><B>Listing Price</B></TD>
<TD><B>Complex</B></TD>
<TD><B>Address</B></TD>
<TD><B>City</B></TD>
<?php //if ("aView1"== "on"): ?>
<TD><B>Comm</B></TD>
<TD><B>Bonus</B></TD>
<TD><B>Total Comm</B></TD>
<?php //endif; ?>
<TD><B>Details</B></TD>
<TD><B>Add</B></TD>
</TR>

<?php

while ($j< $numberOfRows2)
{

if (($j%2)==0) { $bgColor = "#FFFFFF"; } else { $bgColor = "#C0C0C0"; }

$listingNum = mysql_result($result2,$j,"bonusType") . mysql_result($result2,$j,"propID");
$bed = mysql_result($result2,$j,"bed");
$bath = mysql_result($result2,$j,"bath");
$price = mysql_result($result2,$j,"price");
$complex = mysql_result($result2,$j,"propType");
$address = mysql_result($result2,$j,"address");
$city = mysql_result($result2,$j,"city");
$baseComm = mysql_result($result2,$j,"price") * .03;
$bonus = mysql_result($result2,$j,"price") * .02;
$total = $baseComm + $bonus;
$bonusType = mysql_result($result2,$j,"bonusType");
$add = '<input type ="checkbox" name="cb['.$i.']" value='.$i.'">';
$agentFname = mysql_result($result2,$j,"listingAgentFname");
$agentLname = mysql_result($result2,$j,"listingAgentLname");
$agentPhone = mysql_result($result2,$j,"listingAgentPhone");
$agentName = $agentFname ." " . $agentLname;
$details = '<a href="details.pdf">Details</a>';

?>

<TR BGCOLOR="<?php echo $bgColor; ?>">
<TD nowrap><?php echo $listingNum; ?></TD>
<TD nowrap><?php echo $bed; ?></TD>
<TD nowrap><? echo $bath; ?></TD>
<TD nowrap>$<?php echo number_format($price, 0, '', ','); ?></TD>
<TD nowrap><?php echo $complex; ?></TD>
<TD nowrap><?php echo $address; ?></TD>
<TD nowrap><?php echo str_replace("_"," ",$city); ?></TD>
<?php //if ("aView1"== "on"): ?>
<TD nowrap>$<?php echo number_format($baseComm, 2, '.', ','); ?></TD>
<TD nowrap>$<?php echo number_format($bonus, 2, '.', ','); ?></TD>
<TD nowrap>$<?php echo number_format($total, 2, '.', ','); ?></TD>
<?php //endif; ?>

<TD nowrap><?php echo $details; ?></TD>
<TD nowrap><?php echo $add; ?></TD>
</TR>

<?php
$j++;

} // end while
?>
</TABLE>

<?php
} // end of if numberOfRows > 0
?>

<div id="view"><a href="works4.php?aView1=on">Agent View</a></div>

</body>
</html>

and thats it.

Shores

10:18 am on Feb 3, 2009 (gmt 0)

10+ Year Member



The first error i see is that you should use $_GET["aView1"] to access the parameter on the url, and not "aView1".

That said, you should NOT use so extensively mysql_result: that is a very slow function which should be used when you need to retrieve only one "cell" (one field of one record) of a big result set without using the rest of the results.

The classical way of reading results is this: 


$result=mysql_query("here the sql query");
while ($record=mysql_fetch_assoc($result)):
echo $record["fieldname1"]
endwhile;

As you see, you don't need to know in advance how many records are there, and since you code shows anyway the full result set, why reading the number of rows? Also, the function mysql_fetch_assoc returns an array keyed on field names, and that means that you don' have to create temporary intermediate variables such as you do in your code, you could just do an echo $record["fieldname"]!

That said, try this code: 


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>
</title>
</head>
<body>
<?
$orderByQuery = " ORDER BY price DESC";
$pt = $_POST['propType'];
$c = $_POST['city'];
$b = $_POST['bed'];
$pt2 = "'". $pt ."'";
$c2 = "'". $c ."'";
$sql1 = "SELECT * FROM priorityListingsSub WHERE propType= (" . $pt2 . ") AND city= (" . $c2 . ") AND bed= " . $b . " AND price BETWEEN " . $_POST['priceMin'] . " AND " . $_POST['priceMax'] . " AND bonusType='SU' " . $orderByQuery;
$result1 = mysql_query($sql1);
$numberOfRows1 = mysql_num_rows($result1);
$sql2 = "SELECT * FROM priorityListingsSub WHERE propType= (" . $pt2 . ") AND city= (" . $c2 . ") AND bed= " . $b . " AND price BETWEEN " . $_POST['priceMin'] . " AND " . $_POST['priceMax'] . " AND bonusType='BO' " . $orderByQuery;
$result2 = mysql_query($sql2);
$numberOfRows2 = mysql_num_rows($result2);
if ($numberOfRows1 ==0) {
?>Sorry. No records found !
<?
}
else if ($numberOfRows1 >0){
$i=0;
?>
<TABLE CELLSPACING="0" CELLPADDING="3" BORDER="1" WIDTH="100%">
<TR><TD><B>Listing #</B></TD><TD><B>BR</B></TD><TD><B>BA</B></TD><TD><B>Listing Price</B></TD><TD><B>Complex</B></TD><TD><B>Address</B></TD><TD><B>City</B></TD>
<?php if ($_GET["aView1"]== "on"): ?><TD><B>Comm</B></TD><TD><B>Bonus</B></TD><TD><B>Total Comm</B></TD>
<?php endif;?><TD><B>Details</B></TD><TD><B>Add</B></TD>
</TR>
<?php
while ($i< $numberOfRows1)
{
if (($i%2)==0) { $bgColor = "#FFFFFF"; } else { $bgColor = "#C0C0C0"; }
$listingNum = mysql_result($result1,$i,"bonusType") . mysql_result($result1,$i,"propID");
$bed = mysql_result($result1,$i,"bed");
$bath = mysql_result($result1,$i,"bath");
$price = mysql_result($result1,$i,"price");
$complex = mysql_result($result1,$i,"propType");
$address = mysql_result($result1,$i,"address");
$city = mysql_result($result1,$i,"city");
$baseComm = mysql_result($result1,$i,"price") * .03;
$bonus = mysql_result($result1,$i,"price") * .02;
$total = $baseComm + $bonus;
$bonusType = mysql_result($result1,$i,"bonusType");
$add = '<input type ="checkbox" name="cb[]" value="">';
$agentFname = mysql_result($result1,$i,"listingAgentFname");
$agentLname = mysql_result($result1,$i,"listingAgentLname");
$agentPhone = mysql_result($result1,$i,"listingAgentPhone");
$agentName = $agentFname ." " . $agentLname;
$details = '<a href="details.pdf">Details</a>';
?>
<TR BGCOLOR="<?php echo $bgColor; ?>">
<TD nowrap>
<?php echo $listingNum; ?></TD>
<TD nowrap>
<?php echo $bed; ?></TD>
<TD nowrap>
<? echo $bath; ?></TD>
<TD nowrap>$
<?php echo number_format($price, 0, '', ','); ?></TD>
<TD nowrap>
<?php echo $complex; ?></TD>
<TD nowrap>
<?php echo $address; ?></TD>
<TD nowrap>
<?php echo str_replace("_"," ",$city); ?></TD>
<?php if ($_GET["aView1"]== "on"): ?>
<TD nowrap>$
<?php echo number_format($baseComm, 2, '.', ','); ?></TD>
<TD nowrap>$
<?php echo number_format($bonus, 2, '.', ','); ?></TD>
<TD nowrap>$
<?php echo number_format($total, 2, '.', ','); ?></TD>
<?php endif; ?>
<TD nowrap>
<?php echo $details; ?></TD>
<TD nowrap>
<?php echo $add; ?></TD>
</TR>
<?php
$i++;
} // end while
?>
</TABLE>
<?php
} // end of if numberOfRows > 0
?>
<?php
// Start of the "Bonus" Listings
if ($numberOfRows2 ==0) {
?>Sorry. No records found !
<?
}
else if ($numberOfRows2 >0){
$j=0;
?>
<TABLE CELLSPACING="0" CELLPADDING="3" BORDER="1" WIDTH="100%">
<TR><TD><B>Listing #</B></TD><TD><B>BR</B></TD><TD><B>BA</B></TD><TD><B>Listing Price</B></TD><TD><B>Complex</B></TD><TD><B>Address</B></TD><TD><B>City</B></TD>
<?php //if ($_GET["aView1"]== "on"): ?><TD><B>Comm</B></TD><TD><B>Bonus</B></TD><TD><B>Total Comm</B></TD>
<?php //endif; ?><TD><B>Details</B></TD><TD><B>Add</B></TD>
</TR>
<?php
while ($j< $numberOfRows2)
{
if (($j%2)==0) { $bgColor = "#FFFFFF"; } else { $bgColor = "#C0C0C0"; }
$listingNum = mysql_result($result2,$j,"bonusType") . mysql_result($result2,$j,"propID");
$bed = mysql_result($result2,$j,"bed");
$bath = mysql_result($result2,$j,"bath");
$price = mysql_result($result2,$j,"price");
$complex = mysql_result($result2,$j,"propType");
$address = mysql_result($result2,$j,"address");
$city = mysql_result($result2,$j,"city");
$baseComm = mysql_result($result2,$j,"price") * .03;
$bonus = mysql_result($result2,$j,"price") * .02;
$total = $baseComm + $bonus;
$bonusType = mysql_result($result2,$j,"bonusType");
$add = '<input type ="checkbox" name="cb['.$i.']" value='.$i.'">';
$agentFname = mysql_result($result2,$j,"listingAgentFname");
$agentLname = mysql_result($result2,$j,"listingAgentLname");
$agentPhone = mysql_result($result2,$j,"listingAgentPhone");
$agentName = $agentFname ." " . $agentLname;
$details = '<a href="details.pdf">Details</a>';
?>
<TR BGCOLOR="<?php echo $bgColor; ?>">
<TD nowrap>
<?php echo $listingNum; ?></TD>
<TD nowrap>
<?php echo $bed; ?></TD>
<TD nowrap>
<? echo $bath; ?></TD>
<TD nowrap>$
<?php echo number_format($price, 0, '', ','); ?></TD>
<TD nowrap>
<?php echo $complex; ?></TD>
<TD nowrap>
<?php echo $address; ?></TD>
<TD nowrap>
<?php echo str_replace("_"," ",$city); ?></TD>
<?php //if ($_GET["aView1"]== "on"): ?>
<TD nowrap>$
<?php echo number_format($baseComm, 2, '.', ','); ?></TD>
<TD nowrap>$
<?php echo number_format($bonus, 2, '.', ','); ?></TD>
<TD nowrap>$
<?php echo number_format($total, 2, '.', ','); ?></TD>
<?php //endif; ?>
<TD nowrap>
<?php echo $details; ?></TD>
<TD nowrap>
<?php echo $add; ?></TD>
</TR>
<?php
$j++;
} // end while
?>
</TABLE>
<?php
} // end of if numberOfRows > 0
?>
<div id="view">
<a href="works4.php?aView1=on">Agent View</a>
</div>
</body>
</html>
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 9:46 am May 1, 2026