Topic: mysql php result with ajax (xajax)

Hi.
I been for about a week trying to find a script, testing several to use ajax to display my mysql result with ajax so the page dont have to be refreshed to display the result. However I have not been able with any as my javascript knowledge is minimum. I need a script that handle the whole mysql query, not only whats in the form as in the form only the dates are choosen, (id is declared in the script) and in the mysql result there are many vars to be displayed.
Just found this xajax, didnt know about it at all. What I see strange is that I cant see any movement from the xajax team since 2010...as if its is an abandoned project???
Anyway can this be done in xajax?
My mysql query that I display with php is rather complex and I will post the code (shortened where the ifs are similar).
Any help is appreciated:

    <?php
        include("conex/conexion.php");
if ($_POST['enviar']) {
 $llegada = $month."-".$day;
  $salida = $month2."-".$day2;
$monthyear = explode('-', $month); 
$arrival_display = $day .' '. $monthyear[1] .' '. $monthyear[0]; 
$monthyear2 = explode('-', $month2); 
$departure_display = $day2 .' '. $monthyear2[1] .' '. $monthyear2[0]; 
$arrival_display = ltrim($arrival_display, "0"); 
$arrival_display = str_replace(" 01", " Jan", $arrival_display); 
$arrival_display = str_replace(" 02", " Feb", $arrival_display); 
etc
$departure_display = ltrim($departure_display, "0"); 
$departure_display = str_replace(" 01", " Jan", $departure_display); 
$departure_display = str_replace(" 02", " Feb", $departure_display); 
etc 
if ($salida > $llegada){

//miramos si disponible
$result = mysql_query ("SELECT DATE_FORMAT(llegada, '%e %b %Y') as lleg, DATE_FORMAT(salida, '%e %b %Y') as sal,tiporeserva, propiedad, TO_DAYS('$salida') - TO_DAYS('$llegada') as dias from bookings where ( propiedad = '$propiedad' )
AND (('$llegada' BETWEEN llegada AND date_sub(salida, interval +1 day)) 
or ('$salida' BETWEEN date_sub(llegada, interval -1 day) AND salida) or (llegada <= '$llegada' AND salida >= '$salida') or (llegada >= '$llegada' AND salida <= '$salida'))", $dbh);
if ($row = mysql_fetch_object($result)) {
if ($row->tiporeserva=="larga"){ echo "This property is available only for long term rentals, consult us for a price."; } 
else { 
do{
$propiedad = str_replace("_", " ", $propiedad); 
echo "<span class='red'>$propiedad is occupied from ". $row->lleg ." until ". $row->sal ."</span><br>"; 
$availability="<span class='red'>No, $propiedad is not available from $arrival_display to $departure_display.</span>"; 
echo "</table> \n"; 
} while ($row = mysql_fetch_object($result));
} 
}
else {

$sql = "SELECT llegada, salida, propiedad, TO_DAYS('$salida') - TO_DAYS('$llegada') as dias from bookings where ( propiedad = '$propiedad' ) AND ((llegada = '$salida') OR (salida = '$llegada'))"; 
$rows = mysql_fetch_assoc(mysql_query($sql)); 
$dias=$rows['dias'];
if ($salida==$rows['llegada'])
{  $availability= "<span class=\"red\">For dates $arrival_display to $departure_display, consultation is necessary as there is a booking arriving the same day as your
departure date.</span>"; 
if ($dias<7)
{
$half_round="Consultation is necessary as prices are based on 7 nights."; }
 
else {

// fin mirar si disponible
$result = mysql_query ("SELECT 
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' BETWEEN nov_inicio AND nov_fin) 
THEN sum(novbr) * (TO_DAYS('$salida') - TO_DAYS('$llegada')) ELSE NULL END) AS price,
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' > nov_fin) 
THEN sum(novbr) * (TO_DAYS(dec_inicio) - TO_DAYS('$llegada'))ELSE NULL END) AS price1,
etc.

FROM precios where precios.id_propiedad = '$propiedad'
", $dbh);

while ($row = mysql_fetch_array($result)){
$price=$row["price"]; 
$price1=$row["price1"];
$price2=$row["price2"]; 
$price3=$row["price3"]; 
etc
} 

$total=$price+$price1+etc;
$half_round = round(($total*2), 0)/2;

}} 
elseif ($llegada==$rows['salida'])
{  $availability= "<span class=\"red\">For dates $arrival_display to $departure_display, consultation is necessary as there is a booking leaving the same day as your
arrival date.</span>"; 
if ($dias<7)
{
$half_round="Consultation is necessary as prices are based on 7 nights."; }
 
else {

// fin mirar si disponible
$result = mysql_query ("SELECT 
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' BETWEEN nov_inicio AND nov_fin) 
THEN sum(novbr) * (TO_DAYS('$salida') - TO_DAYS('$llegada')) ELSE NULL END) AS price,
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' > nov_fin) 
THEN sum(novbr) * (TO_DAYS(dec_inicio) - TO_DAYS('$llegada'))ELSE NULL END) AS price1,
etc

FROM precios where precios.id_propiedad = '$propiedad'
", $dbh);

while ($row = mysql_fetch_array($result)){
$price=$row["price"]; 
$price1=$row["price1"];
$price2=$row["price2"]; 
$price3=$row["price3"]; 
etc
} 

$total=$price+$price1+etc.;
$half_round = round(($total*2), 0)/2;

}}

else { 
$sqls = "SELECT TO_DAYS('$salida') - TO_DAYS('$llegada') as dias from bookings "; 
$rowss = mysql_fetch_assoc(mysql_query($sqls)); 
$dias=$rowss['dias'];
$propiedad = str_replace("_", " ", $propiedad);
$availability="<span class=\"red\">Yes, $propiedad is available from $arrival_display to $departure_display.</span>";
$propiedad = str_replace(" ", "_", $propiedad);
if ($dias<7)
{
$half_round="Consultation is necessary as prices are based on 7 nights."; }
 
else {

// fin mirar si disponible
// cambiar según semana santa
$result = mysql_query ("SELECT 
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' BETWEEN nov_inicio AND nov_fin) 
THEN sum(novbr) * (TO_DAYS('$salida') - TO_DAYS('$llegada')) ELSE NULL END) AS price,
(CASE WHEN ('$llegada' BETWEEN nov_inicio AND nov_fin and '$salida' > nov_fin) 
THEN sum(novbr) * (TO_DAYS(dec_inicio) - TO_DAYS('$llegada'))ELSE NULL END) AS price1,
etc.

FROM precios where precios.id_propiedad = '$propiedad'
", $dbh);

while ($row = mysql_fetch_array($result)){
$price=$row["price"]; 
$price1=$row["price1"];
$price2=$row["price2"]; 
etc
} 

$total=$price+$price1+etc.;
$half_round = round(($total*2), 0)/2;

}
}
}
  }//end if salida mayor
 else {echo "<span class=\"red\">There is an error with the dates please try again, arrival: $arrival_display  and departure: $departure_display </span>";}
}
?> 
            <?php
