Using PHP to query db, fetch results using AJAX as a variable, query db again using initial variable values

Question

Using PHP to query db, fetch results using AJAX as a variable, query db again using initial variable values

All really :)

This is clearer when I put it in bullet points, what I want to do, so here it is:

I have two forms on the page, a search form and a profile edit form. Both are in working condition, individually.
The edit profile form is split into pages in my MySQL tbl, allowing me to edit the values for each column. Currently, the inclusion of all rows (i.e., all saved profiles) is configured.
the search form accepts any of 17 different search variables and searches for the same table to search for a profile or group of profiles.
I want to be able to enter a search query (for example, name: "Bob"), request tbl, as I do, but use AJAX to return a unique identifier for the results as an array stored in a variable. Then I want to be able to transfer this variable asynchronously to my profile profile form request (the search form will have a submit button ...) so that I can now view all the rows in my table (for example, where the name contains “Bob”) and only those lines.

Is this possible with the corresponding languages? Can someone help me put them together?

I am in the middle of PHP and MySQL, but I am completely new to AJAX. I only ever used it to display a text string in a certain area - as can be seen from the demos everywhere :) Therefore, considering me as a five-year-old is very much appreciated!

, :

:

//pagination base
if (isset($_GET['page'])) { $page = $_GET['page']; }
else { $page = 1; }
$max_results = 1;
$from = (($page * $max_results) - $max_results);

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM profiles"),0);
$total_pages = ceil($total_results / $max_results);

echo '<span id="pagination">' . 'Record ' . $page . ' of ' . $total_results . ' Records Returned. ';

if($total_results > $max_results)
{
    if($page > 1)
    { $prev = ($page - 1);
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><input type='submit' value='&lt;&lt;' /></a>";
    }
    if($page == 1)
    { echo "<input type='submit' value='&lt;&lt;' />"; }
    if($page < $total_pages)
    { $next = ($page + 1);
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\"><input type='submit' value='&gt;&gt;' /></a>";
    }
    if($page == $total_pages)
    { echo "<input type='submit' value='&gt;&gt;' />";
    }
}
echo '</span></p>';
?>

// the query, currently selecting all but which I would have include a WHERE clause (WHERE ProfileID = $profileid...)

