Using Ajax GET Value in another form

[MartynLearnsPHP]

I am currently trying to write a page for people to log on and allocate guests to seats for their event. I am working on 3 files. The main page whereby they select the table that they want to allocate guests to, a js file and a php file which takes the selected table and allows the information to be managed.

 

My main select a table page is set up so that the user only views the tables that they have chosen to use for their event and shows the name or number that they have allocated to that table.

<form action="" method="post">
  <label>Select Table</label>
  <select name="table guest" onChange="showUser(this.value)">
  <option value="">-Please Select-</option>
  <?php
	$selectguest = DB::getInstance()->query("SELECT * FROM guesttables WHERE adminid='{$adminid}' && admin='{$admin}'");
	foreach ($selectguest->results() as $selectguestresults)
	if ($selectguestresults->position1=="1")
	{echo "<option value ='position1'>" . $selectguestresults->name1 . "</option>";}
	if ($selectguestresults->position2=="1")
	{echo "<option value ='position2'>" . $selectguestresults->name2 . "</option>";}
	if ($selectguestresults->position3=="1")
	{echo "<option value ='position3'>" . $selectguestresults->name3 . "</option>";}
	if ($selectguestresults->position4=="1")
	{echo "<option value ='position4'>" . $selectguestresults->name4 . "</option>";}
	if ($selectguestresults->position5=="1")
	{echo "<option value ='position5'>" . $selectguestresults->name5 . "</option>";}
	if ($selectguestresults->position6=="1")
	{echo "<option value ='position6'>" . $selectguestresults->name6 . "</option>";}
	if ($selectguestresults->position7=="1")
	{echo "<option value ='position7'>" . $selectguestresults->name7 . "</option>";}
	if ($selectguestresults->position8=="1")
	{echo "<option value ='position8'>" . $selectguestresults->name8 . "</option>";}
	if ($selectguestresults->position9=="1")
	{echo "<option value ='position9'>" . $selectguestresults->name9 . "</option>";}
	if ($selectguestresults->position10=="1")
	{echo "<option value ='position10'>" . $selectguestresults->name10 . "</option>";}
	if ($selectguestresults->position11=="1")
	{echo "<option value ='position11'>" . $selectguestresults->name11 . "</option>";}
	if ($selectguestresults->position12=="1")
	{echo "<option value ='position12'>" . $selectguestresults->name12 . "</option>";}
  ?>
  </SELECT>	
</form>
<script src="js/allocatetables.js"></script>
<div id="txtALLOCATE"></div>

My js file is:

function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtALLOCATE").innerHTML="";
  return;
  } 
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)
    {
    document.getElementById("txtALLOCATE").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","allocatetables.php?q="+str,true);
xmlhttp.send();
}

And my referenced php file is:

<?php
require 'core/memberinit.php';
$member = new Member();
$admin = $member->data() ->admin_username;
$adminid = $member->data() ->adminid;

if(isset($_GET['q'])) {
	$q = html_entity_decode($_GET['q']);
	
	$seatnumbers = DB::getInstance()->query("SELECT * FROM guesttables WHERE adminid = '{$adminid}'");
	foreach ($seatnumbers->results()as $seatresults) {
	if ($q="position1") {$seats=$seatresults->seat1;
	} else {
	if ($q="position2") {$seats=$seatresults->seat2;
	} else {
	if ($q="position3") {$seats=$seatresults->seat3;
	} else {
	if ($q="position4") {$seats=$seatresults->seat4;
	} else {
	if ($q="position5") {$seats=$seatresults->seat5;
	} else {
	if ($q="position6") {$seats=$seatresults->seat6;
	} else {
	if ($q="position7") {$seats=$seatresults->seat7;
	} else {
	if ($q="position8") {$seats=$seatresults->seat8;
	} else {
	if ($q="position9") {$seats=$seatresults->seat9;
	} else {
	if ($q="position10") $seats=$seatresults->seat10;
	} else {
	if ($q="position11") {$seats=$seatresults->seat11;
	} else {
	if ($q="position12") {$seats=$seatresults->seat12;
	}}}}}}}}}}}}}

	$seatedguests = DB::getInstance()->query("SELECT * FROM guests WHERE adminid='{$adminid}' && table='$q'");
	foreach ($seatedguests->results() as $seatedguestsresults) {
	echo "Guest: " . $seatedguestsresults->firstname . " " . $seatedguestsresults->lastname . " (Seat Number: " . $seatedguestsresults->seat . ")<br>";
	}
	echo $q;
	echo "<br>";
	echo $seats;
}
	