mysql_close($dbh);
echo "<p class=\"resulttext\">Available: $availability<br>Price in euros: <span class=\"red\">$half_round</span><br>Nights: <span class=\"red\">$dias</span></p>";
?>

2 (edited by romacron 2011-11-27 9:42:55 PM)

Re: mysql php result with ajax (xajax)

Hello Helenp

i don't read your query(for xajax the query is not important).

PHP: make sure that you have es query result an array or an object
if you want to display the whole query write an loop

PHP:

$objResponse= new xajaxResponse();

$responseHtmlString=""
foreach($queryResults as $resultLine){

$responseHtmlString.= .....something from your query such as " id is: ".$resultLine->id." content of the table line ".$resultLine->content;

}
$objResponse->assign('whereever_you_want_html-id','innerHTML',$responseHtmlString);

have a look in the full xajax package with testing folder. There are many examples.
For Xajax (normal use cases) you don't need js- knowledge

3 (edited by helenp 2011-11-28 11:44:16 AM)

Re: mysql php result with ajax (xajax)

Hi,
Thanks, I am trying and checked all tests etc.
I have php 5.2, which do you recomend to use the beta version or the version 5?

Well the way I have is a php/html page wich have the php code and form included.
In the incleded php page I have added your code and tried to add my information (some as are many smile)
However I get this error: Fatal error: Class 'xajaxResponse' not found in /home/javiercd/public_html/calculator6.php on line 3

Also to get the queries that are on the included php file in the form the dates must be choosen, so the query is executed with a form, so I dont know what to put on the form, onsubmit-something or onclick-something as the query is used and not the form.
Thanks.


this is what I got so far

 <?php
        require_once("xajax/xajax_core/xajax.inc.php"); 
 $objResponse= new xajaxResponse();
$responseHtmlString="";

foreach($queryResults as $resultLine){

$responseHtmlString.=
" id is: ".$resultLine->availability." content of the table line ".$resultLine->half_round;
}

$objResponse->assign('message','innerHTML',$responseHtmlString);
        include("conex/conexion.php");
