MySqli Help

Hi,

We have an arena which we sell trade stand space to traders for the annual highland games. I want to allocate space to traders based on their space requirements

I have two dynamic select boxes on my page. These select boxes are for the Admin to allocate a location(pitch) that the client requires

Select One menu pulls info from two tables - the user table and product table. This then tells the admin what user requires what product!

i.e. User 15 requires product 8 - (product 8 is a pitch with the size of 15x15)

Select Two Menu pulls the info from a table that hold the pitch locations 40 pitches in total ranging in size based on the requirement for a particular year.

Once both fields have been selected this is then saved into a table with the traderID and pitchID ready for printing.

The thing I am struggling with is the SQLi code. In both select options I want the traderID and pitchID removed from the dropdowns once they have been allocated.

This did work in mySlq but I cannot get it to work in MySqli. below is my Sqlii code without the additional table. I can get it to show what pitches have been allocated but not the other way around.

<?php
$rsTraders = new WA_MySQLi_RS("rsTraders",$admin_chg,0);
$rsTraders->setQuery("SELECT tbl_traders.traderID, tbl_traders.trading_name, tbl_products.ProductSKU, tbl_products.ProductDimensions FROM tbl_traders LEFT OUTER JOIN tbl_products ON tbl_traders.pitch_req = tbl_products.ProductID WHERE tbl_traders.approved = 1");
$rsTraders->execute();
?>

this query gives all info

<?php
$rsTraders = new WA_MySQLi_RS("rsTraders",$admin_chg,0);
$rsTraders->setQuery("SELECT tbl_traders.traderID, tbl_traders.trading_name, tbl_products.ProductSKU, tbl_products.ProductDimensions, tbl_pitch_allocation.* FROM tbl_traders LEFT OUTER JOIN tbl_products ON tbl_traders.pitch_req = tbl_products.ProductID LEFT OUTER JOIN tbl_pitch_allocation ON tbl_traders.traderID = tbl_pitch_allocation.traderID WHERE tbl_traders.approved = 1");
$rsTraders->execute();
?>

I want to set it up so that the traderID is removed from the list if it exists in the table...I have been trying this for a few days with not luck.

New fresh eyes needed...I think I'm too close to the issue..

Thanks

Andy

  This did work in mySlq  

what was the recordset to get this t work in MySQL?

the MySQLi recordset will be the same.

Hi Jason,

That's the problem...I did have it working but when I upgraded I inadvertently deleted the recordset because I thought I could be smart and recreate without any problems.

I'm nearly there I just forgot the steps.

As the image attached shows, the last three columns are from the allocated table, with the others on the left coming from the other two tables and space where they have not been allocated yet.

Sorry to be a pain..

Andy

Hi Jason,

I got there eventually

<?php
$rsTraders = new WA_MySQLi_RS("rsTraders",$admin_chg,0);
$rsTraders->setQuery("SELECT tbl_traders.traderID, tbl_products.ProductDimensions, tbl_traders.approved, tbl_traders.trading_name FROM (tbl_traders LEFT JOIN tbl_pitch_allocation ON tbl_traders.traderID = tbl_pitch_allocation.traderID) LEFT JOIN tbl_products ON tbl_traders.pitch_req = tbl_products.ProductID WHERE tbl_pitch_allocation.traderID Is Null and tbl_traders.approved=1;");
$rsTraders->execute();
?>

Thanks

Andy

ok, glad to hear you found it.