Obviously I have a lot more to write on my last page, but I am coming unstuck at the first hurdle. My echoed out $q is showing the correct reference (ie. position1, position2, etc) and $seats is showing the correct number of seats allocated to a table. However, my request to view guests seated at the table ($seatedguests) isn't recognising $q in the search option. 

 

I am sure this is a basic flaw in my knowledge, but I've spent nearly 2 weeks trying to get past this and I am at my wits end, so any help, prompting or suggestions on how I should be using $q to select the appropriate table would be very, very gratefully appreciated.

[Ch0cu3r]

You have a logic flaw in allocatetables.php

	foreach ($seatnumbers->results()as $seatresults) {
	if ($q="position1") {$seats=$seatresults->seat1;
	} else {
	if ($q="position2") {$seats=$seatresults->seat2;
	} else {
	if ($q="position3") {$seats=$seatresults->seat3;
	} else {
	if ($q="position4") {$seats=$seatresults->seat4;
	} else {
	if ($q="position5") {$seats=$seatresults->seat5;
	} else {
	if ($q="position6") {$seats=$seatresults->seat6;
	} else {
	if ($q="position7") {$seats=$seatresults->seat7;
	} else {
	if ($q="position8") {$seats=$seatresults->seat8;
	} else {
	if ($q="position9") {$seats=$seatresults->seat9;
	} else {
	if ($q="position10") $seats=$seatresults->seat10;
	} else {
	if ($q="position11") {$seats=$seatresults->seat11;
	} else {
	if ($q="position12") {$seats=$seatresults->seat12;
	}}}}}}}}}}}}}

Your are using the wrong operator for checking $q, you should be using the comparison operator == not the assignment operator =

 

This will result in $q being always set to position12 (overriding the value the user had submitted). So when your code goes to query the guests table it will always be looking for table position12

[Barand]

As soon as you see something like "position1, position2, position3,...,positionN" or "seat1, seat2,..., seatN" then that is a clue that something is wrong. You should be using arrays. That mass of if() statements could then be reduced to something like

foreach ($seatnumbers->results()as $seatresults) {
    $seats=$seatresults->seat[$q];
}

Edited November 26, 2015 by Barand

[MartynLearnsPHP]

Thanks for the input Ch0cu3r and Barand. You've unearthed a problem that I hadn't yet encountered with the '==' issue. And I can see the benefit of using an array and that is something that I will definitely come back to and address, although I will need to go back and amend my database first because I have tried to simplify my code above by reducing it so that it is not unnecessrily long. Not all tables are called position1, position2, position3, etc (ie. there are 4 top table options and some side tables which I will need to rename for database purposes before I can use the array).

 

EDIT: It occurred to me that I could implement Barand's suggestion as top tables are just positiontop1, positiontop2, etc and so the option codes could be top1, top2, etc so it would still apply. However after implementing that it is no longer working and giving me the number of seats allocated to a table.

 

However, my primary issue at the moment is using $q to select from my guests database. That code just isn't recognising the  table='$q' in:

$seatedguests = DB::getInstance()->query("SELECT * FROM guests WHERE adminid='{$adminid}' && table='$q'");

Edited November 26, 2015 by MartynLearnsPHP

[mac_gyver]

your database design is storing same type of data, that only differs in its value, spread out in columns, which makes any code to use that data overly complicated. you are looping over all the row(s) the first query matches (is there even more than one row per adminid?), to get a value out of the correct column, just to use it in the second query to find the actual data.

 

if your database is designed correctly, ALL that code would go-a-way and be replaced with a single JOINed query.

 