if ($_POST['enviar']) 
Here my queries{

Re: mysql php result with ajax (xajax)

I managed to take away the error putting the php before the doctype,
however it does not work at all, so something is wrong.
I added some things I found reading tutorials, but its not correct:

<?php
require_once("xajax/xajax_core/xajax.inc.php"); 
$xajax = new xajax();
$xajax->register(XAJAX_FUNCTION, 'enviar');
$xajax->configure('debug',true);
$xajax->processRequest();


$xajax->configure('javascript URI','xajax/');
        function enviar($post)
 {
 $objResponse= new xajaxResponse();
$responseHtmlString="";

foreach($queryResults as $resultLine){

$responseHtmlString.=
" id is: ".$resultLine->availability." content of the table line ".$resultLine->half_round;
}

$objResponse->assign('message','innerHTML',$responseHtmlString);
}

        include("conex/conexion.php");
if ($_POST['enviar']) { 

Re: mysql php result with ajax (xajax)

Hello, i know programming is hard work ;-)

hav a look in this different types of query results

i think you Problem is the type or typecasting of your DB results

for an string you can not use the foreach or an for loop


<?php /**
 * @author  romacron
 * @copyright 
 */

/**
 * method for an query result array
 * enviar()
 * 
 * @param mixed $post
 * @return void
 */
function enviar( $post )
{
    
    /*first validate Post something like this(is not in xajax Framework )*/
    $securedPostVars=  anSecureingClass::securePost($post);
    
    /*make your queries in database*/
    $queryResults=array()
    $queryResults= DB::getMyDataFromTable($securedPostVars['someID']);
    
    $objResponse = new xajaxResponse();

    $responseHtmlString = "";
    
    if(!is_array($queryResults)){
        $objResponse->alert('use the other method ');
        return $objResponse;
    }
    foreach ( $queryResults as $resultLine )
    {

        $responseHtmlString .= " id is: " . $resultLine->availability . " content of the table line " . $resultLine->half_round;

    }
    $objResponse->assign( 'message', 'innerHTML', $responseHtmlString );
  return $objResponse;
}

/**
 * method for an single query result array
 * enviar()
 * 
 * @param mixed $post
 * @return void
 */
function enviar( $post )
{
    
    /*first validate Post something like this(is not in xajax Framework )*/
    $securedPostVars=  anSecureingClass::securePost($post);
    
    /*make your queries in database*/
    $queryLine=array()
    $queryLine= DB::getMyDataFromTable($securedPostVars['specialID']);
    
    $objResponse = new xajaxResponse();

    $responseHtmlString = "";

    $responseHtmlString .= " id is: " . $queryLine->availability . " content of the table line " . $queryLine->half_round;


    $objResponse->assign( 'message', 'innerHTML', $responseHtmlString );
    return $objResponse;
}  ?>

does it helps a little bit?

6 (edited by helenp 2011-11-29 5:53:07 PM)

Re: mysql php result with ajax (xajax)

Thanks very much,
do you mean I should use both functions or only one?
As they have the same name I get this error:
Fatal error: Cannot redeclare enviar() (previously declared in /home/javiercd/public_html/calculator7.php:26) in /home/javiercd/public_html/calculator7.php on line 127

Also in this: $queryResults= DB::getMyDataFromTable($securedPostVars['someID']); in someID what should I put, the name of a table (I have more than one table) or an id in the html?

Also in the form I have this;  onclick="xajax_enviar(xajax.getFormValues('formId'));" dont know if this is correct or not.

Thousands of thanks

Re: mysql php result with ajax (xajax)

have a look in this structure
you must fill your code to the correct places
it demonstrates only the structur

<?php /*other code you need*/

include_once ( "conex/conexion.php" );

function enviar( $formVars )
{
    $objResponse = new xajaxResponse();

    $resultsArray = calculateSomething( $formVars );
    
    
    $lineNumber = 0; //counter
    foreach ( $resultsArray as $resultLine )
    {
        $theMessage = makeMeHtml( $lineNumber++, $resultLine );
        /*push the message into xajax response STack*/
        $objResponse->append( 'messages', 'innerHTML', $theMessage );
    }
    return $objResponse;
}
/**
 * small helper i dont like html-tags in my php
 * makeMeHtml()
 * 
 * @return void
 */
function makeMeHtml( $lineNumber, $message )
{
    $line = "";
    $line .= '<div>message number:<strong>' . $lineNumber . '</strong></div>';
    $line . = '<div>the Message is:' . $message . '</div>';
    $line .= "<hr><br />";
    return $line;
}
/**
 * put inside your DB Code
 * do not use echo!!!!
 * calculateSomething()
 * 
 * @param mixed $post
 * @return array
 */
function calculateSomething( $post )
{
    $returnMessages = array();
    /*your code here*/

    /*
    * please dont echo it!!!!!
    * echo "is occupied"
    */
    $returnMessages[] = "is occupied";
    //ccode
    $returnMessages[] = " some other resulte";

    /*return all messages from the Query to the output function*/
    return $returnMessages;
} ?>


<?php /*other stuff to include xajax must me before*/
$xajax->register( XAJAX_FUNCTION, 'enviar' ); ?>
<html>

<form id="we-must-take-the-form-id">
//other formstuff
<a onclick="xajax_enviar(xajax.getFormValues('we-must-take-the-form-id'));"></a>
</form>
<div id="messages">the xajax responses goes here</div>

</html>

..hope this helps

Use the Xajax Tests folder there are enough good examples to getting startet