<?php
$sql = ("SELECT * FROM profiles 
    ORDER BY ProfileID 
    LIMIT $from, $max_results");

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{

$profile = $row['ProfileID'];

?>

<form id="profile-form" action="profile-engine.php" method="post">
    <input type="hidden" name="formid" value="edit-profile">
    <input type="hidden" name="thisprofile" value="<?php echo $profile; ?>">

    <table id="profile-detail" class="profile-form">
    <tr>
        <td>        
    <label for="profile-name">Name:</label>
        <?php
            $nameresult = mysql_query("SELECT ProfileName 
                            FROM profiles 
                            WHERE ProfileID = '$profile'");
            $row = mysql_fetch_array($nameresult);
        ?>
    <input type="text" class="text" name="profile-name" id="profile-name" value="<?php echo $row['ProfileName']; ?>" />
        </td>

//goes on in this vein for another 16 inputs :)

:

//connection established        

$query = "SELECT * FROM profiles"; 

$postParameters = array("name","height","gender","class","death","appro","born","tobiano","modifier","adult","birth","sire","dam","breeder","owner","breed","location");
$whereClause = " WHERE 1 = 1";
foreach ($postParameters as $param) {
    if (isset($_POST[$param]) && !empty($_POST[$param])) {
        switch ($param) {
            case "name":
                $whereClause .= " AND ProfileName LIKE '%".$_POST[$param]."%' ";
                break;
            case "height":
                $whereClause .= " AND ProfileHeight='".$_POST[$param]."' ";
                break;
            case "gender":
                $whereClause .= " AND ProfileGenderID='".$_POST[$param]."' ";
                break;
//more cases....
        }
    }
}
$query .= $whereClause;

$result = mysql_query("$query");

$values = array();
while ($row = mysql_fetch_array($result)) {
$values[] =  $row['ProfileID'];
    }
/*  
//just me checking that it worked...
foreach( $values as $value => $id){
    echo "$id <br />";
}
*/


    mysql_close($con);

, ! !

+3

ajax php mysql

Eamonn 15 '11 23:20

1

Robot Woods · Accepted Answer · 2011-05-17T20:22:38+0000

: , , , JS . , , , , ...

( , , ):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript">
var query = new Object();
var resp;
var i;
function next(on){
    i=on+1;
    if(i==resp.results.length){i--;}
    fillForm(i);
}
function prior(on){
    i=on-1;
    if(i<0){i++;}
    fillForm(i);
}

function fillForm(i){   
    document.getElementById("paginate").innerHTML='<button onclick="prior('+i+')">&lt;</button>'+(i+1)+' of '+resp.results.length+'<button onclick="next('+i+')">&gt;</button>';
    document.getElementById("name").value=resp.results[i].name;
    document.getElementById("height").value=resp.results[i].height;
    document.getElementById("gender").value=resp.results[i].gender;
    document.getElementById("class").value=resp.results[i].class;
    document.getElementById("death").value=resp.results[i].death;
    document.getElementById("appro").value=resp.results[i].appro;
    document.getElementById("born").value=resp.results[i].born;
    document.getElementById("tobiano").value=resp.results[i].tobiano;
    document.getElementById("modifier").value=resp.results[i].modifier;
    document.getElementById("adult").value=resp.results[i].adult;
    document.getElementById("birth").value=resp.results[i].birth;
    document.getElementById("sire").value=resp.results[i].sire;
    document.getElementById("dam").value=resp.results[i].dam;
    document.getElementById("breeder").value=resp.results[i].breeder;
    document.getElementById("owner").value=resp.results[i].owner;
    document.getElementById("breed").value=resp.results[i].breed;
    document.getElementById("location").value=resp.results[i].location;
    document.getElementById("id").value=resp.results[i].id;
    document.getElementById("saveButton").innerHTML='<button onclick="save()">Save</button>';
}
function getData(){
    query.name=document.getElementById('query_name').value;
    query.gender=document.getElementById('query_gender').value;
    var variables='';
    variables+='name='+query.name;
    variables+='&gender='+query.gender;
    var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    resp = JSON.parse(xmlhttp.responseText);
    fillForm(0);
    }
  }
xmlhttp.open("GET","searchNav.php?"+variables,true);
xmlhttp.send(); 
}

function save(){
    var saving="";
    saving+='?q='+query;
    saving+='&name='+document.getElementById('name').value;
    saving+='&height='+document.getElementById('height').value;
    saving+='&gender='+document.getElementById('gender').value;
    saving+='&class='+document.getElementById('class').value;
    saving+='&death='+document.getElementById('death').value;
    saving+='&appro='+document.getElementById('appro').value;
    saving+='&born='+document.getElementById('born').value;
    saving+='&tobiano='+document.getElementById('tobiano').value;
    saving+='&modifier='+document.getElementById('modifier').value;
    saving+='&adult='+document.getElementById('adult').value;
    saving+='&birth='+document.getElementById('birth').value;
    saving+='&sire='+document.getElementById('sire').value;
    saving+='&dam='+document.getElementById('dam').value;
    saving+='&owner='+document.getElementById('owner').value;
    saving+='&breed='+document.getElementById('breed').value;
    saving+='&breeder='+document.getElementById('breeder').value;
    saving+='&location='+document.getElementById('location').value;
    saving+='&id='+document.getElementById('id').value;
        var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    resp = JSON.parse(xmlhttp.responseText);
    fillForm(0);
    }
  }
xmlhttp.open("GET","saveEdits.php"+saving,true);
xmlhttp.send(); 
}
</script>
</head>
<body>
<div id="search">
    <table>
    <tr><td>Name:</td><td><input type="text" id="query_name" /></td></tr>
    <tr><td>Gender:</td><td><input type="text" id="query_gender" /></td></tr></table>
    <button onclick="getData()">Search</button>   