i reviewed your two most recent threads. it turns out i provided help in both of them. this problem of writing out huge quantities of repetitive code, was in the way, before, of getting your code to do what you want, and it is still getting in the way, now, of producing code that works or debugging why the code isn't working.

 

i would start by getting your database table(s) designed correctly. this will make writing the code and queries easy.

[MartynLearnsPHP]

There is only ever one row per adminid. I think my database is fairly simple in it's design. In the guests database which I am trying to select from the columns are simply:

id

firstname

lastname

admin

adminid

table

seat

So I am just trying to select from the table where the adminid = that one row and the table = the selected table. 

 

My previous query was on a much more complicated private messaging system which is why that got over complicated - but as far as I can see this one should be quite straight forward - if only I could understand why $q isn't being recognised as a search option.

[MartynLearnsPHP]

OK. I'm just trying to break this all down so that I can rebuild from the bottom up. But one quick question.

 

What reason could there be for...

$seatnumbers = DB::getInstance()->query("SELECT * FROM guesttables WHERE adminid = '{$adminid}'");
	foreach ($seatnumbers->results()as $seatresults) {
	if ($q=="positiontop1") {
		$seats=$seatresults->seattop1;
	} else {
	if ($q=="positiontop2") {
		$seats=$seatresults->seattop2;
	} else {
	if ($q=="positiontop3") {
		$seats=$seatresults->seattop3;
	} else {
	if ($q=="positiontop4") {
		$seats=$seatresults->seattop4;
	} else {
	if ($q=="position1") {
		$seats=$seatresults->seat1;
	} else {
	if ($q=="position2") {
		$seats=$seatresults->seat2;
	} else {
	if ($q=="position3") {
		$seats=$seatresults->seat3;
	} else {
	if ($q=="position4") {
		$seats=$seatresults->seat4;
	} else {
	if ($q=="position5") {
		$seats=$seatresults->seat5;
	} else {
	if ($q=="position6") {
		$seats=$seatresults->seat6;
	} else {
	if ($q=="position7") {
		$seats=$seatresults->seat7;
	} else {
	if ($q=="position8") {
		$seats=$seatresults->seat8;
	} else {
	if ($q=="position9") {
		$seats=$seatresults->seat9;
	} else {
	if ($q=="position10") {
		$seats=$seatresults->seat10;
	}}}}}}}}}}}}}}}}}

outputing an answer whereas

$seatnumbers = DB::getInstance()->query("SELECT * FROM guesttables WHERE adminid = '{$adminid}'");
	foreach ($seatnumbers->results()as $seatresults) {
		$seats=$seatresults->seat[$q];
	}

does not?

[Barand]

I did not say that would work. Read my reply again.

 

I said "if you restructure your data and use arrays then you could reduce it  to something like that". It was an example of what might be possible, not code for you to blindly copy/paste into your existing code.

[MartynLearnsPHP]

Sorry. I misunderstood your post. I thought you were suggesting an alternative line.

 

I'll come back to that later then and first try to work out why $q isn't being recognised in my select query.

[Ch0cu3r]

I'll come back to that later then and first try to work out why $q isn't being recognised in my select query.

$q is most likely set to a value that is not in your table? Why not echo the query to see what value $q contains and maybe dump the query results to see if they are what you are expecting them to be

$sql = "SELECT * FROM guests WHERE adminid='{$adminid}' && table='$q'";
$seatedguests = DB::getInstance()->query($sql);

echo "<pre>Query: $sql";

// dump query results
echo "\nQuery Results: " . print_r($seatnumbers->results(), true) . "</pre>";

[MartynLearnsPHP]

Thanks, Ch0cu3r. That's opened my eyes. When I was previously echoing $q it was showing the table position, which is what I wanted, but but dumping the results it is showing results from my table database rather than my guests database so I can see that the problem is coming from the AJAX GET transfer of data which means that I now know where to start looking to try and resolve my issue.

 

An analogy that I once heard and quite liked. "I now have a torch and know where to shine it rather than trying to fumble around in the dark."