[SOLVED] Can someone point me in the right direction(s) please??

[Mardoxx]

Here's what I would like to happen....

 

 

I have a table stored in MySQL:

Table: Data

ID (unique)

Name

Data

1

Adam

Data1

2

Bill

Data2

3

Charlie

Data3

...

...

...

26

Zoe

Data26

 

 

[*]An HTML form gets generated with a drop-down menu with all the NAMES in it and a button saying ADD/REMOVE next to it.

[For now though, we can just pretend it says ADD]

[*]When the button is pressed, the associated ID with the selected NAME from the table gets passed to an array, say, $id_array (which contains just the ID, nothing else).

[*]AJAX/Javascript is then called and gets the IDs from the array $id_array, connects to the database then displays a table (echo "<pre>". print_r($id_array) . "</pre>"; will do for now) showing the corresponding NAME - DATA

[*]The drop-down menu will now be re-drawn but WITHOUT the already-chosen names [i though of this and it can be done by a foreach loop on the array [b]$id_array[/b]

foreach ($id_array as $id) { $query .= AND ID != $id }

• Once the drop-down list has been re-drawn I can then select ANOTHER name from the list and click ADD
  
• This will then add the corresponding ID to the array $id_array
  
• AJAX/Javascript is then called again to display the (now TWO) data in a table by looking at the numbers in $id_array
  
• The drop-down menu is then re-drawn again WITHOUT the (now two) already chosen names
  
• The process can then be continued until no data is left unselected.
  

 

Now, this is (what I think is) a large task and may be quite a lot to ask.

What I don't know how to do is the AJAX bit, the PHP seems quite easy.

Would anyone be able to help me with it/push me in the right directions?

 

I would be VERY grateful if someone could!

 

Thanks very much in advance!!!

 

 

 

EDITED BY akitchin: done .

[xtopolis]

Turn Right ---->

 

 

 

But on a serious note, what you want to do can be done easily.  The question I have is about the "selected" table.  How will the table be stored?  Is it temporary, or more permanent?.. that is up to you, but won't be much of an issue anyway.

 

Anyway.

-The person clicks the add button, it  can be added to a javascript array.

-Here you can either way for them to push a button to get the data, or have it update as they click add.  The query will do you something like "SELECT name, data FROM yourTable WHERE ID IN(1,2)" [where 1 and 2 are stored IDs that have been added.

-On that click "ADD" event, you would have javascript remove the selections from the "ADD" table so that they can't be clicked again

-While the name,data table is updating, you can either reload the page ( modifying the add table query by making use of $_SESSION, or just use AJAX to redraw the table by doing the same select as above, but modifying it to be ..."WHERE ID NOT IN(1,2)" [again referring the selected IDs]

 

 

Aside from that, you'll have test it yourself in small steps.  If you can get it to work in PHP first, I'd do that.  It's easy to modify it into javascript later if you already have a working PHP example.

[Mardoxx]

The table is permanent and not to be changed by the script.

The reason I want it to keep referring back to the database is because I am going to incorporate a selective-search box instead of the drop down (I've already got an example of that working though)

 

Once the ADD button is pressed the following could happen:

 

If the IDs are in an array (in javascript) I could then post it (using AJAX) to,say, selected.php?id_array=js_id_array

that script could then select the data from the database using the posted IDs and then

SELECT NAME, DATA from Data WHERE ID IN(1,2)  BLAH BLAH BLAH

(1 & 2 were in the array)

it could then return the html of the table with the NAME and DATA ready to be "AJAX`d" into a container somewhere on the page (I don't know how to do this, yet)

 

The drop-down get's redrawn by the same method, posting to ANOTHER script and returning the NEW table.

 

 

 

that would work, would it not?

[xtopolis]

Whatever you're trying to do can work,  but I'm just not sure "what" you're doing.  When I asked about the table thing, I was referring to the derived table from your main table.

 

ex:

(table from database)

Name1 - Add

Name2 - Add

Name3 - Add

 

(click Name1 - Add, do your stuff)

(database table)

Name2 - Add

Name3 - Add

 

(derived table)

Name1 - Add

 

--------

This new table ,"derived" table.. would this be permanent, or kept only for refining your search query?

 

-There are MANY Ajax tutorials which show you how to get a feel for Ajax and learn the basics.  In essence, it's only getting the Javascript to behave and using the correct methods (POST, GET, and return plaintext or XML) that are the difficult parts.

 

So let's start from the top.  What do you have?  Where (specifically, one problem at a time) are you stuck?

 

[Also, referring back the database seems unnecessary IF you pull all records at the beginning.  You could do this all in Javascript for the "refining" portion, and only use Ajax to execute the actual search based on the 'derived' IDs in your new search table.]

 

[Mardoxx]

sorry If i'm not very clear, I'm really bad with explaining things

 

1 - (table1 :- data retrieved from database selected by sql query

SELECT Id, Name FROM names)

1 - Name1 - Add

2 - Name2 - Add

3 - Name3 - Add

$id_array = array();

*NOTE1

 

2 - [Add is clicked]

 

3 - The corresponding ID of the selected (added) Name gets appended to the array:

$id_array = array(2);

 

4 - (table2 :- data retrieved from database selected by NEW sql query

SELECT ID, Name FROM names WHERE ID != $id_array

and REPLACES table1)

1 - Name1 - Add

3 - Name3 - Add

 

5 - (dtable1 :- derived table)

(data retrieved from database selected by DIFFERENT sql query

SELECT Name, Data FROM names WHERE ID == $id_array

showing all data BUT the ones with IDs that are in the array $id_array)

Name2 - Data2

 

---------------------------

If at this point enough data is selected you can then stop and ignore the rest

If more data needs to be selected, carry on

---------------------------

 

6 - [Add is clicked in table2]

 

7 - The corresponding ID to the newly selected data is added to the id_array

$id_array = array(2,1);

 

8 - (table3 :- data retrieved from database selected by NEW sql query

SELECT ID, Name FROM names WHERE ID != $id_array

and REPLACES table2)

3 - Name3 - Add

 

9 - (dtable2 :- derived table)

(data retrieved from database selected by DIFFERENT sql query

SELECT Name, Data FROM names WHERE ID == $id_array

showing all data BUT the ones with IDs that are in the array $id_array

This REPLACES dtable1)

Name2 - Data2

Name1 - Data1

 

---------------------------

The process can continue until all data is selected, or until no more data is needed

---------------------------

 

 

I hope this makes more sense!!!

 

I'll have a go at making a pure PHP version with no dynamic content and see how it goes...

 

thanks for your help so far

 

 

*NOTE1:

This can be echoed like

<?php
echo "<select>\n";
while ($row = mysql_fetch_row($result)) {
     echo "<option value='$row[iD]'>$row[Name]</option>\n";
}
echo "</select>\n";
?>

 

[Mardoxx]

I've created ONE php script to get the data for the two tables:

 

getdata.php

<?php
$debug = true;
$dbhost = "localhost";
$dbusername = "names";
$dbpassword = "password";
$maindb = "data";

include('includes.php');  //contains database connect stuff
databaseConnect($dbhost, $dbusername, $dbpassword, $maindb);

$id_list = $_GET['id_list'];
$table = $_GET['table'];


switch ($table) {
case "main":
	//Data that will be shown in the main table
	$query = "SELECT id, name FROM names";
	$not = "NOT";
       		break;
case "selected":
	//Data that will be shown in the selected table:<br />";
	$query = "SELECT name, data FROM names";
	$not = "NOT";
       		break;
default:
	$error = true; //quick and easy way to stop people messing around
}



if (isset($id_list) && $id_list != '' && !$error) {

$id_array = explode(",", $id_list);	

foreach ($id_array as $id) {

	if (!isInteger2($id)) { 
		//we know that IDs are ONLY going to be integers if they're not numeric we know that someone is trying to pull a fast one!
		$error = true;
		break; //breaks out of the foreach loop
	}

}

if(!$error) {
	//quick and easy way to stop people messing around
	$query .= " WHERE id $not IN ($id_list)";
}

}

if(!$error) {
//quick and easy way to stop people messing around
$result = mysql_query($query) or die(mysql_error());

include ('debug_functions.php');
echo dump_sql_table($query,$result);
} else {
echo "Nothing can be returned, there was a mofukken error<br />";
}

function isInteger2($int){
    return ((string) $int) === ((string)(int) $int); 
/* this only returns true if $int is an integer or string
integer; is_int only works on integers which won't work
seeing as the array is strung, is_numeric works on negatives
which we don't want ctype_digit works with leading zeros
which we don't want. etc etc... */
}


?>

 

 

so if I browse to

getdata.php?id_list=1,3&table=main

 

it will output

ID

Name

2

Bill

4

David

...

...

 

 

where as if I browse to

getdata.php?id_list=1,3&table=selected

 

it will output

Name

Data

Adam

13432455t3

Charlie

14jrwejre8

 

 

Now, where do I go from here? 

[xtopolis]

You kinda confused me there with that last post.  I have made a "simple" example.. it's not very clean, but it's to get my point across, and has most of the elements you need I think.

 

It looks kind of long, but it's not really.  My database is setup as name_id(int), name_person(varchar255), name_data(varchar255).  The page queries back to itself for the ajax (isset($_GET['refine']))...

 

There is a function in there called "getSelected()" which gets the id values from the listbox.  You could modify that part of it to remove / change / subtract from one box and add to another, etc depending on what you want to do.

 

Later you see my simple AJAX (get) function.  I pass a url string of CSV values, and get them later.  I didn't bother with any validity checking for the example, but you could handle that on your own.

 

Anyway, hopefully you can dissect this enough to get the functionality you need.  You should be able to copy paste this and change your database info to match , as long as the column order matches to see it work.  Hope this helps a bit.

 

<?php
$mysqli = new mysqli(databaseinfo);
//main query
    $sql = "SELECT * FROM names WHERE 1 LIMIT 50";
    $result = $mysqli->query($sql);
    
    $select = '';
    while(list($id, $name) = mysqli_fetch_array($result)) {
    
        $select .= "<option value='$id'>$name</option>\n";
    
    }

//sub query
if(isset($_GET['refine'])){
    
    $ids = substr($_GET['refine'], 0, strlen($_GET['refine']) - 1);
    $sub_sql = "SELECT * FROM names WHERE name_id IN($ids)";

    $result = $mysqli->query($sub_sql);
    
    while(list($name,$data) = mysqli_fetch_array($result)) {
    
        print "$name - $data<br />";
    
    }
    exit;
}
?>
<html>
<head>
<script type="text/javascript">
function loopSelected()
{
    var txtSelectedValuesObj = document.getElementById('valuesOut');
    var selectedArray = new Array();
    var selectedString = '';
    var selObj = document.getElementById('main');
    var i;
    var count = 0;
    for (i=0; i<selObj.options.length; i++) {
        if (selObj.options[i].selected) {
          selectedArray[count] = selObj.options[i].innerHTML;
          selectedString += selObj.options[i].value + ',';
          count++;
        }
    }
    txtSelectedValuesObj.innerHTML = selectedArray;
    
    getData(selectedString);
}

//ajax element
function newXHRO()
{
  try { return new XMLHttpRequest(); } catch(e) {}
  try { return new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) {}
  try { return new ActiveXObject("Microsoft.XMLHTTP"); } catch (e) {}
  alert("XMLHttpRequest not supported");
  return null;
}

//ajax query
function getData(values)
{

  var XHRO = new newXHRO();
  

  var url = 'test1.php';
    url += '?refine=' + values;
  var x = document.getElementById('dataOut');
    x.innerHTML = '<img src="http://www.xtopolis.com/imgs/loading.gif" alt="Loading..." />';


  XHRO.open('GET', url, true);


  XHRO.onreadystatechange=function()
  {
    if(XHRO.readyState==4 && XHRO.status==200)
    {
      x.innerHTML = XHRO.responseText;
    }
  }
  
  XHRO.send(null);

  return false;
}

</script>
</head>
<body>

    <select id="main" multiple="multiple" size="10">
        <?php echo $select; ?>
    </select>
    <input type="button" value="Get Selected" onclick="loopSelected();" />

    <p id="valuesOut"></p>
    
    <p id="dataOut"></p>
</body>
</html>

[xtopolis]

edit: the sub query should probably be:

    $sub_sql = "SELECT name_person, name_data FROM names WHERE name_id IN($ids)";

 

instead.. but it doesn't really matter for the example.

[Mardoxx]

thanks man

 

but...

 

WHERE name_id IN(1,2)

 

gives me the same as

 

WHERE name_id IN(2,1)

 

because I want the list to be given in the order selected with the first selected one at the bottom

 

 

//edit

also lol

 

xtopolis

 

    * Devotee

    *

    * Offline Offline

    * Gender: Male

    * Posts: 1,337

[Mardoxx]

found it

 

ORDER BY FIND_IN_SET(id, '$ids')