</div>
<div id="results">
    <div id="paginate"></div>
    <input type="hidden" id="id" />
    <table>
    <tr><td>Name:</td><td><input type="text" id="name" /></td></tr>
    <tr><td>Height:</td><td><input type="text" id="height" /></td></tr>
    <tr><td>Gender:</td><td><input type="text" id="gender" /></td></tr>
    <tr><td>Class:</td><td><input type="text" id="class" /></td></tr>
    <tr><td>Death:</td><td><input type="text" id="death" /></td></tr>
    <tr><td>Appro:</td><td><input type="text" id="appro" /></td></tr>
    <tr><td>Born:</td><td><input type="text" id="born" /></td></tr>
    <tr><td>Tobiano:</td><td><input type="text" id="tobiano" /></td></tr>
    <tr><td>Modifier:</td><td><input type="text" id="modifier" /></td></tr>
    <tr><td>Adult:</td><td><input type="text" id="adult" /></td></tr>
    <tr><td>Birth:</td><td><input type="text" id="birth" /></td></tr>
    <tr><td>Sire:</td><td><input type="text" id="sire" /></td></tr>
    <tr><td>Dam:</td><td><input type="text" id="dam" /></td></tr>
    <tr><td>Breeder:</td><td><input type="text" id="breeder" /></td></tr>
    <tr><td>Owner:</td><td><input type="text" id="owner" /></td></tr>
    <tr><td>Breed:</td><td><input type="text" id="breed" /></td></tr>
    <tr><td>Location:</td><td><input type="text" id="location" /></td></tr>
    </table>
    <div id="saveButton"></div>
</div>
</body>
</html>

:

<?php
//connection established        
$query = "SELECT * FROM profiles"; 
$postParameters = array("name","height","gender","class","death","appro","born","tobiano","modifier","adult","birth","sire","dam","breeder","owner","breed","location");
$whereClause = " WHERE 1 = 1";
foreach ($postParameters as $param) {
    if (isset($_POST[$param]) && !empty($_POST[$param])) {
       $whereClause .= " AND ".$param."='".$_POST[$param]."'";
    }
}
$query .= $whereClause;
$result = mysql_query("$query");
echo "{\"results\":";
if($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
echo "[";
    echo "{\"name\":\"".$row["name"]."\",";
    echo "\"height\":\"".$row["height"]."\",";
    echo "\"gender\":\"".$row["gender"]."\",";
    echo "\"class\":\"".$row["class"]."\",";
    echo "\"death\":\"".$row["death"]."\",";
    echo "\"appro\":\"".$row["appro"]."\",";
    echo "\"born\":\"".$row["born"]."\"";
    echo "\"tobiano\":\"".$row["tobiano"]."\"";
    echo "\"modifier\":\"".$row["modifier"]."\"";
    echo "\"adult\":\"".$row["adult"]."\"";
    echo "\"birth\":\"".$row["birth"]."\"";
    echo "\"sire\":\"".$row["sire"]."\"";
    echo "\"dam\":\"".$row["dam"]."\"";
    echo "\"breeder\":\"".$row["breeder"]."\"";
    echo "\"owner\":\"".$row["owner"]."\"";
    echo "\"breed\":\"".$row["breed"]."\"";
    echo "\"location\":\"".$row["location"]."\"";
    //echo "\"id\":\"".$row["id"]."\"";
    echo "}";
}
else{
echo "\"no\"}";
exit;
}
while($row = mysql_fetch_array($data,MYSQL_ASSOC)){
    echo ",{\"name\":\"".$row["name"]."\",";
    echo "\"height\":\"".$row["height"]."\",";
    echo "\"gender\":\"".$row["gender"]."\",";
    echo "\"class\":\"".$row["class"]."\",";
    echo "\"death\":\"".$row["death"]."\",";
    echo "\"appro\":\"".$row["appro"]."\",";
    echo "\"born\":\"".$row["born"]."\"";
    echo "\"tobiano\":\"".$row["tobiano"]."\"";
    echo "\"modifier\":\"".$row["modifier"]."\"";
    echo "\"adult\":\"".$row["adult"]."\"";
    echo "\"birth\":\"".$row["birth"]."\"";
    echo "\"sire\":\"".$row["sire"]."\"";
    echo "\"dam\":\"".$row["dam"]."\"";
    echo "\"breeder\":\"".$row["breeder"]."\"";
    echo "\"owner\":\"".$row["owner"]."\"";
    echo "\"breed\":\"".$row["breed"]."\"";
    echo "\"location\":\"".$row["location"]."\"";
    //echo "\"id\":\"".$row["id"]."\"";
    echo "}";
}
echo "]}";
?>

Using PHP to query db, fetch results using AJAX as a variable, query db again using initial variable values

More articles: