mysql and php count problem..

Question

i have count problem in php...

i create drop down list dynamically from mysql...and what i want is when i select the first drop down menu how can i get the search result count..like 10 products from databse..

look my code..

ajax..file

var xmlHttp

function showCount(str) { xmlHttp=GetXmlHttpObject(); if (xmlHttp==null) { alert ("Your browser does not support AJAX!"); return; } var url="phpApplication.php"; url=url+"?action=count2"; url=url+"&sid="+Math.random(); xmlHttp.onreadystatechange=stateChanged; xmlHttp.open("POST",url,true); xmlHttp.send(null); }

function stateChanged() { if (xmlHttp.readyState==4) { document.getElementById("countR").innerHTML=xmlHttp.responseText; } }

function GetXmlHttpObject() { var xmlHttp=null; try { // Firefox, Opera 8.0+, Safari xmlHttp=new XMLHttpRequest(); } catch (e) { // Internet Explorer try { xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) { xmlHttp=new ActiveXObject("Microsoft.XMLHTTP"); } } return xmlHttp; }

PHP Application

class Application {

 public function __construct () { 

} 


function conn($sql)

{

  $host      = "localhost";
  $username  = "root";
  $pwd       = "";
  $dbname    = "example";

//echo "commnecing connection to local db<br>"
if (!($conn=mysql_connect($host, $username, $pwd)))  {
    printf("error connecting to DB by user = $username and pwd=$pwd");
    exit;
}
$db3=mysql_select_db($dbname,$conn) or die("Unable to connect to local database");


$result = mysql_query($sql) or die ("Can't connect because ". mysql_error());

return $result;

}//end function

function dropdown($field, $table) { //initialize variables $oHTML = ''; $result = '';

//check to see if the field is passed correctly if (($field == "")||($table == "")) { die("No column or table specified to create drop down from!"); }

$sql = "select distinct($field) from $table";

//call the db function and run the query $result = $this->conn($sql);

//if no results are found to create a drop down return a textbox if ((!$result) ||(mysql_num_rows($result)==0)) { $oHTML .= ""; }elseif (($result)&&(mysql_num_rows($result)>0)){

//build the select box out of the results
$oHTML .= "<select name='$field' onchange='showCount(this.value)'>\n<option value='all'>All</option>\n";
while ($rows = mysql_fetch_array($result))
{
  $oHTML .= "<option value='".$rows[$field]."'>".$rows[$field]."</option>\n";

}
$oHTML .= "</select>\n";

} //send the value back to the calling code return $oHTML; }//end function

function countResult(){

$sql2 = "select * from produkt_finder_table where 1 ";

if ( $q == (!empty($_POST['Bauform_d']))&&($_POST['Bauform_d'] != 'all')) { $sql2 .= " and Bauform_d like '". addslashes($_POST['Bauform_d'])."%' "; }

if ((!empty($_POST['Beschreibung_d']))&&($_POST['Beschreibung_d'] != 'all')) { $sql2 .= " and Beschreibung_d like '". addslashes($_POST['Beschreibung_d'])."%' "; }

if ((!empty($_POST['SelectSchutzart_Cust_Bereich_d']))&&($_POST['SelectSchutzart_Cust_Bereich_d'] != 'all')) { $sql2 .= " and SelectSchutzart_Cust_Bereich_d like '". addslashes($_POST['SelectSchutzart_Cust_Bereich_d'])."%' "; }

if ((!empty($_POST['SelectErfassungswinkel_Cust_Bereich_d']))&&($_POST['SelectErfassungswinkel_Cust_Bereich_d'] != 'all')) { $sql2 .= " and SelectErfassungswinkel_Cust_Bereich_d like '". addslashes($_POST['SelectErfassungswinkel_Cust_Bereich_d'])."%' "; }

if ((!empty($_POST['ArtikelProduktgruppe_d']))&&($_POST['ArtikelProduktgruppe_d'] != 'all')) { $sql2 .= " and ArtikelProduktgruppe_d like '". addslashes($_POST['ArtikelProduktgruppe_d'])."%' "; }

$result2 = $this->conn($sql2); //run query

if (!$result2){ die("No results due to database error.
".mysql_error()); }

if (mysql_num_rows($result2)==0) { echo "0"; }else{

echo mysql_num_rows($result2);

} }

function count1(){

if ((!empty($_POST['Bauform_d']))&&($_POST['Bauform_d'] != 'all')) {

}

$sql2 .= " and Bauform_d like '". addslashes($_POST['Bauform_d'])."%' ";
 $sql2 = "select * from produkt_finder_table where 1 ";
 $result2 = $this->conn($sql2);
  if (!$result2){ die("No results due to database error.<br>".mysql_error());  }

if (mysql_num_rows($result2)==0) { echo "0"; }else{

echo mysql_num_rows($result2);

}

//run query

}

function countResultAll(){

$sql = "select * from produkt_finder_table where 1 ";

if ((!empty($_POST['searchfield']))&&($_POST['searchfield'] != 'all')) { $sql .= " and ArtikelName like '". addslashes($_POST['searchfield'])."%' "; }

$result = $this->conn($sql); //run query

if (!$result){ die("No results due to database error.
".mysql_error()); }

if (mysql_num_rows($result)==0) { echo "0"; }else{

echo mysql_num_rows($result);

} }

function search() {

//base sql $sql = "select * from produkt_finder_table where 1 ";

//get the values from the form

if ((!empty($_POST['Bauform_d']))&&($_POST['Bauform_d'] != 'all')) { $sql .= " and Bauform_d like '". addslashes($_POST['Bauform_d'])."%' "; }

if ((!empty($_POST['Beschreibung_d']))&&($_POST['Beschreibung_d'] != 'all')) { $sql .= " and Beschreibung_d like '". addslashes($_POST['Beschreibung_d'])."%' "; }

if ((!empty($_POST['SelectSchutzart_Cust_Bereich_d']))&&($_POST['SelectSchutzart_Cust_Bereich_d'] != 'all')) { $sql .= " and SelectSchutzart_Cust_Bereich_d like '". addslashes($_POST['SelectSchutzart_Cust_Bereich_d'])."%' "; }

if ((!empty($_POST['SelectErfassungswinkel_Cust_Bereich_d']))&&($_POST['SelectErfassungswinkel_Cust_Bereich_d'] != 'all')) { $sql .= " and SelectErfassungswinkel_Cust_Bereich_d like '". addslashes($_POST['SelectErfassungswinkel_Cust_Bereich_d'])."%' "; }

if ((!empty($_POST['ArtikelProduktgruppe_d']))&&($_POST['ArtikelProduktgruppe_d'] != 'all')) { $sql .= " and ArtikelProduktgruppe_d like '". addslashes($_POST['ArtikelProduktgruppe_d'])."%' "; }

//run query $result = $this->conn($sql);

if (!$result){ die("No results due to database error.
".mysql_error()); }

if (mysql_num_rows($result)==0) { echo "No Results found!"; }else{

 echo "<table border='1' class='corner'><th width='332'><a href='product.php'>Back</a></th>";
 echo "<th>Product Result</th>";
 echo "<td width='40' align='center' style=' font-size:16px'><div id='countR'>";
 $this->countResult();
 echo "</div></td>";
    echo "</table>";

// echo '<div style="float:right; font-size:16px">'.$this->countResultAll().'</div>';
// echo "<div style='float:left; font-size:16px'>Produkt Result</div>";

echo "<table border='1' class='corner'>"; 

 $i = 1;
if((mysql_num_rows($result) % 2) == 0){
$complete = false;
 }else{
$complete = true;
 }

while ($rows= mysql_fetch_array($result))
{

  echo "<td>";
  echo "<ul><li class='addResult'><span class='red'>".$rows['Bauform_d']."<br /><span class='black'>".$rows['ArtikelName']."</span></span><img src='{imag}' alt='' /> <a href='#'>More Info</a> </li></ul>";
  echo "</td>";

if(($i % 2) == 0){

   echo "</tr><tr>";
}   

  $i++;

} if($complete == true){

echo "<td>&nbsp;</td>";

}

echo "</tr></table>";
  echo "<table border='1' class='corner'><th width='332'><a href='product.php'>Back</a> <div></div></th>";
 echo "<th>Product Result</th>";
 echo "<td width='40' align='center' style=' font-size:16px'>";
 $this->countResult();
 echo "</td>";
    echo "</table>";

} }

function show_form() {

$Bauform = $this->dropdown('Bauform_d','produkt_finder_table'); $Beschreibung = $this->dropdown('ArtikelGruppe_d','produkt_finder_table'); $SelectSchutzart = $this->dropdown('SelectSchutzart_Cust_Bereich_d','produkt_finder_table'); $SelectErfassungswinkel = $this->dropdown('SelectErfassungswinkel_Cust_Bereich_d','produkt_finder_table'); $ArtikelProduktgruppe = $this->dropdown('ArtikelProduktgruppe_d','produkt_finder_table');

echo " Search Form Count Montageart:$Bauform ProduktGruppe:$Beschreibung Speisung:$SelectSchutzart Reichweite: $SelectErfassungswinkel Erfwinkel:$ArtikelProduktgruppe

    <tr>
       <td colspan='2' align='center'><input type='submit' name='submit' value='Go!'></td>
    </tr>
    </table>
    </form>";

}//end function

function searchAll() {

//base sql $sql = "select * from produkt_finder_table where 1 ";

//get the values from the form //NOTE: You should do way more valdation on the values before you attempt to process anything

if ((!empty($_POST['searchfield']))&&($_POST['searchfield'] != 'all')) { $sql .= " and ArtikelName like '". addslashes($_POST['searchfield'])."%' "; }

//run query $result = $this->conn($sql);

if (!$result){ die("No results due to database error.
".mysql_error()); }

if (mysql_num_rows($result)==0) { echo "No Results found!"; }else{

 echo "<table border='1' class='corner'><th width='332'><a href='product.php'>Back</a></th>";
 echo "<th>Product Result</th>";
 echo "<td width='40' align='center' style=' font-size:16px'><div id='countR'>";
 $this->countResultAll();
 echo "</div></td>";
 echo "</table>";

// echo '<div style="float:right; font-size:16px">'.$this->countResultAll().'</div>';
// echo "<div style='float:left; font-size:16px'>Produkt Result</div>";

echo "<table border='1' class='corner'>"; 

 $i = 1;
if((mysql_num_rows($result) % 2) == 0){
$complete = false;
 }else{
$complete = true;
 }

while ($rows= mysql_fetch_array($result))
{

  echo "<td>";
  echo "<ul><li class='addResult'><span class='red'>".$rows['Bauform_d']."<br /><span class='black'>".$rows['ArtikelName']."</span></span><img src='{imag}' alt='' /> <a href='#'>More Info</a> </li></ul>";
  echo "</td>";

if(($i % 2) == 0){

   echo "</tr><tr>";
}   

  $i++;

} if($complete == true){

echo "<td>&nbsp;</td>";

}
echo ""; echo "Back "; echo "Product Result"; echo ""; $this->countResultAll(); echo ""; echo "";

} } }

$action = $_GET['action'];

$proFin = new Application();

switch($action) {

 case 'show':
        $proFin->show_form();
    break;

    case 'search':
        $proFin->search();
    break; 

    case 'searchAll':
        $proFin->searchAll();
    break; 

 case 'count':
        $proFin->countResult();
    break; 

 case 'count1':
        print 'hallo';
    break; 

 case 'count2':

  if (!empty($_POST))

{

    var_dump($_POST);

}

    break; 

 case 'count3':
        echo $rows[$field];
    break; 
    }

Answer 1

I've tested this on apache with firefox and "empty" is replaced with "23".

test.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head>
<title>Test ajax</title>
</head>
<script language="javascript">
var xmlHttp

function showCount(str)
{
 xmlHttp=GetXmlHttpObject();
 if (xmlHttp==null)
  {
   alert ("Your browser does not support AJAX!");
   return;
  }
 var url="testajax.php";
 url=url+"?action=count2";
 url=url+"&sid="+Math.random();
 xmlHttp.onreadystatechange=stateChanged;
 xmlHttp.open("POST",url,true);
 xmlHttp.send(null);
}

function stateChanged()
{
 if (xmlHttp.readyState==4)
 {
  document.getElementById("countR").innerHTML=xmlHttp.responseText;
 }
}

function GetXmlHttpObject()
{
 var xmlHttp=null;
 try
  {
   // Firefox, Opera 8.0+, Safari
   xmlHttp=new XMLHttpRequest();
  }
 catch (e)
  {
   // Internet Explorer
   try
     {
      xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
     }
   catch (e)
     {
      xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
 return xmlHttp;
}
</script>
<body bgcolor="#FFFFFF" text="#000000" link="#0000FF" vlink="#000080" alink="#FF0000" onload="showCount( 'heelo' )" >
<span id="countR">empty</span>
</body>
</html>

testajax.php

<?

if( $_REQUEST[ 'action' ] == 'count2' )
{
    header( 'Content-type: text/xml' );
    echo <<<XMLTOP
<?xml version="1.0" encoding="UTF-8"?>

XMLTOP;
        echo( '<countr>23</countr>' );
}
?>

// for the mysql row counting in mysql use You could use this to get the count and put as the first row.

$query = "SELECT SQL_CALC_FOUND_ROWS ...";

$result = mysql_query( $query ) or LogFatalError( "Database error: " . mysql_error() );
$count  = mysql_result( mysql_query( 'SELECT FOUND_ROWS()' ), 0, 0 );